Comparing Nulls

[sri75]

HI,

in one of peoplesoft delivered maps,
the logic in transformer stage is like this

if len(Source_Row_Split.DT_ISSUED)=0 then '@NULL' else Source_Row_Split.DT_ISSUED
DT_ISSUED coulmn data type is timestamp.so for the first time ,if there is no values in that column ,it is putting null values in the table.

4 keys combination is used as primary key.DT_ISSUED is one of them

first time when we ran the job the table has the following record

EMPD = 'KR0031'
ACCOM = 'CRC'
DT_ISSUED = (it has null value)
M_C = -
update_timestamp='2007-04-23'

for the second run there is no change in the source data, so it should update all records with todays date.
but this is happening here,for the second run datstage is treating the above record as a new record and tried it insert but failed with the following error

ORA-00001: unique constraint (SYSADM.PS_LEAVE_ACCRUAL) violated
when I assigned default value to the date filed ,it worked fine.

if len(Source_Row_Split.DT_ISSUED)=0 then '1900-01-01 00:00:00 'else Source_Row_Split.DT_ISSUED

is there any way to compare nulls in datastage.

Thanks

[chulett]

You don't really compare nulls per se, something is either null or is not null. If you are asking how one can detect nulls, there is the IsNull() function you can use.

Also, if you have a situation where a 'blank' field or an empty string gets treated like a null as Oracle is wont to do, then you need to be able to detect that situation as well. The length check as shown is close but best to trim it as well:

Code: Select all

if len(trim(Source_Row_Split.DT_ISSUED))=0 then '1900-01-01 00:00:00 'else Source_Row_Split.DT_ISSUED

Should work more better.

[sri75]

you mean this is not correct (setting the null value to the key)?we have to put some default values, right ?

if len(Source_Row_Split.DT_ISSUED)=0 then @NULL else Source_Row_Split.DT_ISSUED

in the update statment's where clause

the update statment is comparing value of source and the value of the target i.e null=null

UPDATE PS_ACCO set CHOOL_CODE=?,SCHOOL=?,STATE_OTHER=?
WHERE EMPLID=? AND ACCOM=? AND DT_ISSUED=TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS') AND MAJOR_CODE=?

that's is the reason it is treating all records with null values of dt_issued column are new for the second run..PLease advice me what to do here