Home » SQL & PL/SQL » SQL & PL/SQL » Why does my SQL not work?
Why does my SQL not work? [message #676094] Tue, 14 May 2019 11:40 Go to next message
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 #676095 is a reply to message #676094] Tue, 14 May 2019 11:54 Go to previous messageGo to next message
John Watson
Messages: 8022
Registered: January 2010
Location: Global Village
Senior Member
I don't understand what you are asking. If you show the rows you are querying and the result you want, it would help.

When you post the queries and everything else, please use [code] tags as described here How to use code tags and make your code easier to read
You have been asked to do this before.
Re: Why does my SQL not work? [message #676096 is a reply to message #676094] Tue, 14 May 2019 11:55 Go to previous messageGo to next message
Michel Cadot
Messages: 66632
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Does C_COLUMN2 contain NULL?

[Updated on: Tue, 14 May 2019 11:55]

Report message to a moderator

Re: Why does my SQL not work? [message #676097 is a reply to message #676095] Tue, 14 May 2019 11:57 Go to previous messageGo to next message
jvanh
Messages: 17
Registered: August 2013
Junior Member
Values in table_1 like PREFIX_12345, values in table_2 like 12345. Goal: I don't want to select value PREFIX_12345 from table_1 if value 12345 exists in table_2.

Yes, should have used [code] instead of [b], too quick, sorry.
Re: Why does my SQL not work? [message #676098 is a reply to message #676097] Tue, 14 May 2019 12:06 Go to previous messageGo to next message
Michel Cadot
Messages: 66632
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SELECT DISTINCT REPLACE (C_COLUMN1, 'PREFIX_','') 
FROM TABLE_1 T1 
WHERE T1.C_COLUMN1 like 'PREFIX_%'
  AND (T1.C_COLUMN1 NOT IN (SELECT nvl(C_COLUMN2,'#') FROM TABLE_2));
Re: Why does my SQL not work? [message #676099 is a reply to message #676097] Tue, 14 May 2019 12:09 Go to previous messageGo to next message
BlackSwan
Messages: 26604
Registered: January 2009
Location: SoCal
Senior Member
post CREATE TABLE statements for all involved tables.
post INSERT statements to populate tables with needed sample data.

post explanation of the requirements to produce desired results

SHOW us the desired results based upon requirements & sample data.


Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: Why does my SQL not work? [message #676100 is a reply to message #676099] Tue, 14 May 2019 12:17 Go to previous messageGo to next message
jvanh
Messages: 17
Registered: August 2013
Junior Member
Hi - suggested SQL doesn't work.


CREATE TABLE "TABLE1" (	"COLUMN1" VARCHAR2(20 BYTE));
COMMIT;
   
CREATE TABLE "TABLE2" (	"COLUMN2" VARCHAR2(20 BYTE));
COMMIT;

Insert into IPC.TABLE1 (COLUMN1) values ('PREFIX_12345');
Insert into IPC.TABLE1 (COLUMN1) values ('PREFIX_23456');
Insert into IPC.TABLE2 (COLUMN2) values ('12345');
COMMIT;


SELECT DISTINCT REPLACE (COLUMN1, 'PREFIX_','') 
FROM TABLE1 T1 
WHERE T1.COLUMN1 like 'PREFIX_%'
AND (T1.COLUMN1 NOT IN (SELECT nvl(COLUMN2,'#') FROM TABLE2));
Re: Why does my SQL not work? [message #676101 is a reply to message #676100] Tue, 14 May 2019 12:21 Go to previous messageGo to next message
jvanh
Messages: 17
Registered: August 2013
Junior Member
To be more precise, I expected only 23456 as result, but it returns both 12345 and 23456.
It supports my suspicion the replaced value is not used for the AND statement part.
Re: Why does my SQL not work? [message #676102 is a reply to message #676101] Tue, 14 May 2019 12:29 Go to previous messageGo to next message
Michel Cadot
Messages: 66632
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 #676103 is a reply to message #676102] Tue, 14 May 2019 12:33 Go to previous messageGo to next message
jvanh
Messages: 17
Registered: August 2013
Junior Member
Bingo - thanks v much Michel
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
Solomon Yakobson
Messages: 2878
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.
Previous Topic: Package transfer from one to other oracle database
Next Topic: Extract clob xml data to get node value
Goto Forum:
  


Current Time: Sun Oct 13 18:36:45 CDT 2019