Lookup Problem

[jayram99]

Hi Everyone,

I have a record in File having valid Address in Address1 and Spaces in Address2 and Address3.

IN FILE
I am using the function to set the Address2 and Address3 to Null for having spaces.
If (IsNull(Input.InsuredAddress2) or Len(trim(Input.InsuredAddress2)) = 0) then @NULL else trim(Input.InsuredAddress2).

IN DB2,

I have valid value in Address1 and NULL values for Address2 and Address3.

If I do Lookup for only Address1 , I am getting the Matched Record.

If I do Lookup for Address1 having Value, Address2 with NULL,Address3 with Null with the data in DB2 Table which has value for Address1 ,Address2 is NULL and Address3 is NULL. I couldn't get the matched record.

Please help me to resolve the same.

Thanks & Regards,
Jayaram

[rleishman]

Have a look at the SQL generated by the lookup. It will probably be something like

Code: Select all

WHERE address1 = :1
AND address2 = :2
AND address3 = :3

Replace :3 with a NULL value, and this won't work even from your DB2 client. NULL is never "equal" to anything.

If address2 and 3 are nullable, then you will have to supply a user-defined SQL. In Oracle, the sytax would be:

Code: Select all

WHERE address1 = :1
AND NVL(address2,'X') = NVL(:2, 'X')
AND NVL(address3,'X') = NVL(:3, 'X')