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
I'd like to use "LIKE", but I can't.
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 12
- Joined: Tue Mar 27, 2007 11:08 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
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.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 12
- Joined: Tue Mar 27, 2007 11:08 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
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