Problem with lookup condition continue

[rhaddur]

1) MY job is having lookup

first lookup with conditon(isnotnull(x ) and isnotnull(y)) --continue and looup is reject

ref
|
|
ds---------lkp-------output


when I am replcing the above datastage job with DB2 Query
where fields
src ref

x (nullable yes) x (nullable yes)
x (nullable yes) y (nullable yes)
a (nullable no)
b (nullable no)
c (nullable yes)

Query

select
a,
b,
CASE WHEN LTrim(RTrim(x)) <> '' THEN x ELSE a END AS x ,
IPBLMSP.IICNL1 AS IICNL1,
CASE WHEN LTrim(RTrim(y)) <> '' THEN y ELSE b END AS y

from srource src
inner join on reference ref
(src.x=ref.x
and src.y=ref.y)

the above query is not working kindly guide me on the same

[ray.wurlod]

Define "is not working".

[John Smith]

you got to be careful when you try to mimic a DS lookup using joins within the database. DS lookup is a left outer join.

[SAMEENA]

Hi,
In the query check for nullability should also be there
The query should be like -

CASE when x IS NULL......or CASE when x IS NOT NULL....

[OddJob]

Look up is also an 'Inner Join' if you drop records that don't match!