Re: Is there a CASE statement in PL/SQL?

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1997/07/30
Message-ID: <33dee201.1629315@www.sigov.si>#1/1

On Tue, 29 Jul 1997 15:05:01 +0100, "Jonathan Atkinson" <jonathan.atkinson_at_foundationsystems.co.uk> wrote:

> ....[SNIP].....
>I wonder if there is a PL/SQL equivalent of the SELECT CASE statement to
>replace all the ELSE IFs as shown in the function below?
>
>Also, is it viable and/or worthwhile to write these functions in PRO*C?
>
>CREATE OR REPLACE FUNCTION WEEKBEG (indate DATE) RETURN DATE IS
> day_number INTEGER;
> minus_days INTEGER;
>BEGIN
>-- THIS FN RETURNS THE DATE OF THE MONDAY OF THE WEEK CONTAINING
>-- THE DATE. THIS IS THE PREVIOUS MONDAY IF THE DATE IS NOT A MONDAY
>-- AND THE DATE VALUE ITSELF IF IT IS A MONDAY.
>-- THIS MIMICS THE SQLBASE @WEEKBEG function.
>
>CREATE OR REPLACE FUNCTION WEEKBEG (indate DATE) RETURN DATE IS
> .....[SNIP THE FUNCTION'S LOGIC]....
>-- Return Monday / Week start
>RETURN indate - minus_days;
>END WEEKBEG;
>/
>

There is no CASE statement in PL/SQL (at least until ver. 2.3), so you have to use IF-ELSIF constructs. Sometimes you can mimic the construct using Oracle's DECODE sql function.

For your particular example (date of Monday of the week containing particular date) you can do it in a single simple SQL statement:

SELECT TRUNC(indate,'D') FROM dual;

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Wed Jul 30 1997 - 00:00:00 CDT