November 17, 2016 at 9:16 am
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.
November 17, 2016 at 9:21 am
I can't tell what you're trying to do, but do you get the right results if you remove the first MAX?
John
November 17, 2016 at 9:24 am
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])
November 17, 2016 at 9:28 am
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?
November 17, 2016 at 9:31 am
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
November 17, 2016 at 9:31 am
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
November 17, 2016 at 9:34 am
krypto69 (11/17/2016)
I need the maxThis 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
November 18, 2016 at 9:13 am
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