May 27, 2004 at 5:10 am
I have query within a stored procedure which I wish to put a conditional order by on. The extracted query is:
select
[text],
count(webusageid) as hits,
right(convert(varchar,[timestamp],106),8) as [month],
deptid,
left(convert(varchar,[timestamp],112),6) as [month_order]
from
tbl_webusage inner join tbl_person
on tbl_webusage.personnelid = tbl_person.personnelid
and
tbl_webusage.projectcodeid = @project
and
([timestamp] > (@fromdate - 1)
or
@fromdate = '')
and
([timestamp] < (@todate + 1)
or
@todate = '')
and
(tbl_webusage.personnelid not in (select personnelid from
tbl_projectperson where projectcodeid = @project)
or
@all = 1)
left outer join fisp_transfer.dbo.tbl_fisp_people fisp
on tbl_person.staff_number = fisp.emplid COLLATE
database_default
inner join tbl_menudisplay
on tbl_webusage.displayid = tbl_menudisplay.displayid
group by
[text],
right(convert(varchar,[timestamp],106),8),
left(convert(varchar,[timestamp],112),6),
deptid
order by
case when @orderby = 1 then [text] end asc,
case when @orderby = 2 then
left(convert(varchar,[timestamp],112),6) end asc,
case when @orderby = 3 then deptid end asc,
case when @orderby = 0 then
left(convert(varchar,[timestamp],112),6) end asc
This produces an error Column name 'tbl_webusage.timestamp' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
I have tried adding count(timestamp) as dummy to the select list but I still get the above error message. If I add timestamp to the group by it happily creates the procedure but obviously the returned select is not what I need.
It was quite happy with my original query which was:
select
[text],
count(webusageid) as hits,
right(convert(varchar,[timestamp],106),8) as [month],
deptid,
left(convert(varchar,[timestamp],112),6) as [month_order]
from
tbl_webusage inner join tbl_person
on tbl_webusage.personnelid = tbl_person.personnelid
and
tbl_webusage.projectcodeid = @project
and
([timestamp] > (@fromdate - 1)
or
@fromdate = '')
and
([timestamp] < (@todate + 1)
or
@todate = '')
and
(tbl_webusage.personnelid not in (select personnelid from
tbl_projectperson where projectcodeid = @project)
or
@all = 1)
left outer join fisp_transfer.dbo.tbl_fisp_people fisp
on tbl_person.staff_number = fisp.emplid COLLATE
database_default
inner join tbl_menudisplay
on tbl_webusage.displayid = tbl_menudisplay.displayid
group by
[text],
right(convert(varchar,[timestamp],106),8),
left(convert(varchar,[timestamp],112),6),
deptid
order by
left(convert(varchar,[timestamp],112),6)
Does anybody know how I can convince SQL Server that it is a valid query?
May 27, 2004 at 6:04 am
Try this
order by
(case when @orderby = 1 then [text]
when @orderby = 2 then left(convert(varchar,[timestamp],112),6)
when @orderby = 3 then deptid
when @orderby = 0 then left(convert(varchar,[timestamp],112),6) end)
Note: If [text] is a text datatype column you cannot use it for ordering unless you convert to a a char or varchar datatype. Also ASC is default and understood.
Also, although this works in may not be the best thing to do. If another application is pulling this data in and you are using a client side cursor (which I prefer for fairly small datasets) then consider using the Sort method in the ADO Recordet object.
May 27, 2004 at 6:43 am
No good - I still get the same error message.
May 27, 2004 at 9:19 am
Still doesn't work - I had tried the equivalent of that with count(timestamp) already but max didn't work either.
I have created a view on the table so that my right(convert(varchar,[timestamp],106),8) and left(convert(varchar,[timestamp],112),6) are selected in the view so that I am just referring to the column names and it is a happy bunny. The fact that technically there is no difference is annoying but there you go!
May 27, 2004 at 10:09 am
Try (explination after):
select
[text],
count(webusageid) as hits,
right(convert(varchar,[timestamp],106),8) as [month],
deptid,
left(convert(varchar,[timestamp],112),6) as [month_order]
from
tbl_webusage inner join tbl_person
on tbl_webusage.personnelid = tbl_person.personnelid
and
tbl_webusage.projectcodeid = @project
and
([timestamp] > (@fromdate - 1)
or
@fromdate = '')
and
([timestamp] < (@todate + 1)
or
@todate = '')
and
(tbl_webusage.personnelid not in (select personnelid from
tbl_projectperson where projectcodeid = @project)
or
@all = 1)
left outer join fisp_transfer.dbo.tbl_fisp_people fisp
on tbl_person.staff_number = fisp.emplid COLLATE
database_default
inner join tbl_menudisplay
on tbl_webusage.displayid = tbl_menudisplay.displayid
group by
[text],
right(convert(varchar,[timestamp],106),8),
left(convert(varchar,[timestamp],112),6),
deptid,
case when @orderby = 1 then [text] end,
case when @orderby = 2 then
left(convert(varchar,[timestamp],112),6) end,
case when @orderby = 3 then deptid end,
case when @orderby = 0 then
left(convert(varchar,[timestamp],112),6) end
order by
case when @orderby = 1 then [text] end asc,
case when @orderby = 2 then
left(convert(varchar,[timestamp],112),6) end asc,
case when @orderby = 3 then deptid end asc,
case when @orderby = 0 then
left(convert(varchar,[timestamp],112),6) end asc
If you use a case for some reason that case must also be in your group by. All your case statements resolve to nothing or something already grouped on, so adding the cases to your group should not change the results at all. You may take a performace hit but this should work.
Thanks for the post, I didn't know that about case statements (which I use all the time.)
May 28, 2004 at 1:52 am
Thanks for that - that works!
I have left the query using the view as I suspect you are right in that the above will be slower, but I was sure it could be acheived with a single query and hate being beaten 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply