x

Subquery, aggregate

This works:

 SELECT     (SELECT Job.JobName from Job where JobID = MAX(CAST(Batch.JobID as varchar(36)))) as JobName
             , Batch.BatchID
             , Batch.SheetMat
             , (SELECT Job.SubmittedBy from Job where JobID = MAX(CAST(Batch.JobID as varchar(36)))) as SubmittedBy
             , MAX(ProgressType.ProgressStep) as ProgressStep
                             FROM        BatchProgress 
                         INNER JOIN Batch ON BatchProgress.BatchID = Batch.BatchID
                         INNER JOIN ProgressType on BatchProgress.ProgressTypeID =
 ProgressType.ProgressTypeID                  
 GROUP BY Batch.BatchID, Batch.SheetMat
 HAVING MAX(ProgressType.ProgressStep) not in (3,6,7)
 order by Batch.BatchID

This does not work:

 SELECT     (SELECT Job.JobName from Job where JobID = MAX(CAST(Batch.JobID as varchar(36)))) as JobName
         , Batch.BatchID
         , Batch.SheetMat
         , (SELECT Job.SubmittedBy from Job where JobID = MAX(CAST(Batch.JobID as varchar(36)))) as SubmittedBy
         , (SELECT ProgressType.ProgressType from ProgressType where ProgressStep = MAX(ProgressType.ProgressStep)) as ProgressStep
                      FROM        BatchProgress 
                     INNER JOIN Batch ON BatchProgress.BatchID = Batch.BatchID
                     INNER JOIN ProgressType on BatchProgress.ProgressTypeID = 
 ProgressType.ProgressTypeID               
 GROUP BY Batch.BatchID, Batch.SheetMat
 HAVING MAX(ProgressType.ProgressStep) not in (3,6,7)
 order by Batch.BatchID

error says:
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.

If I use a function instead of the 2nd example, it works. Can I recreate the stored procedure without needing a function?

Thanks!

more ▼

asked Aug 02, 2010 at 02:05 PM in Default

avatar image

cheeseman
14 1 1 4

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Are you sure that this is what you want? The error is caused by this line:

 (select ProgressType.ProgressType from ProgressType 
     where ProgressStep = max(ProgressType.ProgressStep)) as ProgressStep

but I am not sure that you want to select ProgressType as ProgressStep. If you do though then you can restate the line in question like this:

 (select ProgressType.ProgressType from ProgressType 
     where ProgressStep = 
         (select max(ProgressType.ProgressStep) from ProgressType)) 
         as ProgressStep

Oleg

One more thing I would like to point out. From the query in your question, it looks like the JobID of the Job table is defined as varchar while the JobID of the Batch table as uniqueidentifier. If the size of the JobID of Job table is 36 then you are OK, if the size is greater then there is one gotcha: sometimes the scripts which insert records into table like Job use {} brackets to denote the GUID. To SQL Server both formats are perfecly valid, i.e.

 select 
     cast('13E8C824-A9FE-4F38-AACF-36754836FD5A' as uniqueidentifier) OK,
     cast('{13E8C824-A9FE-4F38-AACF-36754836FD5A}' as uniqueidentifier) OK2;

but to the cast they are not because the version with the brackets has 38 characters not 36. To protect the innocent, it is safer to do the casting to varchar(38) rather then 36 just in case. Again, if your JobID of the Job table is defined as varchar(36) then you don't have this problem.

Oleg

more ▼

answered Aug 02, 2010 at 02:22 PM

avatar image

Oleg
17.2k 3 7 28

Oleg,

I know MAX(ProgressType.ProgressStep). I would like to see the description associated with this item (ProgressType.ProgressType). Using your proposed solution the results are not correct - this is identical to the query:

(select ProgressType.ProgressType from ProgressType where ProgressStep = 7) as ProgressStep

now I can create case logic to display the correct answer, one WHEN for each row in the ProgressType table. This is an expensive query and so I am seeking alternatives.

if I substitute this subquery for the following function: ,dbo.GetProgressType(MAX(ProgressType.ProgressStep)) as ProgressStep

dbo.GetProgressType defined as such: ( @ProgressStep int ) RETURNS varchar(50) AS BEGIN DECLARE @ResultVar varchar(50)

 SET @ResultVar = (SELECT ProgressType from ProgressType where ProgressStep = @ProgressStep)

 RETURN @ResultVar

END

This also returns the correct answer. But again I am hoping to avoid the expensive overhead.

Thanks!

Aug 02, 2010 at 02:40 PM cheeseman

@cheeseman I modified your query and pasted it into my answer. Please let me know if this works. I hope I was able to deduce the info you need correctly.

Aug 02, 2010 at 03:27 PM Oleg

@Oleg WOW that works great. I want to learn more about that WITH command.

Thanks for the comments about uniqueidentifier columns.

Aug 03, 2010 at 09:40 AM cheeseman

@cheeseman This is known as CTE or common table expression in T-SQL (http://msdn.microsoft.com/en-us/library/ms175972.aspx). In this case, it does not provide any particular benefit except it makes the statement somewhat easier to read. There are numerous benefits associated with CTEs though and they are well worth studying. If you copy the contents of the select statement of the CTE with parentheses included and paste it just before the word records in the bottom select then that select will be all you need:

select
    Job.JobName, records.BatchID, records.SheetMat, 
    Job.SubmittedBy, ProgressType.ProgressType
    from
    (
        select
            Batch.BatchID, Batch.SheetMat, 
            -- etc, the lines are removed to fit into comment
            having max(ProgressType.ProgressStep) not in (3, 6, 7)
    )
    records 
    inner join Job
        on records.JobID = Job.JobID
    inner join ProgressType
        on records.ProgressStep = ProgressType.ProgressStep
    order by records.BatchID;

The above evaluates to the same as the script in the answer, but I find using CTE as very convenient so I do use it often at least for clarity.

Aug 03, 2010 at 10:02 AM Oleg

@Oleg - congratualtons for your first Silver badge. :-D

Aug 03, 2010 at 12:07 PM Mark
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x70
x49

asked: Aug 02, 2010 at 02:05 PM

Seen: 1783 times

Last Updated: Aug 03, 2010 at 12:10 AM

Copyright 2016 Redgate Software. Privacy Policy