Lookup works in 7.0 but fails in 7.0.1

vinodlakshmanan · Post by **vinodlakshmanan** » Sun Aug 07, 2005 10:54 pm

I have a server job which runs weekly, wherein I extract data from a staging table and DM table. The staging data is loaded from a DSS and data extracted from the staging is that which has changed in the DSS within the last 8 days. A comparison is then done within a transformer on an ID between the staging data and DM data. If the ID is new it is inserted into the DM table. This works fine in 7.0 development server. However, on the production server which is 7.0.1, rows get randomly dropped every week.
How the comparison is done is, the ID of staging data is compared with the ID of the DM data. If the DM ID is NULL (checked using IsNull()), then the row is passed through.
Why does this problem happen? As I can see, there is some difference between server lookup stage of 7.0 and 7.0.1; or there is some difference is the functioning of IsNull().
Please let me know if anyone knows of any such issue.

Regards,
Vinod

roy · Post by **roy** » Mon Aug 08, 2005 12:00 am

Hi,
IMHO, you can try to verify your assumptions.
Run the 7.0 having your source from production (for reading only) and output it to a test DB or files.
If the result is the same as 7.0.1 on prod then you missed something that your dev/test environments doesn't contain or missed something else.
If the results are different then you might be on to something different in versions.

By the way do you use NLS in tihs job and do you promote it via VC?

IHTH,

vinodlakshmanan · Post by **vinodlakshmanan** » Mon Aug 08, 2005 12:36 am

Hi Roy,
I have already done this and the results of the job are different in both cases, and that's how I have narrowed it down to IsNull(). And no, the default NLS mapping is used in the job and the default NLS mapping is the same on both servers.

Regards,
Vinod

roy · Post by **roy** » Mon Aug 08, 2005 3:27 am

Do oyu use VC to promote jobs?
If So does this job by any chance has NLS in a where part?

vinodlakshmanan · Post by **vinodlakshmanan** » Mon Aug 08, 2005 10:46 am

No I do not use VC.

roy · Post by **roy** » Tue Aug 09, 2005 4:50 am

I guess you'll need to ready a test case and contact your support provider

I must say I'm

some what by the fact your not working on the same version in dev/prod.

If your performing lookups and testing the lookup data for IsNull(key column) try putting the lookup link's NOTFOUND attribute instead and see if it works ok (it's in the link variables).

IHTH,

vinodlakshmanan · Post by **vinodlakshmanan** » Tue Aug 09, 2005 11:06 am

Just getting it clear - NOTFOUND signifies that data is not present in the reference link, right? Which means that data in the driver has not been found in the reference file. Am I correct?

Regards,
Vinod

pnchowdary · Post by **pnchowdary** » Tue Aug 09, 2005 12:46 pm

Yes Vinodlakshmanan, you are exactly right.