ORA-01036: Error and Substring does not work

[kashif007]

Hi

I am trying to fetch data from an reference Oracle Table using OCI stage to do a Lookup. I had put a custom query in the reference table as follows:

SELECT
SUBSTR(NAME,7,11) NAME,
DESC1,
VALUE1,
DBSTATUS
FROM
IX_FLR_FLOORPLAN
WHERE
NAME = :1 and
DBSTATUS = 1
and
NAME like 'STORE%'

Using this query the data can be viewed from the OCI stage successfully but the substring does not work for some reason. Also the job runs successfully.

When I remove "NAME = :1" from the query and view the data substring logic works but the job aborts with the error ORA-01036: illegal variable name/number.
Note: NAME field is also the primary key.

Can anyone suggest me to get the Substring working in my job. Two fields in the output are blank because the substrings not working.

[betterthanever]

..

[chulett]

Why check if NAME is equal to something *and* like something?

Regardless, return the substring into a new field, select the full name back into the key field.

[kashif007]

I kind of figured out myself. Solution to make the SUBSTR to work is to put the SUBSTR function in the Derivatives of the OCI stage. Rather than building a Custom Query.

Thanks