Home » SQL & PL/SQL » SQL & PL/SQL » Compare passing values of array with table column (Oracle 9.2.0.5)
Compare passing values of array with table column [message #389915] Wed, 04 March 2009 02:06 Go to next message
deb.b
Messages: 44
Registered: December 2008
Member
I need to check the passing value of an array with a column value of table A.
If the value found in column 1 in table A then the row of table A will be inserted into a new table table B with status say T
and if not found then it will be inserted as status F ( table b will have additional column 'Status' ).

I am not being able to figure out how to do this.



Thanks
Re: Compare passing values of array with table column [message #389918 is a reply to message #389915] Wed, 04 March 2009 02:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use MERGE.

Post a Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Compare passing values of array with table column [message #389933 is a reply to message #389918] Wed, 04 March 2009 03:43 Go to previous messageGo to next message
deb.b
Messages: 44
Registered: December 2008
Member

Hi,

Here is the test case below. Requirement is we are passing array list value and check with a table column
(here with EMPID) if it exits that row will be inserted in table NEW_EMP with status T else with status F

ARRAY IN LIST VALUE='001','003', '004'


CREATE TABLE EMP
( EMPID VARCHAR2(3),
  EMPNAME VARCHAR2(25),
  DEPTID  VARCHAR2(3));


INSERT INTO EMP VALUES ('001', 'X', '01') ;
INSERT INTO EMP VALUES ('002', 'Y', '02') ;
INSERT INTO EMP VALUES ('003', 'Z', '02') ;
INSERT INTO EMP VALUES ('004', 'A', '03') ;
INSERT INTO EMP VALUES ('005', 'B', '04') ;


CREATE TABLE NEW_EMP
( EMPID VARCHAR2(3),
  EMPNAME VARCHAR2(25),
  DEPTID  VARCHAR2(3));



expected output:

 EMPID  EMPNAME DEPTID STATUS

  001    X    01    T
  002    Y    02    F
  003    Z    02    T
  004    A    03    T
  005    B    04    F 


How can we do this using MERGE by comparing an array value list.






thanks
Re: Compare passing values of array with table column [message #389935 is a reply to message #389933] Wed, 04 March 2009 03:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What part don't you know how to do it?
Select from a list? search in Barbara last 12 hours posts.
Merge from the previous select? Post what you already tried.

Regards
Michel
Re: Compare passing values of array with table column [message #389938 is a reply to message #389935] Wed, 04 March 2009 04:06 Go to previous messageGo to next message
deb.b
Messages: 44
Registered: December 2008
Member
I do not know how to use array here.Would you please help?

Thanks
Re: Compare passing values of array with table column [message #389944 is a reply to message #389938] Wed, 04 March 2009 04:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just read Barbara's last post.

Regards
Michel
Re: Compare passing values of array with table column [message #389948 is a reply to message #389944] Wed, 04 March 2009 04:59 Go to previous messageGo to next message
deb.b
Messages: 44
Registered: December 2008
Member

thanks. But how I will implement the condition of insert?

when matched with the column value then
insert the row in a new table B with status T and for non matched rows with status F in table B( non matched rows also will be inserted but with status F)
Re: Compare passing values of array with table column [message #389950 is a reply to message #389915] Wed, 04 March 2009 05:10 Go to previous messageGo to next message
deb.b
Messages: 44
Registered: December 2008
Member
CREATE OR REPLACE PROCEDURE Test
    (i_emp_id IN VARCHAR2)
  AS
   BEGIN
   	FOR i IN
       insert into new_emp 
  	  (SELECT empid, empname, deptid,'T'
   	   FROM   emp
   	   WHERE  empid IN
   		  (SELECT *
 		   FROM   TABLE (CAST (str2tbl (i_emp_id) AS MyTableType)))
   	   ) union all
		(SELECT empid, empname, deptid,'F'
   	   FROM   emp
   	   WHERE  empid NOT IN
 		  (SELECT *
 		   FROM   TABLE (CAST (str2tbl (i_emp_id) AS MyTableType)))       
 	LOOP
  	  --DBMS_OUTPUT.put_line ('EmpNo ' || i.empno);
  	END LOOP;
END Test;
/



Can I go ahead like this or it would be wrong?

Re: Compare passing values of array with table column [message #389951 is a reply to message #389948] Wed, 04 March 2009 05:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But how I will implement the condition of insert?

It is merge condition that determines if it is update or insert.

Regards
Michel
Re: Compare passing values of array with table column [message #389952 is a reply to message #389951] Wed, 04 March 2009 05:14 Go to previous messageGo to next message
deb.b
Messages: 44
Registered: December 2008
Member
How merge will help for this problem?
Re: Compare passing values of array with table column [message #389954 is a reply to message #389952] Wed, 04 March 2009 05:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry you insert in both case, so no merge.

Regards
Michel
Re: Compare passing values of array with table column [message #389956 is a reply to message #389950] Wed, 04 March 2009 05:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I didn't see your code.
Just keep insert and remove the rest.

Regards
Michel
Re: Compare passing values of array with table column [message #389957 is a reply to message #389915] Wed, 04 March 2009 05:26 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
I don't think you want to use merge for this.
I think you want to use insert as select and an outer join.
Coupled with a CASE statement to get the status.
Something like this:

INSERT INTO new_emp (<columns>)
SELECT <columns from array>, <columns from emp)
(CASE WHEN emp.emp_id IS NULL THEN 'F'
     ELSE 'T'
END) status
FROM array, emp
WHERE emp.empid(+) = array.empid



EDIT: removed erroneous (+)

[Updated on: Wed, 04 March 2009 05:26]

Report message to a moderator

Re: Compare passing values of array with table column [message #390112 is a reply to message #389956] Thu, 05 March 2009 00:47 Go to previous messageGo to next message
deb.b
Messages: 44
Registered: December 2008
Member
CREATE OR REPLACE PROCEDURE Test
    (i_emp_id IN VARCHAR2)
  AS
   BEGIN
   	
       insert into new_emp 
  	  (SELECT empid, empname, deptid,'T'
   	   FROM   emp
   	   WHERE  empid IN
   		  (SELECT *
 		   FROM   TABLE (CAST (str2tbl (i_emp_id) AS MyTableType)))
   	   ) union all
		(SELECT empid, empname, deptid,'F'
   	   FROM   emp
   	   WHERE  empid NOT IN
 		  (SELECT *
 		   FROM   TABLE (CAST (str2tbl (i_emp_id) AS MyTableType)))       
 	
END Test;
/


Can I go ahead this way?

thanks
Re: Compare passing values of array with table column [message #390114 is a reply to message #390112] Thu, 05 March 2009 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Can I go ahead this way?

What happened when you tried it?

Regards
Michel
Re: Compare passing values of array with table column [message #390118 is a reply to message #390114] Thu, 05 March 2009 01:10 Go to previous messageGo to next message
deb.b
Messages: 44
Registered: December 2008
Member
I wanted to know if there is any better approach to do this avoiding NOT IN

thanks Michel
Re: Compare passing values of array with table column [message #390121 is a reply to message #390118] Thu, 05 March 2009 01:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
NOT IN may not be evil.
You can adapt cookiemonster's solution with a select from the array inside the select list.

Regards
Michel
Re: Compare passing values of array with table column [message #390151 is a reply to message #390121] Thu, 05 March 2009 03:56 Go to previous message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
I believe an outer join, as cookiemonster suggested, would be more efficient. You can use case or decode or nvl2 to derive the status.

SCOTT@orcl_11g> CREATE TABLE OLD_EMP
  2  ( EMPID VARCHAR2(3),
  3    EMPNAME VARCHAR2(25),
  4    DEPTID  VARCHAR2(3));

Table created.

SCOTT@orcl_11g> 
SCOTT@orcl_11g> BEGIN
  2    INSERT INTO old_EMP VALUES ('001', 'X', '01') ;
  3    INSERT INTO old_EMP VALUES ('002', 'Y', '02') ;
  4    INSERT INTO old_EMP VALUES ('003', 'Z', '02') ;
  5    INSERT INTO old_EMP VALUES ('004', 'A', '03') ;
  6    INSERT INTO old_EMP VALUES ('005', 'B', '04') ;
  7  END;
  8  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> 
SCOTT@orcl_11g> CREATE TABLE NEW_EMP
  2  ( EMPID VARCHAR2(3),
  3    EMPNAME VARCHAR2(25),
  4    DEPTID  VARCHAR2(3),
  5    STATUS  VARCHAR2 (1));

Table created.

SCOTT@orcl_11g> 
SCOTT@orcl_11g> 
SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE Test
  2    (i_depts IN VARCHAR2)
  3  AS
  4  BEGIN
  5    INSERT INTO new_emp (empid, empname, deptid, status)
  6    SELECT old_emp.empid, old_emp.empname, old_emp.deptid,
  7  	      NVL2 (array.COLUMN_VALUE, 'T', 'F')
  8    FROM   old_emp,
  9  	      TABLE (CAST (str2tbl (i_depts) AS MyTableType)) array
 10    WHERE  old_emp.empid = array.COLUMN_VALUE (+);
 11  END Test;
 12  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> execute test ('001,003,004')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT * FROM new_emp ORDER BY empid
  2  /

EMP EMPNAME                   DEP S
--- ------------------------- --- -
001 X                         01  T
002 Y                         02  F
003 Z                         02  T
004 A                         03  T
005 B                         04  F

SCOTT@orcl_11g> 


Previous Topic: ROWNUMBER () OVER Partition in Oracle 9i
Next Topic: Calling unix command from pl/sql
Goto Forum:
  


Current Time: Sat Dec 03 18:08:31 CST 2016

Total time taken to generate the page: 0.06527 seconds