Home » SQL & PL/SQL » SQL & PL/SQL » sql "where" conditions (array)
sql "where" conditions (array) [message #197698] Thu, 12 October 2006 06:28 Go to next message
eli.levi@comverse.com
Messages: 1
Registered: October 2006
Junior Member
Hi all,

my sql is :
SELECT *
FROM CM_FILE_INFO_HIS,CM_SCM_INFO
WHERE (FI_UNIT_NAME ='unit1' or FI_UNIT_NAME ='unit2' ) AND FI_ID=SCM_FILE_ID;


i may have more then 'unit1' and 'unit2', i get those name in array. can i ask somthing like "WHERE FI_UNIT_NAME IN myArray",
or maybe u have another idea ?
Re: sql "where" conditions (array) [message #197709 is a reply to message #197698] Thu, 12 October 2006 06:54 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
The script:
CREATE TABLE mhe_foo(thename VARCHAR2(10))
/

INSERT INTO mhe_foo VALUES ('BART'   );
INSERT INTO mhe_foo VALUES ('JOHN'   );
INSERT INTO mhe_foo VALUES ('JAMES'  );
INSERT INTO mhe_foo VALUES ('CARL'   );
INSERT INTO mhe_foo VALUES ('FRANK'  );
INSERT INTO mhe_foo VALUES ('VINCENT');
INSERT INTO mhe_foo VALUES ('JACK'   );

SELECT the_value
FROM   TABLE(string_to_tab('JACK,JAMES',','))
/

SELECT thename
FROM   mhe_foo
WHERE  thename in ( SELECT the_value
                    FROM   TABLE(string_to_tab('JACK,JAMES',','))
                  )
/

DROP TABLE mhe_foo
/


The run:
SQL> @orafaq

Table created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


THE_VALUE
----------------------------------------

JACK
JAMES

2 rows selected.


THENAME
----------
JACK
JAMES

2 rows selected.


Table dropped.

SQL>


string_to_tab is a pipelined function. Its source is on the board. It generates a table from a delimited string.

MHE
Previous Topic: Stored Procedure ORA-06550 error in Oracle XE
Next Topic: Different explain plan for same sql
Goto Forum:
  


Current Time: Sun Dec 04 22:30:02 CST 2016

Total time taken to generate the page: 0.07203 seconds