dtstagetype orderdate begin select sdtstage bdtordered

Dtstagetype orderdate begin select sdtstage bdtordered

This preview shows page 5 - 6 out of 6 pages.

ship_date bb_basketstatus.dtstage%type; order_date bb_basket.dtordered%type; begin select s.dtstage, b.dtordered into ship_date, order_date from bb_basketstatus s natural join bb_basket b where idbasket = basket_id and idstage = 5; if (ship_date - order_date > 1) then return 'CHECK'; else return 'OK'; end if; end; Assignment 6-6: Adding Descriptions for Order Status Codes When a shopper returns to the Web site to check an order's status, information from the BB_BASKETSTATUS table is displayed. However, only the status code is available in the BB_BASKETSTATUS table, not the status description. Create a function named STATUS_DESC_SF that accepts a stage ID and returns the status description. The descriptions for stage IDs are listed in Table 6-3. Test the function in a SELECT statement that retrieves all rows in the BB_BASKETSTATUS table for basket 4 and displays the stage ID and its description. TABLE 6-3 Basket Stage Descriptions Stage ID Description 1 Order Submitted 2 Accepted, sent to shipping 2 Back-ordered 4 Cancelled 5 Shipped
Image of page 5
create or replace function status_desc_sf (stage_id bb_basketstatus.idstage%type) return varchar2 is description varchar2(26); begin if (stage_id = 1) then description := 'Order submitted'; elsif (stage_id = 2) then description := 'Accepted, sent to shipping'; elsif (stage_id = 3) then description := 'Backordered'; elsif (stage_id = 4) then description := 'Cancelled'; else description := 'Shipped'; end if; return description; end; / select dtstage, rpad(status_desc_sf(idstage), 26) "Status Description" from bb_basketstatus where idbasket = 4;
Image of page 6

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture