Sparse look up query

devsonali · Post by **devsonali** » Tue Jun 30, 2015 8:26 am

Hello all,

I have the following scenario

Source file - transformer - look up database (being looked up sparse look up )-output

The idea is for each row value (varchar) , I need to to break the string into words (which I am handling with loop variables) and look up each word to database to get matching records

For example
Source input is "A BMP C D XYZ" then I break them up into 'A' , 'BMP','C','D','XYZ' in a separate row say Input_break (Which I have already tested) and do a sparse look up where database column_name like '%orchestrate.Input_break%'

The problem : I am expecting a lot of records out of database (because a lot of rows do exist in database satisfying the query) , However , I only get 5 rows in the output all of which populate a null (or empty) database column_name along with each word (for the input_break) column

Output

input_break,database_colume_name
'A' ,
'BMP',
'C',
'D',
'XYZ',

Thanks for looking

ShaneMuir · Post by **ShaneMuir** » Tue Jun 30, 2015 9:52 am

Without testing - and this is pure speculation, I am guessing that the problem is that like statement is looking for the literal '%orchestrate.Input_break%' rather than the value that you are actually wanting to pass to that value.

I have never tried to pass a like statement to a sparse lookup. But as a guess I would think that you might have to pass the % as part of your text and just have in the query column_name like orchestrate.Input_break

where input_break would equal %value%.

Again this is all just a guess.

priyadarshikunal · Post by **priyadarshikunal** » Tue Jun 30, 2015 9:57 am

You should check the query in db stage and run the query by providing this value replacing the variable in any other database client. How many records are you getting?

rkashyap · Post by **rkashyap** » Tue Jun 30, 2015 10:39 am

You can also try to use 'instr' function instead of 'like'.

devsonali · Post by **devsonali** » Tue Jun 30, 2015 12:04 pm

Thanks for all your inputs

Firstly I checked if my column name is exactly what I get in the transformer and then I took off the % sign , I get the exact same result , this tells me there is something wrong , but I just don't know what is it .

Secondly , I did check the number of records with like statement in database and I am getting plenty on each word .

Finally, When I try to test it with an = sign instead of like and ensuring the record I test exists in database , I get the correct result .

devsonali · Post by **devsonali** » Tue Jun 30, 2015 12:16 pm

rkashyap wrote:You can also try to use 'instr' function instead of 'like'.

I think that will give you the position , i am looking for the matching string here
Thank you

rkashyap · Post by **rkashyap** » Tue Jun 30, 2015 12:51 pm

INSTR will indeed give the position. However if returned value is greater than 0, then string is present.i.e

Code: Select all

WHERE INSTR(Tab_Col,searched_string) >0

devsonali · Post by **devsonali** » Tue Jun 30, 2015 12:53 pm

Yeah , but as I mentioned , my requirement is to collect all the matching records (from database ) from each word within each string (input)

rkashyap · Post by **rkashyap** » Tue Jun 30, 2015 1:13 pm

We don't know the root cause of this issue. However if the issue is due to "like" clause then INSTR can be used.

Code: Select all

WHERE INSTR(Tab_Col,searched_string) >0

is equivalent of

Code: Select all

WHERE Tab_Col like "%searched_string%"

devsonali · Post by **devsonali** » Wed Jul 01, 2015 1:13 pm

Now , I see what your were saying , Yes the query works fine but only in external sql portal , not in database stage (Oracle)

However , if I hard code with a value say 'A' to test the sql
Instead of 'ORCHESTRATE.searched_string'

select tab_col from table_T
WHERE INSTR(Tab_Col,'A') >0 then I get required rows in datastage

When I try INSTR(Tab_Col,'ORCHESTRATE.searched_string') >0 , I get only 5 ROWS (Just as how I got by using LIKE statement)

Thanks again

rkashyap · Post by **rkashyap** » Wed Jul 01, 2015 1:32 pm

Try without enclosing searched string in quotes i.e.

Code: Select all

INSTR(Tab_Col,ORCHESTRATE.searched_string) >0

devsonali · Post by **devsonali** » Thu Jul 02, 2015 8:19 am

I tried that earlier but saw the following error
"The connector could not find a column in the input schema to match parameter"

That's when I added the quote

ShaneMuir · Post by **ShaneMuir** » Thu Jul 02, 2015 8:39 am

Not sure what your reference DB is but what you want to do is write the where clause as some sort of concatenated string eg In oracle the following does work

select field1, field2 from TableName
where Tab_Col like '%'||ORCHESTRATE.LKP_STRING||'%'

devsonali · Post by **devsonali** » Thu Jul 02, 2015 8:59 am

i am using Oracle but the concatenation fails with the same error

ShaneMuir · Post by **ShaneMuir** » Thu Jul 02, 2015 9:02 am

devsonali wrote:i am using Oracle but the concatenation fails with the same error

What is the exact sql you have in your sparse query?
What is the exact error you are getting?
What is the exact column name you are using in the lookup stage?