Is there a problem with the case and order by when you attempt to order by a
column that has been labeled?
example (with assist from user MySqlServer, thanks you)
CREATE TABLE tenbeat (
tenbeatcol1 int,
tenbeatcol2 int
)
INSERT INTO tenbeat VALUES(3,4)
INSERT INTO tenbeat VALUES(1,2)
INSERT INTO tenbeat VALUES(0,0)
/* this works */
select *,
case tenbeatcol1 when 1 then 'yes' else 'no' end as yesno
from tenbeat
order by yesno
/* this gives me an invalid column on the order by */
declare @.ord as varchar(10)
select @.ord = 'ok'
select *,
case tenbeatcol1 when 1 then 'yes' else 'no' end as yesno
from tenbeat
order by
case @.ord
When 'ok' Then yesno
Else tenbeatcol1
end
drop table tenbeat
thanks (any ideas?)
kes
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kesORDER BY allows you to specify
1. col_name
2. col_alias
3. col_position
It is the CASE statement that is generating the error. CASE does not work
with col_alias.. therefore gving error
Rakesh
"WebBuilder451" wrote:
> Is there a problem with the case and order by when you attempt to order by
a
> column that has been labeled?
> example (with assist from user MySqlServer, thanks you)
> CREATE TABLE tenbeat (
> tenbeatcol1 int,
> tenbeatcol2 int
> )
> INSERT INTO tenbeat VALUES(3,4)
> INSERT INTO tenbeat VALUES(1,2)
> INSERT INTO tenbeat VALUES(0,0)
> /* this works */
> select *,
> case tenbeatcol1 when 1 then 'yes' else 'no' end as yesno
> from tenbeat
> order by yesno
> /* this gives me an invalid column on the order by */
> declare @.ord as varchar(10)
> select @.ord = 'ok'
> select *,
> case tenbeatcol1 when 1 then 'yes' else 'no' end as yesno
> from tenbeat
> order by
> case @.ord
> When 'ok' Then yesno
> Else tenbeatcol1
> end
> drop table tenbeat
> thanks (any ideas?)
> kes
> --
> thanks (as always)
> some day i''m gona pay this forum back for all the help i''m getting
> kes|||You can reference an alias in the "order by" clause, but not from an
expression, the references should be straight. You will have to use the
expression used in the column list of the "select" statement.
-- works
select 1 as c1
order by c1
-- does not work
select 1 as c1
order by case when c1 = 1 then 1 else 0 end
> case tenbeatcol1 when 1 then 'yes' else 'no' end as yesno
> from tenbeat
> order by
> case @.ord
> When 'ok' Then yesno
> Else tenbeatcol1
...
order by
case @.ord
When 'ok' Then case tenbeatcol1 when 1 then 'yes' else 'no' end
Else tenbeatcol1
end
but now you will have another problem, and this is that the result's
datatype of a case expression is equal to the one with higher precedence in
it. In your case, column [tenbeatcol1] is "int" and the inner "case"
expression yield a "varchar", when sql server try to convert values 'yes' or
'no' to "int", then an error will araise.
Example:
use northwind
go
declare @.c varchar(15)
set @.c = 'yes'
select
case when c1 = 1 then 'yes' else 'no' end
from
(
select 1 as c1
union all
select 2 as c1
) as t1
order by
case
when @.c = 'yes' then case when c1 = 1 then 'yes' else 'no' end
else c1
end
go
you have to use datatypes and / or values that can be implicitly converted
in order to sql server promote then or you have to convert them explicitly.
declare @.c varchar(15)
set @.c = 'yes'
select
case when c1 = 1 then 'yes' else 'no' end
from
(
select 1 as c1
union all
select 2 as c1
) as t1
order by
case
when @.c = 'yes' then case when c1 = 1 then 'yes' else 'no' end
else ltrim(c1) <--
end
go
How do I use a variable in an ORDER BY clause?
http://www.aspfaq.com/show.asp?id=2501
AMB
"WebBuilder451" wrote:
> Is there a problem with the case and order by when you attempt to order by
a
> column that has been labeled?
> example (with assist from user MySqlServer, thanks you)
> CREATE TABLE tenbeat (
> tenbeatcol1 int,
> tenbeatcol2 int
> )
> INSERT INTO tenbeat VALUES(3,4)
> INSERT INTO tenbeat VALUES(1,2)
> INSERT INTO tenbeat VALUES(0,0)
> /* this works */
> select *,
> case tenbeatcol1 when 1 then 'yes' else 'no' end as yesno
> from tenbeat
> order by yesno
> /* this gives me an invalid column on the order by */
> declare @.ord as varchar(10)
> select @.ord = 'ok'
> select *,
> case tenbeatcol1 when 1 then 'yes' else 'no' end as yesno
> from tenbeat
> order by
> case @.ord
> When 'ok' Then yesno
> Else tenbeatcol1
> end
> drop table tenbeat
> thanks (any ideas?)
> kes
> --
> thanks (as always)
> some day i''m gona pay this forum back for all the help i''m getting
> kes|||try this:
declare @.ord as varchar(10)
select @.ord = 'ok'
Select * from
(select tenbeatcol1,tenbeatcol2,
case tenbeatcol1
when 1
then 'yes'
else 'no'
end 'yesno'
from tenbeat) t
order by case
When @.ord = 'ok'
Then yesno
Else convert(varchar,tenbeatcol1)
end
"WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
news:14F08262-2096-4A4D-825A-23F6EE8F2682@.microsoft.com...
> Is there a problem with the case and order by when you attempt to order by
> a
> column that has been labeled?
> example (with assist from user MySqlServer, thanks you)
> CREATE TABLE tenbeat (
> tenbeatcol1 int,
> tenbeatcol2 int
> )
> INSERT INTO tenbeat VALUES(3,4)
> INSERT INTO tenbeat VALUES(1,2)
> INSERT INTO tenbeat VALUES(0,0)
> /* this works */
> select *,
> case tenbeatcol1 when 1 then 'yes' else 'no' end as yesno
> from tenbeat
> order by yesno
> /* this gives me an invalid column on the order by */
> declare @.ord as varchar(10)
> select @.ord = 'ok'
> select *,
> case tenbeatcol1 when 1 then 'yes' else 'no' end as yesno
> from tenbeat
> order by
> case @.ord
> When 'ok' Then yesno
> Else tenbeatcol1
> end
> drop table tenbeat
> thanks (any ideas?)
> kes
> --
> thanks (as always)
> some day i''m gona pay this forum back for all the help i''m getting
> kes|||could the posting below work w/o a big performance hit?
[Select * from
(select tenbeatcol1,tenbeatcol2,
case tenbeatcol1
when 1
then 'yes'
else 'no'
end 'yesno'
from tenbeat) t
order by case
When @.ord = 'ok'
Then yesno
Else convert(varchar,tenbeatcol1)
end
]
thanks
kes
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"Alejandro Mesa" wrote:
> You can reference an alias in the "order by" clause, but not from an
> expression, the references should be straight. You will have to use the
> expression used in the column list of the "select" statement.
> -- works
> select 1 as c1
> order by c1
> -- does not work
> select 1 as c1
> order by case when c1 = 1 then 1 else 0 end
>
> ...
> order by
> case @.ord
> When 'ok' Then case tenbeatcol1 when 1 then 'yes' else 'no' end
> Else tenbeatcol1
> end
> but now you will have another problem, and this is that the result's
> datatype of a case expression is equal to the one with higher precedence i
n
> it. In your case, column [tenbeatcol1] is "int" and the inner "case"
> expression yield a "varchar", when sql server try to convert values 'yes'
or
> 'no' to "int", then an error will araise.
> Example:
> use northwind
> go
> declare @.c varchar(15)
> set @.c = 'yes'
> select
> case when c1 = 1 then 'yes' else 'no' end
> from
> (
> select 1 as c1
> union all
> select 2 as c1
> ) as t1
> order by
> case
> when @.c = 'yes' then case when c1 = 1 then 'yes' else 'no' end
> else c1
> end
> go
> you have to use datatypes and / or values that can be implicitly converted
> in order to sql server promote then or you have to convert them explicitly
.
> declare @.c varchar(15)
> set @.c = 'yes'
> select
> case when c1 = 1 then 'yes' else 'no' end
> from
> (
> select 1 as c1
> union all
> select 2 as c1
> ) as t1
> order by
> case
> when @.c = 'yes' then case when c1 = 1 then 'yes' else 'no' end
> else ltrim(c1) <--
> end
> go
> How do I use a variable in an ORDER BY clause?
> http://www.aspfaq.com/show.asp?id=2501
>
> AMB
> "WebBuilder451" wrote:
>|||thanks that's an alternative, i've asked someone else the same question, but
will this cause a performance hit?
this'd be great if it didn't
thanks
kes
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"Pradeep Kutty" wrote:
> try this:
> declare @.ord as varchar(10)
> select @.ord = 'ok'
> Select * from
> (select tenbeatcol1,tenbeatcol2,
> case tenbeatcol1
> when 1
> then 'yes'
> else 'no'
> end 'yesno'
> from tenbeat) t
> order by case
> When @.ord = 'ok'
> Then yesno
> Else convert(varchar,tenbeatcol1)
> end
> "WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
> news:14F08262-2096-4A4D-825A-23F6EE8F2682@.microsoft.com...
>
>|||depends on the data and how u index the table...
see the execution plan and profiler metrics...
if its bad try union all...
Prad
"WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
news:56A0A748-8E0D-4F28-A52A-40C500CB5090@.microsoft.com...
> thanks that's an alternative, i've asked someone else the same question,
> but
> will this cause a performance hit?
> this'd be great if it didn't
> thanks
> kes
> --
> thanks (as always)
> some day i''m gona pay this forum back for all the help i''m getting
> kes
>
> "Pradeep Kutty" wrote:
>|||so far it's ok, no difference, It's pulling from 5 tables/w total 1,000,000+
records, returning 3,500 rows in about 1 sec for the worst case so it's ok.
thanks
kes
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"Pradeep Kutty" wrote:
> depends on the data and how u index the table...
> see the execution plan and profiler metrics...
> if its bad try union all...
> Prad
> "WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
> news:56A0A748-8E0D-4F28-A52A-40C500CB5090@.microsoft.com...
>
>|||Can you do the sorting in the client application?
AMB
"WebBuilder451" wrote:
> could the posting below work w/o a big performance hit?
> [Select * from
> (select tenbeatcol1,tenbeatcol2,
> case tenbeatcol1
> when 1
> then 'yes'
> else 'no'
> end 'yesno'
> from tenbeat) t
> order by case
> When @.ord = 'ok'
> Then yesno
> Else convert(varchar,tenbeatcol1)
> end
> ]
> thanks
> kes
> --
> thanks (as always)
> some day i''m gona pay this forum back for all the help i''m getting
> kes
>
> "Alejandro Mesa" wrote:
>|||I can do a query of queries in the app language (it's cold fusion) This like
making a dataview on a dataset and sorting in DOT.NET. Although it is faster
than the dot.net dataview (sorry to blaspheme) it'd still be slower than the
suggested solution. In the end it seems that real speed all comes down to th
e
sql and the dbdesign.
thanks
kes
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"Alejandro Mesa" wrote:
> Can you do the sorting in the client application?
>
> AMB
> "WebBuilder451" wrote:
>
