Logic Question

g_rkrish · Post by **g_rkrish** » Thu Nov 08, 2007 11:44 am

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....

ray.wurlod · Post by **ray.wurlod** » Thu Nov 08, 2007 8:58 pm

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.

g_rkrish · Post by **g_rkrish** » Thu Nov 08, 2007 10:13 pm

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....

ray.wurlod · Post by **ray.wurlod** » Fri Nov 09, 2007 4:07 pm

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.

g_rkrish · Post by **g_rkrish** » Mon Nov 12, 2007 1:39 pm

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,

ray.wurlod · Post by **ray.wurlod** » Mon Nov 12, 2007 6:53 pm

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.

g_rkrish · Post by **g_rkrish** » Tue Nov 13, 2007 11:12 am

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.....

g_rkrish · Post by **g_rkrish** » Tue Nov 13, 2007 6:17 pm

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.....

ray.wurlod · Post by **ray.wurlod** » Tue Nov 13, 2007 11:00 pm

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.

g_rkrish · Post by **g_rkrish** » Wed Nov 14, 2007 1:18 am

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.....

ray.wurlod · Post by **ray.wurlod** » Wed Nov 14, 2007 5:41 am

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.

g_rkrish · Post by **g_rkrish** » Wed Nov 14, 2007 12:53 pm

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.....

ray.wurlod · Post by **ray.wurlod** » Wed Nov 14, 2007 3:25 pm

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.

g_rkrish · Post by **g_rkrish** » Wed Nov 14, 2007 3:43 pm

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,

chulett · Post by **chulett** » Wed Nov 14, 2007 4:33 pm

Time to involve your official Support provider, it seems.