DECODE problems

From: Prasanna Wadkar <R2770C_at_WACCVM.CORP.MOT.COM>
Date: Wed, 10 Jan 1996 15:20:32 -0700
Message-Id: <9601102255.AA06023@alice.jcc.com>

Hi,

I have two questions regarding DECODE,

1. Can I use DECODE in a UPDATE statement on the left hand side of SET?

   UPDATE temp

      SET DECODE(update_var, 1, col1,
                             2, col2) = update_value;

   I have 8 columns to be updated based on the value of update_var. I can always use the IF-THEN-ELSE IF construct or a Dynamic SQL in PL/SQL, but just wanted to know if this can be done and also to keep the code size small.

2. Is there any restriction on using DECODE in a FUNCTION or PROCEDURE ?

   While using DECODE in a SELECT statement in a PL/SQL function / procedure, I get the following error,

ERROR

---

PL/SQL: SQL Statement ignored
PLS-00307: too many declarations of 'DECODE' match this call

    The SELECT statement is like this,

        SELECT DECODE(SUBSTR(period, 3, 1), 'W', week_beg_dt,


                                            'M', mth_beg_dt,


                                            'Q', qtr_beg_dt),


               DECODE(SUBSTR(period, 3, 1), 'W', week_end_dt,


                                            'M', mth_end_dt,


                                            'Q', qtr_end_dt)


          INTO start_dt, end_dt
          FROM calendr
         WHERE year_num = SUBSTR(period, 1, 2)
           AND DECODE(SUBSTR(period, 3, 1),
                        'W', wk_num,
                        'M', mth_num,
                        'Q', qtr_num)
                        = TO_NUMBER(SUBSTR(period, 4, 2));

period can have values from '95W01' - '95W52' or '95M01' - '95M12'

The above statement works fine if I execute it as a regular SQL statement in SQL*PLUS, but if I try to use DECODE in a FUNCTION or PROCEDURE I get the above error. Earlier too I had the same problem , but at that time I just rewrote the statement using IF-THEN-ELSE. Now I want to know if DECODE does not work in a function or procedure or do I have to code DECODE in a different manner in func. / proc. ?

Thanks in Advance.
Prasanna. Received on Wed Jan 10 1996 - 17:55:28 CST