problem with lookup
Moderators: chulett, rschirm, roy
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
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
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
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,
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,
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?
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?
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
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.us1aslam1us wrote:But I am just curious to know why suggestion#2 didn't work out?
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Spent enough time on this one, no use trying to figure out what was wrong with the design.chulett wrote:{Wipes the sweat back on forehead}
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA