Home » SQL & PL/SQL » SQL & PL/SQL » Need a "Select" Before "Cursor" (Oracle 10g)
Need a "Select" Before "Cursor" [message #304589] Wed, 05 March 2008 14:55 Go to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member
Hello everyone,

I need to get a date from a table and base my Select in my Cursor on that date.

Is there a way to perform a "Select... Into ... " go get this date before the Cursor statement?

Any help would be appreciated.

Thanks,
Lou
Smile
Re: Need a "Select" Before "Cursor" [message #304590 is a reply to message #304589] Wed, 05 March 2008 14:59 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
>Is there a way to perform a "Select... Into ... " go get this date before the Cursor statement?
Yes or just make it a sub-select in the WHERE clause
Re: Need a "Select" Before "Cursor" [message #304591 is a reply to message #304590] Wed, 05 March 2008 15:09 Go to previous messageGo to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member
Well, here's the thing....

I don't have the date to select on. There are many records in the table with only 2 different dates. One is a future date and the other is near the current date. I only want to select one of these two dates based upon a parm passed.

If the parm is 'Y', then I want only the future date. I still don't know what the actual date is.

How can I formulate the Sub-Select in the Where clause if I don't know the actual date to select on?

Thanks,
Lou
icon10.gif  Re: Need a "Select" Before "Cursor" [message #304599 is a reply to message #304590] Wed, 05 March 2008 15:49 Go to previous messageGo to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member
I've got it to the point where I can get the date from another table Smile , but the Parameter tells me which date to use.

How can I do this:

Cursor Get_Recs is

Select * From Table1
Where Table_Date = (
If Parm_Flag = 'F' Then (Select Future_Date From Table2)
Else (Select Current_Date From Table2))

Records Get_Recs%ROWTYPE;

I hope you get the idea, but I'm trying to formulate my Select in my Cursor based upon a parm.

Any Ideas????

Thanks,
Lou
Re: Need a "Select" Before "Cursor" [message #304606 is a reply to message #304599] Wed, 05 March 2008 16:28 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
DECODE
Re: Need a "Select" Before "Cursor" [message #304778 is a reply to message #304606] Thu, 06 March 2008 10:01 Go to previous messageGo to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member
Barbara,

What do you mean

"DECODE"

???


Re: Need a "Select" Before "Cursor" [message #304780 is a reply to message #304589] Thu, 06 March 2008 10:14 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
pcd_lou,
too bad for you that both the search function on this forum & GOOGLE seem to be broken for you.
Re: Need a "Select" Before "Cursor" [message #304782 is a reply to message #304778] Thu, 06 March 2008 10:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://tahiti.oracle.com/

Regards
Michel
Re: Need a "Select" Before "Cursor" [message #304791 is a reply to message #304778] Thu, 06 March 2008 12:06 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Join your tables and use DECODE to specify which column you want to compare based on the parameter value. You can search the online documentation for the DECODE syntax. I have provided an example below.

SCOTT@orcl_11g> VARIABLE parm_flag VARCHAR2(1)
SCOTT@orcl_11g> EXEC :parm_flag := 'F'

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT d.deptno, d.dname, e.deptno, e.ename, e.comm
  2  FROM   dept d, emp e
  3  WHERE  d.deptno = DECODE (:parm_flag, 'F', e.deptno, e.comm / 10)
  4  /

    DEPTNO DNAME              DEPTNO ENAME            COMM
---------- -------------- ---------- ---------- ----------
        10 ACCOUNTING             10 CLARK
        10 ACCOUNTING             10 KING
        10 ACCOUNTING             10 MILLER
        20 RESEARCH               20 JONES
        20 RESEARCH               20 FORD
        20 RESEARCH               20 ADAMS
        20 RESEARCH               20 SMITH
        20 RESEARCH               20 SCOTT
        30 SALES                  30 WARD              500
        30 SALES                  30 TURNER              0
        30 SALES                  30 ALLEN             300
        30 SALES                  30 JAMES
        30 SALES                  30 BLAKE
        30 SALES                  30 MARTIN           1400

14 rows selected.

SCOTT@orcl_11g> EXEC :parm_flag := 'T'

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> /

    DEPTNO DNAME              DEPTNO ENAME            COMM
---------- -------------- ---------- ---------- ----------
        30 SALES                  30 ALLEN             300

SCOTT@orcl_11g> 

Re: Need a "Select" Before "Cursor" [message #304803 is a reply to message #304791] Thu, 06 March 2008 15:42 Go to previous messageGo to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member
Barbara,

Thank you for your example. I researched the fomatting and function of the DECODE on this site and I don't think it will work for me in this instance. I did learn a lot and will probably use it in another case, but I don't see it here.

The parm that is passed tells the program which date to use from another table.

I need to get a row from a table with two dates using a parm for what row to get. Then use a parm to choose which date to use in the Select. I need to do this:

Begin
Select Date1, Date2 Into v_Date1, v_Date2
From Table1
Where Code = v_Code_Parm;
End;

If v_Date_Switch_Parm = 'Y' Then
s_Date := v_Date1;
Else
s_Date := v_Date2;

Cursor is Get_Recs
Select * From Table2
Where Row_Date = s_Date;

My_Rec Get_Recs%RowType;

Begin
Processing........

This is what I need to accomplish. I am not that knowlegable in PL/SQL so there may be a better or different way to do this. But this flow is what I need. I can get either date based upon the parm, but I still need the Cursor to Select on one of the dates from the other table.

Any Ideas?

Thanks,
Lou
Re: Need a "Select" Before "Cursor" [message #304822 is a reply to message #304803] Thu, 06 March 2008 17:28 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
It really helps if you copy and paste genuine stuff instead of changing table names and column names and variable names from post to post. Please read and follow the forum guidelines. I was trying to avoid spoon feeding and get you to think for yourself, but apparently it has come down to proving that what I am suggesting works, so here is a more complete example for you. You can get everything you need for your cursor in one select.

-- test data:
SCOTT@orcl_11g> SELECT * FROM table1
  2  /

DATE1     DATE2           CODE
--------- --------- ----------
06-MAR-08 07-MAR-08          1

SCOTT@orcl_11g> SELECT * FROM table2
  2  /

ROW_DATE  SOME_COL
--------- --------------------
06-MAR-08 current data
07-MAR-08 future data


-- procedure:
SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE your_proc
  2    (v_code_parm	 IN table1.code%TYPE,
  3  	date_switch_parm IN VARCHAR2)
  4  AS
  5    CURSOR get_recs IS
  6    SELECT t2.*
  7    FROM   table2 t2, table1 t1
  8    WHERE  t2.row_date = DECODE (date_switch_parm, 'Y', t1.date1, t1.date2)
  9    AND    t1.code = v_code_parm;
 10    my_rec get_recs%ROWTYPE;
 11  BEGIN
 12    OPEN get_recs;
 13    DBMS_OUTPUT.PUT_LINE ('----------------');
 14    LOOP
 15  	 FETCH get_recs INTO my_rec;
 16  	 EXIT WHEN get_recs%NOTFOUND;
 17  	 DBMS_OUTPUT.PUT_LINE (my_rec.row_date);
 18  	 DBMS_OUTPUT.PUT_LINE (my_rec.some_col);
 19  	 DBMS_OUTPUT.PUT_LINE ('----------------');
 20    END LOOP;
 21  END your_proc;
 22  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.


-- execution:
SCOTT@orcl_11g> SET SERVEROUTPUT ON
SCOTT@orcl_11g> EXEC your_proc (1, 'Y')
----------------
06-MAR-08
current data
----------------

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> EXEC your_proc (1, 'F')
----------------
07-MAR-08
future data
----------------

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> 


Re: Need a "Select" Before "Cursor" [message #305021 is a reply to message #304822] Fri, 07 March 2008 09:39 Go to previous message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member
Barbara,

Yes! That's it!

Thank you.

I didn't copy my code in to the message, because there's a lot of stuff around it that was irronious and proprietary.

I think what you sent will work just fine.

Thank you very much!
--------------------

Smile

Lou
Previous Topic: How to execute a procedure from within a package?
Next Topic: hot to clean queues oracle message queues
Goto Forum:
  


Current Time: Fri Dec 09 11:43:00 CST 2016

Total time taken to generate the page: 0.08417 seconds