Why does my SQL not work? [message #676094] |
Tue, 14 May 2019 11:40 |
|
jvanh
Messages: 17 Registered: August 2013
|
Junior Member |
|
|
Hi,
It seems to me the second line of below SQL is not working as replaced C_COLUMN1 value is not passed on - this is an assumption. Any tips how to possibly make this work without schema adjustments?
SELECT DISTINCT REPLACE (C_COLUMN1, 'PREFIX_','') FROM TABLE_1 T1 WHERE T1.C_COLUMN1 like 'PREFIX_%'
AND (T1.C_COLUMN1 NOT IN (SELECT C_COLUMN2 FROM TABLE_2));
Thanks for any feedback.
|
|
|
|
|
|
|
|
|
|
Re: Why does my SQL not work? [message #676102 is a reply to message #676101] |
Tue, 14 May 2019 12:29 |
|
Michel Cadot
Messages: 68653 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:It supports my suspicion the replaced value is not used for the AND statement part.
Of course, why should it?
SQL> SELECT DISTINCT REPLACE (COLUMN1, 'PREFIX_','')
2 FROM TABLE1 T1
3 WHERE T1.COLUMN1 like 'PREFIX_%'
4 AND (REPLACE (T1.COLUMN1, 'PREFIX_','') NOT IN (SELECT nvl(COLUMN2,'#') FROM TABLE2));
REPLACE(COLUMN1,'PRE
--------------------
23456
|
|
|
|
Re: Why does my SQL not work? [message #676104 is a reply to message #676102] |
Tue, 14 May 2019 13:39 |
Solomon Yakobson
Messages: 3273 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Underscore is operator LIKE wildcard, so in general T1.COLUMN1 like 'PREFIX_%' will return T1.COLUMN1 that starts with PREFIX, not with PREFIX_. Wildcard needs to be escaped in order to be treated as a regular character:
T1.COLUMN1 like 'PREFIX\_%' ESCAPE '\'
SY.
|
|
|