Solved

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

Posted on 2013-01-22
25
519 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
  • 4
  • +3
25 Comments
 
LVL 77

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 70

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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 77

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 77

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 32

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 77

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
 

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 77

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 77

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 77

Expert Comment

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

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 77

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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 video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

751 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