lookup using 'like' Keys

[v2kmadhav]

hello DS Gurus

we have just a month back started moving from oracle to teradata.

now the problem is that ...

i have to do a lookup on a reference table in terdata. im using teradata API stage

my input master link has called_numbers that are like

0044795674689
0044324793434320
00123784504
008849504343

etc

note: there could be discrepancy between different field names between old code and present represented example.

On the CDR_LU_MSC (my teradata lookup table) table we have rows:-



MSC_ID CO_Country_Id



001809452% VCT (St. Vincent)

001809444% GRD (Grenada)

001809% DOM (Dominican Republic)

001% USA (United States)

0044% GBR (great britain)

these fields are varchar. the reason why we have % is because previously for the old server jobs which were using orcale lookup reference table the % was used as a wild char with the following sql.

select z.dialling_prefix_id, z.co_country_id
from (select dialling_prefix_id, co_country_id
from cdr_lu_dialling_prefix
where :1 like dialling_prefix_id
order by dialling_prefix_id desc) z
where rownum < 2
where the bind variable :1 was the input stream called_number.

the reason why we had order by and row num is because the longest value is compared first. and the country code was extracted.

Now the same logic was neccesary to be used to extract the relevant country code.

when i try the same sql in teradata we realised that :1 wasnt understood by teradata.

error: something like name or ? expected between : and 1.

then i used USING MSC_ID Varchar(20) that the generic sql carries forward and that says no data sent or recieved uses USING.

then i was trying amny things and found out taht a auto generated sql has a sql

USING msci_id(varchar(20))
select msc_id, country_id from cdr_lu_msc where msc_id=:msc_id

error: fields obviously wont match

then i tried using the same logic as where :msc_id like msc_id
error: something expected between : and msc_id

now after a lot of search i realised that a ? is what decides that the variable is to be passed for the teradata sql.

then the sql is with where clause
where ?1 like msc_id

then when i execute that sql in tereadata assistant it says enter the parameter 1. i enter it and it works fine.

when i write a user defined query in teradata lookup stage
it says something like between or in or like expected between ? and 1.

it is clear that im not able to find the right syntax for satisfying datstage and teradata together.
so if anyone has any idea or if you couldnt suggest a better solution it would be much much appreciated.
thanks in advance.
if anything is unclear please feel free to post and i would get back to you.

cheers
madhav

[v2kmadhav]

hi everyone

the fact that no one has replied was suprising indeed but.
i raised an issue with IBM on this and they said that in a parallel job even using a API stage we cannot have the feature of using like in the user defined sql in the lookup table which u can achieve in a server canvas.
this is probably going to be escalated and a solution would probably be incorporated in the next release...
however the fact is that its a limitation for us who are working on 7x that we cant even get a patch to fix that...
so we have to design such a scenario with a seperate logic...

for now i am using post scripts on the teradata table to load these values alone after my final load process happens with datastage.

thats a new bug added.