I am trying to execute a dbjoin against an oracle database where 1 of the join keys might be null. So for example I have 2 join key fields version and revision. Revision may be null. I am trying to execute the following query:
select * from item_version where version = ? and nvl(revision,‘X’) = nvl(?,‘X’)
But this does not work. Is there another way to match a join key that may be or may not be null?
Just a little clarification. The input data to join with is coming from a text file. In this case, the revision is blank. I do an insert in another step and the revision gets inserted as a null value. I also tried the following sql query thinking that my input key is a blank field but this again did not work:
select * from item_version where version = ? and nvl(revision,‘’) = ?
It seems that Oracle driver can’t handle with nvl function on right side. Try to set “revsion” field not nullable with default value (eg. ‘X’); then on DBJoin input there will be not null revisions and the component should work with following query:
select * from item_version where version = ? and nvl(revision,‘X’) = ?
Thanks. That works.