asked on

MS SQL: Multiple columns are specified in an aggregated expression containing an outer reference.

In this small query:

SELECT
(
SELECT
SUM(CASE BMUT1.BMU_NAAR_BUD_ID WHEN B.BUD_ID THEN BMUT1.BMU_BEDRAG ELSE 0-BMUT1.BMU_BEDRAG END)
FROM
BUDGETMUTATIE BMUT1
WHERE
(
BMUT1.BMU_NAAR_BUD_ID = B.BUD_ID
OR
BMUT1.BMU_VAN_BUD_ID = B.BUD_ID
)
AND
BMUT1.BMU_EINDDATUM IS NULL
AND
BMUT1.BMU_MPR_ID = B.BUD_MPR_ID
AND
BMUT1.BMU_INITIEEL <> 0
) BMU_BEDRAG
FROM
BUDGET B
WHERE
B.BUD_EINDDATUM IS NULL;

MS SQL returns me the following error:
Msg 8124, Level 16, State 1, Line 1
Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression.

-----------------
This query is actually a small part of a much, much larger query I converted out of an MS Access project (here there's no problem) resulting in the same error. I narrowed it down to the query you see above to find the source off the problem ...

What am i overseeing ?? I'm looking at this little piece for 2 days now, and it's driving me crazy ... :P

Guy Hengel [angelIII / a3]

what about this:

SELECT
SUM(CASE BMUT1.BMU_NAAR_BUD_ID WHEN B.BUD_ID THEN BMUT1.BMU_BEDRAG ELSE 0-BMUT1.BMU_BEDRAG END) BMU_BEDRAG
FROM BUDGETMUTATIE BMUT1
JOIN BUDGET B
ON
(
BMUT1.BMU_NAAR_BUD_ID = B.BUD_ID
OR
BMUT1.BMU_VAN_BUD_ID = B.BUD_ID
)
AND BMUT1.BMU_EINDDATUM IS NULL
AND BMUT1.BMU_MPR_ID = B.BUD_MPR_ID
AND BMUT1.BMU_INITIEEL <> 0
WHERE B.BUD_EINDDATUM IS NULL;

FidoDildo

ASKER

nope sorry ...

as mentioned the query is part of a much much larger query:

----SQL START:-----------------------------------------------------
SELECT
BUD_ID
,SEF_ID
,BUD_MPR_ID
,BMU_ID
,BMU_VAN_BUD_ID
,BMU_NAAR_BUD_ID
,BMU_INITIEEL
,(CASE BMU_VAN_BUD_ID WHEN BUD_ID THEN
(CASE BMU_NAAR_BUD_ID WHEN NULL THEN 'Vermindering' ELSE 'Naar '+(
Select
PRO_CODE_PATH
FROM
PROJECT P1
WHERE
P1.PRO_ID = (
SELECT
BUD_PRO_ID
FROM
BUDGET A
WHERE
A.BUD_ID = BMU_NAAR_BUD_ID)
)+' / '+ (
Select
KOS_CODE_PATH
FROM
KOSTENSOORT K1
WHERE
K1.KOS_ID = (
SELECT
BUD_KOS_ID
FROM
BUDGET B
WHERE
B.BUD_ID = BMU_NAAR_BUD_ID
)
)
END) ELSE
(CASE BMU_NAAR_BUD_ID WHEN BUD_ID THEN
(CASE BMU_VAN_BUD_ID WHEN NULL THEN 'Toevoeging' ELSE 'Van '+ (
Select
PRO_CODE_PATH
FROM
PROJECT P2
WHERE
P2.PRO_ID = (
SELECT
BUD_PRO_ID
FROM
BUDGET C
WHERE
C.BUD_ID = BMU_VAN_BUD_ID
)
)+' / '+ (
Select
KOS_CODE_PATH
FROM
KOSTENSOORT K2
WHERE
K2.KOS_ID = (
SELECT
BUD_KOS_ID
FROM
BUDGET D
WHERE
D.BUD_ID = BMU_VAN_BUD_ID
)
)
END) ELSE
'' END)
END) AS BMU_TYPE
,BMU_AANTAL
,BMU_EEN_ID
,BMU_PRIJSPEREENHEID
,(CASE BMU_VAN_BUD_ID WHEN BUD_ID THEN 0-BUDGETMUTATIE.BMU_BEDRAG ELSE BUDGETMUTATIE.BMU_BEDRAG END) AS BMU_BEDRAG
,BMU_BTW_ID
,(CASE BMU_VAN_BUD_ID WHEN BUD_ID THEN 0-BUDGETMUTATIE.BMU_BTW ELSE BUDGETMUTATIE.BMU_BTW END) AS BMU_BTW
,BMU_INGANGSDATUM
,BMU_OMSCHRIJVING
,BMU_GEBRUIKER
FROM
(
BUDGET
INNER JOIN
SELECTFILTER
ON
BUDGET.BUD_PRO_ID = SELECTFILTER.SEF_PRO_ID
AND
BUDGET.BUD_KOS_ID = SELECTFILTER.SEF_KOS_ID
AND
BUDGET.BUD_MPR_ID = SELECTFILTER.SEF_MPR_ID
)
LEFT JOIN
BUDGETMUTATIE
ON
(BUDGETMUTATIE.BMU_MPR_ID = BUDGET.BUD_MPR_ID)
AND
(
(BUDGETMUTATIE.BMU_VAN_BUD_ID = BUDGET.BUD_ID)
OR
(BUDGETMUTATIE.BMU_NAAR_BUD_ID = BUDGET.BUD_ID)
)
WHERE
BUD_EINDDATUM IS NULL
AND
BMU_EINDDATUM IS NULL
AND
BUDGETMUTATIE.BMU_INITIEEL = 0

UNION

SELECT
BUD_ID
,SEF_ID
,BUD_MPR_ID
,0
,BUD_ID
,BUD_ID
,1
,'Initieel' AS BMU_TYPE
,NULL
,NULL
,NULL
,(
SELECT
SUM(CASE BMU_NAAR_BUD_ID WHEN BUD_ID THEN BMU_BEDRAG ELSE 0-BMU_BEDRAG END)
FROM
BUDGETMUTATIE
WHERE
(
BMU_NAAR_BUD_ID = BUD_ID
OR
BMU_VAN_BUD_ID = BUD_ID
)
AND
BMU_EINDDATUM IS NULL
AND
BMU_MPR_ID = BUD_MPR_ID
AND
BMU_INITIEEL <> 0
)
,NULL
,(
SELECT
SUM (CASE BMU_NAAR_BUD_ID WHEN BUD_ID THEN BMU_BTW ELSE 0-BMU_BTW END)
FROM
BUDGETMUTATIE
WHERE
(
BMU_NAAR_BUD_ID = BUD_ID
OR
BMU_VAN_BUD_ID = BUD_ID
)
AND
BMU_EINDDATUM IS NULL
AND
BMU_MPR_ID = BUD_MPR_ID
AND
BMU_INITIEEL <> 0
)
,BUD_INGANGSDATUM
,BUD_OMSCHRIJVING
,BUD_GEBRUIKER
FROM
BUDGET
INNER JOIN
SELECTFILTER
ON
BUDGET.BUD_PRO_ID = SELECTFILTER.SEF_PRO_ID
AND
BUDGET.BUD_KOS_ID = SELECTFILTER.SEF_KOS_ID
AND
BUDGET.BUD_MPR_ID = SELECTFILTER.SEF_MPR_ID
WHERE
BUD_EINDDATUM IS NULL;

----SQL END-----------------------------------------------------

as you see the query is a ver small part in the second part off the union (twice)

rw3admin

Can you post table structures of 'PROJECT', 'BUDGET', 'KOSTENSOORT', 'SELECTFILTER' and
'BUDGETMUTATIE'.

so angelIII or I can run this query in QA and see where the error is?

FidoDildo

ASKER

Here they are:

CREATE TABLE [dbo].[BUDGET](
      [BUD_BEGINDATUM] [datetime] NOT NULL,
      [BUD_EINDDATUM] [datetime] NULL,
      [BUD_GEBRUIKER] [varchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [BUD_ID] [int] NOT NULL,
      [BUD_INGANGSDATUM] [datetime] NOT NULL,
      [BUD_KOS_ID] [int] NOT NULL,
      [BUD_MPR_ID] [int] NOT NULL,
      [BUD_OMSCHRIJVING] [varchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [BUD_PRO_ID] [int] NOT NULL,
      [BUD_PROGNOSEHARD] [money] NOT NULL,
      [BUD_PROGNOSEHARDBTW] [money] NOT NULL,
      [BUD_PROGNOSEHARDDATUMVOORL] [datetime] NULL,
      [BUD_PROGNOSEHARDSTATUS] [int] NULL,
      [BUD_PROGNOSEINGANGSDATUM] [datetime] NULL,
      [BUD_PROGNOSEOMSCHRIJVING] [varchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [BUD_PROGNOSEZACHT] [money] NOT NULL,
      [BUD_PROGNOSEZACHTBTW] [money] NOT NULL,
      [BUD_PROGNOSEZACHTDATUMVOORL] [datetime] NULL,
      [BUD_PROGNOSEZACHTSTATUS] [int] NULL,
CONSTRAINT [pk_BUDGET0] PRIMARY KEY CLUSTERED
(
      [BUD_ID] ASC,
      [BUD_MPR_ID] ASC,
      [BUD_BEGINDATUM] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

/*-------------------------------------------*/

CREATE TABLE [dbo].[BUDGETMUTATIE](
      [BMU_AANTAL] [float] NULL,
      [BMU_BEDRAG] [money] NOT NULL,
      [BMU_BEGINDATUM] [datetime] NOT NULL,
      [BMU_BTW] [money] NOT NULL,
      [BMU_BTW_ID] [int] NULL,
      [BMU_CODE] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [BMU_EEN_ID] [int] NULL,
      [BMU_EINDDATUM] [datetime] NULL,
      [BMU_GEBRUIKER] [varchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [BMU_ID] [int] NOT NULL,
      [BMU_INGANGSDATUM] [datetime] NOT NULL,
      [BMU_INITIEEL] [bit] NOT NULL,
      [BMU_LABEL] [varchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [BMU_MPR_ID] [int] NOT NULL,
      [BMU_NAAR_BUD_ID] [int] NULL,
      [BMU_OMSCHRIJVING] [varchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [BMU_PRIJSPEREENHEID] [money] NULL,
      [BMU_VAN_BUD_ID] [int] NULL,
CONSTRAINT [pk_BUDGETMUTATIE0] PRIMARY KEY CLUSTERED
(
      [BMU_ID] ASC,
      [BMU_MPR_ID] ASC,
      [BMU_BEGINDATUM] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

/*-------------------------------------------*/

CREATE TABLE [dbo].[KOSTENSOORT](
      [KOS_CODE] [varchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [KOS_CODE_PATH] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [KOS_GEBRUIKER] [varchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [KOS_ID] [int] NOT NULL,
      [KOS_KOS_ID] [int] NULL,
      [KOS_MPR_ID] [int] NOT NULL,
      [KOS_NIVEAU] [int] NULL,
      [KOS_OMSCHRIJVING] [varchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [KOS_PATH] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [pk_KOSTENSOORT0] PRIMARY KEY CLUSTERED
(
      [KOS_ID] ASC,
      [KOS_MPR_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

/*-------------------------------------------*/

CREATE TABLE [dbo].[PROJECT](
      [PRO_AANMAAKDATUM] [datetime] NOT NULL,
      [PRO_BEGINDATUM] [datetime] NOT NULL,
      [PRO_CODE] [varchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [PRO_CODE_PATH] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [PRO_EINDDATUM] [datetime] NULL,
      [PRO_FASE] [int] NOT NULL,
      [PRO_FASEDATUM] [datetime] NOT NULL,
      [PRO_GEBRUIKER] [varchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [PRO_GGR_ID] [int] NOT NULL,
      [PRO_ID] [int] NOT NULL,
      [PRO_INGANGSDATUM] [datetime] NOT NULL,
      [PRO_MEMO] [varchar](4096) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [PRO_MPR_ID] [int] NOT NULL,
      [PRO_NAAM] [varchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [PRO_NIVEAU] [int] NULL,
      [PRO_OMSCHRIJVING] [varchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [PRO_PATH] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [PRO_PRO_ID] [int] NULL,
      [PRO_PROJECTEINDDATUM] [datetime] NULL,
CONSTRAINT [pk_PROJECT0] PRIMARY KEY CLUSTERED
(
      [PRO_ID] ASC,
      [PRO_MPR_ID] ASC,
      [PRO_BEGINDATUM] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

/*-------------------------------------------*/

CREATE TABLE [dbo].[SELECTFILTER](
      [SEF_BSI_COUNT] [int] NULL,
      [SEF_BUD_ID] [int] NULL,
      [SEF_DATUM] [datetime] NOT NULL,
      [SEF_FIXATIEDATUM] [datetime] NULL,
      [SEF_GEB_ID] [int] NULL,
      [SEF_ID] [int] NOT NULL,
      [SEF_KOS_DIEPTE] [int] NULL,
      [SEF_KOS_ID] [int] NULL,
      [SEF_KOS_ID1] [int] NULL,
      [SEF_KOS_ID2] [int] NULL,
      [SEF_KOS_NIV_WG_TM] [int] NULL,
      [SEF_KOS_NIV_WG_VAN] [int] NULL,
      [SEF_KOS_NIVEAU] [int] NULL,
      [SEF_KOS_PATH] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [SEF_MPR_ID] [int] NOT NULL,
      [SEF_OSI_COUNT] [int] NULL,
      [SEF_PEILDATUM] [datetime] NOT NULL,
      [SEF_PRO_DIEPTE] [int] NULL,
      [SEF_PRO_ID] [int] NULL,
      [SEF_PRO_NIV_WG_TM] [int] NULL,
      [SEF_PRO_NIV_WG_VAN] [int] NULL,
      [SEF_PRO_NIVEAU] [int] NULL,
      [SEF_PRO_PATH] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [pk_SELECTFILTER0] PRIMARY KEY CLUSTERED
(
      [SEF_ID] ASC,
      [SEF_MPR_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

rw3admin

The problem is this line
SUM(CASE BMUT1.BMU_NAAR_BUD_ID WHEN B.BUD_ID THEN BMUT1.BMU_BEDRAG ELSE 0-BMUT1.BMU_BEDRAG END)

if you replace "WHEN B.BUD_ID " to some value lets say "When 0" your query works,

SUM(CASE BMUT1.BMU_NAAR_BUD_ID WHEN 0 THEN BMUT1.BMU_BEDRAG ELSE 0-BMUT1.BMU_BEDRAG END)

are there any other joins between BUDGET and BUDGETMUTATIE that you are missing, I have to go in a meeting I will take care of your query once I come back...
and thanks for posting table structures it helped a lot

rw3admin

ASKER CERTIFIED SOLUTION

rw3admin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

FidoDildo

ASKER

Thats it ...

You're the greatest ! ... thanx for putting in all the effort ...

Oh jeah:
In the start my nick was Fido Dido, just days after 7up started using the Fido character (hmm 20 years ago or so?), beceause in those days Fido was the spitting image of me ...

a few months after using that name i pulled a joke on a girlfriend (never mind what joke, not suited for every reader of this site ;) ) and since that day i was called Fido Dildo ... as you can see i still use it actively ... :)

Thanx again ...

rw3admin

Thanks... hey I remember a cool video game around Fido Dido... your remember Cool Spot on Sega?...