Sparse look up query

[devsonali]

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?

* SELECT K.COMP_NM FROM COMP_NAME K
WHERE K.COMP_NM '%'||ORCHESTRATE.COMP_NM||'%')

* Lookup_41,0: The following SQL statement failed: SELECT K.COMP_NM FROM COMP_NAME K
WHERE K.COMP_NM like '%'||:COMP_NM||'%').
Lookup_41,0: The OCI function OCIStmtExecute returned status -1. Error code: 933, Error message: ORA-00933: SQL command not properly ended. (CC_OraStatement::executeSelect, file CC_OraStatement.cpp, line 3,846)

* LOOKUP Stage gets a column called COMP_NM from source (string to be searched) and the database column name is also COMP_NM (i tried to rename the source column to something else and tried as well )

[rkashyap]

Code: Select all

* Lookup_41,0: The following SQL statement failed: SELECT K.COMP_NM FROM COMP_NAME K 
WHERE K.COMP_NM like '%'||:COMP_NM||'%'). 

There is an unmatched ")" in the sql command. This can be the root cause of ORA-00933.

[devsonali]

Oh ...You are right, My bad , I didn't check the SQL hard enough ...
That solves the problem.

However, How did you figure out that we need to concatenate the string with % signs ...I mean there seems to be no way to figure that out from the log . How do one get an idea ?

Thanks again for all your inputs

[rkashyap]

Cool. Quotes act as escape characters, so initially ORCHESTRATE.COMP_NM was not getting substituted by the passed value. The issue was resolved once you moved ORCHESTRATE.COMP_NM outside the quotes and concatenated it using pipes.

[devsonali]

That helps , Many thanks .

Marking the issue as resolved.