problem with lookup

[ridar]

Hi,
Also please check the condition Not(IsNull(Lkp.KeyColumn)) in the transformer. This will make sure you have a matching record in the lookup. Otherwise there are chances that only the constraint is validated. Experts, please correct me if i am wrong. Also, please try to check if all the date formats are correct. As KCBland has already pointed out try to check the date command using SQL query with the same format what you use in the job. Check if it is returning proper values.

Ridar

[us1aslam1us]

Hi,

I did the suggestion#2 before and i got the wrong output which i mentioned earlier.

If i try suggestion#1 then i will get the following:

input:1670 rows
reference:24 rows(which should be my output)
output:1670 rows with id, suffix, position to be null for rows which r not found in the lookup. Moreover, I don't need those rows with id, suffix,position with a null value because those r the extra rows which shouldn't be present in my output.

thanks,

[kcbland]

This is getting frustrating because we're talking in circles.


The purpose of a multi-row lookup is:

1. Produce a product, meaning multiply your primary input stream by the referenced rows. Your initial request has ALWAYS been to produce more output than input.

2. Do a multiple choice selection from a couple of referenced rows.


In suggestion #1 from the previous answer, why aren't you using the CONSTRAINT to limit which rows you require? Constrain the output to those rows found in the lookup, and also only those rows who have the appropriate date range. I didn't think I needed to state that.

Otherwise in your current design, why aren't you putting the date requirement in the WHERE clause as I suggested? Why do a multi-row lookup when you can do a 1 or 0 row lookup, either the row is there according to the key structure and with business date value between the start and end date?

[us1aslam1us]

Hi,

I tried the suggestion#1 and i am getting the correct output.
Thanks a lot for all your help.

Thanks,

[kcbland]

{Wipes the sweat off the forehead}

Glad that one is fixed. :D

[us1aslam1us]

Hi,

But I am just curious to know why suggestion#2 didn't work out?

Thanks,

[chulett]

{Wipes the sweat back on forehead}

[kcbland]

But I am just curious to know why suggestion#2 didn't work out?

You were trying to do something the more difficult way, and therefore had to more carefully design the job. I suspect something was not quite right. The solution used now is very easy to understand and not so complicated.

[kcbland]

{Wipes the sweat back on forehead}

Spent enough time on this one, no use trying to figure out what was wrong with the design.

[us1aslam1us]

Hi,

Thanks a lot to all of u for helping me to solve the problem.