Logic Question
Moderators: chulett, rschirm, roy
Logic Question
Hi I have a Logic questions..It's basicall a table match up but
There are two options in the table 1 to look in to table 2...
Table has following examples of vaules
MatchString Type
10 1
104 1
14578 2
Where 1 represent starts with that string we should macth a string from another table.....
where 2 Represent Contains that string match with same string that we are matching for earlier one....
Table 2 has values like
1045578
104444
1034567
2414578
when ever i find the those matched values i should populate to another table with the table 2 value....
this is the logic that i need to code..so i tried to search with the substring and the mulitple row look up i used the uv stage..but am not getting the result i wanted..Pls help me out this....
There are two options in the table 1 to look in to table 2...
Table has following examples of vaules
MatchString Type
10 1
104 1
14578 2
Where 1 represent starts with that string we should macth a string from another table.....
where 2 Represent Contains that string match with same string that we are matching for earlier one....
Table 2 has values like
1045578
104444
1034567
2414578
when ever i find the those matched values i should populate to another table with the table 2 value....
this is the logic that i need to code..so i tried to search with the substring and the mulitple row look up i used the uv stage..but am not getting the result i wanted..Pls help me out this....
Last edited by g_rkrish on Thu Nov 08, 2007 10:08 pm, edited 1 time in total.
RK
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Two Transformer stages, with the second lookup being conditional upon success of the first (a constraint in the first Transformer stage). Use other output links if needed to capture rows that fail either lookup.
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.
ray.wurlod wrote:Two Transformer stages, with the second lookup being conditional upon success of the first (a constraint in the first Transformer stage). Use other output links if needed to capture rows that fail ei ...
Ray,
can i know what constrint i should use here..also does codntion you are suggesting here is with transformer....Also the substring i am using is
substrings(table2string,1,len(table1string) = table1string
This is not seems to be working also..I solved the starts with option by left function..is this a right method to use or are you suggesting some thing else...
Thanks for your help....
RK
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
I read the problem as the more general one where, if the second column is 1 you need a lookup WHERE KEYVALUE LIKE '?%' but, if the second column is 2 you need a lookup WHERE KEYVALUE LIKE '%?%' - you can not do the both in the same Transformer stage. You also can't solve it using Hashed File stages.
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.
ray.wurlod wrote:I read the problem as the more general one where, if the second column is 1 you need a lookup WHERE KEYVALUE LIKE '?%' but, if the second column is 2 you need a lookup WHERE KEYVALUE LIKE '% ...
sorry ray for the late replay..I tried your way but where do i give the like statement..But i can't able to under stand can you explain me in detail....
Thanks,
RK
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You give the SQL statement in the stage that is servicing your reference link.
As for "in detail", perhaps purchasing premium membership would permit you to read my reply in its entirety.
As for "in detail", perhaps purchasing premium membership would permit you to read my reply in its entirety.
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.
There no error but it retrives 0 records but we do have matching records though..what can we do about it Ray....ray.wurlod wrote:You give the SQL statement in the stage that is servicing your reference link.
As for "in detail", perhaps purchasing premium membership would permit you to read my reply in its entirety.
Thank you very much for your help.....
RK
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Stop. Step back. Ignore DataStage for the moment. Write out (in English) a complete specification of what is supposed to happen, including one or two examples. Then post that here.
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.
Ok here is what it is...ray.wurlod wrote:Stop. Step back. Ignore DataStage for the moment. Write out (in English) a complete specification of what is supposed to happen, including one or two examples. Then post that here. ...
We have a tableA. where we have strings like 1000 10001 1004..
There is another tableb which has real data....
The question is is ..
We need to find ...
1.What are the string in Table B that starts with strings in table A
2.What are the strings in Table B that contains strings in table A
3.What are the strings in Table B that is equal strings to table A
Pls let me know if you need more explanation on the problem.....
RK
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Used-defined SQL (except, perhaps, for #3) on the reference input link's database stage.
SELECT columns FROM TableB WHERE keycolumn LIKE '?%';
SELECT columns FROM TableB WHERE keycolumn LIKE '%?%';
SELECT columns FROM TableB WHERE keycolumn = '?';
In all cases, provide the key value from TableA in the Reference Key expression.
SELECT columns FROM TableB WHERE keycolumn LIKE '?%';
SELECT columns FROM TableB WHERE keycolumn LIKE '%?%';
SELECT columns FROM TableB WHERE keycolumn = '?';
In all cases, provide the key value from TableA in the Reference Key expression.
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.
sorry Ray to bother you more..This problem is just annyoing me much....
SELECT columns FROM TableB WHERE keycolumn LIKE '?%'------Not works even though there is a matched column it just gives me 0 records but i did a work around with left function in the constraint part;
SELECT columns FROM TableB WHERE keycolumn LIKE '%?%' same result as before...but there is no work around...this is where am stuck;
SELECT columns FROM TableB WHERE keycolumn = '?' it works...;
thank you very very much..i don't know how to express my thanks ray for helping me patiently.....
SELECT columns FROM TableB WHERE keycolumn LIKE '?%'------Not works even though there is a matched column it just gives me 0 records but i did a work around with left function in the constraint part;
SELECT columns FROM TableB WHERE keycolumn LIKE '%?%' same result as before...but there is no work around...this is where am stuck;
SELECT columns FROM TableB WHERE keycolumn = '?' it works...;
thank you very very much..i don't know how to express my thanks ray for helping me patiently.....
RK
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
I don't really have much else to offer. What stage type are you using on the reference link? If it's OCI you might try :1 instead of ? for the parameter marker.
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.