problem with lookup
Moderators: chulett, rschirm, roy
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
problem with lookup
Hi All,
I have a problem with the lookup. The reference input is generating 2000 rows. the primary input is generating 10 rows. The output as per the lookup should be 30 rows but i am getting only 10 rows as primary input. Moreover the output for the 10 rows is also wrong. Its taking only the first row from the lookup And it is updating the input .
Can, u please help me out with this problem.
Thanks,
I have a problem with the lookup. The reference input is generating 2000 rows. the primary input is generating 10 rows. The output as per the lookup should be 30 rows but i am getting only 10 rows as primary input. Moreover the output for the 10 rows is also wrong. Its taking only the first row from the lookup And it is updating the input .
Can, u please help me out with this problem.
Thanks,
Your statements are nearly indecipherable.
The primary input stream will determine how many target rows will be written. The reference streams only effect the number of target rows if:
1. A constraint limits the output of the row from a transformer based on a reference lookup value.
2. Multi-row reference lookup is enabled.
3. You're embedding LF or CR-LF in the output data when writing to a sequential file.
So, if you have more target rows than source, the question becomes by which method? All reference lookups are equi-joins, so a failure of a reference lookup will not inhibit the output of the row on the primary input stream.
Can you please digest these points and restate what your job is doing? A diagram might help, such as SEQ --> XFMw/ODBC reference -->SEQ
The primary input stream will determine how many target rows will be written. The reference streams only effect the number of target rows if:
1. A constraint limits the output of the row from a transformer based on a reference lookup value.
2. Multi-row reference lookup is enabled.
3. You're embedding LF or CR-LF in the output data when writing to a sequential file.
So, if you have more target rows than source, the question becomes by which method? All reference lookups are equi-joins, so a failure of a reference lookup will not inhibit the output of the row on the primary input stream.
Can you please digest these points and restate what your job is doing? A diagram might help, such as SEQ --> XFMw/ODBC reference -->SEQ
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
There is no lookup whatsoever in that diagram. Therefore (unless you have a constraint expression in the Transfomer) the expected rows out will be the same as the number of rows in.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
What do you mean by "entire data"? Row counts would be helpful at this point. If you have 1000 rows in the primary input stream, your multi-row reference lookup could generate N rows per input row, so do you mean that every input row is getting 2000 output rows generated? It would seem that your WHERE clause on the reference lookup is insufficient to reduce the number of output rows to what you desire.
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
Hi,
I am getting 2000 rows as output. The primary input columns are equally(approximately) divided into 2000 rows. Actually i should get an output of 24 rows after the lookup but i am getting2000 rows.
INPUT:7 ROWS
LOOKUP:2000 ROWS
OUTPUT:24 ROWS but I got 2000 ROWS
Can, u please help me out with it.
Thanks,
I am getting 2000 rows as output. The primary input columns are equally(approximately) divided into 2000 rows. Actually i should get an output of 24 rows after the lookup but i am getting2000 rows.
INPUT:7 ROWS
LOOKUP:2000 ROWS
OUTPUT:24 ROWS but I got 2000 ROWS
Can, u please help me out with it.
Thanks,
Pick 1 row of data from your primary input stream. Take your EXACT reference lookup SQL and fill in the WHERE clause cursor values and run that query in a SQL editor. Do you get the exact answer you need?
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
Hi,
Can you also put up the constraint that you have given in the ransformer? That would help in anlyzing the problem faster. Wrong constraints or wrong key matches can cause such problems. Also the data has to be analysed. If your input key column for the 20 records matches with the lookup key columns for the 2000 records then u wud get such an output. So also check the key columns specified in the job(not in the database alone).
Ridar
Can you also put up the constraint that you have given in the ransformer? That would help in anlyzing the problem faster. Wrong constraints or wrong key matches can cause such problems. Also the data has to be analysed. If your input key column for the 20 records matches with the lookup key columns for the 2000 records then u wud get such an output. So also check the key columns specified in the job(not in the database alone).
Ridar
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
Your query must be malformed if it's not returning 24 rows per lookup. Why isn't your constraint part of the WHERE clause?
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
If you can put an condition in the constraint that uses columns from the primary and reference links, then pass the primary link values as "keys" to the reference lookup and use the cursor variable number in the query as part of the WHERE condition. You'll reduce the number of rows returned in the multi-row lookup, probably increasing performance.us1aslam1us wrote:My constraint has columns from primary as well as from the reference
I still can't figure out why your lookup isn't correct, I'm hoping this is just a miscommunication in how you've built your reference lookup.
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
Hi
I really think i have been unable to explain u guys properly.But let me give u an example.
Here is my input:
Id Suffix Position
1 00 QA
2 00 QA
3 03 BA
3 05 BA
4 00 CA
4 01 CA
4 02 CA
All the three columns are primary keys
Here is my reference input
Id Suffix Position Date1 Date2
0 00 HR 23/07/05 27/07/05
1 00 QA 23/07/05 27/07/05
1 00 QA 24/07/05 28/07/05
1 01 QA 24/07/05 28/07/05
2 00 BA 23/07/05 27/07/05
and so on i have 1670 rows here
The first three colums are key in the reference
Now i am not giving any contstraints in the transformer and my output should be some what like this:
Id Suffix Position Date1 Date2
1 00 QA 23/07/05 27/07/05
1 00 QA 24/07/05 28/07/05
2 00 BA 23/07/05 27/07/05
and so on ....
But i am getting some haywire output and i think definite;y i am doing something very silly somewhere.
SEQ>>>>XFM/ODBC reference>>>>>SEQ
Thanks
I really think i have been unable to explain u guys properly.But let me give u an example.
Here is my input:
Id Suffix Position
1 00 QA
2 00 QA
3 03 BA
3 05 BA
4 00 CA
4 01 CA
4 02 CA
All the three columns are primary keys
Here is my reference input
Id Suffix Position Date1 Date2
0 00 HR 23/07/05 27/07/05
1 00 QA 23/07/05 27/07/05
1 00 QA 24/07/05 28/07/05
1 01 QA 24/07/05 28/07/05
2 00 BA 23/07/05 27/07/05
and so on i have 1670 rows here
The first three colums are key in the reference
Now i am not giving any contstraints in the transformer and my output should be some what like this:
Id Suffix Position Date1 Date2
1 00 QA 23/07/05 27/07/05
1 00 QA 24/07/05 28/07/05
2 00 BA 23/07/05 27/07/05
and so on ....
But i am getting some haywire output and i think definite;y i am doing something very silly somewhere.
SEQ>>>>XFM/ODBC reference>>>>>SEQ
Thanks
Suggestion #1, turn your job around and use the reference as the driver table. Rather than using a 1 to many processing design, use a many to 1 processing. If your reference table sets the maximum number of rows you are allowed, then it should be the driver. You won't be doing a multi-row lookup anymore, it will be a straight lookup against your current input.
Suggestion #2, set the primary key on the reference to be Id, Suffix, and Position. Enable multi-row lookup. For every row in the input, the lookup should find all rows that match the Id, Suffix, and Position supplied. For each row found in the reference, you will get the input row repeated.
Suggestion #2, set the primary key on the reference to be Id, Suffix, and Position. Enable multi-row lookup. For every row in the input, the lookup should find all rows that match the Id, Suffix, and Position supplied. For each row found in the reference, you will get the input row repeated.
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