Pattern matching in Sparse lookup

[kumarjit]

Hi.

I am trying to do a SPARSE lookup from a reference table(SQL Server, accessed by ODBC Connector stage) and the actual lookup logic is given below:

Code: Select all

SELECT COUNT(1) AS TESTCNT FROM TEST WHERE SUBSTRING(TEST_ID,1,6)=orchestrate.SRC_CD

The column SRC_CD is coming from the stream link and the record count in the stream is 1.
But datastage throws me the following warning whenever I use the SUBSTRING function.

Unable to determine association between statement parameters and table columns. The connector will not be able to obtain external schema and only limited schema reconciliation will be performed

I tried to remove the SUBSTRING function in the query to the following but to no avail.

Code: Select all

SELECT COUNT(1) AS TESTCNT FROM TEST WHERE TEST_ID LIKE 'orchestrate.SRC_CD%' 

The job ABORTS totally.

Any advises will be of great help.

Thanks.
Kumarjit.

[qt_ky]

Try removing that % sign and the quotes from the SQL; seems like those easily caused problems. Not sure if case matters... I have a sparse lookup that works fine with all uppercase:

... WHERE NAME LIKE ORCHESTRATE.NAME

I should mention too that in a Transformer stage derivation before the sparse lookup, we add the % signs where needed:

'%' : UpCase(Trim(lnk.NAME)) : '%'

[kumarjit]

IT WORKED !!!!


@qt_ky : Thank you very much. Appreciate ..

Warm Regards,
Kumarjit.