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

cheeseman gravatar image

cheeseman
14 1 1 2

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

1 answer: sort oldest

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

<!-- Begin Edit

Please try this one, it might be a little cheaper to do it this way:

;with records (BatchID, SheetMat, JobID,  ProgressStep) as
(
    select
        Batch.BatchID, Batch.SheetMat, 
        max(cast(Batch.JobID as varchar(36))) JobID,
        max(ProgressType.ProgressStep) ProgressStep 
        from Batch inner join BatchProgress
            on Batch.BatchID = BatchProgress.BatchID
        inner join ProgressType 
            on BatchProgress.ProgressTypeID = 
                ProgressType.ProgressTypeID                  
        group by Batch.BatchId, Batch.SheetMat
        having max(ProgressType.ProgressStep) not in (3, 6, 7)
)
    select
        Job.JobName, records.BatchID, records.SheetMat, 
        Job.SubmittedBy, ProgressType.ProgressType
        from records inner join Job
            on records.JobID = Job.JobID
        inner join ProgressType
            on records.ProgressStep = ProgressType.ProgressStep
        order by records.BatchID;

End Edit -->

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

Oleg gravatar image

Oleg
15.9k 2 4 24

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x58
x46

asked: Aug 02, 2010 at 02:05 PM

Seen: 1540 times

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