Update, case statement and sum

[mirth]

I would like to update a decimal column in a temporary table based on
a set of Glcodes from another table. I search for a set of codes and
then want to sum the value for each row matching the Glcodes. The
problem is I keep getting multiple rows returned errors.

"Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as
an expression.
The statement has been terminated."

This is correct as there can be many rows matching the Glcodes for
each iteration of the case statement and I need to catch them all.

I have posted some of the code below and would appreciate any help as
I'm scratching my head over this one. It's all very much work in
progress again.

Regards,

DECLARE @CostCentre NVARCHAR(3)
DECLARE @COID NVARCHAR(3)
DECLARE @TheYear NVARCHAR(5)
DECLARE @PlorBS NVARCHAR(2)
DECLARE @BusinessUnit NVARCHAR(50)
DECLARE @Branch NVARCHAR(3)

SET @CostCentre = 'xxx'
SET @COID = 'inc'
SET @TheYear = '2004'
SET @PlorBS = 'x2'
SET @BusinessUnit = 'PBUS'
SET @Branch = ‘usa'


CREATE TABLE #SummaryTempTab le (
[GLD_ACCTNG_PER] int,
[Order Num] decimal(9,2),
[Summary Description] varchar(50),
[Summary Amount] decimal(9,2)
)

INSERT INTO #SummaryTempTab le VALUES(199999, 1.1, 'Tot Ext Sales',
0.0)
INSERT INTO #SummaryTempTab le VALUES(199999, 1.2, 'Tot Int Sales',
0.0)
INSERT INTO #SummaryTempTab le VALUES(199999, 1.3, 'Inter Mark Up',
0.0)
INSERT INTO #SummaryTempTab le VALUES(199999, 2.1, 'Tot Ext Costs',
0.0)
INSERT INTO #SummaryTempTab le VALUES(199999, 2.2, 'Tot Int Costs',
0.0)
INSERT INTO #SummaryTempTab le VALUES(199999, 2.3, 'Inter Mark Up
Charges', 0.0)


UPDATE #SummaryTempTab le
SET [Summary Amount] = (SELECT sum(CASE
WHEN ((ACT_GL_NO between '4000' and '4059') or (ACT_GL_NO between
'4065' and '4999') or (ACT_GL_NO IN ('4062','4063') )) THEN GLD_Total
WHEN (ACT_GL_NO IN ('4060','4064') ) THEN GLD_Total
WHEN (ACT_GL_NO = '4061') THEN GLD_Total
WHEN ((ACT_GL_NO between '5000' and '5059') or (ACT_GL_NO between
'5065' and '5401') or (ACT_GL_NO IN ('5805','5806', '5062','5063')) )
THEN GLD_Total
WHEN (ACT_GL_NO IN ('5060','5064') ) THEN GLD_Total
WHEN (ACT_GL_NO = '5061') THEN GLD_Total
ELSE 0
END)
FROM howco_dw_test.d bo.cubeFinanceP eriod
WHERE ([coid] = @COID) AND (GLD_SSN_BRH = @Branch) AND
(GLD_ACCTNG_PER like @TheYear) AND ACT_GL_NO BETWEEN 4000 AND 9999
AND GLD_CST_CTR IN ('008','021','0 31','041')
GROUP BY ACT_GL_NO, GLD_ACCTNG_PER)

[John Bell]

Re: Update, case statement and sum

Hi

Without information about cubeFinancePeri od it is not possible to re-create
your error!
The Sub-SELECT statement will return more than one row. If you ran this
statement separately it should show that. You probably need some form or
correlation between the ACT_GL_NO, GLD_ACCTNG_PER in cubeFinancePeri od and
your columns in #SummaryTempTab le .Check out the FROM clause in the UPDATE
statement. An example of this can also be found at: http://tinyurl.com/32xlt

It also seems that you have multiple WHERE clauses in the CASE statement
that may be combined into one or possibly used as the where clause.

John


"mirth" <contactmirth@y ahoo.co.uk> wrote in message
news:7869c14.04 06230253.12cfdb d7@posting.goog le.com...[color=blue]
> I would like to update a decimal column in a temporary table based on
> a set of Glcodes from another table. I search for a set of codes and
> then want to sum the value for each row matching the Glcodes. The
> problem is I keep getting multiple rows returned errors.
>
> "Subquery returned more than 1 value. This is not permitted when the
> subquery follows =, !=, <, <= , >, >= or when the subquery is used as
> an expression.
> The statement has been terminated."
>
> This is correct as there can be many rows matching the Glcodes for
> each iteration of the case statement and I need to catch them all.
>
> I have posted some of the code below and would appreciate any help as
> I'm scratching my head over this one. It's all very much work in
> progress again.
>
> Regards,
>
> DECLARE @CostCentre NVARCHAR(3)
> DECLARE @COID NVARCHAR(3)
> DECLARE @TheYear NVARCHAR(5)
> DECLARE @PlorBS NVARCHAR(2)
> DECLARE @BusinessUnit NVARCHAR(50)
> DECLARE @Branch NVARCHAR(3)
>
> SET @CostCentre = 'xxx'
> SET @COID = 'inc'
> SET @TheYear = '2004'
> SET @PlorBS = 'x2'
> SET @BusinessUnit = 'PBUS'
> SET @Branch = 'usa'
>
>
> CREATE TABLE #SummaryTempTab le (
> [GLD_ACCTNG_PER] int,
> [Order Num] decimal(9,2),
> [Summary Description] varchar(50),
> [Summary Amount] decimal(9,2)
> )
>
> INSERT INTO #SummaryTempTab le VALUES(199999, 1.1, 'Tot Ext Sales',
> 0.0)
> INSERT INTO #SummaryTempTab le VALUES(199999, 1.2, 'Tot Int Sales',
> 0.0)
> INSERT INTO #SummaryTempTab le VALUES(199999, 1.3, 'Inter Mark Up',
> 0.0)
> INSERT INTO #SummaryTempTab le VALUES(199999, 2.1, 'Tot Ext Costs',
> 0.0)
> INSERT INTO #SummaryTempTab le VALUES(199999, 2.2, 'Tot Int Costs',
> 0.0)
> INSERT INTO #SummaryTempTab le VALUES(199999, 2.3, 'Inter Mark Up
> Charges', 0.0)
>
>
> UPDATE #SummaryTempTab le
> SET [Summary Amount] = (SELECT sum(CASE
> WHEN ((ACT_GL_NO between '4000' and '4059') or (ACT_GL_NO between
> '4065' and '4999') or (ACT_GL_NO IN ('4062','4063') )) THEN GLD_Total
> WHEN (ACT_GL_NO IN ('4060','4064') ) THEN GLD_Total
> WHEN (ACT_GL_NO = '4061') THEN GLD_Total
> WHEN ((ACT_GL_NO between '5000' and '5059') or (ACT_GL_NO between
> '5065' and '5401') or (ACT_GL_NO IN ('5805','5806', '5062','5063')) )
> THEN GLD_Total
> WHEN (ACT_GL_NO IN ('5060','5064') ) THEN GLD_Total
> WHEN (ACT_GL_NO = '5061') THEN GLD_Total
> ELSE 0
> END)
> FROM howco_dw_test.d bo.cubeFinanceP eriod
> WHERE ([coid] = @COID) AND (GLD_SSN_BRH = @Branch) AND
> (GLD_ACCTNG_PER like @TheYear) AND ACT_GL_NO BETWEEN 4000 AND 9999
> AND GLD_CST_CTR IN ('008','021','0 31','041')
> GROUP BY ACT_GL_NO, GLD_ACCTNG_PER)[/color]