DSXchange

Posted: **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?????

Posted: **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

Posted: **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.

Posted: **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.?

Posted: **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????

Posted: **Fri Mar 23, 2007 6:57 am**

Code: Select all

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

Posted: **Fri Mar 23, 2007 8:51 am**

I cant see the code......

Posted: **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.

Posted: **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????

Posted: **Fri Mar 23, 2007 1:00 pm**

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

Posted: **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???

Posted: **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

Posted: **Fri Mar 23, 2007 10:36 pm**

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

Posted: **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..

Posted: **Sat Mar 24, 2007 3:00 am**

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

DSXchange

query based on conditon???

query based on conditon???