Syntax Error on Load

[#1]

I feel silly asking this simple question, but I can't see what I'm doing wrong.  I've looked for commas, semicolons, colons, I think I have them all in the right places.

Excerpt of my script -

ODBC CONNECT TO EM7G2DB;

devicetable:

LOAD

    'G2' AS Version,

    device as DeviceName,

    id as DeviceID,

    ip as DeviceIP,

    `roa_id` roaid,

    active as Status,

    1 AS DeviceCount;

SQL SELECT

    IF(active='0', 'Active',

     IF(active='1', 'Disabled',

      IF(active='2', 'Maintenance',

       IF(active='5', 'Pending', 'unknown')))) AS Status,

    device,

    id,

    ip,

    `roa_id`

FROM master.`legend_device`;

This errors with this error, and yet still loads the data -

Syntax error, missing/misplaced FROM:

devicetable:

LOAD

    'G2' AS Version,

    device as DeviceName,

    id as DeviceID,

    ip as DeviceIP,

    `roa_id` roaid,

    active as Status,

    1 AS DeviceCount

devicetable:

LOAD

    'G2' AS Version,

    device as DeviceName,

    id as DeviceID,

    ip as DeviceIP,

    `roa_id` roaid,

    active as Status,

    1 AS DeviceCount

Thoughts?  Thanks in advance.

[#2 · Accepted solution]

Hi,

I couldn't find the field 'active' in the sql part.
Also you have loaded --  `roa_id` roaid..  I am not sure, if we can load like this. I think it should be 'roa_id` as roaid.

[#3]

Hi,

You may missing an 'AS' keyword at 'roa_id' field.

Regards,

Amay

[jagan]

Hi,

Try this

devicetable:

LOAD

    'G2' AS Version,

    device as DeviceName,

    id as DeviceID,

    ip as DeviceIP,

    `roa_id` as roaid,

    Status,

    1 AS DeviceCount;

SQL SELECT

    IF(active='0', 'Active',

     IF(active='1', 'Disabled',

      IF(active='2', 'Maintenance',

       IF(active='5', 'Pending', 'unknown')))) AS Status,

    device,

    id,

    ip,

    `roa_id`

FROM master.`legend_device`;

The issue is with this two lines

`roa_id` roaid,

active as Status,

Hope this helps you.

Regards,

jagan.

[er_mohit]

Try this

devicetable:

LOAD

    'G2' AS Version,

    device as DeviceName,

    id as DeviceID,

    ip as DeviceIP,

    [`roa_id`] as roaid,

         active as    Status,

    1 AS DeviceCount;

SQL SELECT

    IF(active='0', 'Active',

     IF(active='1', 'Disabled',

      IF(active='2', 'Maintenance',

       IF(active='5', 'Pending', 'unknown')))) AS Status,

    device,

    id,

    ip,

    `roa_id`

FROM master.`legend_device`;

The issue is with this two lines

`roa_id` roaid,

active as Status,

`roa_id` in SQL it takes as field but to alias its name then you should be used [] this bracket

hope it helps

[#6]

Thanks all.  Yes, it was as simple as that, I just needed a new set of eyes looking at it.  Thank you.