lookup 1

[sashu12]

hi all,

I have a source table which needs to be loaded into two targets, depending on a flag value 0 or 1. This flag value is stored in another table.

Actually I need to lookup two tables, the source table and the table which contains the flag is linked by another table

the logic is something like this

table1.attribute1 = (lookup table2.attribute from table2
where table2.attribute = (lookup table3.attribute
from table3
where table3.flag = 1


this is for target1

and similarly i should send it to target2 where flag = 0

can some one help me in implementing this

 

[blom0344]

Create views on the joined lookup tables and base the lookup on the view..

Ties Blom

 

[sashu12]

thanks blom for the answer, it really helped me. well sorry for asking this is there any other way to solve this

 

[blom0344]

Use an override SQL in the lookup definition?
In designer terms using a view is probably 'cleaner'.
Override SQL has its limitations.

Ties Blom

 

[fearthellama]

Can't you use two lookups and then filter the records going into the two tables? Is there something complicating this that I am not aware of?

 

[sashu12]

well fearthellama, i am not able to understand what to write the condition in the lookup. do u have any solution

 

[blom0344]

With 2 lookups you can achieve this as well, there is no need to modify the 'normal' lookup conditions.

Ties Blom

 

[sashu12]

hi blom, can u tell me how this can be achieved

 

[blom0344]

Perform first lookup to table2 first and then use the result returned to perform a lookup to table3.

Ties Blom

 

[sashu12]

hey blom0344,

well i am just not getting how to do that. let me explain my problem

first of all i have created two unconnected lookups for table2 and table3. then i created lookup ports as l_attribute2 for table2 and l_attribute3 for table3.

Now i take a router transformation to divide the source into two targets depending on the flag condition.

the problem is when i am writing the filter condition for each group in the router. I am not able to write the condition.

I was trying to make it look this way

table1.attribute = :lkp_table2lkp_table3(table1.attribute) and table3.flag = 1

but i dont know this is the answer. bcoz i cant use the table3.flag = 1, as i am using an unconnected lookup, so i can use only one return type. i think i am clear to all

can u help me with how to write the condition

 

[fearthellama]

The first message you posted had this:

table1.attribute1 = (
lookup table2.attribute from table2
where table2.attribute = (
lookup table3.attribute from table3
where table3.flag = 1
)
)

Am I to understand that each lookup returns a single record or does table1.attribute1 end up containing a set or array?

What I thought you were asking is how can you lookup a value from the source in table3 and use it's return value to lookup a value in table2. The logic would then look like this:

table1.attribute1 = (
lookup table2.attribute from table2
where table2.attribute = (
lookup table3.attribute from table3
where table3.flag = 1
AND table3.SOMEVALUE = source.SOMEVALUE
)
)

And the transformation logic would then be:

Sourceort:SomeValue -> lkpTable3ort:SomeValue
(lkpTable3 then looks up table3.attribute)

lkpTable3ort:Table3Attr -> lkpTable2ort:Table3Attr
(lkpTable2 then looks up table3.attribute)

lkpTable2ort:Table2Attr -> rejoins the source record at the next transformation.

 

[sashu12]

hi fearthelama, this is exact situation. I have a table called telmast, whose records are to be divided into mac and non-mac incidents. and the following is a condition.

telmast is the source and i have to load into two files mac and non- mac the following is for mac.

Lookup the MAC incidents from the TELMAST where the TELMAST.SUBJECT =(lookup SUBJECTS.SEQUENCE
from SUBJECTS
where SUBJECTS.GROUP =
(lookup SUBJTYPES.GROUP
from SUBJTYPES
where CODE = ' MAC'))

this is my problem. now can u understand.

thanks

 

[blom0344]

sashu,

Are you even trying to understand what we are contibuting?
Did you try my view approach?

Ties Blom

 

[sashu12]

hey blom

well i understood what u have told. well i even got the result from ur view method. i just got curious with the second approach and was trying for that.

the view method is absolutely working and i have checked with that. I was trying the other method of creating two lookups. that is it

 

[sashu12]

The problem i have got is, i am not supposed to create views, and if u can help me with creating lookups

 

[ArtieChoke]

Use connected lookups as fearthellama shows in his example.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[sashu12]

well guys thanks for ur view, especially blom, i have got the solution because of him.

i did with the help of second approach told by blom. I didnt know how to implement that, but i tried and got it thanks anyways guys