load from database where field IN

[didierodayo]

Hello I need some help with this script please.

I have tried this and it is not working. I ma loading from the database.

Provider_Service:

LOAD STUDENT_ID,

NAME,

AGE

WHERE STUDENT_ID = ('00345','00360','00370','00240','00320','00700')

SQL SELECT *

FROM DWH."STUDENTDTLS";

I have also tried and not working aswell.

SET vTOPID= ('00345','00360','00370','00240','00320','00700')

Provider_Service:

LOAD STUDENT_ID,

NAME,

AGE

WHERE STUDENT_ID = $( vTOPID)

SQL SELECT *

FROM DWH."STUDENTDTLS";

What am I doing wrong?

Thanks

Didier

[#2 · Accepted solution]

filter:

load * inline [

STUDENT_ID_key

'00345',

'00360',

'00370',

'00240',

'00320',

'00700'

];

LOAD STUDENT_ID,

NAME,

AGE

WHERE exists (STUDENT_ID_key,STUDENT_ID)

SQL SELECT *

FROM DWH."STUDENTDTLS";

or

LOAD STUDENT_ID,

NAME,

AGE

WHERE STUDENT_ID='00700' or STUDENT_ID='00320' .....

SQL SELECT *

FROM DWH."STUDENTDTLS";

or direcly on sql:

LOAD STUDENT_ID,

NAME,

AGE

SQL SELECT *

FROM DWH."STUDENTDTLS" where STUDENT_ID like "00700" or ....;

[didierodayo]

Hi Rodrigo,

I like the first option, but what if am applying the filter to multiple tables that are not joined, can I do something like this?

filter:

load * inline [

STUDENT_ID_key

'00345',

'00360',

'00370',

'00240',

'00320',

'00700'

];

TableA:

LOAD STUDENT_ID,

NAME,

AGE

WHERE exists (STUDENT_ID_key,STUDENT_ID)

SQL SELECT *

FROM DWH."STUDENTDTLS";

TableB:

LOAD STUDENT_ID,

ADDRESS,

NUMBER

WHERE exists (STUDENT_ID_key,STUDENT_ID)

SQL SELECT *

FROM DWH."STUDENTCONTACT";

[sunny_talwar]

May be like this:

filter:

load * inline [

STUDENT_ID_key

'00345',

'00360',

'00370',

'00240',

'00320',

'00700'

];

TableA:

LOAD STUDENT_ID,

     NAME,

     AGE

WHERE exists (STUDENT_ID_key,STUDENT_ID)

SQL SELECT *

FROM DWH."STUDENTDTLS";

Left Join (TableA)

LOAD STUDENT_ID,

     ADDRESS,

     NUMBER

SQL SELECT *

FROM DWH."STUDENTCONTACT";

[didierodayo]

Thanks Rodrigo and Sunny. Much appreciated.