jeudi 26 août 2021

Oracle If-else statement issues

Hi guys im planning to generate my report using stored procedure (oracle), but what i want to display is if the procedure is executed, i want the end of the report message to be ( End of the report for "specified year" and if there isnt any data at all i want to display ( " NO Record for "specified year" )

CREATE OR REPLACE PROCEDURE booking_pending_report(in_month IN NUMBER,in_year IN NUMBER) AS

cursor pending_booking_basedondate_cursor is 
    select bo.journeystatus As "Journey Status",bo.bookingdate As "Booking Date",bo.fromdestination As "Location From",bo.todestination As "Desired Location", co.customerid As "Customer ID", co.customername As "Customer Name", co.customertel AS "Customer Phone No", concat(TRUNC((SYSDATE - TO_DATE(co.customerdob, 'DD-MON-YYYY'))/ 365.25),' years old') as "Customer Age", co.customergender as "Gender"
    from booking bo, customer co
    where bo.journeystatus = 'Pending' and bo.bookingdate between to_date('01-'||in_month||'-'||in_year,'dd-mm-yyyy') AND to_date('30-'||in_month||'-'||in_year,'dd-mm-yyyy') and bo.customerid = co.customerid
    group by bo.journeystatus, bo.fromdestination,bo.todestination, bo.bookingdate, co.customerid, co.customername, co.customertel, co.customerdob, co.customergender
    order by bookingdate;


pending_booking_basedondate_record pending_booking_basedondate_cursor%ROWTYPE;
v_count number(2);
v_countforchecking number(2);

BEGIN

DBMS_OUTPUT.PUT_LINE('-----------------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE(rpad('.',30)||rpad('ON DEMAND REPORT',34)); 
DBMS_OUTPUT.PUT_LINE(rpad('.',6)||'Booking that are still on "Pending" in year '||in_year); 
DBMS_OUTPUT.PUT_LINE('-----------------------------------------------------------------------------');


OPEN pending_booking_basedondate_cursor;
v_count:=1;
v_countforchecking :=0;

Loop
 fetch pending_booking_basedondate_cursor into pending_booking_basedondate_record;

  if pending_booking_basedondate_cursor%NOTFOUND and v_countforchecking = 1 then
   DBMS_OUTPUT.PUT_LINE(rpad('.',23)||'End of RECORD for the year '||in_year||'!!!');
   CLOSE pending_booking_basedondate_cursor;
   EXIT;
  elsif pending_booking_basedondate_cursor%NOTFOUND then
  DBMS_OUTPUT.PUT_LINE(rpad('.',23)||'NO RECORD for the year '||in_year||'!!!');
  CLOSE pending_booking_basedondate_cursor;
  EXIT;
  else
    DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
    DBMS_OUTPUT.PUT_LINE('('||v_count||')'||' Customer ('||pending_booking_basedondate_record."Customer Name"||')');
    DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
    DBMS_OUTPUT.PUT_LINE('Booking Status               :'||pending_booking_basedondate_record."Journey Status");
    DBMS_OUTPUT.PUT_LINE('Booking Date                 :'||pending_booking_basedondate_record."Booking Date");
    DBMS_OUTPUT.PUT_LINE('Location From                :'||pending_booking_basedondate_record."Location From");
    DBMS_OUTPUT.PUT_LINE('Location From                :'||pending_booking_basedondate_record."Desired Location");
    DBMS_OUTPUT.PUT_LINE('Customer Age                 :'||pending_booking_basedondate_record."Customer Age");
    DBMS_OUTPUT.PUT_LINE('Customer Phone number        :'||pending_booking_basedondate_record."Customer Phone No");
    DBMS_OUTPUT.PUT_LINE('Customer Gender              :'||pending_booking_basedondate_record."Gender");
  v_count:=v_count+1;
  v_countforchecking := v_countforchecking+1;
 end if;
END Loop;


DBMS_OUTPUT.PUT_LINE('-----------------------------------------------------------------------------'); 
DBMS_OUTPUT.PUT_LINE(rpad('.',30)||'END OF REPORT'); 
DBMS_OUTPUT.PUT_LINE('-----------------------------------------------------------------------------'); 
END;
/

I want my report to be something like this (IF theres record)

SQL> exec booking_pending_report(3,2021)

----------------------------------------------------------------------------
.                             ON DEMAND REPORT
.     Booking that are still on "Pending" in year 2021
-----------------------------------------------------------------------------
--------------------------------------------------
(1) Customer (Hollee)
--------------------------------------------------
Booking Status               :Pending
Booking Date                 :22/MAR/2021
Location From                :Kedah
Location From                :Pahang
Customer Age                 :31 years old
Customer Phone number        :013-5941276
Customer Gender              :Female
--------------------------------------------------
(2) Customer (Casey)
--------------------------------------------------
Booking Status               :Pending
Booking Date                 :24/MAR/2021
Location From                :Perak
Location From                :Johor
Customer Age                 :21 years old
Customer Phone number        :016-3818244
Customer Gender              :Female
.                     **End OF RECORD** for the year 2021!!!
-----------------------------------------------------------------------------
.                             END OF REPORT
-----------------------------------------------------------------------------

but the output kept giving me this result!

SQL> exec booking_pending_report(3,2021)

-----------------------------------------------------------------------------
.                             ON DEMAND REPORT
.     Booking that are still on "Pending" in year 2021
-----------------------------------------------------------------------------
--------------------------------------------------
(1) Customer (Hollee)
--------------------------------------------------
Booking Status               :Pending
Booking Date                 :22/MAR/2021
Location From                :Kedah
Location From                :Pahang
Customer Age                 :31 years old
Customer Phone number        :013-5941276
Customer Gender              :Female
--------------------------------------------------
(2) Customer (Casey)
--------------------------------------------------
Booking Status               :Pending
Booking Date                 :24/MAR/2021
Location From                :Perak
Location From                :Johor
Customer Age                 :21 years old
Customer Phone number        :016-3818244
Customer Gender              :Female
.                      **NO RECORD** for the year 2021!!! >> it doesnt make sense as there is record
-----------------------------------------------------------------------------
.                             END OF REPORT
-----------------------------------------------------------------------------

I only wanna display the no record when:

SQL> exec booking_pending_report(3,2028)
-----------------------------------------------------------------------------
.                             ON DEMAND REPORT
.     Booking that are still on "Pending" in year 2028
-----------------------------------------------------------------------------
.                      **NO RECORD** for the year 2028!!!
-----------------------------------------------------------------------------
.                             END OF REPORT
-----------------------------------------------------------------------------

I suspect there is something wrong with my if else statement within the loop! Please help!

Aucun commentaire:

Enregistrer un commentaire