query based on conditon???

ahmedwaseem2000 · Post by **ahmedwaseem2000** » Thu Mar 22, 2007 2:58 am

Hi,

How do i write different queries based on different conditions within db2 stage? i have a column based on which i need to write 3 insert queries and 1 update query is it possible without using multiple db2 stages or transformer?????

gbusson · Post by **gbusson** » Thu Mar 22, 2007 3:35 am

Well, the upsert is useful.but you have only one insert.

Other solution : load your rows in a staging table in DB2, with a code defining the query (ex: 1 for the update, 2 for insert 1).

Then launch a SQL script to make your different queries

DSguru2B · Post by **DSguru2B** » Thu Mar 22, 2007 9:34 am

Depending upon the conditions, build your insert/update logic and load it to a file (.sql). Then execute the .sql file from command line which you can do in after job subroutine using ExecSh.

swades · Post by **swades** » Thu Mar 22, 2007 12:03 pm

so you have any constrains to do this insert/update action.I mean any particular order issue like first update and then insert etc.?

ahmedwaseem2000 · Post by **ahmedwaseem2000** » Fri Mar 23, 2007 6:27 am

Yes, Now i need to set the predcedence, I would want to run the update first and then insert. How do i do that????

ray.wurlod · Post by **ray.wurlod** » Fri Mar 23, 2007 6:57 am

Code: Select all

      ----->  DB2 (update only)
               :
               : (reject)
               :
               V
              DB2 (insert or update)

ahmedwaseem2000 · Post by **ahmedwaseem2000** » Fri Mar 23, 2007 8:51 am

I cant see the code......

swades · Post by **swades** » Fri Mar 23, 2007 12:10 pm

ahmedwaseem2000 wrote:I cant see the code......

because you are not premium member,
can you tell us more about condition,and sequence path.

ahmedwaseem2000 · Post by **ahmedwaseem2000** » Fri Mar 23, 2007 12:30 pm

Well, In a table there is a row with a flag and expiry timestamp column. which, I would want to expire by updating the flag to N and current timestamp to the expiry date column and then insert a new row with an active flag. to achieve this i tried upsert but it is inserting the row but not updating the previous row. do you know why????

DSguru2B · Post by **DSguru2B** » Fri Mar 23, 2007 1:00 pm

You have to do inserts and updates seperately. Not in one query.

ahmedwaseem2000 · Post by **ahmedwaseem2000** » Fri Mar 23, 2007 1:17 pm

Well, I have written both the queries in DB2 UDB stage with upsert option selected but that doesnt work. Does that mean that in upsert either insert or update works and never both together. Incase, if i want to do it within the same DB2 UDB stage how am i going to do that???

swades · Post by **swades** » Fri Mar 23, 2007 5:47 pm

Are you using DB2UDB Enterprise Stage ?

If yes then
set Write method = Upsert then
set Upsert Mode=User-defined Update & Insert or other option as per your requirement.

Thanks

ahmedwaseem2000 · Post by **ahmedwaseem2000** » Fri Mar 23, 2007 10:36 pm

Well, I have already done that, But still of no use.

us1aslam1us · Post by **us1aslam1us** » Sat Mar 24, 2007 12:52 am

What is the prcedence in your upsert? Paste you SQL statement. Unless you are inserting and updating the same record there should not be any issue..

ahmedwaseem2000 · Post by **ahmedwaseem2000** » Sat Mar 24, 2007 3:00 am

Yes, I am inserting and updating the same record. will that be an issue???