An aggregate may not appear in the WHERE clause

  • Hi,

    Having trouble with this one:

    SELECT * FROM [SQLNODEBSTAGE].[PEC_PROD].[DBO].[Staging_Eligibility]

    WHERE MAX(staging_eligibility_id) <> (SELECT MAX(staging_eligibility_id) FROM [SQLNODEBSTAGE].[STAGING_Archive].[DBO].[Staging_Eligibility_archive])

    An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

  • I can't tell what you're trying to do, but do you get the right results if you remove the first MAX?

    John

  • I need the max

    This seems to work :

    SELECT * FROM [SQLNODEBSTAGE].[PEC_PROD].[DBO].[Staging_Eligibility]

    WHERE (select max(staging_eligibility_id) FROM [SQLNODEBSTAGE].[PEC_PROD].[DBO].[Staging_Eligibility]) <> (SELECT MAX(staging_eligibility_id) FROM [SQLNODEBSTAGE].[STAGING_Archive].[DBO].[Staging_Eligibility_archive])

  • Have you tried with HAVING? https://msdn.microsoft.com/en-us/library/ms180199.aspx

    What are you trying to do? Shouldn't the subqueries be correlated somehow?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • I'd be surprised if that does what you intend it to. Assuming that the staging_eligibility_id column exists in both tables, it will return all rows from Staging_Eligibility table if the maximum values of staging_eligibility_id are different in each table, or no rows if they're the same. Can you describe what you're trying to do, and provide table DDL and sample data in the form of INSERT statements?

    John

  • If there isn't some correlation between the tables just declare variables, get the max of each table separately then do a single query where id > max of other??

    May not even need first max...

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • krypto69 (11/17/2016)


    I need the max

    This seems to work :

    SELECT * FROM [SQLNODEBSTAGE].[PEC_PROD].[DBO].[Staging_Eligibility]

    WHERE (select max(staging_eligibility_id) FROM [SQLNODEBSTAGE].[PEC_PROD].[DBO].[Staging_Eligibility]) <> (SELECT MAX(staging_eligibility_id) FROM [SQLNODEBSTAGE].[STAGING_Archive].[DBO].[Staging_Eligibility_archive])

    I agree with the others that this might not really be what you want, but if it is, this should be more efficient.

    ;

    WITH Staging_Eligibility_CTE AS (

    SELECT *, MAX(staging_eligibility_id) OVER() AS mx

    FROM [SQLNODEBSTAGE].[PEC_PROD].[DBO].[Staging_Eligibility]

    )

    SELECT *

    FROM Staging_Eligibility_CTE

    WHERE mx <> ( SELECT MAX(staging_eligibility_id) FROM [SQLNODEBSTAGE].[STAGING_Archive].[DBO].[Staging_Eligibility_archive] )

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • The error message makes perfect sense and is quite clear. What are you taking a maximum of? Where is the group by, or over clause?. See you can find a book on SQL and read how the clauses are executed; in particular, pay attention to the scoping rules. This is one of the tricky parts of the language. My guess is that you might want to do it with something like this:

    WITH Archive_Max (staging_eligibility_id_max)

    AS

    (SELECT MAX(staging_eligibility_id)

    FROM Staging_Eligibility_Archive)

    WITH Staging_Max (staging_eligibility_id_max)

    AS

    (SELECT MAX(staging_eligibility_id)

    FROM Staging_Eligibility_Somethings)

    SELECT S.* -- bad coding!

    FROM Archive_Max AS A,

    Staging_Max AS S

    WHERE A.staging_eligibility_id_max <> S.staging_eligibility_id_max.

    But frankly, I think you need to throw for this out and start over. We do not use "select *" in production code; it is unpredictable, and very expensive to execute. Eligibility is not an entity or relationship! It is an attribute, and we do not put attributes in their own tables. We also do not have a identifiers for values.

    Since you did not bother to post DDL (please, please read the forum rules), we cannot be much help to you. This invalid table looks like it is getting worse. You seem to have a design flaw called "attribute , which means that you views an attribute to set up multiple tables when you should have only had one. The example I like to use it. I am teaching this is having a schema with "male_personnel" and "female_personnel" which obviously should have been merely "personnel"; it was split on the sex code. Yours seems to be split between staging and archive, which are status attributes.

    Books in Celko Series for Morgan-Kaufmann Publishing
    Analytics and OLAP in SQL
    Data and Databases: Concepts in Practice
    Data, Measurements and Standards in SQL
    SQL for Smarties
    SQL Programming Style
    SQL Puzzles and Answers
    Thinking in Sets
    Trees and Hierarchies in SQL

Viewing 8 posts - 1 through 7 (of 7 total)

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