|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
case and order by problem?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 ORDER 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 Show quote "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 Show quote "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 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 -- Show quotethanks (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 > > > 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 Can you do the sorting in the client application?
AMB Show quote "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: > > > 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 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 the sql and the dbdesign. thanks kes -- Show quotethanks (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: > > > 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 > > > > > > > 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 On Fri, 2 Sep 2005 06:33:03 -0700, Alejandro Mesa wrote:
(snip) (snip) Show quote >you have to use datatypes and / or values that can be implicitly converted Hi Alejandro,>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 But that will result in alphabetic ordering of the numbers (1 - 11 - 2); I don't think that's what the OP wants. Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) Why make it hard? Try this:
select *, case tenbeatcol1 when 1 then 'yes' else 'no' end as yesno from #tenbeat order by case When @ord <> 'ok' Then tenbeatcol1 End, yesno Gert-Jan WebBuilder451 wrote: Show quote > > 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 how would i fit this here?
order by CASE @thsSrt WHEN 'Exchange' THEN exchange WHEN 'Company' THEN csiStkCompany WHEN 'PFSig' THEN PFBuySell WHEN 'PFXO' THEN stkhstXO WHEN 'PFTrend' THEN Trend WHEN 'RSSig' THEN RSBuySell WHEN 'RSXO' THEN stkhstRSXO WHEN 'tenWK' THEN tenBeat <------ HERE WHEN 'sym' THEN csistksym1 ELSE csistksym1 END -- Show quotethanks (as always) some day i''m gona pay this forum back for all the help i''m getting kes "Gert-Jan Strik" wrote: > Why make it hard? Try this: > > select *, > case tenbeatcol1 when 1 then 'yes' else 'no' end as yesno > from #tenbeat > order by > case When @ord <> 'ok' Then tenbeatcol1 End, yesno > > Gert-Jan > > > 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 > I am missing some information here, so I'll make some assumptions.
I think you suggest, that all names in the THEN clauses (such as "exchange", "csiStkCompany", etc.) are column names, with the exception of "tenBeat" which is an alias of the SELECT in question. If that is the case, then you could rewrite your ORDER BY clause as: ORDER BY CASE @thsSrt WHEN 'Exchange' THEN exchange WHEN 'Company' THEN csiStkCompany WHEN 'PFSig' THEN PFBuySell WHEN 'PFXO' THEN stkhstXO WHEN 'PFTrend' THEN Trend WHEN 'RSSig' THEN RSBuySell WHEN 'RSXO' THEN stkhstRSXO WHEN 'tenWK' THEN NULL <--- will leave the resultset unsorted for this situation WHEN 'sym' THEN csistksym1 ELSE csistksym1 END, tenBeat <--- additional ORDER BY expression Gert-Jan WebBuilder451 wrote: Show quote > > how would i fit this here? > > order by > CASE @thsSrt > WHEN 'Exchange' THEN exchange > WHEN 'Company' THEN csiStkCompany > WHEN 'PFSig' THEN PFBuySell > WHEN 'PFXO' THEN stkhstXO > WHEN 'PFTrend' THEN Trend > WHEN 'RSSig' THEN RSBuySell > WHEN 'RSXO' THEN stkhstRSXO > WHEN 'tenWK' THEN tenBeat <------ HERE > WHEN 'sym' THEN csistksym1 > ELSE csistksym1 > END > -- > thanks (as always) > some day i''m gona pay this forum back for all the help i''m getting > kes [snip] On Fri, 2 Sep 2005 09:04:05 -0700, WebBuilder451 wrote:
Show quote >how would i fit this here? Hi WebBuilder451,> >order by >CASE @thsSrt > WHEN 'Exchange' THEN exchange > WHEN 'Company' THEN csiStkCompany > WHEN 'PFSig' THEN PFBuySell > WHEN 'PFXO' THEN stkhstXO > WHEN 'PFTrend' THEN Trend > WHEN 'RSSig' THEN RSBuySell > WHEN 'RSXO' THEN stkhstRSXO > WHEN 'tenWK' THEN tenBeat <------ HERE > WHEN 'sym' THEN csistksym1 > ELSE csistksym1 >END It's better to post the complete problem at once :-) Assuming that the tenBeat column is more or less the same as the YesNo column in your previous question: Option #1 (can only be used if all columns are of the same datatype) order by CASE @thsSrt WHEN 'Exchange' THEN exchange WHEN 'Company' THEN csiStkCompany WHEN 'PFSig' THEN PFBuySell WHEN 'PFXO' THEN stkhstXO WHEN 'PFTrend' THEN Trend WHEN 'RSSig' THEN RSBuySell WHEN 'RSXO' THEN stkhstRSXO WHEN 'tenWK' THEN CASE tenbeatcol1 WHEN 1 THEN 'yes' ELSE 'no' END WHEN 'sym' THEN csistksym1 ELSE csistksym1 END Option #2 (grouping similar datatypes) order by -- Alphabetic columns CASE @thsSrt WHEN 'PFXO' THEN stkhstXO WHEN 'PFTrend' THEN Trend WHEN 'tenWK' THEN CASE tenbeatcol1 WHEN 1 THEN 'yes' ELSE 'no' END WHEN 'sym' THEN csistksym1 END, -- NNumeric columns CASE @thsSrt WHEN 'Exchange' THEN exchange WHEN 'Company' THEN csiStkCompany WHEN 'RSSig' THEN RSBuySell WHEN 'RSXO' THEN stkhstRSXO END, -- Datetime columns CASE @thsSrt WHEN 'PFSig' THEN PFBuySell WHEN 'PFXO' THEN stkhstXO END Option #3 (no need to check datatypes; will always work) order by CASE WHEN @thsSrt = 'Exchange' THEN exchange END, CASE WHEN @thsSrt = 'Company' THEN csiStkCompany END, CASE WHEN @thsSrt = 'PFSig' THEN PFBuySell END, CASE WHEN @thsSrt = 'PFXO' THEN stkhstXO END, CASE WHEN @thsSrt = 'PFTrend' THEN Trend END, CASE WHEN @thsSrt = 'RSSig' THEN RSBuySell END, CASE WHEN @thsSrt = 'RSXO' THEN stkhstRSXO END, CASE WHEN @thsSrt = 'tenWK' THEN CASE tenbeatcol1 WHEN 1 THEN 'yes' ELSE 'no' END END, CASE WHEN @thsSrt = 'sym' THEN csistksym1 END, CASE WHEN @thsSrt NOT IN ('Exchange','Company',...) THEN csistksym1 END END Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) 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 Show quote "WebBuilder451" <WebBuilder***@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 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 -- Show quotethanks (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" <WebBuilder***@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 > > > depends on the data and how u index the table...
see the execution plan and profiler metrics... if its bad try union all... Prad Show quote "WebBuilder451" <WebBuilder***@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: > >> 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" <WebBuilder***@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 >> >> >> 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 -- Show quotethanks (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" <WebBuilder***@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: > > > >> 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" <WebBuilder***@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 > >> > >> > >> > > >  |
|||||||||||||||||||||||
Other interesting topics