Having Same Column name twice in target DB stage

[DS_SUPPORT]

I have a situation like, i want to update the target table with the values present in the Hashed file. I am having all the keycolumns of the target table present in the hashed file also. The job design is

Code: Select all

Hashfile ---> Transformer --> Target(DRS).

But the issue is, I want to update a non key column to value 'X' , by checking the same non key column, something like

Code: Select all

UPDATE TABLE SET NK1 = 'X' WHERE K1= 'INPUT VAL' AND K2 = 'INPUT VAL' AND K3 = 'INPUT VAL' AND K4 = 'INPUT VAL' AND NK1 = 'Y'    (NK - Non Key, K - Key)

I want to update the column NK1 to value 'X', if it is only with a vlaue 'Y',. The issue is , we cannot enter the same column name (NK1, IN THIS CASE) in the Column tab. As of now as a workaround I have defined NK1 only once and marked it as Non key, i have converted the Target Update action as "User-Defined SQL", and wrote the SQL shown above. It is working fine, but i prefer to use "Update Existing Rows only" target action , as we will use this job with multiple Databases.

Please advice, how to handle this?

[sachin1]

sorry with your requirement of "Update existing rows only" it is not possible to check non-key columns, user-defined sql is best solution for this kind of requirement.

[DS_SUPPORT]

I can mark the second nonkey column as Key , in the DRS stage, so that it will be a part of where condition, but the problem is , i cannot enter the same column names twice, that is the issue.

[ray.wurlod]

How would you do it if DataStage were not involved?

I see no parameter markers in your SQL - how do you provide values from DataStage to the table? The columns can be called anything within DataStage - just choose a different name for the second incarnation of the column. The columns in your job design map in order to the parameter markers in the SQL.

[chulett]

I guess 'INPUT VAL' is what you substituted for the actual parameter markers in the job?

As Ray notes, you'll need to use a different name for the second version of the 'same column' to hold the old value. Then custom sql is required to map everything into the corresponding parameter markers so you can properly mention the original column name twice in the sql.

[DS_SUPPORT]

Thanks for the inputs, Yes, "INPUT VAL" is the actual parameters passed.

As mentioned earlier, i dont have any problems in writing User Defined SQL, but if possible i want to update the rows , with the generated sql (Update Existing Rows only).

i have converted the Target Update action as "User-Defined SQL", and wrote the SQL shown above. It is working fine, but i prefer to use "Update Existing Rows only" target action , as we will use this job with multiple Databases.

So if i want to use this target update action "Update Existing rows only", and if i define the second version of the column in a different name, only that name will be part of the SQL and the SQL will fail. So is there any way to handle this , by using any job parameter substitued to the column name or any other alternatives without going for User-Defined SQL.

[ray.wurlod]

Yes, basically what we described. However, for that to work from generated SQL your table will need a SYNONYM defined for the column that has to appear twice. This is one of those cases where user-defined SQL is an easier answer.

[DS_SUPPORT]

Thanks for the Inputs, Marking the Thread as Resoved.