CASE, DECODE, If Then...

[alembong]

First, I am not much of a database person. I usually just develop Crystal Reports off of databases. So, I apologize in advance if I'm not being clear.

We have a huge stored procedure set up for one of our reports. There are tons of DECODE statements. There is a set of DECODE lines that are related and are giving me the fields I need. New logic needs to be added so that when it is prior to a certain year, this logic stays as is, otherwise it needs to follow a set of different logic.

Oversimplified, what I want is this:
If Year > 2005
Then X
Else Y

However, Y = 10 sets of DECODE statements that define 10 values. Likewise, X would also need to define 10 values. I would prefer not to re-write the set in Y because each one of the 10 values can consist of 169 DECODEs. It's just messy.

I tried

CASE 
WHEN Year > '2005' 
THEN DECODE(a,b,c,0) value1, DECODE (c,d,e,0) value2,
ELSE DECODE(a,b,DECODE(z,y,x,0),0) value1, DECODE(c,d,DECODE(m,n,o,0),0) value2,
END

but I kept getting missing keyword. So, any help would be greatly appreciate, and if I need to explain anything further let me know.

 

[LKBrwnDBA]

If the CASE statement is NOT within a SELECT statement, try this:

CASE 
WHEN Year > '2005' 
THEN value1:=DECODE(a,b,c,0); value2:=DECODE (c,d,e,0);
ELSE value1:=DECODE(a,b,DECODE(z,y,x,0),0); value2:=DECODE(c,d,DECODE(m,n,o,0),0)
END;

Else If the CASE statement is within a query, then it can only return ONE value.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[alembong]

Unfortunately, it is within a SELECT statement.

Can I use the DECODE to include Year > '2005'? None of the DECODEs that I've seen address > or < values, so I wasn't sure I could use it and/or how to use it.

 

[SantaMufasa]

Pogi,

There are several syntactical issues with your code:

1) (as LK mentions) a CASE statement can return only one expression.
2) Your "dangling" comma prior to the "END" causes Oracle to look for yet another expression.

Here is working code that simulates your situation. I've used literals instead of variables since I don't have your actual code and values (but just swap out the literals for your variables):

col value1 format a6
col value2 format a6
col a heading "Text" format a
select 'Hello' a
      ,CASE when to_char(sysdate,'YYYY')>'2005'
            then decode('a','b','c',0)
            else decode('a','b',decode('z','y','x',0),0)
            end value1
      ,CASE when to_char(sysdate,'YYYY')>'2005'
            then decode('c','d','e',0)
            else decode('c','d',decode('m','n','o',0),0)
            end value2
from dual
/

Text  VALUE1 VALUE2
----- ------ ------
Hello 0      0

1 row selected.

Let us know if this is useful.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[ChrisHunt]

169 DECODEs? That is messy.

Could you not rewrite them as a PL/SQL function stored in the database, or have some kind of lookup table? Maintaining queries with that many DECODEs must be a nightmare.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[alembong]

Oh, I totally agree that maintaining these queries are a nightmare!! But understanding them enough to change them to something else completely is out of my expertise.

I did end up with something that works out using SIGN in my first DECODE:

DECODE(SIGN(TO_NUMBER(VG.BUDGET_FISCAL_YEAR) - TO_NUMBER('2005')), 1, DECODE('a','b','c',0), DECODE('a','b',decode('z','y','x',0),0)) value 1

Thanks everyone for suggestions and maybe one day when I learn more of this stuff, I can update these stored procs so they aren't so crazy!