Page 1 of 2
Logic Question
Posted: Thu Nov 08, 2007 11:44 am
by g_rkrish
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....
Posted: Thu Nov 08, 2007 8:58 pm
by ray.wurlod
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.
Posted: Thu Nov 08, 2007 10:13 pm
by g_rkrish
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....
Posted: Fri Nov 09, 2007 4:07 pm
by ray.wurlod
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.
Posted: Mon Nov 12, 2007 1:39 pm
by g_rkrish
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,
Posted: Mon Nov 12, 2007 6:53 pm
by ray.wurlod
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.
Posted: Tue Nov 13, 2007 11:12 am
by g_rkrish
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.
Ray,
I do have that with my friend..Thanks for your response.....
Posted: Tue Nov 13, 2007 6:17 pm
by g_rkrish
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.
There no error but it retrives 0 records but we do have matching records though..what can we do about it Ray....
Thank you very much for your help.....
Posted: Tue Nov 13, 2007 11:00 pm
by ray.wurlod
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.
Posted: Wed Nov 14, 2007 1:18 am
by g_rkrish
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. ...
Ok here is what it is...
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.....
Posted: Wed Nov 14, 2007 5:41 am
by ray.wurlod
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.
Posted: Wed Nov 14, 2007 12:53 pm
by g_rkrish
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.....
Posted: Wed Nov 14, 2007 3:25 pm
by ray.wurlod
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.
Posted: Wed Nov 14, 2007 3:43 pm
by g_rkrish
ray.wurlod wrote: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. ...
I tried with db2API,ODBC,DRDBMS,UV......none of them works ray....
Thanks,
Posted: Wed Nov 14, 2007 4:33 pm
by chulett
Time to involve your official Support provider, it seems.