Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2006-11-10
8
Medium Priority
?
1,605 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 143

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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
 
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 2000 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
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.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

636 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