DSXchange

oracle Sparse lookup with like option is not working,do we have any alternative method to do the below operation.

Here is an exmaple below

SELECT
PATIENT.FIRST_NAME
FROM
PATIENT PATIENT
WHERE
PATIENT.FIRST_NAME LIKE 'ORCHESTRATE.FIRSTNAME%'

the input FIRTSNAME is coming from a row generator.The datatype is varchar(15) for both the oracle database table & row generator.Even though there is a matching record its rejecting the record.The value in the database table is 'CHARLENE' & the value in row generator is 'CHARLE'.

When you don't get any answers, best to just reply to your original post to 'bump' it back up into the limelight rather than post the exact same question again.

It got resolved and here is the solution

trim(a.FIRST_NAME) like trim(ORCHESTRATE.FIRST_NAME) || '%'

That makes sense. Your original syntax made everything in the quotes a literal so it was looking for the literal string "'ORCHESTRATE.FIRSTNAME%" (including the percent sign) and not resolving the field reference or seeing the wildcard.