help in the below logic

[pandeesh]

Hi,

I have 2 links from 2 db2 tables with data like below

Code: Select all

source:
---------
key1,key2,val1,val2
-----------------------
123,121,120,128
123,120,120,123
123,112,119,100

Reference:
-------------
key1,key2,val1,val2
-----------------------
123,120,120,123
123,112,119,100

The logic is :

Code: Select all

 source.key1=Ref.Key1 and source.key2<> Ref.key2 
and if any data with source.val1=ref.val1 then check src.val2>ref.val2 and satisfies choose the record.
else if any record with src.val2>ref.val2 choose that.

SO, the result should be:

Code: Select all

key,key2,val1,val2,key2_1,val1_1,val2_1
------------------------------------------------
123,121,120,128,120,120,123
123,120,120,123,112,119,100

Is this possible without using merge stage in a server job?
For using merge, i need to write those to 2 seq files and then i need to proceed. Is there any other way for this?

Thanks

[ray.wurlod]

You could, for example, do it via lookup in a Transformer stage.

[pandeesh]

Obviously I cannot use hashed files since there are 2 records with same key1 from reference link.
.even if I use key1,key2 both as keys
I want to check equality for first key and non equality for the second key2.
If I use database stages directly , I am getting multiple rows returned from reference and last row is used warning.
Please help me in this server job.
i am not able to use NOTFOUND since i want to check one key for equaity and other for non equality.

[ray.wurlod]

Who mentioned hashed files?

Do the lookup against DB2, and allow multiple return rows from the reference link.

[pandeesh]

Is retuning multiple rows from db2 enterprise stage possible in server job ?
By default I am getting multiple rows returned from reference warning .
Do you want me to use odbc stage for extracting from db2?
I just went through the odbc stage and I don't understands reference SQL and source SQL .
So I tried to check some other way .
Finally again I ll delve into odbc stage and use that .

Please let me know your comments

[ray.wurlod]

Is retuning multiple rows from db2 enterprise stage possible in server job ?

No, because there's no such thing as a DB2 Enterprise stage in a server job. That said, I don't believe that the DB2 Connector (which IS available) supports multi-row return from reference link. The ODBC stage, however, does.

Source SQL doesn't have a WHERE clause. Reference SQL does.

[pandeesh]

Thanks ray.
But I am not able to understand why there are 2 SQL in odbc stage .
However we are having a source link or the transformer from db2 stage.
Then why we nee again source SQL in odbc stage ?
Is that optional ?

[kandyshandy]

The logic is :

Code: Select all

 source.key1=Ref.Key1 and source.key2<> Ref.key2 

SO, the result should be:

Code: Select all

key,key2,val1,val2,key2_1,val1_1,val2_1
------------------------------------------------
123,121,120,128,120,120,123
123,120,120,123,112,119,100

Is your expected output correct? Based on your condition, only one record should be in the output. i.e. with key1 = 123 and key2 = 121

[kandyshandy]

Also, let me know if your reference could have duplicates on the combination of Key1 and Key2.

[ray.wurlod]

The ODBC stage generates both SQL statements. Which one it uses depends on what kind of link (stream or reference) its output link is.

[pandeesh]

I have a user defined SQL which I used in the db2 stage previously .
So I believe I can use the same SQL in reference SQL in odbc stage .
I haven't yet been with my system . I ll check and update if I face any issues .