Help On Output SQL

mkeevil · Post by **mkeevil** » Thu Mar 31, 2005 7:59 am

Hello all, I am having problems with a job and would like to ask everyone's input. I have a job that extracts data from a SQL server and then places the data on a AS400 table. I only need to insert the data if it doesn't already exist. When you use the self create SQL you can only select insert or insert/update, but I need a insert if not exists. I tryied to use a user-defined sql but can't figure out the syntax. Currently it has:

INSERT INTO ACSPRDDTA.APPDWNSC(APACCT, APAGREE, APINSDT, APUSERID) VALUES (?,?,?,?);

But I need it to be more like:

INSERT INTO ACSPRDDTA.APPDWNSC(APACCT, APAGREE, APINSDT, APUSERID) VALUES(from the input file) WHERE APACCT NO IN (SELECT APACCT FROM ACSPRDDTA.APPDWNSC)

Anyone help me with this?

rgandra · Post by **rgandra** » Thu Mar 31, 2005 9:17 am

Hi

If i understand your problem correctly here is the solution.

1)use sql server as source.
2)use as400 table as look up.
3) in the transformer use a constraint i.e ISNULL(COLUMN)=@TRUE.
4) send the output to target as400.

Any questions let me know
sandeep

roy · Post by **roy** » Thu Mar 31, 2005 9:20 am

Hi,
this was already discussed befor.

in short perform a lookup and use a constraint of NOTFOUND on the lookup input link.
this will cause only new data to pass on to the DB stage.

IHTH,

mkeevil · Post by **mkeevil** » Thu Mar 31, 2005 9:30 am

The only problem I see with this is if the AS/400 file gets large, I will be bringing the whole file though the ODBC connection to do the filter. I was looking for a solution on the output.

roy · Post by **roy** » Thu Mar 31, 2005 10:03 am

Hi,
you need only bring the key columns, that should be smaller in size.

the other way would be loading the entire thing to a table in the AS400 side and running a user defined SQL something to outer join hte 2 and load only records not in the existing target table.
or something similar

you can also perform a direct odbc lookup ot the AS400 table but that means 1 sql per input row sent to the AS400 which might be a problem.

IHTH,

mkeevil · Post by **mkeevil** » Thu Mar 31, 2005 10:26 am

What do you mean by do a lookup, do you mean the lookup stage? I don't have that on my designer?

roy · Post by **roy** » Thu Mar 31, 2005 11:57 am

Hi,
Lookup means build a hash file with keys and do a lookup via transformer stage and reference link to the hash or directly to the DB stage.