SQL Logic

[kpsita]

Hi,

I have a simple sql logic which I have to implement in Datastage.
The sql is
Select * from table A
where A.Field1 NOT IN (Select Field1 from Table B)

How do I implement this logic in datastage. I know if it in IN table B then I can use inner join to get result. But if it is NOT IN then how do I get this implemented.I am asking this because in the above case Table1 is a file and table B is a table.

Thanks!!!!

[anbu]

Use Lookup stage and set Lookup Failure to Reject. Lookup reject link gives the records from file which is not there in table

[kris007]

And feed the output link of the LookUp Stage to a Copy Stage or Sequential file stage with path /dev/null

[dougcl]

And feed the output link of the LookUp Stage to a Copy Stage or Sequential file stage with path /dev/null

Does this encourage garbage collection?

[kpsita]

Thanks for your reply. But can I use lookup if the reference table is in millions. Is there any other options other than lookup.

Thanks

[kris007]

Does this encourage garbage collection?

I am not sure I follow you. Output link for the lookup stage is a required link. And since the OP is interested only in the data from the reject link, its a good option to write the data from the output link into /dev/null or a copy stage without an output link as it is not necessary.

[kris007]

Thanks for your reply. But can I use lookup if the reference table is in millions. Is there any other options other than lookup.
Thanks

Depends on how big your table is, how many columns you are bringing in, how large the memory is, and how fast your reference table is growing in terms of records.

[chulett]

Why not simply use your SQL as is?

[kpsita]

I can't use the sql as such because my master source is a file.

Thanks

[anbu]

Did you try the Lookup?

[kris007]

I can't use the sql as such because my master source is a file.Thanks

A solution has already been provided by anbu and myself.