I'd like to use "LIKE", but I can't.

Richard615 · Post by **Richard615** » Wed Apr 18, 2007 10:15 am

I'm going buggy looking through Oracle-related postings, and so far haven't seen this issue addressed.

Basically, I'm creating user-defined SQL in an Oracle Enterprise stage. I want to use the LIKE constraint in my SQL with a variable, but I can't get it to work. As in:

WHERE COL1 LIKE '%ORCHESTRATE.KEYFLD'

The reason is that Oracle requires me to put quotes around the qualifying string (which contains the % character). But putting in the quotes (either single or double) passes the variable name as a literal. But I obviously need the variable resolved.

If I was doing this in shell scripting I would simply use double instead of single quotes - but in DS that doesn't seem to make a difference. I could probably add the '%' character to the qualifying variable upstream, and lose the quotes altogether, but for reasons I won't get into here I reeeeeally want to avoid doing that.

Any ideas as to what syntax I can use (if any) to make this happen?

Thanks,

-> Richard

ray.wurlod · Post by **ray.wurlod** » Wed Apr 18, 2007 4:16 pm

Odd behaviour! Sounds like a bug: there's no good reason to treat it as a literal string. Have you involved your support provider?

Then again, ORCHESTRATE.KEYFLD is not a value - it's a reference to a property. So maybe you do need to build the comparison string upstream.

vmcburney · Post by **vmcburney** » Wed Apr 18, 2007 6:33 pm

I think it's the %ORCHESTRATE.KEYFLD, DataStage isn't recognising it as a field so it is passed through as a string. Have you tried some type of SQL concatenation like ''' || '%' || ORCHESTRATE.KEYFIELD || '''. Excuse my Oracle syntax, it's been a long time and I'm working from memory.

ray.wurlod · Post by **ray.wurlod** » Wed Apr 18, 2007 6:51 pm

I don't think that's possible in a WHERE clause. Only in a SELECT clause. But I could be wrong. Don't have access to an Oracle database today to test it.

Richard615 · Post by **Richard615** » Thu Apr 19, 2007 9:49 am

Thanks all.

Using the "||" concatenation did the trick. Previously I was trying the '%':KEYFIELD syntax, attempting to have DS resolve the combined string. Which, as I mentioned, resolved to '%'Value instead of '%Value'. I feel like a wee bit of a chowder-head that I didn't think to try having Oracle perform the string concatenation instead.

D'oh!

Thanks again,

-> Richard