I am very new to this type of sql and currently cannot figure out what is wrong with this code

set server output on
declaring
 cust name varchar2;
 b date date;
 description varchar2(100);
 veh varchar2(50);
start
 select c.first_name || ', ' || c.surname, b.bill_date, di.description, v.vehicle_type
 into cust_name, b_date, description, veh
 from customer c, billing b, delivery_items di, driver_deliveries dd, vehicle v, staff s
 where c.customer_id = b.customer_id
 and s.staff_id = bill.staff_id
 and s.staff_id = di.staff_id
 and di.delivery_item_id = dd.delivery_item_id
 and v.vin_number = dd.vin_number
 and b.bill_date = '10 November 2016'
 dbms_output.put_line('CUSTOMER: ' || cust_name);
 dbms_output.put_line('BILL DATE: ' || b_date);
 dbms_output.put_line('DESCRIPTION: ' || description);
 dbms_output.put_line('VEHICLE: ' || vehicle)
end;

1 Answers

2
Gordon Linoff On

There are multiple small mistakes. Does this work?

declare
  v_custname varchar2(200;
  v_b_date date;
  v_description varchar2(100);
  v_veh varchar2(50);
begin
  select c.first_name || ', ' || c.surname, b.bill_date, di.description, v.vehicle_type
  into v_custname, v_b_date, v_description, v_veh
  from customer c join
       billing b
       on c.customer_id = b.customer_id join
       staff s
       on s.staff_id = bill.staff_id join
       delivery_items di
       on s.staff_id = di.staff_id join
       driver_deliveries dd
       on di.delivery_item_id = dd.delivery_item_id join
       vehicle v
       on v.vin_number = dd.vin_number;

  where b.bill_date = date '2016-11-10';

  dbms_output.put_line('CUSTOMER: ' || v_custname);
  dbms_output.put_line('BILL DATE: ' || v_b_date);
  dbms_output.put_line('DESCRIPTION: ' || v_description);
  dbms_output.put_line('VEHICLE: ' || v_veh)
end;

This assumes