DSXchange

Target table consists of

ENAME DEPT
Diana 10
Steve 20
Kane 20
Smith 10
Diana 20

For above if i want update employee "Diana" deparment 10 to 30 where i will get only ename and dept information from source

say UPDATE EMPLOYEE SET DEPT=30 WHERE ENAME LIKE 'Diana' AND DEPT = 10

How can we achive above in datastage ?


Thanks,
Anupam

Is the value Diana and 10 aer fixed?
Or can you parameterize it?
You can simply achieve this using transformer with If-Then-Else condition.

sb_akarmarkar wrote:say UPDATE EMPLOYEE SET DEPT=30 WHERE ENAME LIKE 'Diana' AND DEPT = 10

A 'like' with no wildcards is the same as saying 'equals', which turns this into a plain old ordinary update statement.

If you really mean to use the 'like' you'll need custom sql and to wrap your field value in the appropriate wildcard characters for your unmentioned database - '%' for Oracle, for example.

ok If you see my update statement UPDATE EMPLOYEE SET DEPT=30 WHERE ENAME LIKE 'Diana' AND DEPT = 10

in this DEPT is coming in SET and WHERE clause how datasatge will represent in gnerated one for update

Means updating key column used by WHERE clause ...

Thanks,
Anupam

Use an OCI/ODBC stage to select from the target table rows of interest, transform then however you desire, then pass the result to an OCI/ODBC to load. E T L

Not much clear with your requirement.
What is that you expect to change in on DETP derivation.

kumar_s wrote:Not much clear with your requirement.
What is that you expect to change in

Code: Select all

If DEPT=30  And ENAME = 'Diana' Then 10 Else DEPT.

on DETP derivation. ...

I want to update DEPT to 10 where DEPT is 30

In database data in is already exists.... I want to update table with gerated query or userdefined query in target databas.e

In tarnsformer map DEPT---->DEPT
how to pass value to WHERE clause and SET clause


Thanks,
Anupam

Need more info on how did you get the number 30. Are you aggregating the dept grouped by ENAME and then want to go into the table and update all dept keyed on ENAME?
Need more clarification on your requirement.

DSguru2B wrote:Need more info on how did you get the number 30. Are you aggregating the dept grouped by ENAME and then want to go into the table and update all dept keyed on ENAME?
Need more clarification on your requirement.

one more new table consists of employee information where dept is 30 for employee shifted for one department to other...

Thanks,
Anupam

As Ken mentioned, do you transformation as you will. While sending it to the update, specify all the conditions as keys. That will take care of it.

If the question is simply how do I use a column twice in the generated sql - once in the where clause and once not - you need User Defined SQL for that. Simple example:
Pass the column in twice (different names, of course) once with the old and once with the new value. Make the one to be used in the where clause as the key and then make sure you handle your parameter markers properly, either numbered appropriately or in the correct order.

With the given FI-Then-Else condition you can directly replace the target table with the whole set of input table.

chulett wrote:If the question is simply how do I use a column twice in the generated sql - once in the where clause and once not - you need User Defined SQL for that. Simple example:

Code: Select all

UPDATE TABLE X SET Y=A WHERE Y=B

Pass the column in twice (different names, of course) once with the old and once with the new value. Make the one to be used in the where clause as the key and then make sure you handle your parameter markers properly, either numbered appropriately or in the correct order.

Yes craig got me correctly.....
I cant use parameter as there can be department 20 change to 30 also
any suggestions...

Thanks,
Anupam

Let it change. You need to pass that as an extra column to your Database stage as a key. No one is asking you to parametrize it.

DSguru2B wrote:Let it change. You need to pass that as an extra column to your Database stage as a key. No one is asking you to parametrize it.

Yes i am passing Old DEPT and New DEPT too..

Thanks,
Anupam