Solved

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

Posted on 2006-11-10
8
1,434 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]
ID: 17913911
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
ID: 17914013
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
ID: 17916824
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
ID: 17936224
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 11

Expert Comment

by:rw3admin
ID: 17938873
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
ID: 17939400
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
ID: 17944981
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
ID: 17948275
Thanks... hey I remember a cool video game around Fido Dido... your remember Cool Spot on Sega?...
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

947 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

23 Experts available now in Live!

Get 1:1 Help Now