NOT EXISTS clause in Oracle

[bschrist1]

Hi,

I am trying to do the following:

I have a SQL statement that I need to convert into a DataStage job.

The SQL statement is shown below:

SELECT a.field1,
a.field2,
b.field3,
FROM table1 a
,table2 b
WHERE a.field1 = b.field1
AND NOT EXISTS (SELECT 1 FROM table3 c
WHERE c.field1 = a.field1
AND c.field2 = a.field2);

My question is, is there a way to handle the "NOT EXISTS" part in DataStage?? Table 1 is an AS400 table and table 3 is an Oracle table.

Thx for your help

[kduke]

In this case you can build a hash file out of the select in the where clause and do a lookup. You need a 2 part key.

You could also do user defined SQL and use this statement as is.