Solved

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

Posted on 2006-11-10
8
1,419 Views
Last Modified: 2008-02-01
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


0
Comment
Question by:FidoDildo
  • 4
  • 3
8 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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;
0
 

Author Comment

by:FidoDildo
Comment Utility
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)
0
 
LVL 11

Expert Comment

by:rw3admin
Comment Utility
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?


0
 

Author Comment

by:FidoDildo
Comment Utility
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]

0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 11

Expert Comment

by:rw3admin
Comment Utility
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
0
 
LVL 11

Accepted Solution

by:
rw3admin earned 500 total points
Comment Utility
FidoDildo
following query works,....one question though... were you going for the name FidoDido and Dildo is a typo or ... well never mind :)


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,
            BUDGET B
      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 and
             B.BUD_ID=C.BUD_ID and
                 B.BUD_MPR_ID=C.BUD_MPR_ID and
            B.BUD_BEGINDATUM=C.BUD_BEGINDATUM             
      )       BMU_BEDRAG
  FROM
    BUDGET C
  WHERE
    C.BUD_EINDDATUM IS NULL
0
 

Author Comment

by:FidoDildo
Comment Utility
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 ...
0
 
LVL 11

Expert Comment

by:rw3admin
Comment Utility
Thanks... hey I remember a cool video game around Fido Dido... your remember Cool Spot on Sega?...
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now