Load statement/SQL - "Field not found"

[kinjal1645]

Hello,

I need help with below script.

Not sure why it is giving error while loading from Table1 which is coming from database.

When Table1 was loaded from excel it didn't gave any error and was able to read all the fields.

--> script

Table1:

SQL SELECT

          FIELD_A,

          FIELD_B,

          FIELD_C

FROM .... ;

Table2:

LOAD FIELD_A,

Subfield(FIELD_C, '#', 1) as temp1;

SubField(FIELD_C, '#', 2) as temp2;

LOAD FIELD_A,

Subfield(FIELD_C, '$') as temp3;

LOAD FIELD_A, FIELD_C

Resident Table1;

-->error

Field not found - <FIELD_C>

LOAD FIELD_A, FIELD_C

Resident Table1;

Kindly help

Thank you

Kinjal

[sunny_talwar · Accepted solution]

Try this

Table1:

SQL SELECT

          FIELD_A,

          FIELD_B,

          FIELD_C

FROM .... ;

Table2:

LOAD FIELD_A,

Subfield(temp3, '#', 1) as temp1;

SubField(temp3, '#', 2) as temp2;

LOAD FIELD_A,

Subfield(FIELD_C, '$') as temp3;

LOAD FIELD_A, FIELD_C

Resident Table1;

[Colin-Albert]

No need to use a resident load for this it can be done in a single pass.

You had an error because your preceeding load for Table2 did not include FIELD_C as an output to the next stage

This will work without needing a resident load.

Table1:

LOAD          // This loads runs second

     FIELD_A,

     Subfield(temp3, '#', 1) as temp1;

     SubField(temp3, '#', 2) as temp2;

LOAD      // This load runs first

     *,

     Subfield(FIELD_C, '$') as temp3;

SQL SELECT

          FIELD_A,

          FIELD_B,

          FIELD_C

FROM .... ;

Have a look at this blog for more detailsPreceding Load

[kinjal1645]

Thank you