Msg 8127 problem.

Hi,
I need help with this sql. It works for SQL Server 2000, SP3,
(8.00.760) but it fails in SQL Server 2005 (9.00.1399.06).
SELECT DATEPART(yy, os.[CreateTS]) as 'YEARCOL', DATEPART(mm,
os.[CreateTS]) as 'MONTHCOL', DATEPART(dd, os.[CreateTS]) as 'DAYCOL',
DATEPART(hh, os.[CreateTS]) as 'HOURCOL', min(os.[CreateTS]) as
'CreateTS',
SUM(os.[Lines]) as 'Lines'
FROM aTable os WITH (NOLOCK)
GROUP BY DATEPART(yy, os.[CreateTS]), DATEPART(mm, os.[CreateTS]),
DATEPART(dd, os.[CreateTS]) , DATEPART(hh, os.[CreateTS]) ORDER BY
os.[CreateTS]
I am not good at sql, any help is greatly appreciated. The SQL is
dynamically generated by a .Net code and called using OleDb command
class.
Thanks, Jose
You must order by columns in the select list or group by list.
As you say it's not the best SQL as it is generated.
You could:
order by DATEPART(yy, os.[CreateTS]), DATEPART(mm, os.[CreateTS]),
DATEPART(dd, os.[CreateTS]) , DATEPART(hh, os.[CreateTS])
or
order by min(os.[CreateTS])
or
order by 1,2,3,4
which means order by columns 1,2,3,4 in the select list.
The choice depends on the order you want and in the last case how lazy you
want to be
The syntax on SQL 2005 is generally tighter than 2000. Most people probably
had the odd issue with their code when they tested the upgrade.
Paul
"Cuellar" <cuellar1706@.gmail.com> wrote in message
news:1159915384.934558.170910@.i42g2000cwa.googlegr oups.com...
> Hi,
> I need help with this sql. It works for SQL Server 2000, SP3,
> (8.00.760) but it fails in SQL Server 2005 (9.00.1399.06).
> SELECT DATEPART(yy, os.[CreateTS]) as 'YEARCOL', DATEPART(mm,
> os.[CreateTS]) as 'MONTHCOL', DATEPART(dd, os.[CreateTS]) as 'DAYCOL',
> DATEPART(hh, os.[CreateTS]) as 'HOURCOL', min(os.[CreateTS]) as
> 'CreateTS',
> SUM(os.[Lines]) as 'Lines'
> FROM aTable os WITH (NOLOCK)
> GROUP BY DATEPART(yy, os.[CreateTS]), DATEPART(mm, os.[CreateTS]),
> DATEPART(dd, os.[CreateTS]) , DATEPART(hh, os.[CreateTS]) ORDER BY
> os.[CreateTS]
> I am not good at sql, any help is greatly appreciated. The SQL is
> dynamically generated by a .Net code and called using OleDb command
> class.
> Thanks, Jose
>
|||Fantastic, that explain the problem. It works.
I end up using the ALIAS:
order by 'CreateTS'
which it seems to work too, and I assume is equivalent to the:
order by min(os.[CreateTS])
one of the versions that you mentioned.
Thank so much,
Jose
Paul Cahill wrote:[vbcol=seagreen]
> You must order by columns in the select list or group by list.
> As you say it's not the best SQL as it is generated.
>
> You could:
> order by DATEPART(yy, os.[CreateTS]), DATEPART(mm, os.[CreateTS]),
> DATEPART(dd, os.[CreateTS]) , DATEPART(hh, os.[CreateTS])
> or
> order by min(os.[CreateTS])
> or
> order by 1,2,3,4
> which means order by columns 1,2,3,4 in the select list.
> The choice depends on the order you want and in the last case how lazy you
> want to be
> The syntax on SQL 2005 is generally tighter than 2000. Most people probably
> had the odd issue with their code when they tested the upgrade.
> Paul
>
>
> "Cuellar" <cuellar1706@.gmail.com> wrote in message
> news:1159915384.934558.170910@.i42g2000cwa.googlegr oups.com...
|||You are welcome.
Paul
"Cuellar" <cuellar1706@.gmail.com> wrote in message
news:1159977911.674457.60370@.m7g2000cwm.googlegrou ps.com...
> Fantastic, that explain the problem. It works.
> I end up using the ALIAS:
> order by 'CreateTS'
> which it seems to work too, and I assume is equivalent to the:
> order by min(os.[CreateTS])
> one of the versions that you mentioned.
> Thank so much,
> Jose
> Paul Cahill wrote:
>