Oracle functions with long decodes/cases

Question

Hi lads,

I have another simple one. How to make a long decode inside a function in oracle?

My select looks like this:

select something, sum(ofsomethingelse)
from a_table
where code in 
('390','391','392','393','394','395','396','397','398','400','402','406',
'407','408','409','410','411','412','413','414','416','418','471','473',
'1734','1742','1735','1736','1737','1738','1739','1740','1741','1745',
'1748','1752','1760','1753','1754','1755','1756','1757','1758','1759',
'1763','1766','1902','1904','1003','1011','1004','1005','106','1007',
'1008','1009','1010','1159','1161','1015','1023','1016','1017','1018',
'1019','1020','1021','1022','1164','1166','1189','1191','1201','1209',
'1202','1203','1204','205','1206','1207','1208','1356','1358','1213',
'1221','1214','1215','1216','1217','1218','1219','1220','1361','1363',
'1386','1388','1401','1409','1402','1403','1404','1405','1406','1407',
'1408','1557','1559','1413','1421','1414','1415','1416','1417','1418',
'1419','1420','1562','1564','1587','1589','9033','9034','9035','9036',
'9037','9038','909','9040','9049','9050','9051','9052')
group by something
order by 1

And I have a couple more of large code lists like that which I want to turn into one neat query.

Something like:

CREATE OR REPLACE FUNCTION grouping_func (id_in IN varchar2)
RETURN varchar2
AS
res varchar(255);
BEGIN
   res := CASE id_in
            WHEN id_in in ([long list of ids from query1]) THEN 'Group1'
            WHEN id_in in ([long list of ids from query2]) THEN 'Group2'
            WHEN id_in in ([long list of ids from query3]) THEN 'Group3'
            ELSE id_in
          END;
    RETURN res;
END;

so I can have a clear query that just uses this function into the group by and everything the way I like :)

The problem is I cant use that id_in in ([long list of ids from query3]) in the switch cases and I'm quite a n00b in plsql...

May I get suggestions of elegant ways of doing it?

thanks!

f.

Answer 1

Here is a possible solution: create 2 tables :

create table GROUPS 
(
GRP_ID INTEGER,
GRP_NAME VARCHAR2(20) // name of the group
);

create table LONGLIST
(
LL_ID INTEGER,
LL_NAME  VARCHAR2(20) // item of your big list
GRP_ID INTEGER // (foreign key)
);

This way you only need to join the tables, no CASE or DECODE needed

The final query would look something like that :

select g.grp_name, sum(ofsomethingelse)
from a_table a
inner join longlist ll on ll.ll_name = a.code
inner join groups g on g.grp_id = ll.grp_id
group by g.grp_name

Answer 2

Just make sure your long lists of ids do not intersect.

CREATE OR REPLACE FUNCTION grouping_func(id_in IN varchar2) RETURN varchar2 AS
  res varchar2(255);
BEGIN
  select gr
    into retval
    from (select 'Group1' gr
            from dual
           where id_in in ('[long list of ids from query1]')
          union all
          select 'Group2' gr
            from dual
           where id_in in ('[long list of ids from query2]')
          union all
          select 'Group3' gr
            from dual
           where id_in in ('[long list of ids from query3]'));

  exception 
    when no_data_found then
     return null;
    when too_many_rows then
     return null;    
END;

Not the brightest thing, I guess, but will work for your function. And, yes, it's better to store these codes in a separate table you could join into your query, indeed.

Answer 3

Actually, the only problem with your first stab is that you've mixed up the two syntaxes of the CASE expression.

If you follow the CASE keyword with an expression (e.g. id_in), then you are doing a switch on the value of that expression, and each of the WHEN clauses must include a single expression that will be checked for equality against the first expression.

Alternatively, you can skip the expression immediately after CASE, and specify a full boolean condition in each WHEN clause.

So, either of these should work for you:

   res := CASE id_in
         WHEN 390 THEN 'Group1'
         WHEN 391 THEN 'Group1'
         WHEN 392 THEN 'Group2'
         ...etc...

   res := CASE
            WHEN id_in in ([long list of ids from query1]) THEN 'Group1'
            WHEN id_in in ([long list of ids from query2]) THEN 'Group2'
            WHEN id_in in ([long list of ids from query3]) THEN 'Group3'
            ELSE id_in
          END;

Note that overall, I agree with others that the preferable way to do this is store the mapping of ID values to groups in another table and change the query to a join.