Load a file with sql conditioning to another file

[mauriziob]

Hi, i've to load a file but the records must be conditioning to another load.

Example:

LOAD

  A  as [key1], B  as [CodeFile01_B], C  as [CodeFile01_C];

SQL

   select A, B, C from lib01.File01;

LOAD

  A  as [key1], B  as [CodeFile02_B], C  as [CodeFile02_C], D  as [CodeFile02_D], where exists(A);

SQL

   select AA, BB, CC, DD from libe02.File02;

The record of File02 must be load if this exist in File01.

Can i make this operation?

If i launch the script there isn't any error but the second load doesn't load any record!

Thanks.

[alexandros17 · Accepted solution]

Change

LOAD

  A  as [key1], B  as [CodeFile02_B], C  as [CodeFile02_C], D  as [CodeFile02_D], where exists(A);

SQL

   select AA, BB, CC, DD from libe02.File02;

in

LOAD

  A  as [key1], B  as [CodeFile02_B], C  as [CodeFile02_C], D  as [CodeFile02_D], where exists([key1,A);

SQL

   select AA, BB, CC, DD from libe02.File02;

let me know

[anbu1984]

Since you renamed your field, you should use alias

LOAD

  A  as [key1], B  as [CodeFile02_B], C  as [CodeFile02_C], D  as [CodeFile02_D] where exists([key1]);

SQL

   select AA, BB, CC, DD from libe02.File02;

[mauriziob]

Hi, the alias without the old field name don't function! I followed indication of Alessandro. Thanks to all.

[anbu1984]

[key1] - Refers to field in first load

A - Refers to field in your second load

LOAD

  A  as [key1], B  as [CodeFile02_B], C  as [CodeFile02_C], D  as [CodeFile02_D], where exists([key1],A);

You cannot use alias in where if it is created in same Load (Ex: Load A as B From Resident Temp Where B > 1 -- This won't work). My reply didn't work since [key 1] was not present in where clause. You can try like this

Load * where exists([key1]);

LOAD

  A  as [key1], B  as [CodeFile02_B], C  as [CodeFile02_C], D  as [CodeFile02_D];

SQL

   select AA, BB, CC, DD from libe02.File02;