case and order by problem?

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:
>

Cascading Parameters Unreliable

I find that cascading parameters work intermittently. In addition, a
report with a drop down list will sometimes attempt to execute before
the user has made a selection.make sure you have the parameters in the proper order for cascading to work
properly. i.e. country parm first in your parameter list, state parameter
second, city parameter third... all the cascading parameters have to be
ahead of your other non-cascading parameters. to ensure the report will not
execute before a user selects parms... just leave 1 parm without default
value.
"mjhillman@.msn.com" wrote:
> I find that cascading parameters work intermittently. In addition, a
> report with a drop down list will sometimes attempt to execute before
> the user has made a selection.
>|||I am calling two stored procedures that take the same parameter. I
only have one parameter in the parameter list. Does this suggestion
apply to this scenario as well? Do I have to modify the SP to use a
different parameter name? (I did not write the SPs I am just executing
reports against an existing WMS.). Thanks.

Capture IP Address

Does anyone know how to configure profiler or a way to capture an IP address
of a Failed Login Attempt user to a SQL Server 2000?
Thanks in advance.
JohnJohn,
If you are getting hack in attempts, just go to the command prompt and
type
netstat -n
The SQL attacks will be on 1433 and will be listed as time wait (assuming
you check when you are being hacked).
Russ Stevens|||Keeping up to date with database security can get so stressful makes me sick
to gut.
Thank you very much Russ for your helpful post. I really appreciate it.
-J
"Russell Stevens" <rustyprogrammer@.online.nospam> wrote in message
news:u3mohmk7FHA.3976@.TK2MSFTNGP15.phx.gbl...
> John,
> If you are getting hack in attempts, just go to the command prompt and
> type
> netstat -n
> The SQL attacks will be on 1433 and will be listed as time wait (assuming
> you check when you are being hacked).
> Russ Stevens
>|||Hmmm...I apologize if this is a novice observation but I just noticed
something interesting that I thought I would share...
I have some MS Access front end databases linked to SQL Server 2000
databases as the backend (tables) on our internal network. I create new SQL
logins for every user that requests access to the database and capture every
user action through SQL Profiler. In analyzing the Profiler trace logs I
noticed some Login Failed attempts to our Master database which really
raised my concern. The interesting part is that the Login Failed attempt
kept saying user 'Admin'. I know that we don't have a specific user name to
any of our databases named 'Admin' especially to our Master. I just did a
test and went to open my linked table through MS Access to SQL Server 2000
and that the Profiler logged the event as a Login Failed attempt to the
Master database even though the linked table is to one of my other defined
databases. Then the dsn odbc login pop up comes up and then I log in with
my valid specified user name and password which is not 'Admin' and am able
to login successfully. All of the Login Failed attempts with 'Admin'
throughout my trace logs have a successful login immediately after with a
valid user name. So it appears that when accessing a SQL Server table
through a link from MS Access it by default tries to access the Master
database with the default user name of 'Admin' and then prompts the user for
the valid login name and password. So maybe these weren't hack attempts
(which I am truly hoping)?
Would be interested if someone could confirm to me if this whole process is
accurate?
Thanks in advance.
-J
"John" <IDontLikeSpam@.Nowhere.com> wrote in message
news:%23pr5wns7FHA.3808@.TK2MSFTNGP10.phx.gbl...
> Keeping up to date with database security can get so stressful makes me
> sick to gut.
> Thank you very much Russ for your helpful post. I really appreciate it.
> -J
> "Russell Stevens" <rustyprogrammer@.online.nospam> wrote in message
> news:u3mohmk7FHA.3976@.TK2MSFTNGP15.phx.gbl...
>