target_lookup on same field doesn't work

[Ole_Dufour]

I'm trying to set a value in case of an update on the source. 

REFERENCE_EXPORT should be set to 'value is null' when REFERENCE_EXPORT = null.

REFERENCE_EXPORT should be set to 'valeur was not null :' concatenated with its current value when REFERENCE_EXPORT is not null.

For some reason in either case the value is set to  'valeur was not null :'. This surprises me because whenever the value of REFERENCE_EXPORT is null it should be set to 'value is null'.

Our target table is 'PERSONNE_PHYSIQUE' which contains a column 'SBL__S_CONTACT__ROW_ID' which is mapped to $ROW_ID of the source table.

What am I overlooking ? I have tried with and without ifnull() in my transformation, same result.

case ifnull(target_lookup('NO_CACHING','BPU','PERSONNE_PHYSIQUE','REFERENCE_EXPORT','SBL__S_CONTACT__ROW_ID=:1',$ROW_ID),'null')
when 'null'  then
'value is null'
else
'valeur was not null :' ||  ifnull(target_lookup('NO_CACHING','BPU','PERSONNE_PHYSIQUE','REFERENCE_EXPORT','SBL__S_CONTACT__ROW_ID=:1',$ROW_ID),'null') 
end

case target_lookup('NO_CACHING','BPU','PERSONNE_PHYSIQUE','REFERENCE_EXPORT','SBL__S_CONTACT__ROW_ID=:1',$ROW_ID)

when null  then 
'value is null'
else
'valeur was not null :' ||  target_lookup('NO_CACHING','BPU','PERSONNE_PHYSIQUE','REFERENCE_EXPORT','SBL__S_CONTACT__ROW_ID=:1',$ROW_ID)

 end

[jubli24]

I swear half the errors I run into with my excel reports involves source data in the wrong format. I use that text to column feature constantly. Select column, click the text to column button, click next, click next, select the text radio button (or occasionally date), and hit okay.

[Ole_Dufour · Accepted solution]

I just found out that it works when we evaluate against an empty string :

case target_lookup('NO_CACHING','BPU','PERSONNE_PHYSIQUE','REFERENCE_EXPORT','SBL__S_CONTACT__ROW_ID=:1',$ROW_ID)

when '' then -- we evaluate against an empty string
'value is null'
else
'valeur was not null :' ||  target_lookup('NO_CACHING','BPU','PERSONNE_PHYSIQUE','REFERENCE_EXPORT','SBL__S_CONTACT__ROW_ID=:1',$ROW_ID)

 end