cancel
Showing results for 
Search instead for 
Did you mean: 

Can i use an Update query in query transform?

Former Member
0 Kudos
55

Hi All,

I have a source table and a destination table. I have created a job and a DF, in the DF I have selected the Source <mapped to>Query<mapped to>Target

my requirement is when I click on the query transform and do the Mapping, I have a field called Status.

I want to write a query as Update Status set YES where Material type = 'XYZ'. when I do this the original mapping is lost, and when I remap after writing this query my query is lost.

Can anybody help how to achieve this requirement.

Thanks in Advance

Reagrds,

Sharma

Accepted Solutions (1)

Accepted Solutions (1)

ahalya_inturi
Active Participant
0 Kudos

Hi Sharma,

You having Field "Material type" and "Status"

then

you can write logic in mapping tab for "Status" field

Decode( Material type ='XYZ' , 'YES', Default Value)

have drag the column Material type in side the function

Answers (2)

Answers (2)

ahalya_inturi
Active Participant
0 Kudos

Addition to last post,

Now you can see mapping connected to Material type column,

and

you can see code in mapping tab 🙂

Former Member
0 Kudos

Hi All,

I understand from the above posts that their are three possible ways to achieve my requirement. I will try all three and update the thread with my findings.

@Adrian: Do i need to use the same If then else provided by you?  ifthenelse(Name1='SAM','EXT',Name2) then, can you confirm the syntax?


@Ahalya: I wil use the same code provided by you and at the end as suggested by you would drag the Material type into the function.

@ Arun: Can you detail the steps regarding the post load command in the target table?

Regards,

Sharma.

Former Member
0 Kudos

Sharma,

Double click the target table and it will open up a series of tabs. One of the tab is post load command where you can enter the SQL query (update command) and save it. Run the job after that and you will get the result.

Arun

Former Member
0 Kudos

Hi All

Thankyou all for your valuable inputs. I have tested all three solutions and have got the desired output.

Regards,

Former Member
0 Kudos

Hi,

that is not how DS works, you need the following;

source table -> query (here set the where clause to be the items you want to update and set the value you want to update)

then it depends on the target table and its contents, you can either;

1) reply on autocorrect load - possibly risky

2) use a Map transform and set Normal -> Update and all others to Discard

3) use a table comparision transform to compare your incoming data to the target - this will automatically generate the update rows you want

3) is the recommended best approach but the others have their uses in specific situations,

regards,

Adrian

Former Member
0 Kudos

Hi,

Thanks for your input, but I a little confused with what you suggested.

I am trying to set the value in my Query so that when I get my output in my target i should get it with the modified values.

Let me give you an example of it

SourceQueryTarget
Name1Name2Set Name2 = 'EXT' Where Name1 = 'SAM'Name1Name2
SAMPOL
SAMEXT
XXXYUN
XXXYUN

Here you can see that I want my output(target) to have Name2 as EXT if Name1 is SAM.

I want to do the mapping in Query in such a way that when I run my Job I should get target with the changed Hardcoded value as shown above.

Just to add, do I need to write the complete query in the WHERE tab in the Query? would the below syntax work?

Name1= 'SAM' set Name2='EXT'

I am wrong with the above syntax or is their anything I am missing.

Regards,

Sharma

Message was edited by: Sharma Sharma

Former Member
0 Kudos

Hi,

use ifthenelse in the colunm mapping, e.g. ifthenelse(Name1='SAM','EXT',Name2) then,

regards,


Adrian

Former Member
0 Kudos

Write a post load command in the target table. Use the Update sql statement in the post load section of target table and run the job.

Arun