Why does my SQL not work? [message #676094] |
Tue, 14 May 2019 11:40 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/0fa103fda85db71cdad932c288a94e3e?s=64&d=mm&r=g) |
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68659 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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
Solomon Yakobson
Messages: 3275 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.
|
|
|