problem with lookup

[us1aslam1us]

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,

[kcbland]

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

[us1aslam1us]

Hi,

Sorry about that Bland!!!
The diagram is as follows:
Seq--> Transformer--> Seq and the refernce input is ODBC.
The reference is Multi row result set.
Now I am getting the 2000 rows as my output which is incorrect.

Thanks,

[ray.wurlod]

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.

[us1aslam1us]

Hi,

I have ODBC as the reference input to the transformer for lookup and moreover i have a constraint in the transformer.
The output now i am getting is the entire data from the reference input(ODBC) its not checking the lookup keys at all.

Thanks,

[kcbland]

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.

[us1aslam1us]

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,

[kcbland]

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?

[ridar]

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

[us1aslam1us]

Hi,

Here is the following Transformer constraint:

Eff dt<=Req Dt<=term dt

After i analysed i found that i should get an output of 24 rows but i am getting 2000 rows(lookup rows)

Thanks,

[kcbland]

Your query must be malformed if it's not returning 24 rows per lookup. Why isn't your constraint part of the WHERE clause?

[us1aslam1us]

Hi,

My constraint has columns from primary as well as from the reference, thats why its not in the where clause.

constraint:
Reference.eff.dt<=Primary.req.dt<=Reference.term.dt

Thanks,

[kcbland]

My constraint has columns from primary as well as from the reference

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.

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.

[us1aslam1us]

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

[kcbland]

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.