November 7, 2002 at 6:48 pm
I am using case end statement for classifying a set of 60,000 records. I have e.g. 3 fields Sgrp varchar(10), prodcode char(5), itemcode char(8) and I use the below mentioned text for updating the sgrp field. While running the command I get error mentioned in the subject.
update sale2003 set sgrp=
(case
when prodcode="MCBSL" then (case when substring(itemcode,5,1)="I" then "SL-ISO"
when left(itemcode,2)="SB" and substring(itemcode,3,3)="SP0" then "SL-SPB"
when left(itemcode,2)="SC" and substring(itemcode,3,3)="SP0" then "SL-SPC"
Else "SL-MP" End)
When prodcode="MCBGL" then (case When substring(itemcode,5,1)="I" then "GL-ISO"
When right(itemcode,2) between 6 and 32 then "GL-0632"
when right(itemcode,3)='7P5' then "GL-0632"
When right(itemcode,2) between 40 and 63 then "GL-4063"
when (right(itemcode,1) between 1 and 5) and right(itemcode,2) not in ('63') then "GL-U/R"
When right(itemcode,3)="100" then "GL-100"
Else "GL-DC/WAC" End)
When prodcode="HRC" then (case when left(itemcode,2) in ('IE','IF') then "HRC-BOLT"
when left(itemcode,2)="IW" then "HRC-DIN"
Else "HRC-FSTRIP" end)
When prodcode="DBS" then (case When left(itemcode,6)="KSPSAW" then "DBS-ACBOX"
When left(itemcode,4)="KELS" then "DBS-ELE"
When left(itemcode,3)="KSS" then "DBS-KSS"
When left(itemcode,3)="KPS" then "DBS-KPS"
When left(itemcode,4)="KSTI" then "DBS-KSTI"
When left(itemcode,3)="KSL" then "DBS-SUPL"
When left(itemcode,4)="KSPS" then "DBS-KSPS"
When left(itemcode,5)="KSCWIW" then "DBS-WHLINE"
Else "DBS-OTH" End)
When prodcode="TE" then (case When left(itemcode,3)="ITC" then "TE-CO"
Else "TE-SDF" End)
When prodcode="RCCB" then (case when right(itemcode,1)="1" and left(itemcode,2)="ID" then "RCNO-ETI"
when right(itemcode,1)="1" and left(itemcode,2)="IP" then "RCPJ-ETI"
when right(itemcode,1)="0" and left(itemcode,2)="ID" then "RCNO-DOEPK"
when right(itemcode,1)="0" and left(itemcode,2)="IP" then "RCPJ-DOEPK"
End)
Else prodcode End)
Is there any other way to do this (thru cursor or)
Prabin Misra
Prabin Misra
November 8, 2002 at 1:33 am
The operation : -
'When right(itemcode,2) between 6 and 32 then "GL-0632'
Could only be used if you use a numeric value instaed of right(itemcode,2).
Regards,
Andy Jones
.
November 8, 2002 at 10:01 pm
Can I use the value function to convert that to value. e.g. value(right(itemcode,2)). In that case also how can it convert the P5 strings ?
Prabin Misra
Prabin Misra
November 9, 2002 at 11:04 am
You need to evaluate your expression so that datatypes are common and explicitly CAST them to common datatypes when needed (implicitly allowing SQL to deteremine this does not behave the same in all versions of SQL so it is better to always handle explicitly).
Yor problem is area like
When right(itemcode,2) between 40 and 63 then "GL-4063"
Where the value returned may be a number or even a string which does not compare to an INT in anyway.
You need to cast the value to an INT using CAST. However again your string could be something like AA which cannot be CASTed to INT and will fail. But if you check with the ISNUMERIC function first you can know if it is safe and return the value or return a numerice value that is outside of your between range (I prefer to use -1 since I rarely look for negative values in a between).
Anyway try this and see if it helps.
update sale2003 set sgrp=
(
case prodcode
when 'MCBSL' then
(
case
when substring(itemcode,5,1)='I' then 'SL-ISO'
when left(itemcode,2)='SB' and substring(itemcode,3,3)='SP0' then 'SL-SPB'
when left(itemcode,2)='SC' and substring(itemcode,3,3)='SP0' then 'SL-SPC'
Else 'SL-MP'
End
)
When 'MCBGL' then
(
case
When substring(itemcode,5,1)='I' then 'GL-ISO'
When
(
CASE
WHEN ISNUMERIC(right(itemcode,2)) THEN CAST(right(itemcode,2) AS INT)
ELSE -1
END
)
between 6 and 32 then 'GL-0632'
when right(itemcode,3)='7P5' then 'GL-0632'
When
(
CASE
WHEN ISNUMERIC(right(itemcode,2)) THEN CAST(right(itemcode,2) AS INT)
ELSE -1
END
)
between 40 and 63 then 'GL-4063'
when
(
CASE
WHEN ISNUMERIC(right(itemcode,1)) THEN CAST(right(itemcode,1) AS INT)
ELSE -1
END
)
between 1 and 5) and right(itemcode,2) != '63' then 'GL-U/R'
When right(itemcode,3)='100' then 'GL-100'
Else 'GL-DC/WAC'
End
)
When 'HRC' then
(
case
when left(itemcode,2) in ('IE','IF') then 'HRC-BOLT'
when left(itemcode,2)='IW' then 'HRC-DIN'
Else 'HRC-FSTRIP'
end
)
When 'DBS' then
(
case
When left(itemcode,6)='KSPSAW' then 'DBS-ACBOX'
When left(itemcode,4)='KELS' then 'DBS-ELE'
When left(itemcode,3)='KSS' then 'DBS-KSS'
When left(itemcode,3)='KPS' then 'DBS-KPS'
When left(itemcode,4)='KSTI' then 'DBS-KSTI'
When left(itemcode,3)='KSL' then 'DBS-SUPL'
When left(itemcode,4)='KSPS' then 'DBS-KSPS'
When left(itemcode,5)='KSCWIW' then 'DBS-WHLINE'
Else 'DBS-OTH'
End
)
When 'TE' then
(
case
When left(itemcode,3)='ITC' then 'TE-CO'
Else 'TE-SDF'
End
)
When 'RCCB' then
(
case
when right(itemcode,1)='1' and left(itemcode,2)='ID' then 'RCNO-ETI'
when right(itemcode,1)='1' and left(itemcode,2)='IP' then 'RCPJ-ETI'
when right(itemcode,1)='0' and left(itemcode,2)='ID' then 'RCNO-DOEPK'
when right(itemcode,1)='0' and left(itemcode,2)='IP' then 'RCPJ-DOEPK'
End
)
Else prodcode
End
)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply