1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

CURSOR with INNER JOINS

Discussion in 'SQL PL/SQL' started by pditty8811, Feb 7, 2014.

  1. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    I have a cursor with inner joins but I'm getting errors. Do you see any errors in my syntax?

    Here is my syntax:

    Code (Text):


    SET SERVEROUTPUT ON;
      accept x NUMBER(4,0) prompt 'Enter CUSTID: ';
     
    DECLARE
      vCustid NUMBER(4,0);
      vCname VARCHAR2(100);
      vOrders VARCHAR2(100);
     
    BEGIN
      vCustid := &x;
     
      CURSOR vCname_cur IS
      SELECT CNAME
      FROM CUSTOMERS
      WHERE CUSTID = vCustid;
       
      CURSOR vOrders_cur IS  
      SELECT SUM(INVENTORY.PRICE)
      FROM ORDERS
        INNER JOIN ORDERITEMS
        ON ORDERS.ORDERID = ORDERITEMS.ORDERID
          INNER JOIN INVENTORY
          ON ORDERITEMS.PARTID = INVENTORY.PARTID
      WHERE CUSTID = vCustid;

      OPEN vCname_cur;
      FETCH vCname_cur INTO vCname;

      OPEN vOrders_cur;
      FETCH vOrders_cur INTO vOrders;

      vOrders := TO_CHAR(vOrders, '$9G999D00');
     
      DBMS_OUTPUT.put_line(vCname || '  ' || vOrders);
     
      CLOSE vCname_cur;
      CLOSE vOrders_cur;

    EXCEPTION
     WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.put_line('No data was found - '||SQLERRM);
      WHEN OTHERS THEN
        DBMS_OUTPUT.put_line('An error was encountered - '||SQLERRM);
    END;

    Errors:

    Code (Text):
    Error report -
    ORA-06550: line 9, column 10:
    PLS-00103: Encountered the symbol "VCNAME_CUR" when expecting one of the following:

       := . ( @ % ;
    ORA-06550: line 17, column 11:
    PLS-00103: Encountered the symbol "JOIN" when expecting one of the following:

       , ; for group having intersect minus order start union where
       connect
    06550. 00000 -  "line %s, column %s:\n%s"
    *Cause:    Usually a PL/SQL compilation error.
    *Action:
     
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Cursors are placed in the DECLARE section:


    Code (SQL):

    SET SERVEROUTPUT ON;
      accept x NUMBER(4,0) prompt 'Enter CUSTID: ';
     
    DECLARE
      vCustid NUMBER(4,0);
      vCname VARCHAR2(100);
      vOrders VARCHAR2(100);
      CURSOR vCname_cur IS
      SELECT CNAME
      FROM CUSTOMERS
      WHERE CUSTID = vCustid;
     
      CURSOR vOrders_cur IS  
      SELECT SUM(INVENTORY.PRICE)
      FROM ORDERS
        INNER JOIN ORDERITEMS
        ON ORDERS.ORDERID = ORDERITEMS.ORDERID
          INNER JOIN INVENTORY
          ON ORDERITEMS.PARTID = INVENTORY.PARTID
      WHERE CUSTID = vCustid;
     
     
    BEGIN
      vCustid := &x;
     
      OPEN vCname_cur;
      FETCH vCname_cur INTO vCname;
     
      OPEN vOrders_cur;
      FETCH vOrders_cur INTO vOrders;
     
      vOrders := TO_CHAR(vOrders, '$9G999D00');
     
      DBMS_OUTPUT.put_line(vCname || '  ' || vOrders);
     
      CLOSE vCname_cur;
      CLOSE vOrders_cur;
     
    EXCEPTION
     WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.put_line('No data was found - '||SQLERRM);
      WHEN OTHERS THEN
        DBMS_OUTPUT.put_line('An error was encountered - '||SQLERRM);
    END;
     
     
  3. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130

    I still get errors:

    Code (Text):
    ORA-06550: line 7, column 11:
    PLS-00103: Encountered the symbol "=" when expecting one of the following:

       constant exception <an identifier>
       <a double-quoted delimited-identifier> table LONG_ double ref
       char time timestamp interval date binary national character
       nchar
    The symbol "<an identifier>" was substituted for "=" to continue.
    ORA-06550: line 17, column 11:
    PLS-00103: Encountered the symbol "JOIN" when expecting one of the following:

       , ; for group having intersect minus order start union where
       connect
    06550. 00000 -  "line %s, column %s:\n%s"
    *Cause:    Usually a PL/SQL compilation error.
    *Action: