February 26, 2008 at 8:24 am
Hello comunity
I have the following cursor, and i need to evaluate a condition using CASE, but i this error occured:
[Microsoft][SQL Native Client][SQL Server]Case expressions may only be nested to level 10.
DECLARE RunningTotalCursor
CURSOR LOCAL FAST_FORWARD FOR
SELECT conta=cm1.u_tpterc,
' ',
'Saldo Inicial',
0,
sum(edeb) as edeb,
sum(ecred) as ecred
FROM cc,
cm1
WHERE cc.cm=cm1.cm
AND (cm1.u_tpterc like '211%'
OR cm1.u_tpterc like '212%'
OR cm1.u_tpterc like '213%'
OR cm1.u_tpterc like '214%'
OR cm1.u_tpterc like '216%'
OR cm1.u_tpterc like '219%'
OR cm1.u_tpterc like '269%' )
AND cc.no like substring(#1#,1,5)
AND year(datalc)<#4# and CC.FACCSTAMP=' '
GROUP BY cm1.u_tpterc
UNION all
SELECT conta=cm1.u_tpterc,
datalc,
cc.cmdesc,
nrdoc=(case when origem in ('CC','FT') then cc.nrdoc else case when cc.origem='LE' then (select distinct letraini from le (nolock), cc cc1(nolock) where cc1.ccstamp=le.lestamp and cc1.ccstamp=cc.ccstamp) else case when origem in ('RD') then cc.nrdoc else nrdoc end end end ),
edeb,
ecred=(case when origem in('CC','FT','LE','RE') then cc.ecred else case when origem = 'RD' then (cc.ecred-(eivav1+eivav2+eivav3+eivav4+eivav5+eivav6+eivav7+eivav8+eivav9)) else 0 end end)
FROM cc,
cm1
WHERE cc.cm=cm1.cm
AND (cm1.u_tpterc like '211%'
OR cm1.u_tpterc like '212%'
OR cm1.u_tpterc like '213%'
OR cm1.u_tpterc like '214%'
OR cm1.u_tpterc like '216%'
OR cm1.u_tpterc like '219%'
OR cm1.u_tpterc like '269%' )
AND cc.no like substring(#1#,1,5)
AND year(datalc)=#4#
AND month(datalc) between #2# and #3#
UNION all
SELECT CONTA='211',
DATALC as Data,
CMDESC,
cast( NRDOC as varchar(15)) as nrdoc,
0 as edeb,
(CASE WHEN CC.CM IN (71,72) THEN FACC.ETOTAL ELSE 0 END) AS 'ecred'
FROM FACC,
CC
WHERE FACC.FACCSTAMP=CC.FACCSTAMP
AND FACC.NO like substring(#1#,1,5)
AND year(CC.dataLC)=#4#
AND month(CC.dataLC) between #2# and #3# AND CM IN (71,72)
UNION all
--
SELECT (case when lr.u_pagpagar=0 and lr.u_pagchpd=0 then '212' else case when lr.u_pagpagar=1 and lr.u_pagchpd=0 then '215' else '213' end end) as Conta,
'',
'Saldo Inicial',
0,
sum(case when lm.cm=20 then lr.evalor else 0 end) as edeb,
sum(case when lm.cm=21 or lm.cm=18 or lm.cm=3 then lr.evalor else 0 end) as ecred
FROM lr (nolock),
lm (nolock)
WHERE lr.lrstamp=lm.lrstamp
AND lr.letra=lm.letra
AND lr.no like substring(#1#,1,5)
AND lm.cm in(20,21,18,3)
AND year(lm.data)<#4#
GROUP BY lr.u_pagpagar, lr.u_pagchpd
UNION all
SELECT (case when lr.u_pagpagar=0 and lr.u_pagchpd=0 then '212' else case when lr.u_pagpagar=1 and lr.u_pagchpd=0 then '215' else '213' end end) as Conta,
lm.data,
[highlight=#ffff11](case when lr.u_pagpagar=0 and lr.u_pagchpd=0 and lm.cm=20 then 'Aceite LT'
else
case when lr.u_pagpagar=1 and lr.u_pagchpd=0 and lm.cm=20 then 'Aceite PG'
else
case when lr.u_pagpagar=0 and lr.u_pagchpd=1 and lm.cm=20 then'Aceite CHPD'
else
case when lr.u_pagpagar=0 and lr.u_pagchpd=0 and lm.cm=21 then 'DVL-LT'
else
case when lr.u_pagpagar=1 and lr.u_pagchpd=0 and lm.cm=21 then 'DVL-PG'
else
case when lr.u_pagpagar=0 and lr.u_pagchpd=1 and lm.cm=21 then 'DVL-CHPD'
else
case when lr.u_pagpagar=0 and lr.u_pagchpd=0 and lm.cm=18 then 'REG-LT'
else
case when lr.u_pagpagar=1 and lr.u_pagchpd=0 and lm.cm=18 then 'REG-PG'
else
case when lr.u_pagpagar=0 and lr.u_pagchpd=1 and lm.cm=18 then 'REG-CHPD'
else
case when lr.u_pagpagar=0 and lr.u_pagchpd=0 and lm.cm=3 then 'COBDIR-LT'
else
case when lr.u_pagpagar=1 and lr.u_pagchpd=0 and lm.cm=3 then 'COBDIR-PG'
else
case when lr.u_pagpagar=0 and lr.u_pagchpd=1 and lm.cm=3 then 'COBDIR-CHPD'
else ' ' end end end end end end end end end end end end),[/highlight]
lr.letra,
(case when lm.cm=20 then lr.evalor else 0 end) as edeb,
(case when lm.cm=21 or lm.cm=18 or lm.cm=3 then lr.evalor else 0 end) as ecred
FROM lr (nolock),
lm (nolock)
WHERE lr.lrstamp=lm.lrstamp
AND lr.letra=lm.letra
AND lr.no like substring(#1#,1,5)
AND lm.cm in(20,21,18,3)
AND year(lm.data)=#4#
AND month(lm.data) between #2# and #3#
--
UNION ALL
SELECT CONTA=(CASE WHEN FACC.U_EREMESSA=1 THEN (SELECT LEFT(CL.U_CNTTITDS,3) FROM CL (NOLOCK) WHERE CL.NO=FACC.NO) ELSE (SELECT LEFT(CL.CONTAFAC,3) FROM CL (NOLOCK) WHERE CL.NO=FACC.NO) END),
'',
'Saldo Incial',
0,
sum(CASE WHEN CC.CM not in (71,72) THEN FACC.ETOTAL ELSE 0 END) AS 'EDEB',
sum(CASE WHEN CC.CM in(71,72) THEN CC.ECRED ELSE 0 END) AS 'ECRED'
FROM FACC,
CC
WHERE FACC.FACCSTAMP=CC.FACCSTAMP
AND FACC.NO like substring(#1#,1,5)
AND year(CC.dataLC)<#4#
GROUP BY facc.u_eremessa, facc.no
UNION all
SELECT CONTA=(CASE WHEN FACC.U_EREMESSA=1 THEN (SELECT LEFT(CL.U_CNTTITDS,3) FROM CL (NOLOCK) WHERE CL.NO=FACC.NO) ELSE (SELECT LEFT(CL.CONTAFAC,3) FROM CL (NOLOCK) WHERE CL.NO=FACC.NO) END),
cc.datalc,
(CASE WHEN (FACC.CESSAO LIKE 'RE%' AND (CC.CMDESC NOT LIKE 'C46%' AND CC.CMDESC NOT LIKE 'C44%')) THEN 'REMESSA' ELSE CASE WHEN FACC.CESSAO LIKE 'FT%' AND (CC.CMDESC NOT LIKE 'C46%' AND CC.CMDESC NOT LIKE 'C44%') THEN 'FACTORING' ELSE cc.cmdesc END END),
NRDOC,
(CASE WHEN CC.CM not in (71,72,135) THEN FACC.ETOTAL ELSE 0 END) AS 'EDEB',
(CASE WHEN CC.CM in(71,72) THEN CC.ECRED ELSE 0 END) AS 'ECRED'
FROM FACC,
CC
WHERE FACC.FACCSTAMP=CC.FACCSTAMP
AND FACC.NO like substring(#1#,1,5)
AND year(cc.datalc)>=#4#
AND month(cc.datalc) between #2# and #3# and cc.cm <>135
UNION all
SELECT conta='211',
ftrd.datalc,
ltrim(ftrd.cdesc+'-'+cast(ftrd.nrdoc as varchar)),
cast(ft.fno as varchar) as nrdoc,
EDEB=0,
ECRED=round(sum(evreg),2)
FROM ftrd,
ft,
rd
WHERE ftrd.ftstamp=ft.ftstamp
AND rd.rdstamp=ftrd.rdstamp
AND ft.no like substring(#1#,1,5)
AND month(ftrd.datalc) between #2# and #3#
AND year(ftrd.datalc)=#4#
GROUP BY ft.fno, ftrd.datalc, ftrd.cdesc, ftrd.nrdoc, ft.nmdoc
UNION all
SELECT conta='269',
'19000101',
'Saldo Inicial',
0,
EDEB=isnull(SUM(round(evreg/(1+(rd.iva/100)),2)),0),
0 as ecred
FROM ftrd,
ft,
rd
WHERE ftrd.ftstamp=ft.ftstamp
AND rd.rdstamp=ftrd.rdstamp
AND ft.no like substring(#1#,1,5)
AND year(ftrd.datalc)<#4#
UNION all
SELECT conta='269',
ftrd.datalc,
ltrim(ftrd.cdesc+'-'+cast(ftrd.nrdoc as varchar)),
cast(ft.fno as varchar) as nrdoc,
EDEB=round((sum(evreg/(1+(rd.iva/100)))),2),
ECRED = 0
FROM ftrd,
ft,
rd
WHERE ftrd.ftstamp=ft.ftstamp
AND rd.rdstamp=ftrd.rdstamp
AND ft.no like substring(#1#,1,5)
AND month(ftrd.datalc) between #2# and #3#
AND year(ftrd.datalc)=#4#
GROUP BY ft.fno, ftrd.datalc, ftrd.cdesc, ftrd.nrdoc, ft.nmdoc
--ADD WHERE AND ORDER BY CLAUSE HERE IF NEEDED
order by 1,2 asc
OPEN RunningTotalCursor
--I am not sure of the datatypes you need, so change accordingly
DECLARE @conta varchar(20)
DECLARE @data datetime
DECLARE @documento varchar(25)
DECLARE @nrdoc varchar(20)
DECLARE @edeb numeric(10,2)
DECLARE @ecre numeric(10,2)
--set starting value of the running total
DECLARE @RunningTotal numeric(10,2)
DECLARE @contaactual varchar(20)
SET @RunningTotal = 0
SET @contaactual = ''
--hold our results data
DECLARE @Results TABLE
(
conta varchar(20),
data datetime,
documento varchar(25),
nrdoc varchar(20),
edeb numeric(10,2),
ecre numeric(10,2),
RunningTotal numeric(10,2)
)
FETCH NEXT FROM RunningTotalCursor
INTO @conta, @data, @documento,@nrdoc, @edeb, @ecre
WHILE @@FETCH_STATUS = 0
BEGIN
--If there is a credit amount use it; otherwise, use debit
if @conta<>@contaactual
BEGIN
SET @RunningTotal = 0
SET @contaactual = @conta
END
--IF @ecre > 0
--SET @RunningTotal = @RunningTotal -@ecre
--ELSE
SET @RunningTotal = @RunningTotal + (@edeb-@ecre)
--insert values into the results table
INSERT @Results
VALUES (@conta,@data, @documento, @nrdoc, @edeb, @ecre,@RunningTotal)
FETCH NEXT FROM RunningTotalCursor
INTO @conta,@data, @documento, @nrdoc, @edeb, @ecre
END
CLOSE RunningTotalCursor
DEALLOCATE RunningTotalCursor
--select all records from the results table variable
SELECT *
FROM @Results
ORDER BY conta,data
someone could give me an idea, maybe i repeat the code
Thanks
Luis Santos
February 26, 2008 at 8:39 am
[highlight=#ffff11](case when lr.u_pagpagar=0 and lr.u_pagchpd=0 and lm.cm=20 then 'Aceite LT'
else
case when lr.u_pagpagar=1 and lr.u_pagchpd=0 and lm.cm=20 then 'Aceite PG'
else
case when lr.u_pagpagar=0 and lr.u_pagchpd=1 and lm.cm=20 then'Aceite CHPD'
else
case when lr.u_pagpagar=0 and lr.u_pagchpd=0 and lm.cm=21 then 'DVL-LT'
else
case when lr.u_pagpagar=1 and lr.u_pagchpd=0 and lm.cm=21 then 'DVL-PG'
else
case when lr.u_pagpagar=0 and lr.u_pagchpd=1 and lm.cm=21 then 'DVL-CHPD'
else
case when lr.u_pagpagar=0 and lr.u_pagchpd=0 and lm.cm=18 then 'REG-LT'
else
case when lr.u_pagpagar=1 and lr.u_pagchpd=0 and lm.cm=18 then 'REG-PG'
else
case when lr.u_pagpagar=0 and lr.u_pagchpd=1 and lm.cm=18 then 'REG-CHPD'
else
case when lr.u_pagpagar=0 and lr.u_pagchpd=0 and lm.cm=3 then 'COBDIR-LT'
else
case when lr.u_pagpagar=1 and lr.u_pagchpd=0 and lm.cm=3 then 'COBDIR-PG'
else
case when lr.u_pagpagar=0 and lr.u_pagchpd=1 and lm.cm=3 then 'COBDIR-CHPD'
else ' ' end end end end end end end end end end end end),[/highlight]
The problem defines it self very clearly. You cannot nest more than 10 case statements, in which the code you posted does.
I believe what you intended to code was this
case
when lr.u_pagpagar=0 and lr.u_pagchpd=0 and lm.cm=20 then 'Aceite LT'
when lr.u_pagpagar=1 and lr.u_pagchpd=0 and lm.cm=20 then 'Aceite PG'
when lr.u_pagpagar=0 and lr.u_pagchpd=1 and lm.cm=20 then'Aceite CHPD'
when lr.u_pagpagar=0 and lr.u_pagchpd=0 and lm.cm=21 then 'DVL-LT'
when lr.u_pagpagar=1 and lr.u_pagchpd=0 and lm.cm=21 then 'DVL-PG'
when lr.u_pagpagar=0 and lr.u_pagchpd=1 and lm.cm=21 then 'DVL-CHPD'
when lr.u_pagpagar=0 and lr.u_pagchpd=0 and lm.cm=18 then 'REG-LT'
when lr.u_pagpagar=1 and lr.u_pagchpd=0 and lm.cm=18 then 'REG-PG'
when lr.u_pagpagar=0 and lr.u_pagchpd=1 and lm.cm=18 then 'REG-CHPD'
when lr.u_pagpagar=0 and lr.u_pagchpd=0 and lm.cm=3 then 'COBDIR-LT'
when lr.u_pagpagar=1 and lr.u_pagchpd=0 and lm.cm=3 then 'COBDIR-PG'
when lr.u_pagpagar=0 and lr.u_pagchpd=1 and lm.cm=3 then 'COBDIR-CHPD'
else ' '
end,
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply