Home » SQL & PL/SQL » SQL & PL/SQL » optimize the query and store result in a variable (Oracle 11g, Win7)
optimize the query and store result in a variable [message #606444] Fri, 24 January 2014 00:11 Go to next message
nischalinn
Messages: 118
Registered: May 2012
Location: nepal
Senior Member
 
create table prac1 (col1 varchar2(20));
create table prac2 (col1 varchar2(20));
create table prac3 (col1 varchar2(20));

insert into parc1 values ('apple');
insert into parc1 values ('cake');
insert into parc1 values ('berries');
insert into parc1 values ('coke');
insert into parc1 values ('teset');

insert into parc2 values ('apple');
insert into parc2 values ('cake');
insert into parc2 values ('berries');
insert into parc2 values ('coke');

insert into parc3 values ('apple');
insert into parc3 values ('cake');
insert into parc3 values ('berries');
insert into parc3 values ('coke');
insert into parc3 values ('misc');


My problem is to get the data that is not common in the three tables i.e., from PRAC1 "teset" and from PRAC3 "misc".
I've written the query but is there any better way to do this?

Also I've to store the result of this query in a variable, how can I do it?
DECLARE
	DIFFRESULT VARCHAR2(100);
BEGIN
	SELECT DISTINCT COL1 FROM PRAC1
		UNION ALL
	SELECT DISTINCT COL1 FROM PRAC2
		UNION ALL
	SELECT DISTINCT COL1 FROM PRAC3
		MINUS
	(
	SELECT DISTINCT COL1 FROM PRAC1
		INTERSECT
	SELECT DISTINCT COL1 FROM PRAC2
		INTERSECT
	SELECT DISTINCT COL1 FROM PRAC3
	)ORDER BY 1;
// how to store the result of the above query in a variable
END;

Report message to a moderator

Re: optimize the query and store result in a variable [message #606447 is a reply to message #606444] Fri, 24 January 2014 01:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
// how to store the result of the above query in a variable


PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals
Search for cursor, ref cursor.

Report message to a moderator

Re: optimize the query and store result in a variable [message #606448 is a reply to message #606444] Fri, 24 January 2014 01:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
is there any better way to do this?


No need of DISTINCT:
SELECT COL1 FROM PRAC1
UNION ALL
SELECT COL1 FROM PRAC2
UNION ALL
SELECT COL1 FROM PRAC3
MINUS 
( SELECT COL1 FROM PRAC1
  INTERSECT
  SELECT COL1 FROM PRAC2
  INTERSECT
  SELECT COL1 FROM PRAC3
)
ORDER BY 1;

[Updated on: Fri, 24 January 2014 01:13]

Report message to a moderator

Re: optimize the query and store result in a variable [message #606522 is a reply to message #606444] Sat, 25 January 2014 11:09 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
There are some assumptions being made which have not been explicitly noted. For example, we all are assuming that each table has a UNIQUE constraint on col1? Assuming this is true, this would also work. Given the OP's original query, this is by no means assured.

select col1
from (
       SELECT COL1 FROM PRAC1
       UNION
       SELECT COL1 FROM PRAC2
       UNION
       SELECT COL1 FROM PRAC3
     )
group by col1
having count(*) = 1
/


If you want to return this as a single value, then you need to look into USER DEFINED TYPES and create yourself a table type to contain the list of returned values. Something like this:

create type string_table is table of varchar2(30)
/

create function f1 return string_table is
   return_v string_table := string_table();
begin
   select table(cast(select * from (
   select col1
   from (
          SELECT COL1 FROM PRAC1
          UNION
          SELECT COL1 FROM PRAC2
          UNION
          SELECT COL1 FROM PRAC3
        )
   group by col1
   having count(*) = 1
                                   ) as string_table
                    )
               )
   into return_v
   from dual
   ;
   return (return_v);
end;
/


This is just an example. There may be better ways to do this today given newer features of 10g/11g. Good luck. Kevin

Report message to a moderator

Re: optimize the query and store result in a variable [message #606524 is a reply to message #606522] Sat, 25 January 2014 11:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
we all are assuming that each table has a UNIQUE constraint on col1


I don't think so. At least, me, I am not assuming this.

Report message to a moderator

Re: optimize the query and store result in a variable [message #606531 is a reply to message #606522] Sat, 25 January 2014 14:43 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
Kevin Meade wrote on Sat, 25 January 2014 12:09
For example, we all are assuming that each table has a UNIQUE constraint on col1? Assuming this is true, this would also work.


No it would't. UNION returns distinct rows. Therefore every group will have count = 1. If you make assumption col1 is unique:

select  col1
  from  (
          SELECT COL1 FROM PRAC1
         UNION ALL
          SELECT COL1 FROM PRAC2
         UNION ALL
          SELECT COL1 FROM PRAC3
        )
  group by col1
  having count(*) != 3
/

COL1
--------------------
misc
teset

SQL>


And this type of task when col1 isn't unique is usually resoved by using weights:

select  col1
  from  (
          SELECT COL1,1 WEIGHT FROM PRAC1
         UNION ALL
          SELECT COL1,2 WEIGHT FROM PRAC2
         UNION ALL
          SELECT COL1,3 WEIGTH FROM PRAC3
        )
  group by col1
  having sum(distinct weight) != 6
/

COL1
--------------------
misc
teset

SQL>


SY.

Report message to a moderator

Re: optimize the query and store result in a variable [message #606623 is a reply to message #606531] Mon, 27 January 2014 09:04 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
right

Report message to a moderator

Previous Topic: Populating Primary Key Column using Sequence
Next Topic: what is the name of the sequence for my table
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Apr 23 14:04:40 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.