CODEWITHSUNDEEP
oracle-databaseplsql
Vegeta ZA
Vegeta ZA

Reputation: 70

Selecting a specific date with a PL/SQL statement

I need to display Customer, Bill Date, Description and Vehicle from the following tables: CUSTOMER, BILLING, DELIVERY_ITEMS, VEHICLE

I do not understand the error that I get when I try to compile the code.

Here's my code:

 DECLARE
  FNAME VARCHAR2(20);
  SNAME VARCHAR2(20);
  BILLDATE DATE;
  DESCRIP VARCHAR2(50);
  VEH VARCHAR2(30);

  BEGIN
  SELECT FIRST_NAME, SURNAME, BILL_DATE, DESCRIPTION, VEHICLE_TYPE
  INTO FNAME, SNAME, BILLDATE, DESCRIP, VEH
   FROM CUSTOMER, BILLING, DELIVERY_ITEMS, VEHICLE
   WHERE BILL_DATE ('10/NOV/16', 'DD/MM/YY')<DATE;


    DBMS_OUTPUT.PUT_LINE('CUSTOMER : '|| FNAME  ||','|| SNAME);
    DBMS_OUTPUT.PUT_LINE('BILL DATE: '|| BILLDATE);
    DBMS_OUTPUT.PUT_LINE('DESCRIPTION: '|| DESCRIP);
    DBMS_OUTPUT.PUT_LINE('VEHICLE: '|| VEH);
    END;
/

I am getting the following error report:

Error report -
ORA-06550: line 12, column 51:
PL/SQL: ORA-00936: missing expression
ORA-06550: line 9, column 7:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action

Thanks in advance!

Upvotes: 0

Views: 159

Answers (3)

Avrajit Roy
Avrajit Roy

Reputation: 3303

Very first question that has to be asked is are you expecting singular resultset or multi row resultset? If singular result set is expected then the WHERE condition has to be modifed as illustrated below

DECLARE
  FNAME    VARCHAR2(20);
  SNAME    VARCHAR2(20);
  BILLDATE DATE;
  DESCRIP  VARCHAR2(50);
  VEH      VARCHAR2(30);
BEGIN
  SELECT FIRST_NAME,
    SURNAME,
    BILL_DATE,
    DESCRIPTION,
    VEHICLE_TYPE
  INTO FNAME,
    SNAME,
    BILLDATE,
    DESCRIP,
    VEH
  FROM CUSTOMER,
    BILLING,
    DELIVERY_ITEMS,
    VEHICLE
  WHERE BILL_DATE = TO_DATE('10/NOV/16', 'DD/MON/YY');
  DBMS_OUTPUT.PUT_LINE('CUSTOMER : '|| FNAME ||','|| SNAME);
  DBMS_OUTPUT.PUT_LINE('BILL DATE: '|| BILLDATE);
  DBMS_OUTPUT.PUT_LINE('DESCRIPTION: '|| DESCRIP);
  DBMS_OUTPUT.PUT_LINE('VEHICLE: '|| VEH);
END;

And if you are expexcting multiple results then should always go with BULK Collect. In this case as just for illustration purpose i am using row by row processing. Hope this helps

BEGIN
  FOR I IN
  (SELECT FIRST_NAME,
    SURNAME,
    BILL_DATE,
    DESCRIPTION,
    VEHICLE_TYPE
  FROM CUSTOMER,
    BILLING,
    DELIVERY_ITEMS,
    VEHICLE
  WHERE BILL_DATE = TO_DATE('10/NOV/16', 'DD/MON/YY')
  )
  LOOP
    DBMS_OUTPUT.PUT_LINE('CUSTOMER : '|| I.FIRST_NAME ||','|| I.SURNAME);
    DBMS_OUTPUT.PUT_LINE('BILL DATE: '|| I.BILL_DATE);
    DBMS_OUTPUT.PUT_LINE('DESCRIPTION: '|| I.DESCRIPTION);
    DBMS_OUTPUT.PUT_LINE('VEHICLE: '|| I.VEHICLE_TYPE);
  END LOOP;
END;

Upvotes: 0

Praveen
Praveen

Reputation: 9335

Try for loop if your select statement returns more than one row

BEGIN
    for i in (
         SELECT FIRST_NAME, SURNAME, BILL_DATE, DESCRIPTION, VEHICLE_TYPE
         FROM CUSTOMER, BILLING, DELIVERY_ITEMS, VEHICLE
         WHERE TO_DATE('10/NOV/16', 'DD/MM/YY') < BILL_DATE
    ) loop
        DBMS_OUTPUT.PUT_LINE('CUSTOMER : '|| i.FIRST_NAME  ||','|| i.SURNAME);
        DBMS_OUTPUT.PUT_LINE('BILL DATE: '|| i.BILL_DATE);
        DBMS_OUTPUT.PUT_LINE('DESCRIPTION: '|| i.DESCRIPTION);
        DBMS_OUTPUT.PUT_LINE('VEHICLE: '|| i.VEHICLE_TYPE);
    end loop;
END;
/

Upvotes: 0

Aleksej
Aleksej

Reputation: 22949

You have a problem in 

WHERE BILL_DATE ('10/NOV/16', 'DD/MM/YY')<DATE

Assuming that you have a date field in one of your tables (let's name it DATE_FIELD), you can apply a condition (= for example) on it with:

WHERE DATE_FIELD = TO_DATE ('10/NOV/16', 'DD/MM/YY')

or, given that you have a variable BILL_DATE, you can do:

WHERE DATE_FIELD = BILL_DATE

Of course, before using the variable BILL_DATE, you need an assignment, for example:

BILL_DATE := TO_DATE ('10/NOV/16', 'DD/MM/YY')

Besides, if your query returns more than one row, you have to choose:

  1. limit your dataset with some condition, or simply add AND ROWNUM = 1 for a test
  2. use a cursor and loop through it (see an example)
  3. use a BULK COLLECT ( see here for example)

Upvotes: 1

Related Questions