Null in update where clause

[subrat]

Can we update a record if where clause having a column which getting NULL from souce and having null as existing value in table for that record

e.g

UPDATE <TABLE>
SET COLUMN3=ORCHESTRATE.COLUMN3
WHERE (COLUMN1=ORCHESTRATE.COLUMN1 AND COLUMN2=ORCHESTRATE.COLUMN2)

Source
COL1 COL2 COL3
1 NULL 200

Target Table
COL1 COL2 COL3
1 NULL 400

I tried and its not updating, not sure why. Never came accross this scenario before. Can anyone have idea of its alternative to achive same thing.

(COLUMN1 and COLUMN2 both are nullable Yes column)

[chulett]

NULL is never equal to NULL so you'd need explicit "IsNull" checks in the SQL to handle that. Or if the database supports an "NVL" equivalent, wrap both sides of the equality test with equivalent NVL() statements. Make sure you convert nulls to something other than a legitimate data value for that field, make it something unique.

[Sreenivasulu]

As Craig pointed out you are better advised to use user-defined sql where you are expecting null values.
I find this working fine using generated sql in informatica but not in datastage


Regards
Sreeni

[chulett]

Yah, that's been an interesting point now that I am working with Informatica - it seems to treat nulls as empty strings or at least in a fashion that I'm not used to. For example, it allows you to concatenate null fields into strings without any adverse affects, something I had to see first hand to believe. But in the "real" world they represent an unknown value and need to be handled explicitly to get them to be recognized properly and not wreak havoc on your output.

[ray.wurlod]

If COLUMN1 or COLUMN2 is null in any particular row, then the WHERE clause for that particular row cannot be realized.

[Sreenivasulu]

Thanks Ray and Craig for your updates.
The point here is 'datastage' does 'theoretically' correct but what 'informatica' does is 'practically' correct.
Each tool has its own advantages and disadvantages

Regards
Sreeni

[subrat]

I am using User defined Sql only. I am converting null to somevalue for data coming from source and trying to do same from DB2 stage but no luck so far.

@Craig: Can you let me know your thought on way of handeling same on DB2 (update) stage

Thanks

[ray.wurlod]

There is (should be) NO WAY that you can put a null into a Not Null column.

[chulett]

Post your SQL. Unfortunately, I don't know beans about DB2 - what is the equivalent to Oracle's NVL / "null value" function, something which allows you to specify what to convert a null value to in your SQL?

After googling around it looks like the answer is NVL or NVL2.

[subrat]

Post your SQL. Unfortunately, I don't know beans about DB2 - what is the equivalent to Oracle's NVL / "null value" function, something which allows you to specify what to convert a null value to in your SQL?

After googling around it looks like the answer is NVL or NVL2.

After googling I tried with Coalease() function but it is not working. It throwing error as "0" not valid in the context where it is used. Checked the syntax and seems its fine.

UPDATE
<SchemaName>.SAMPLETABLE
SET
MARK_CODE = ORCHESTRATE.MARK_CODE
WHERE
(COL1 = ORCHESTRATE.COL1 AND
COALESCE(COL2,0) = ORCHESTRATE.COL2
)

(From Datastage i am handling null for col2 to set 0 icase find null).

[ray.wurlod]

Check the (DB2) help on COALESCE function.

Perhaps it only takes column names as its arguments?

[chulett]

Why not use one of their NVL functions?

[HariK]

Subrat is the column COL2 is of number type or it is of Character type.

if it is Char then try using COALESCE(COL2,'0') and of course change the derivation in Datastage for this column to have string value.

[subrat]

Subrat is the column COL2 is of number type or it is of Character type.

if it is Char then try using COALESCE(COL2,'0') and of course change the derivation in Datastage for this column to have string value.

Incorporating withing quotes solved the problem. Thanks Guys