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
Lookup Problem
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 252
- Joined: Mon Sep 19, 2005 10:28 pm
- Location: Melbourne, Australia
- Contact:
Have a look at the SQL generated by the lookup. It will probably be something like
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 address2 = :2
AND address3 = :3
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')
Ross Leishman