Using Decode

Posted on 2002-03-19
Last Modified: 2007-11-27
I'm using a Decode statement in my SQL.
To my knowledge Decode have unlimited arguments. I getting an ORA-00939: error-too many arguments in my function.
I looking for some help in make my Decode function more efficient

'250083',       'CBR-AIT'
'229190',       'CEPS'
'229157',       'ROS'
'249830',       'ROS'
'229159',       'ROS'
'229161',       'ROS'
'229162',       'ROS'
'229164',       'ROS'
'229166',       'ROS'
'229167',       'ROS'
'229156',       'ROS'
'228637',      'ACBPS'
'228638',      'ACBPS'
'229146',      'ACBPS'
'229149',      'ACBPS'
'229145',      'ACBPS'
'262425',      'ACBPS'
'262426',      'ACBPS'
'257103',      'AITBCSIVR'
'257102',      'AITBCSIVR'
'228632',      'APROMS-P/L'
'162206',      'Arkansas Wireless'
'170629',      'ARL',
'206640',      'AUDIOTEXT'
'206641',      'AUDIOTEXT'
'206642',      'AUDIOTEXT'
'206644',      'AUDIOTEXT'
'206645',      'AUDIOTEXT'
'206646',      'AUDIOTEXT'
'206643',      'AUDIOTEXT'
'129332',      'AUDIOTEXT'
'228630',      'Bank One'
'228631',      'Bank One'
'211552',      'BI'
'211559',      'BI'
'211553',      'BI'
'211554',      'BI'
'211555',      'BI'
'211556',      'BI'
'211557',      'BI'
'211558',      'BI'
'146870',      'BI'
'153442',      'BRMC'
'211256',      'BSC'
'211257',      'BSC'
'211258',      'BSC'
'211262',      'BSC'
'166701',      'BSC'
'211259',      'BSC'
'211260',      'BSC'
'211261',      'BSC'
'211276',      'BSC'
'103079',      'CALL TRACE IVR'
'130801',      'CALLNOTES'
'223828',      'CALLNOTES'
'180577',      'CALLSTAR'
'217566',      'CARS-IVR'
'129018',      'CCCC'
'249908',      'CCS/CTI'
'249909',      'CCS/CTI'
'249910',      'CCS/CTI'
'249911',      'CCS/CTI'
'249912',      'CCS/CTI'
'249913',      'CCS/CTI'
'249914',      'CCS/CTI'
'249915',      'CCS/CTI'
'249916',      'CCS/CTI'
'249917',      'CCS/CTI'
'257097',      'CCT_AIT_BCS_INBOUND_FRAME'
'257101',      'CCT_AIT_BCS_INBOUND_FRAME'
'257091',      'CCT_AIT_BCS_INBOUND_FRAME'
'257092',      'CCT_AIT_BCS_INBOUND_FRAME'
'257093',      'CCT_AIT_BCS_INBOUND_FRAME'
'257094',      'CCT_AIT_BCS_INBOUND_FRAME'
'257095',      'CCT_AIT_BCS_INBOUND_FRAME'
'257096',      'CCT_AIT_BCS_INBOUND_FRAME'
'257098',      'CCT_AIT_PAYROLL'
'257099',      'CCT_AIT_PAYROLL'
'257100',      'CCT_AIT_PAYROLL'
'222845',      'CCTPBG'
'227192',      'CCTPBG'
'227193',      'CCTPBG'
'227194',      'CCTPBG'
'227195',      'CCTPBG'
'227196',      'CCTPBG'
'227197',      'CCTPBG'
'250946',      'CHD'
'208611',      'CISL'
'208610',      'CISL'
'208604',      'CISL'
'208605',      'CISL'
'208606',      'CISL'
'208607',      'CISL'
'208608',      'CISL'
'208609',      'CISL'
'142805',      'CISL'
'170615',      'CNAB'
'253758',      'COESN'
'162202',      'CRIXC'
'266751',      'CTS-IVR'
'65398',      'CVAS'
'211282',      'CVAS'
'211281',      'CVAS'
'216629',      'DSLIVR'
'216628',      'DSLIVR'
'226357',      'EBOND'
'226355',      'EBOND'
'255113',      'ELOC'
'248046',      'ERMS'
'248047',      'ERMS'
'248048',      'ERMS'
'227293',      'ESP-IVR'
'228636',      'ESP-IVR'
'141748',      'FOD'
'228639',      'FORD-IVR'
'229150',      'FORD-IVR'
'217341',      'FTWLOC'
'213725',      'FTWLOC'
'186185',      'Hotline'
'219901',      'INFOLINE'
'219898',      'INFOLINE'
'219899',      'INFOLINE'
'219902',      'INFOLINE'
'219904',      'INFOLINE'
'219905',      'INFOLINE'
'219903',      'INFOLINE'
'219900',      'INFOLINE'
'65401',      'INFOLINE'
'201695',      'JVR'
'145726',      'KCNOC'
'201685',      'KCSSC'
'211157',      'MEL'
'170603',      'MEL'
'171288',      'MIPS'
'160070',      'MISL'
'214666',      'OUTBOUND'
'139817',      'OUTBOUND'
'225163',      'PBWAI'
'253760',      'PBWAI'
'215219',      'PCC-IVR'
'212891',      'PMD'
'211264',      'QS'
'206650',      'QS'
'206651',      'QS'
'206649',      'QS'
'206648',      'QS'
'206653',      'QS'
'206647',      'QS'
'206652',      'QS'
'142796',      'QS'
'159312',      'RMC'
'211269',      'RMC'
'211270',      'RMC'
'211271',      'RMC'
'211272',      'RMC'
'211273',      'RMC'
'211274',      'RMC'
'211275',      'RMC'
'211268',      'RMC'
'211265',      'RSC'
'194861',      'RSC'
'195254',      'RSC'
'195250',      'RSC'
'195252',      'RSC'
'195257',      'RSC'
'195251',      'RSC'
'195256',      'RSC'
'195249',      'RSC'
'211226',      'SIMPSOL'
'211757',      'SIMPSOL'
'201694',      'SLAM'
'150072',      'SPORT'
'167623',      'TEL-A-BID'
'250947',      'TTRC'
'243215',      'USSC'
'243213',      'USSC'
'243214',      'USSC'
'243217',      'USSC'
'243216',      'USSC'
'123264',      'VRAMS'
'253768',      'WCCBOST'
'253770',      'WCCNY'
'253773',      'WCCPHILLY'
'253771',      'WCCSNET'
'253769',      'WCCWASH'
'253762',      'WRMCBOST'
'253763',      'WRMCNY'
'253766',      'WRMCPHILLY'
'253764',      'WRMCSNET') AS ItemName
Question by:Igwin
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +5
LVL 11

Accepted Solution

pennnn earned 200 total points
ID: 6880955
I think that the best thing you can do is to store all that information in a databse table, instead of typing it in a query.
The table will be relatively small and will contain two columns: ITEMNumber and ItemName (put an index on the ItemNumber column!).
Then in your query you should join the new table using the ItemNumber and select the ItemName.
This approach will also help you maintain the item names, so if anything changes you can go change it in the table istead of changing your code.
Hope that helps!

Author Comment

ID: 6880968
I would like my Decode to work if possible.
LVL 11

Expert Comment

ID: 6881026
You could try nesting the decode statement.
It should be something like this:
decode(substr(itemNumber, 1,2),
'25', decode(itemNumber,
             '250083',      'CBR-AIT'
-- list here all items which start with '25'
-- ....
'21', decode(itemNumber,
             '229190',      'CEPS'
-- and so on...

This means that you have to group them in some way so they are not over the limit...
If you are using substr or any other function, then you won't be able to utilize the index on the itemNumber column (if any).
Hope that helps!
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

ID: 6881035
You could split your decode into several by cascading the function: The last argument is the 'else', so this would be something like:

'250083',      'CBR-AIT'
'229190',      'CEPS'
'229157',      'ROS'
'249830',      'ROS'
'229159',      'ROS'
'229161',      'ROS'
'229162',      'ROS'
'229164',      'ROS'
'229166',      'ROS'
'229167',      'ROS'
'229156',      'ROS',
  '228637',     'ACBPS'
  '228638',     'ACBPS'
  '229146',     'ACBPS'
  '229149',     'ACBPS'
  '229145',     'ACBPS'
  '262425',     'ACBPS'
  '262426',     'ACBPS'



Good luck!
LVL 35

Expert Comment

by:Mark Geerlings
ID: 6881137
I agree with pennnn's first suggestion: use a database table instead.  That is a much simpler and usually better approach than a massive "decode" statement that will need to be changed each time a new item is added.

If you disagree, please tell us the advantage you see in the "decode" approach.

Expert Comment

ID: 6881154
Decode does have limits:

See Oracle7,8,8i SQL Reference Guid
"The maximum number of components in the DECODE expression, including expr, searches, results, and default is 255."

Expert Comment

ID: 6881170
A reminder:
Same thing is Oracle9i SQL Reference:
"The maximum number of components in the DECODE function, including expr, searches, results, and default is 255."
LVL 22

Expert Comment

ID: 6881613
   I agree with the general consensus that a table is the best approach.  However, I will answer you question.  Rather than nest the decodes, I'd suggest the CASE statement (new for 8i):

select case
when ITEMNumber = '250083' then 'CBR-AIT'
when ITEMNumber = '229190' then 'CEPS'
when ITEMNumber in ('229157','249830','229159','229161','229162',
'229164','229166','229167','229156') then 'ROS'
when ITEMNumber in ('228637','228638','229146','229149','229145','262425','262426') then 'ACBPS'
when ITEMNumber in ('257103','257102') then 'AITBCSIVR'
when ITEMNumber = '228632' then 'APROMS-P/L'
when ITEMNumber = '162206' then 'Arkansas Wireless'
when ITEMNumber = '170629' then 'ARL',
when ITEMNumber in ('206640','206641','206642','206644','206645','206646','206643','129332') then 'AUDIOTEXT'
when ITEMNumber in ('228630','228631') then 'Bank One'
when ITEMNumber in ('211552','211559','211553',
'211554','211555','211556','211557','211558','146870') then 'BI'
when ITEMNumber = '153442' then 'BRMC'
when ITEMNumber = ('211256','211257','211258','211262',
'166701','211259','211260','211261','211276') then 'BSC'
when ITEMNumber = '103079' then 'CALL TRACE IVR'
when ITEMNumber in ('130801','223828') then 'CALLNOTES'
when ITEMNumber = '180577' then 'CALLSTAR'
when ITEMNumber = '217566' then 'CARS-IVR'
when ITEMNumber = '129018' then 'CCCC'
when ITEMNumber in ('249908','249909','249910','249911','249912',
'249913','249914','249915','249916','249917') then 'CCS/CTI'
when ITEMNumber in ('257097','257101','257091','257092',
'257093','257094','257095',257096') then     'CCT_AIT_BCS_INBOUND_FRAME'
when ITEMNumber in ('257098','257099','257100') then 'CCT_AIT_PAYROLL'
when ITEMNumber in ('222845','227192','227193',
'227194','227195','227196','227197') then 'CCTPBG'
when ITEMNumber = '250946' then 'CHD'
<whole bunch skipped, but you get the idea>
ELSE 'Undefined'
AS ItemName

Good luck!

Expert Comment

ID: 6881668

The case function also has same limits:
Oracle8i SQL Reference Release 3 (8.1.7)
Chapter: 5  Expressions, Conditions, and Queries
Section:  CASE Expressions
to quote:
 The maximum number of arguments in a CASE expression is 255, and each WHEN ... THEN pair counts as two arguments. To avoid exceeding the limit of 128 choices, you can nest CASE expressions. That is expr1 can itself be a CASE expression.

While you can nest them or approach the solution like Dr.SQL says,   that still makes a pretty bulky sql statement.
I concur with pennnn & markgeer --  a lookup table would be a better approach.


LVL 22

Expert Comment

ID: 6881756
  I think we're in agreement, but to clarify - when several values evaluate to the same case (the "in" lists above), then a CASE is preferrable to a decode.  You can have more value translations in a CASE statement than in a decode as long as you average better than two values per case.  Is it ugly?  Extremely.  Is it the sort of bizarre stipulation that jsp programmers get all the time?  Oh, yes.

Expert Comment

ID: 6882011

There shouldn't be any reason not to use a database table like you're doing. You can still accomplish the same thing in a single query, written as a join (there are other ways too):

select b.item_name
from sometable a, item_catalog b
where a.item_id = b.item_id

You will get better performance and less headaches than trying to put a table's worth of information into your query.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Create Index on a Materialized View 5 60
dbms_crypto.decrypt   errors out 6 59
Oracle Mulit-site configuration 28 73
Check for any ASM patches and install them. 1 23
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question