Home » SQL & PL/SQL » SQL & PL/SQL » Why IN operator in this way is not working? (Oracle 10gR2)
Why IN operator in this way is not working? [message #439616] Mon, 18 January 2010 12:36 Go to next message
szefoski
Messages: 1
Registered: January 2010
Junior Member
Hi!

I wanna do something like this:

CREATE OR REPLACE PROCEDURE T1 (var varchar2)
IS
BEGIN
IF var NOT IN table.column THEN -- column is varchar2
  RAISE_APPLICATION_ERROR(-20205,'foo');
END IF;
END T1;


Why this is not working? And when I change table.column to (SELECT column FROM table) is stil not working. How to fix this?

[Updated on: Mon, 18 January 2010 12:44]

Report message to a moderator

Re: Why IN opertaro in this way is not working? [message #439620 is a reply to message #439616] Mon, 18 January 2010 12:45 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
"not working" is a totally useless statement.
Something can "not work" in a gazillion different ways.

What results do you expect & EXACTLY what do you get?

Without DDL for your tables & DML for your data, we can't run your SQL.
Re: Why IN operator in this way is not working? [message #439622 is a reply to message #439616] Mon, 18 January 2010 12:45 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
First select column value into a variable. Then use the variable in IF.
Re: Why IN opertaro in this way is not working? [message #439623 is a reply to message #439616] Mon, 18 January 2010 12:45 Go to previous message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
select 1 into myvar from dual
where exist (select null from mytable where mycol = var);

(myvar is declared as an integer)
If it does not exist then PL/SQL raises a NO_DATA_FOUND error.

Regards
Michel

[Updated on: Mon, 18 January 2010 12:46]

Report message to a moderator

Previous Topic: Tuning /index
Next Topic: sql and pl/sql
Goto Forum:
  


Current Time: Fri Feb 14 16:34:27 CST 2025