The following query fails in QA:



select cast(year(dinvoice)as char) as csalesyear, replicate('0', 2 - len(cast(month(dinvoice) as char) )) +
cast(month(dinvoice) as char) as csalesmonth
from arinvc
left outer join aritrs on (arinvc.ccustno = aritrs.ccustno and arinvc.cinvno = aritrs.cinvno)
where arinvc.lvoid = 0 and arinvc.cinvno <> 'BAL F/W' and
(ARINVC.CSLPNNO = 'CAROL' and ARINVC.DINVOICE >= '2006-01-01' and ARINVC.DINVOICE <= '2007-01-31'
and ARITRS.CITEMNO IN (SELECT citemno from sr_items_in_group where citemgroup='PROSTATFAMILY'))
group by year(dinvoice), month(dinvoice) order by year(dinvoice), month(dinvoice)

The error messages are:
Server: Msg 8120, Level 16, State 1, Line 1
Column 'arinvc.dinvoice' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'arinvc.dinvoice' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'arinvc.dinvoice' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I never tried to use a group by clause, or select list clause with expressions. It looks like T-sql doesn't support this? Could I put the exact same expression in the group by as in the select list?

In the meantime I am going to retrieve the results into a temp table with the column names csalesyear and csalesmonth, and then do my group by directly on csalesyear and csalesmonth, which I assume should work.

Any help would be appreciated.