CODEWITHSUNDEEP
oracle-databaseplsql
SchizoidCoder
SchizoidCoder

Reputation: 15

Trouble with SQL Function & Case returns

Keeping it simple, I've got a table with a column called IDSTAGE holding a number (1 through 5) and am trying to create a function to take that number and return a description. Heres what I've got:

FUNCTION STATUS_DESC_SF (p_idstage IN BB_BASKETSTATUS.IDSTAGE%TYPE)
RETURN VARCHAR2
AS
BEGIN
    CASE
        WHEN p_idstage = 1 THEN RETURN '1 - Order Submitted'
        WHEN p_idstage = 2 THEN RETURN '2 - Accepted, sent to shipping'
        WHEN p_idstage = 3 THEN RETURN '3 - Back-ordered'
        WHEN p_idstage = 4 THEN RETURN '4 - Cancelled'
        WHEN p_idstage = 5 THEN RETURN '5 - Shipped'
        ELSE RETURN 'Null'
    END CASE;
END STATUS_DESC_SF;

When I run this, and show err i get the following for every line beginning with when.

6/3      PLS-00103: Encountered the symbol "WHEN" when expecting one of
         the following:
         * & = - + ; < / > at in is mod remainder not rem
         <an exponent (**)> <> or != or ~= >= <= <> and or like like2
         like4 likec between || multiset member submultiset
         The symbol ";" was substituted for "WHEN" to continue.

I've done my due diligence and double checked my syntax and as far as I can tell, I've not done anything wrong. I suspect it doesn't like the THEN RETURN part, but thats not where the error is appearing.

Upvotes: 0

Views: 79

Answers (2)

SchizoidCoder
SchizoidCoder

Reputation: 15

So the issue was the lack of semicolons at the end of the then result lines. In SQL they aren't needed, but within a PLSQL Function they are.

Corrected code:

FUNCTION STATUS_DESC_SF
    (p_idstage IN BB_BASKETSTATUS.IDSTAGE%TYPE)
    RETURN VARCHAR2
AS
BEGIN
    CASE
        WHEN p_idstage = 1 THEN RETURN '1 - Order Submitted';
        WHEN p_idstage = 2 THEN RETURN '2 - Accepted, sent to shipping';
        WHEN p_idstage = 3 THEN RETURN '3 - Back-ordered';
        WHEN p_idstage = 4 THEN RETURN '4 - Cancelled';
        WHEN p_idstage = 5 THEN RETURN '5 - Shipped';
        ELSE RETURN 'Null';
    END CASE;
END;

Thanks to kutschkem for providing a good resource for learning proper syntax.

Upvotes: 0

kutschkem
kutschkem

Reputation: 8163

Try this instead:

RETURN CASE
    WHEN p_idstage = 1 THEN '1 - Order Submitted'
    WHEN p_idstage = 2 THEN '2 - Accepted, sent to shipping'
    WHEN p_idstage = 3 THEN '3 - Back-ordered'
    WHEN p_idstage = 4 THEN '4 - Cancelled'
    WHEN p_idstage = 5 THEN '5 - Shipped'
    ELSE 'Null'
END;

Case is an expression

Upvotes: 1

Related Questions