Adding Descriptions for Order Status Codes

Question

Code:

CREATE OR REPLACE FUNCTION STATUS_DESC_SF 
(
  P_STATUS_ID IN bb_basketstatus.IDSTAGE%type 
)
RETURN VARCHAR2 AS 
    lv_idstage bb_basketstatus.IDSTAGE%type;

BEGIN
    select (CASE
            WHEN IDSTAGE = 1 THEN
                'Order submitted'
            WHEN IDSTAGE = 2 THEN
                'Accepted, sent to shipping'
            WHEN IDSTAGE = 3 THEN
                'Back-ordered'
            WHEN IDSTAGE = 4 THEN
                'Cancelled'
            ELSE
                'Shipped'
            END) STAGE_ID
        INTO lv_idstage
    from bb_basketstatus
    where idbasket = P_STATUS_ID;
  RETURN lv_idstage;
END STATUS_DESC_SF;

select idstage, status_desc_sf(idstage) status_desc
from bb_basketstatus
where idbasket = 4;

Question:

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.

Story:

I know I wrote the stored function correctly because when broke it out and have the select statement by itself, it works. When I add it to the function, it doesn't work. I have tried switching the parameter and the local variable lv_idstage, it does nothing. I do get something back from the sql code but the description of the status is 1 | (null) for idstage and status_desc. If you have figured it out already, can you please explain how this is getting a "(null)" to me? I'm not sure how it's doing that.

Thanks for you help!!

Answer 1

It seems you don't need a query inside your function, only a CASE statement:

CREATE OR REPLACE FUNCTION STATUS_DESC_SF (
    P_STATUS_ID IN bb_basketstatus.IDSTAGE%type)
    RETURN VARCHAR2 AS 
  lv_status_desc varchar2(100);

BEGIN
  lv_status_desc :=  
       CASE WHEN P_STATUS_ID = 1 THEN 'Order submitted'
            WHEN P_STATUS_ID = 2 THEN 'Accepted, sent to shipping'
            WHEN P_STATUS_ID = 3 THEN 'Back-ordered'
            WHEN P_STATUS_ID = 4 THEN 'Cancelled'
            ELSE 'Shipped' END;

  return lv_status_desc;
end;