CODEWITHSUNDEEP
sqloracle-databaseplsql
filippo
filippo

Reputation: 5803

Oracle functions with long decodes/cases

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.

Upvotes: 0

Views: 860

Answers (3)

Dave Costa
Dave Costa

Reputation: 48131

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.

Upvotes: 1

Kirill Leontev
Kirill Leontev

Reputation: 10941

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.

Upvotes: 0

guigui42
guigui42

Reputation: 2500

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

Upvotes: 2

Related Questions