Another store procedure question

[nc297]

I'm back with another one that I'm having problems with...

This is what I need to get out of the database.

Count(cossn) where dib-mvt-typ='r', org_id='r%' or 's%' and dib-mvt-seq-num = max dib-mvt_seq_num for that cossn and dib_mvt_seq_num!=null

I tried putting this query together and getting:

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.

Here's my attempt at the query:

Select t.doc, count(t.clmssn) as PendinDDS

from t2dibpend t

where DIB_MVT_TYP='R' and (org_id like 'R%' or org_id like 'S%')

and (dib_mvt_seq_num = max(dib_mvt_seq_num)
and dib_mvt_seq_num is not null)

group by t.doc


What am I doing wrong?

 

[gmmastros]

This might work:

Select t.doc, count(t.clmssn) as PendinDDS

from t2dibpend t

where DIB_MVT_TYP='R' and (org_id like 'R%' or org_id like 'S%')

group by t.doc
Having (dib_mvt_seq_num = max(dib_mvt_seq_num)
and dib_mvt_seq_num is not null)

Not tested.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[nc297]

Thanks gmmastros I will try it in the morning and get back to you.

 

[nc297]

Thanks Gmmastros I got this to work:

Select t.doc, count(t.clmssn) as PendinDDS, dib_mvt_seq_num
from t2dibpend t
where DIB_MVT_TYP='R' and (org_id like 'R%' or org_id like 'S%')
group by t.doc, dib_mvt_seq_num
Having (dib_mvt_seq_num = max(dib_mvt_seq_num)
and dib_mvt_seq_num is not null)
order by doc

I had to add dib_mvt_seq_num to the select and group by.

 

[gmmastros]

That's cool, but I would encourage you to double and triple check your output. Something doesn't seem right. I don't doubt that the query parses and runs (which only means there is no syntax error), but I suspect the query doesn't return the correct results either.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[nc297]

I spent an hour checking and it does return the info that I need.