Igwin
asked on
Using Decode
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
Thank
Decode(ITEMNumber,
'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
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
Thank
Decode(ITEMNumber,
'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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!
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!
You could split your decode into several by cascading the function: The last argument is the 'else', so this would be something like:
Decode(ITEMNumber,
'250083', 'CBR-AIT'
'229190', 'CEPS'
'229157', 'ROS'
'249830', 'ROS'
'229159', 'ROS'
'229161', 'ROS'
'229162', 'ROS'
'229164', 'ROS'
'229166', 'ROS'
'229167', 'ROS'
'229156', 'ROS',
Decode(ITEMNumber,
'228637', 'ACBPS'
'228638', 'ACBPS'
'229146', 'ACBPS'
'229149', 'ACBPS'
'229145', 'ACBPS'
'262425', 'ACBPS'
'262426', 'ACBPS'
...
))
Good luck!
Decode(ITEMNumber,
'250083', 'CBR-AIT'
'229190', 'CEPS'
'229157', 'ROS'
'249830', 'ROS'
'229159', 'ROS'
'229161', 'ROS'
'229162', 'ROS'
'229164', 'ROS'
'229166', 'ROS'
'229167', 'ROS'
'229156', 'ROS',
Decode(ITEMNumber,
'228637', 'ACBPS'
'228638', 'ACBPS'
'229146', 'ACBPS'
'229149', 'ACBPS'
'229145', 'ACBPS'
'262425', 'ACBPS'
'262426', 'ACBPS'
...
))
Good luck!
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.
If you disagree, please tell us the advantage you see in the "decode" approach.
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."
See Oracle7,8,8i SQL Reference Guid
"The maximum number of components in the DECODE expression, including expr, searches, results, and default is 255."
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."
Same thing is Oracle9i SQL Reference:
"The maximum number of components in the DECODE function, including expr, searches, results, and default is 255."
lqwin,
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','1 29332') 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!
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
'229164','229166','229167'
when ITEMNumber in ('228637','228638','229146
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
when ITEMNumber in ('228630','228631') then 'Bank One'
when ITEMNumber in ('211552','211559','211553
'211554','211555','211556'
when ITEMNumber = '153442' then 'BRMC'
when ITEMNumber = ('211256','211257','211258
'166701','211259','211260'
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
'249913','249914','249915'
when ITEMNumber in ('257097','257101','257091
'257093','257094','257095'
when ITEMNumber in ('257098','257099','257100
when ITEMNumber in ('222845','227192','227193
'227194','227195','227196'
when ITEMNumber = '250946' then 'CHD'
<whole bunch skipped, but you get the idea>
ELSE 'Undefined'
AS ItemName
Good luck!
Well
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.
dBalaski
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.
dBalaski
dbalaski,
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.
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.
lgwin,
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.
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.
ASKER