Databases
StackOverflow2013
Postsdbo
POUsing 2 cursors, one to get a parameter for the other
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUsing 2 cursors, one to get a parameter for the other
    primarykey
    data
    text
    <p>I am doing an exercies that requires me to use 2 explicit cursors. One cursor is to get a parameter for the second. The end goal is to find the most recent 'rental date' for each car based on registration. For example, registration 345JKL was rented on the 01/06/2010, 07/09/2011 and 08/09/2013. I want this to only return the most recent date which is 08/09/2013 and I want it to provide a most recent date for every registration in the table.</p> <p>I am aware that there are better ways to do this such as MAX, subqueries etc (both of which I am not allowed to use), but as a 'lateral thinking exercise' I am required to do it without inbuilt functions, subqueries and the rest of the things that make life easy.</p> <p>I am a little stuck with this one.</p> <p>This is what I have so far which is getting me nowhere:</p> <pre><code> declare v_maxdate DATE; v_reg VARCHAR2(20); cursor reg_cur IS SELECT * FROM i_car; v_car reg_cur%ROWTYPE; cursor c_reg (reg i_booking.registration%TYPE) IS SELECT date_reserved from i_booking WHERE registration = reg; v_date c_reg%ROWTYPE; begin FOR v_date IN c_reg (v_car.registration) LOOP v_maxdate := '01/JAN/90'; If v_date &gt; v_maxdate THEN v_maxdate := v_date; end if; end loop; end; </code></pre> <p>It is throwing me this error:</p> <pre><code>If v_date &gt; v_maxdate THEN * ERROR at line 17: ORA-06550: line 17, column 11: PLS-00306: wrong number or types of arguments in call to '&gt;' ORA-06550: line 17, column 1: PL/SQL: Statement ignored </code></pre> <p>I figured rather than continuing to bang my head on the desk I would ask for guidance.</p> <p>Your help is appreciated.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USSplunk
    1. USSplunk
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COYou do not need a cursor. You can just happily join the two tables.
      singulars
      1. POUsing 2 cursors, one to get a parameter for the other
      1. USwildplasser
    2. COAs I said, part of the exercise is to do it this way, which is a bugger but it is what it is.
      singulars
      1. POUsing 2 cursors, one to get a parameter for the other
      1. USSplunk
    3. COYeah cursors are like smoking if you have not started dont. Most of the companies I have worked for using a cursor normally resulted in getting into serious hot water. Go look at RBAR and understand why we dont use cursors in the real world.
      singulars
      1. POUsing 2 cursors, one to get a parameter for the other
      1. USNamphibian
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload