Solved

Oracle Cursor gets data from 2nd table if 1st table is empty

Posted on 2013-01-22
25
508 Views
Last Modified: 2013-01-23
I'm trying to write a cursor as follows,

'if the first_table does not have the point_name record then I need to go to the second_table to get the data for this record (how many points are in the second_table)'.

For example first_table and second_table are identical with
columns = point_seq, point_name

1, A
2, A
3, A
1, B
2, B

CURSOR cur_point_sequence
   IS
SELECT point_seq                                
        FROM first_table
       WHERE point_name = 'A'
-- if 'A' doesn't exist in first_table then get data from second_table --  
SELECT point_seq                              
        FROM second_table
       WHERE points_name = 'A'
0
Comment
Question by:talahi
  • 9
  • 8
  • 4
  • +3
25 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38806599
For any points in tableB that don't exist in tableA?

Maybe this:

SELECT point_seq                                
        FROM first_table
       WHERE point_name = 'A'
union all
SELECT point_seq                              
        FROM second_table
       WHERE points_name not in (select point_name from first_table)

If you can't do something like this, it will likely involve code.
0
 

Author Comment

by:talahi
ID: 38806606
BTW UNION or UNION ALL will not work.  If the point_name record exists in first_table then I would always use the data in that table.  Only when it doesn't exist in first_table do I get the data from second_table.
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 38806611
CURSOR cur_point_sequence
   IS
SELECT 'A' as tbl, point_seq                                
        FROM first_table
       WHERE point_name = 'A'
UNION DISTINCT
SELECT 'B', point_seq                               
        FROM second_table
       WHERE points_name = 'A'
ORDER BY 1

Open in new window

and read only the first record. Another way is to use an outer join to get both values, and programmatically select the correct one.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38806648
>>BTW UNION or UNION ALL will not work.

Take a look at what I posted.  The second query of the union only selects where the point_name doesn't exist in the first table.

Below is a simple test case that shows it in action.

>>UNION DISTINCT

I can't get this to run on my 11.2.0.3 database.


drop table tab1 purge;
create table tab1(point_seq number, point_name char(1));

drop table tab2 purge;
create table tab2(point_seq number, point_name char(1));

insert into tab1 values(1,'A');
insert into tab1 values(2,'A');
insert into tab1 values(3,'A');

insert into tab2 values(4,'A');
insert into tab2 values(1,'B');
insert into tab2 values(2,'B');
commit;


SELECT point_seq, point_name                                
        FROM tab1 first_table
       WHERE point_name = 'A'
union all
SELECT point_seq, point_name                          
        FROM tab2 second_table
       WHERE point_name not in (select point_name from tab1)
/

Open in new window

0
 
LVL 14

Assisted Solution

by:ajexpert
ajexpert earned 250 total points
ID: 38806656
There are many ways to do this...if you want ONLY cursor based solution, here it is...

/* Formatted on 1/22/2013 1:12:59 PM (QP5 v5.115.810.9015) */
DECLARE
   CURSOR cur_point_sequence
   IS
      SELECT   point_seq
        FROM   first_table
       WHERE   point_name = 'A';

   -- if 'A' doesn't exist in first_table then get data from second_table --
   CURSOR cur_point_sequence2
   IS
      SELECT   point_seq
        FROM   second_table
       WHERE   points_name = 'A';


   v_cnt_tabA   PLS_INTEGER := 0;
BEGIN
   SELECT   COUNT (1)
     INTO   v_cnt_tabA
     FROM   first_table
    WHERE   point_name = 'A';


   IF v_cnt_tabA > 0
   THEN
      FOR c1_rec IN cur_point_sequence
      LOOP
         -- YOUR PROCESS HERE
         NULL;
      END LOOP;
   ELSE
      FOR c2_rec IN cur_point_sequence2
      LOOP
         -- YOUR PROCESS HERE
         NULL;
      END LOOP;
   END IF;
END;

END;

Open in new window

0
 

Author Comment

by:talahi
ID: 38806666
Problem with UNION is there could be say 8 records in first_table and 10 records in second_table then my cursor would be looping through 2 extra records that did not exist in first_table.  I had used UNION before I knew of this.
0
 

Author Comment

by:talahi
ID: 38806679
Yes thanks, that looks like it work.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38806680
>>be looping through 2 extra records that did not exist in first_table.

Look at the where clause I added to the second select in the union.  Take a look at my test case.

It does not return the point_name='A' record I inserted in tab2.  It returns 3 'A' records not 4.
0
 
LVL 31

Expert Comment

by:awking00
ID: 38806702
Can you provide some sample data for the two tables and what you expect the cursor to return?
0
 

Author Comment

by:talahi
ID: 38806746
So I played around with it and if first_table does not have point_name='A' then it returns all records from second_table, 'A' and 'B'.
0
 

Author Comment

by:talahi
ID: 38806772
If first_table has an 'A' record then just give me the records from the first_table - ONLY.

Only if first_table does not have an 'A' record then give me the 'A' records from second_table.  Second_table is the master set of tables so it will always have the record.
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 38806828
>>So I played around with it

I assume you mean my union statement?  If so, you will need to add appropriate where clauses in the second select and possibily the 'not in' select.

Using the same 6 records from my test case above:

--look for 'A' records (3 rows)
SELECT point_seq, point_name                                
        FROM tab1 first_table
       WHERE point_name = 'A'
union all
SELECT point_seq, point_name                          
        FROM tab2 second_table
       WHERE  point_name='A' and point_name not in (select point_name from tab1 where point_name = 'A')
/

--look for 'B' records (two rows)
SELECT point_seq, point_name                                
        FROM tab1 first_table
       WHERE point_name = 'B'
union all
SELECT point_seq, point_name                          
        FROM tab2 second_table
       WHERE  point_name='B' and point_name not in (select point_name from tab1 where point_name = 'B')
/

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:talahi
ID: 38806876
Sorry my error. You are correct.  Let me find out how to get additional points to you.
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 38806886
I still think my solution is better in performance, it will perform faster (I hope so)
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38806894
>>Let me find out how to get additional points to you.

No problem with the points.  I'm OK with it.

If you test the two solutions and still wish to give me points, just post back here.  I can re-open this for you.

For future reference:  You can click the 'Request attention' link above and a Moderator will assist you.
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 38806916
I agree, slightwv deserv points for his solution as well.

talahi:
You can still re-open the question by clicking on 'Request attention' link
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38806932
Thanks aj but I'm good.
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 38806981
I appreciate your efforts that you worked on alternative way and wish talahi to do the same.

We should try to set example that good and working solutions are eligbible for points even though question is closed
0
 

Author Comment

by:talahi
ID: 38806986
No one appreciates the help more than I do so I did request and additional points go to slightwv.  Thanks again.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38806990
Thanks guys!  It is appreciated.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38807001
talahi,

If you do play with both methods and their timing, please post the results.  Aside from my own curiosity (and I'm sure ajexpert would like to know), it can help future people that come across this question.
0
 
LVL 15

Expert Comment

by:Franck Pachot
ID: 38811599
Hi,

I just see the question re-opened. Let me propose a more optimized query that will avoid reading the second_table if it finds at least one row from the first one:
SELECT point_seq                                
        FROM first_table
       WHERE exists (select point_name from first_table where rownum=1)
union all
SELECT point_seq                               
        FROM second_table
       WHERE not exists (select point_name from first_table where rownum=1)
/

Open in new window


The key is 'rownum=1' and the FILTER in the execution plan. Look at the following example, especially the Starts column showing that you don't readthe second_table.

Build the example:
drop table first_table;
drop table second_table;
alter session set statistics_level=all;
set linesize 200 pagesize 1000
column point_name format a10 trunc
create table first_table  (point_seq not null, point_name) tablespace users as select rownum,lpad('x',4000,'x') from dual connect by level <=1000;
create table second_table  (point_seq not null, point_name) tablespace users as select * from first_table;
create unique index first_table on first_table(point_seq);
create unique index second_table on second_table(point_seq);

Open in new window


Execution plan with rows in first_table:
-----------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |              |      1 |        |   1000 |00:00:00.01 |      80 |   2 |
|   1 |  UNION-ALL              |              |      1 |        |   1000 |00:00:00.01 |      80 |   2 |
|*  2 |   FILTER                |              |      1 |        |   1000 |00:00:00.01 |      76 |   2 |
|   3 |    INDEX FAST FULL SCAN | FIRST_TABLE  |      1 |   1057 |   1000 |00:00:00.01 |      72 |   0 |
|*  4 |    COUNT STOPKEY        |              |      1 |        |      1 |00:00:00.01 |       4 |   2 |
|   5 |     INDEX FAST FULL SCAN| FIRST_TABLE  |      1 |   1057 |      1 |00:00:00.01 |       4 |   2 |
|*  6 |   FILTER                |              |      1 |        |      0 |00:00:00.01 |       4 |   0 |
|   7 |    INDEX FAST FULL SCAN | SECOND_TABLE |      0 |    894 |      0 |00:00:00.01 |       0 |   0 |
|*  8 |    COUNT STOPKEY        |              |      1 |        |      1 |00:00:00.01 |       4 |   0 |
|   9 |     INDEX FAST FULL SCAN| FIRST_TABLE  |      1 |   1057 |      1 |00:00:00.01 |       4 |   0 |
-----------------------------------------------------------------------------------------------------------

Open in new window


And with no rows in the first_table:
-----------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |              |      1 |        |   1000 |00:00:00.01 |      84 |   3 |
|   1 |  UNION-ALL              |              |      1 |        |   1000 |00:00:00.01 |      84 |   3 |
|*  2 |   FILTER                |              |      1 |        |      0 |00:00:00.01 |       7 |   0 |
|   3 |    INDEX FAST FULL SCAN | FIRST_TABLE  |      0 |   1057 |      0 |00:00:00.01 |       0 |   0 |
|*  4 |    COUNT STOPKEY        |              |      1 |        |      0 |00:00:00.01 |       7 |   0 |
|   5 |     INDEX FAST FULL SCAN| FIRST_TABLE  |      1 |   1057 |      0 |00:00:00.01 |       7 |   0 |
|*  6 |   FILTER                |              |      1 |        |   1000 |00:00:00.01 |      77 |   3 |
|   7 |    INDEX FAST FULL SCAN | SECOND_TABLE |      1 |    894 |   1000 |00:00:00.01 |      72 |   3 |
|*  8 |    COUNT STOPKEY        |              |      1 |        |      0 |00:00:00.01 |       5 |   0 |
|   9 |     INDEX FAST FULL SCAN| FIRST_TABLE  |      1 |   1057 |      0 |00:00:00.01 |       5 |   0 |
-----------------------------------------------------------------------------------------------------------

Open in new window


You see: the FILTER operation executes only one of the branches, and the condition evaluation need only to read one row.

Regards,
Franck.
0
 

Author Closing Comment

by:talahi
ID: 38811693
Thanks for Filter input and will try it.  Only re-opened to award multiple answers from yesterday.

If I could give everyone 500 points I would but moderator limited points to 500 total.  Thanks again for the help and sorry I didn't get to this now to close it again.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38811761
No problem.  A correct answer is more important than the points for many of us here.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now