Syntax Error in an inline query within a SELECT

  • Despite the result of my "inline" query within a SELECT statement being a scalar, that query contains an aggregate and a subquery, so I'm not sure if this is ok or not. I'm not even sure where to look this up. Here's the offending SELECT, which is part of a rather long list of SELECTs with UNION ALLs in between:

    SELECT 1240,'1800 Private Sources - National Heritage Academies',

    (SELECT MAX(AMOUNT) FROM (SELECT CAST(0. AS decimal(18,2)) AS AMOUNT UNION ALL SELECT

    (SELECT ACTUAL FROM @RESULTS WHERE LNUM = 1570) -

    (SELECT ACTUAL FROM @RESULTS WHERE LNUM = 1210) -

    (SELECT ACTUAL FROM @RESULTS WHERE LNUM = 1270) -

    (SELECT ACTUAL FROM @RESULTS WHERE LNUM = 1300) X)),

    (SELECT MAX(AMOUNT) FROM (SELECT CAST(0. AS decimal(18,2)) AS AMOUNT UNION ALL SELECT

    (SELECT BUDGET FROM @RESULTS WHERE LNUM = 1570) -

    (SELECT BUDGET FROM @RESULTS WHERE LNUM = 1210) -

    (SELECT BUDGET FROM @RESULTS WHERE LNUM = 1270) -

    (SELECT BUDGET FROM @RESULTS WHERE LNUM = 1300) X)) UNION ALL

    Am I allowed this, or must I come up with another methodology?

    Steve

    (aka smunson)

    :):):)

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • What's the "X" for at the end of each sub-query? It's where a column alias would go, but you don't need a column alias in the second query of a Union All structure.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Figured it out. You need to move the "X" over one paren.

    Tested this:

    declare @Results table (

    Actual money,

    Budget money,

    LNum int)

    insert into @Results (Actual, LNum, Budget)

    select -5, 1570, 5

    SELECT 1240,'1800 Private Sources - National Heritage Academies',

    (SELECT MAX(AMOUNT) FROM (SELECT CAST(0. AS decimal(18,2)) AS AMOUNT UNION ALL SELECT

    (SELECT ACTUAL FROM @RESULTS WHERE LNUM = 1570) -

    (SELECT ACTUAL FROM @RESULTS WHERE LNUM = 1210) -

    (SELECT ACTUAL FROM @RESULTS WHERE LNUM = 1270) -

    (SELECT ACTUAL FROM @RESULTS WHERE LNUM = 1300))X),

    (SELECT MAX(AMOUNT) FROM (SELECT CAST(0. AS decimal(18,2)) AS AMOUNT UNION ALL SELECT

    (SELECT BUDGET FROM @RESULTS WHERE LNUM = 1570) -

    (SELECT BUDGET FROM @RESULTS WHERE LNUM = 1210) -

    (SELECT BUDGET FROM @RESULTS WHERE LNUM = 1270) -

    (SELECT BUDGET FROM @RESULTS WHERE LNUM = 1300))X)

    Works just fine.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Why thank you kind sir !!! I was going bananas trying different ways to parenthesize, add aliases, etc., but I think I just got so buried deep into them that I lost all track of the need for the alias for the FROM's SELECT clause. Thanks for the extra set of eyes.

    Steve

    (aka smunson)

    :):):)

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • You're welcome.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply