Case statement in Order by clause

Hi

I need a case statement with in the order clause,

There are fields like below

Feild A FieldB LevelA LevelB

xxxx xxxx

xxxx xxxx

xxxx xxxx 3 2

xxxxx xxxx 2 1

xxxxxx xxxxx 1 1

I need to sort LevelA and LevelB first leaving out the null or blank values in the top rows

Feild A FieldB LevelA LevelB

xxxx xxxx 1 1

xxxx xxxx 2 1

xxxx xxxx 3 2

xxxxx xxxx

xxxxxx xxxxx

Thanks

like this:

Code Snippet

create table #t (FieldA varchar(10), FieldB varchar(10), LevelA int, LevelB int)

insert into #t

select 'xxxx', 'xxxx', null, null

union all select 'xxxx', 'xxxx', null, null

union all select 'xxxx', 'xxxx', 3, 2

union all select 'xxxxx', 'xxxx', 2, 1

union all select 'xxxxxx', 'xxxxx', 1, 1

union all select 'xxxxxx', 'xxxxx', 1, 2

union all select 'xxxxxx', 'xxxxx', 1, 3

select *

from #t

order by case when LevelA is not null then 1 else 9 end,

LevelA,

case when LevelB is not null then 1 else 9 end,

LevelB

|||

You can use a CASE expression like below:

order by coalesce(LevelA, power(2., 31)-1), coalesce(LevelB, power(2., 31)-1)

|||Umachandar,

Some readers might why you said "CASE expression," so I'll remark that COALESCE(a,b) is basically the same as CASE WHEN a IS NULL THEN b ELSE a END.

Sowree,

I think the suggestion below will work, and here I've added the requirement that blank values appear last. (If or are number types, they can't be blank, and this code will incorrectly put values of 0 last, because '' is implicitly converted to 0 in the comparison.)

ORDER BY
CASE WHEN a IS NULL OR a = '' THEN 1 ELSE 0 END,
a,
CASE WHEN b IS NULL OR b = '' THEN 1 ELSE 0 END,
b

Your question isn't entirely clear, so you may have to adapt the suggestions to your exact need.

Steve Kass
Drew University
http://www.stevekass.com

Case Sensitive of MSSQL2005

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=246692&SiteID=1
Form reply of "Absolute_Zero":
In order to get the case senstive to work, go inside SQL 2005 and right click the column and choose modify. Under neith the table designer field click the Collation button which will bring you to more advanced options. Next, choose Windows Collation | Dictionary Sort | Case Sensitive. Then queries ran against the column will now check for case sensitivity.

I got a problem.
1) MSSQL2005, by default, data is non case-sensitive?
2) How to use SQL cmd to change "Windows Collation | Dictionary Sort | Case Sensitive"?
3) How to make MSSQL2005, default is case-sensitive?

The default collation is set upon instalaltion time, for changing either tables / database or the whole server, refer to:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36707

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Working with collations is documented in the SQL Server Books Online.

Working with collations

Setting and changing collations

Cheers,

Dan

sql

CASE Problem in Stored Procedure

Can anyone see a problem with this stored procedure.
When I do this one it works fine.

CREATE Procedure SS_SoftList
(
@.CompanyID nvarchar(10),
@.Order varchar(20)
)
As

SELECT
SS_Soft_deploy.Softwarename,
( COUNT(SS_Soft_deploy.Softwarename)) as recordcount

FROM
SS_Soft_deploy

WHERE
SS_Soft_deploy.CompanyID = @.CompanyID

GROUP BY
SS_Soft_deploy.Softwarename

ORDER BY recordcount
--CASE WHEN @.Order = 'softname' THEN Softwarename END,
--CASE WHEN @.Order = 'count' THEN recordcount END
GO

However when I un-rem the CASE statement
ORDER BY
CASE WHEN @.Order = 'softname' THEN Softwarename END,
CASE WHEN @.Order = 'count' THEN recordcount END
GO

It reports an error that says "Invalid Column Name recordcount"
Any Ideas??Recordcount is an alias. You cannot sort on that using the alias.

CASE WHEN @.Order = 'softname' THEN Softwarename END,
CASE WHEN @.Order = 'count' THEN ( COUNT(SS_Soft_deploy.Softwarename)) END

|||Why does it work when I do the
ORDER BY recordcount
??|||Hmm. I did not realize that would work using the alias in the ORDER BY, but apparently it does. It does not work inside the case statement because that is a different expression, and that expression is unaware of the alias. Repeating the expression, however, works fine. If the expression were terribly complex, you could alternately create a user defined function and use that for the row and the ORDER BY (knowing that performance would likely be less than great).

Case order by

Hello
I was wondering if you guys could help me, I have a table with Firstname and
Lastname columns.
And I want to pass a parameter @.SortCol char(10) and @.AscDesc, so I can sort
Firstname ASC/DESC or Lastname ASC/DESC....
how's that done, i need to put 4 different Cases?
TIA
/LasseWhat about that
Use northwind
DECLARE @.col varchar(200)
DECLARE @.Direc varchar(200)
SET @.col = 'OrderID'
SET @.Direc = 'ASC'
EXEC('Select * from Orders Order by ' + @.col + ' ' + @.Direc)
http://www.sommarskog.se/dynamic_sql.html
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Lasse Edsvik" <lasse@.nospam.com> schrieb im Newsbeitrag
news:OKlIIzxSFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Hello
> I was wondering if you guys could help me, I have a table with Firstname
> and
> Lastname columns.
> And I want to pass a parameter @.SortCol char(10) and @.AscDesc, so I can
> sort
> Firstname ASC/DESC or Lastname ASC/DESC....
> how's that done, i need to put 4 different Cases?
> TIA
> /Lasse
>|||SELECT <column list>
FROM <table>
ORDER BY CASE @.AscDesc WHEN 'ASC' THEN
CASE @.SortCol
WHEN 'Firstname' THEN Firstname
WHEN 'Lastname' THEN Lastname
END END ASC,
CASE @.AscDesc WHEN 'DESC' THEN
CASE @.SortCol
WHEN 'Firstname' THEN Firstname
WHEN 'Lastname' THEN Lastname
END END DESC,
Jacco Schalkwijk
SQL Server MVP
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:OKlIIzxSFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Hello
> I was wondering if you guys could help me, I have a table with Firstname
> and
> Lastname columns.
> And I want to pass a parameter @.SortCol char(10) and @.AscDesc, so I can
> sort
> Firstname ASC/DESC or Lastname ASC/DESC....
> how's that done, i need to put 4 different Cases?
> TIA
> /Lasse
>|||Lasse
ORDER BY CASE WHEN @.sort = 'col1' AND @.dir = 1 THEN col1 END ASC,
CASE WHEN @.sort = 'col1' AND @.dir = 0 THEN col1 END DESC,
CASE WHEN @.sort = 'col2' AND @.dir = 1 THEN col2 END ASC,
CASE WHEN @.sort = 'col2' AND @.dir = 0 THEN col2 END DESC
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:OKlIIzxSFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Hello
> I was wondering if you guys could help me, I have a table with Firstname
and
> Lastname columns.
> And I want to pass a parameter @.SortCol char(10) and @.AscDesc, so I can
sort
> Firstname ASC/DESC or Lastname ASC/DESC....
> how's that done, i need to put 4 different Cases?
> TIA
> /Lasse
>|||How do I use a variable in an ORDER BY clause?
http://www.aspfaq.com/show.asp?id=2501
AMB
"Lasse Edsvik" wrote:

> Hello
> I was wondering if you guys could help me, I have a table with Firstname a
nd
> Lastname columns.
> And I want to pass a parameter @.SortCol char(10) and @.AscDesc, so I can so
rt
> Firstname ASC/DESC or Lastname ASC/DESC....
> how's that done, i need to put 4 different Cases?
> TIA
> /Lasse
>
>|||The below uses one input variable to control which column to sort by, and
whether to cort asc or desc...
Use NorthWind
Declare @.Order TinyInt
Set @.Order = 1 -- 2,3,4
-- --
Select * From Customers
Order By Case @.Order
When 1 Then CompanyName
When 3 Then ContactName
End,
Case @.Order
When 2 Then CompanyName
When 4 Then ContactName
End Desc
"Lasse Edsvik" wrote:

> Hello
> I was wondering if you guys could help me, I have a table with Firstname a
nd
> Lastname columns.
> And I want to pass a parameter @.SortCol char(10) and @.AscDesc, so I can so
rt
> Firstname ASC/DESC or Lastname ASC/DESC....
> how's that done, i need to put 4 different Cases?
> TIA
> /Lasse
>
>sql

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

CASE alias in WHERE

Hello,

I found members of this group very helpful for my last queries.
Have one problem with CASE. I can use the column name alias in Order By Clause
but unable to use it in WHERE CLAUSE.
PLS TELL ME IF IT IS POSSIBLE TO USE IT IN WHERE CLAUSE AND SOME ALTERNATIVE.

QUERY:

SELECT
M.SECS =
CASE
WHEN NO_OF_SEC IS NULL THEN -1
WHEN NO_OF_SEC =0 THEN 1
ELSE NO_OF_SEC
END
FROM DOWNLOAD_MASTER M
WHERE M.SECS < 100
ORDER BY M.SECS

Hoping for a immediate reply.
thanks in advanceReferences to column aliases are only valid in the ORDER BY clause. You can
work around this by putting the expression into a derived table:

SELECT secs
FROM
(SELECT secs =
CASE
WHEN no_of_sec IS NULL THEN -1
WHEN no_of_sec = 0 THEN 1
ELSE no_of_sec
END
FROM DOWNLOAD_MASTER) AS M
WHERE secs < 100
ORDER BY secs

--
David Portas
SQL Server MVP
--|||On 5 Jul 2004 04:02:38 -0700, A.V.C. wrote:

>Hello,
>I found members of this group very helpful for my last queries.
>Have one problem with CASE. I can use the column name alias in Order By Clause
>but unable to use it in WHERE CLAUSE.
>PLS TELL ME IF IT IS POSSIBLE TO USE IT IN WHERE CLAUSE AND SOME ALTERNATIVE.
>QUERY:
>SELECT
>M.SECS =
>CASE
>WHEN NO_OF_SEC IS NULL THEN -1
>WHEN NO_OF_SEC =0 THEN 1
>ELSE NO_OF_SEC
>END
>FROM DOWNLOAD_MASTER M
>WHERE M.SECS < 100
>ORDER BY M.SECS
>
>Hoping for a immediate reply.
>thanks in advance

Hi A.V.C.,

Before answering your question, one remark about your query. I advise you
to remove "M." before "SECS". You are using an alias; not a column name.
The name "M.SECS" looks as if you're referring to a column named SECS in
the table named (or aliased) M. I expect the above query to throw an error
because column SECS can't be found in the table DOWNLOAD_MASTER. The order
by clause will probably not throw an error, but that is only because table
names (or aliases) are largely mostly disregarded by SQL Server when
evaluating an roder by clause.

To answer your question: no, this is not possible. To understand why, it
helps to know how an SQL query gets evaluated. Note that this is a
conceptual description; a good RDBMS will change the order of operation to
optimize; as long as the results remain the same that is not a problem.

Step 1: Evaluate FROM clause, build intermediate table from all rows in
the tables used, joined together on the conditions given. If old style
join syntax is used (with the ON conditions in the WHERE clause), this
step will yield the full carthesian product of all tables used.

Step 2: Evaluate WHERE clause, remove rows that don't match the criteria
from intermediate table.

Step 3: Evaluate GROUP BY clause, group rows together according to the
specified arguments.

Step 4: Evaluate HAVING clause, remove groups that don't match the
criteria from intermediate table.

Step 5: Evaluate SELECT clause, build result set to be returned from the
data in the intermediate table.

Step 6: Evaluate ORDER BY, perform sorting.

Officially, columns that are not included in the SELECT clause are not
available for sorting. Many products (like SQL Server) do allow this, but
it is a non-standard extension of the ISO/ANSI SQL-92 specification (and I
don't think that later SQL specifications included this).

Since the alias of a columns or expression is only effective from step 5
but the WHERE clause is evaluated as step 2, it is clear that an alias
can't be used in the WHERE clause.

You also ask for alternatives. In your case, you could try either "WHERE
COALESCE(NO_OF_SEC, -1) < 100" or "WHERE NO_OF_SEC < 100 OR NO_OF_SEC IS
NULL". In more complex cases, you might have to repeat the CASE expression
in the WHERE clause. If you dislike that redundancy, you can always use
the derived table approach. For your query, the equivalent with a derived
table would look like this:

SELECT SECS
FROM (SELECT SECS = CASE
WHEN NO_OF_SEC IS NULL THEN -1
WHEN NO_OF_SEC = 0 THEN 1
ELSE NO_OF_SEC
END
FROM DOWNLOAD_MASTER) AS D
WHERE SECS < 100
ORDER BY SECS
(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you David Portas and Hugo Kornelis

I agree with the point(M.) mentioned by Hugo Kornelis.
I appreciate your way of writing descriptive answers.

Thanks once again.

Cascading Updates / Delete Problem

I have 3 tables set up in the typical customer - order - order item setup. I
have cascading updates & deletes on the customer and order tables. Primary &
secondary keys are correct.
When I delete, or update the customer table key field, it takes about 60
seconds to cascade to the child tables. If I execute manually the delete
operation startting at the order item table, then the orders, and finally the
customer table it only takes a second.
Why is the cascading taking so long, when doing the same task manually takes
a fraction of the time?If you do the manual delete, do you have the cascading foreign leys in place
or not? If you don't, you can probably fix the issue by creating indexes on
the foreign key columns.
--
Jacco Schalkwijk
SQL Server MVP
"Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
news:AEEEB81C-C1F2-4AD1-B2EE-39E4B8C88F32@.microsoft.com...
>I have 3 tables set up in the typical customer - order - order item setup.
>I
> have cascading updates & deletes on the customer and order tables. Primary
> &
> secondary keys are correct.
> When I delete, or update the customer table key field, it takes about 60
> seconds to cascade to the child tables. If I execute manually the delete
> operation startting at the order item table, then the orders, and finally
> the
> customer table it only takes a second.
> Why is the cascading taking so long, when doing the same task manually
> takes
> a fraction of the time?|||All keys are in place.
"Jacco Schalkwijk" wrote:
> If you do the manual delete, do you have the cascading foreign leys in place
> or not? If you don't, you can probably fix the issue by creating indexes on
> the foreign key columns.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
> news:AEEEB81C-C1F2-4AD1-B2EE-39E4B8C88F32@.microsoft.com...
> >I have 3 tables set up in the typical customer - order - order item setup.
> >I
> > have cascading updates & deletes on the customer and order tables. Primary
> > &
> > secondary keys are correct.
> > When I delete, or update the customer table key field, it takes about 60
> > seconds to cascade to the child tables. If I execute manually the delete
> > operation startting at the order item table, then the orders, and finally
> > the
> > customer table it only takes a second.
> > Why is the cascading taking so long, when doing the same task manually
> > takes
> > a fraction of the time?
>
>|||"Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
news:232641B7-91F9-439E-83EE-3ECB623432F8@.microsoft.com...
> All keys are in place.
>
Then you'll need to post a repro.
David|||What about indexes on the foreign key columns?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
news:232641B7-91F9-439E-83EE-3ECB623432F8@.microsoft.com...
> All keys are in place.
> "Jacco Schalkwijk" wrote:
>> If you do the manual delete, do you have the cascading foreign leys in place
>> or not? If you don't, you can probably fix the issue by creating indexes on
>> the foreign key columns.
>> --
>> Jacco Schalkwijk
>> SQL Server MVP
>>
>> "Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
>> news:AEEEB81C-C1F2-4AD1-B2EE-39E4B8C88F32@.microsoft.com...
>> >I have 3 tables set up in the typical customer - order - order item setup.
>> >I
>> > have cascading updates & deletes on the customer and order tables. Primary
>> > &
>> > secondary keys are correct.
>> > When I delete, or update the customer table key field, it takes about 60
>> > seconds to cascade to the child tables. If I execute manually the delete
>> > operation startting at the order item table, then the orders, and finally
>> > the
>> > customer table it only takes a second.
>> > Why is the cascading taking so long, when doing the same task manually
>> > takes
>> > a fraction of the time?
>>

Cascading Updates / Delete Problem

I have 3 tables set up in the typical customer - order - order item setup. I
have cascading updates & deletes on the customer and order tables. Primary &
secondary keys are correct.
When I delete, or update the customer table key field, it takes about 60
seconds to cascade to the child tables. If I execute manually the delete
operation startting at the order item table, then the orders, and finally the
customer table it only takes a second.
Why is the cascading taking so long, when doing the same task manually takes
a fraction of the time?
If you do the manual delete, do you have the cascading foreign leys in place
or not? If you don't, you can probably fix the issue by creating indexes on
the foreign key columns.
Jacco Schalkwijk
SQL Server MVP
"Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
news:AEEEB81C-C1F2-4AD1-B2EE-39E4B8C88F32@.microsoft.com...
>I have 3 tables set up in the typical customer - order - order item setup.
>I
> have cascading updates & deletes on the customer and order tables. Primary
> &
> secondary keys are correct.
> When I delete, or update the customer table key field, it takes about 60
> seconds to cascade to the child tables. If I execute manually the delete
> operation startting at the order item table, then the orders, and finally
> the
> customer table it only takes a second.
> Why is the cascading taking so long, when doing the same task manually
> takes
> a fraction of the time?
|||All keys are in place.
"Jacco Schalkwijk" wrote:

> If you do the manual delete, do you have the cascading foreign leys in place
> or not? If you don't, you can probably fix the issue by creating indexes on
> the foreign key columns.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
> news:AEEEB81C-C1F2-4AD1-B2EE-39E4B8C88F32@.microsoft.com...
>
>
|||"Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
news:232641B7-91F9-439E-83EE-3ECB623432F8@.microsoft.com...
> All keys are in place.
>
Then you'll need to post a repro.
David
|||What about indexes on the foreign key columns?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
news:232641B7-91F9-439E-83EE-3ECB623432F8@.microsoft.com...[vbcol=seagreen]
> All keys are in place.
> "Jacco Schalkwijk" wrote:

Cascading Updates / Delete Problem

I have 3 tables set up in the typical customer - order - order item setup. I
have cascading updates & deletes on the customer and order tables. Primary &
secondary keys are correct.
When I delete, or update the customer table key field, it takes about 60
seconds to cascade to the child tables. If I execute manually the delete
operation startting at the order item table, then the orders, and finally th
e
customer table it only takes a second.
Why is the cascading taking so long, when doing the same task manually takes
a fraction of the time?If you do the manual delete, do you have the cascading foreign leys in place
or not? If you don't, you can probably fix the issue by creating indexes on
the foreign key columns.
Jacco Schalkwijk
SQL Server MVP
"Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
news:AEEEB81C-C1F2-4AD1-B2EE-39E4B8C88F32@.microsoft.com...
>I have 3 tables set up in the typical customer - order - order item setup.
>I
> have cascading updates & deletes on the customer and order tables. Primary
> &
> secondary keys are correct.
> When I delete, or update the customer table key field, it takes about 60
> seconds to cascade to the child tables. If I execute manually the delete
> operation startting at the order item table, then the orders, and finally
> the
> customer table it only takes a second.
> Why is the cascading taking so long, when doing the same task manually
> takes
> a fraction of the time?|||All keys are in place.
"Jacco Schalkwijk" wrote:

> If you do the manual delete, do you have the cascading foreign leys in pla
ce
> or not? If you don't, you can probably fix the issue by creating indexes o
n
> the foreign key columns.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
> news:AEEEB81C-C1F2-4AD1-B2EE-39E4B8C88F32@.microsoft.com...
>
>|||"Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
news:232641B7-91F9-439E-83EE-3ECB623432F8@.microsoft.com...
> All keys are in place.
>
Then you'll need to post a repro.
David|||What about indexes on the foreign key columns?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
news:232641B7-91F9-439E-83EE-3ECB623432F8@.microsoft.com...[vbcol=seagreen]
> All keys are in place.
> "Jacco Schalkwijk" wrote:
>

Cascading query with parameters

I need to get at some information and this is the only way I know how to do it but I need to run these queries in order and using the previous query output. I don't know how to set up the output parameters so they can be used in the second query and the second query results to be used in the third query.

In a nutshell: The first query gets a number from a user as a parameter and pulls one record containing multiple fields. Some of those fields will be needed as parameters in the second query to pull mutltiple records. I need to put some where statements in to narrow down the result set. Then this part I haven't figured out either, but then the third query takes one record from the second result set and pulls mutliple records to put in a report.

I hope at least a good part of this makes sense. Please HELP ME!

ALTER PROCEDURE sp_getOfficerList

@.compositeNumber int

as

SELECT tblCampus.fldCampusCode, tblGroup.fldGroupCode, tblContract.fldGraduationMonthCode, tblComposite.fldCompositeNumber,

tblContract.fldContractCode, tblGroup.fldGroupName, tblCampus.fldCampusName

FROM tblComposite INNER JOIN

tblContract ON tblComposite.fldContractID = tblContract.fldContractID INNER JOIN

tblOrganization ON tblContract.fldOrganizationID = tblOrganization.fldOrganizationID INNER JOIN

tblGroup ON tblOrganization.fldGroupID = tblGroup.fldGroupID INNER JOIN

tblCampus ON tblOrganization.fldCampusID =tblCampus.fldCampusID

WHERE (tblComposite.fldCompositeNumber = @.compositeNumber)

declare

@.campusCode varchar(10),

@.groupCode varchar(5),

@.graduationMonthCode varchar(2)

SELECT tblComposite.fldCompositeNumber

FROM tblContract INNER JOIN

tblComposite ON tblContract.fldContractID = tblComposite.fldContractID INNER JOIN

tblOrganization ON tblContract.fldOrganizationID = tblOrganization.fldOrganizationID INNER JOIN

tblCampus ON tblOrganization.fldCampusID = tblCampus.fldCampusID INNER JOIN

tblGroup ON tblOrganization.fldGroupID = tblGroup.fldGroupID

WHERE (tblCampus.fldCampusCode = @.campusCode) AND (tblGroup.fldGroupCode = @.groupCode) AND

(tblContract.fldGraduationMonthCode = @.graduationMonthCode)

declare

@.lastCompositeNumber int

SELECT DISTINCT tblCameraCard.fldTitle

FROM tblCameraCard INNER JOIN

tblComposite_CameraCard_Link ON tblCameraCard.fldCameraCardID = tblComposite_CameraCard_Link.fldCameraCardID INNER JOIN

tblComposite ON tblComposite_CameraCard_Link.fldCompositeID = tblComposite.fldCompositeID

WHERE (tblComposite.fldCompositeNumber = @.lastCompositeNumber) AND (NOT (tblCameraCard.fldTitle IS NULL)) OR

(tblCameraCard.fldTitle = '')

Ok, let me rephrase my question. I want to take the result from query A and make it be the parameter for query B and take the result from query B and make that the parameter for query C. The results from query C is what I want to put in my report.

I did some research and found I could set a query equal to something. So I did that but how do I get that returned value to the next query?

cascading parameters

Hi,

I have a listbox which selects distinct brands from the products table.

Then another list box which lists all the orders with order description. Each order has a unique system generated ORDERID and the user provides the orderdescription which could be duplicate.

Depending on the Brand selected by the user in the earlier list box, only those orders containing the the brands in order details files should be available for selection in the list box.

e.g. BRands Lisbox shows: Cream A, Cream B and Cream C

If the user selects 'Cream A', then the next list box shows orderdescription as 'Cream A order for regular sale','Cream A order for exhinition sale', 'Cream A order for exhibition sale'

The problem here is that if the user selects a description which is duplicate (could be the case), then the system brings back the wrong order details.

How is it possible to allow a user to select a order description but search on the OrderID?

Thanks for the help

regards

josh

Hi,

this should work by simply defining two parameter queries whereas the orders query uses the parameter value of the brand parameter.

Regards, Alex

Cascading Deletes

When I setup a relationship in Access I can specify that Primary Key deletes cascade down to the Forgien Key. So when I delete an Order Header it cleans up all the items in the Order Details table for me automatically.

Can I get this same functionality in SQL Server 7 without having to write triggers or are triggers the only way?

thanks
dogNo, trigger is not the only way: you could create a procedure that does this for you or, when you create the table or add the constraint specify the on delete option to cascade (see BOL, create table).|||What!?!?!?

SQL Server has cascading deletes! The easiest way to manage them is through the Relationships tab of the Properties dialog box in the Enterprise Manager table design form.

Triggers are NOT necessary for standard cascading.|||Cascade? Like a waterfall?

Has anyone scanned the landscape for a merry-go-round?

:D

Wholly disconnected ramblings bart man...

Seriously...be careful with cascading...should be no need...

I never liked messing with keys...

damn surrogates...

To me, if a key changes, then it's a new entity...or the key is defined improperly...

You lose all history...|||Well, you certainly aren't alone in your aversion to cascading relationships, but I've never had a problem with them.

Disconneted ramblings...

...many...non-sequiturs...

Wish I had a key for elipsis so I didn't have to hit the period key three times...

Must...complete...sentence.... damn!|||Seriously...be careful with cascading...should be no need...

pretty dogmatic Mr. Kaiser, what's your solution to my previous example, if in fact I don't care about history? Say I have OrderNumber as the Primary key in the OrderHeader table and OrderNumber as a Foreign key in the OrderDetails table, how is this a misconfigured key arrangement?|||Wow...dogmatic...

Cool...

You want to cascade...knock yourself out...|||SQL Server has cascading deletes! The easiest way to manage them is through the Relationships tab of the Properties dialog box in the Enterprise Manager table design form.
That seems to be the logical place for it, however the only options I have are:
Check existing data on creation
Enable relationship for INSERT and UPDATE
Enable relationship for replication

maybe a version difference :confused:|||You want to cascade...knock yourself out...
That's your solution?

WOW......
COOL DUDE.......
THANKS FOR THE MOST RIGHTOUS EXPLAINATION.....................
ATS AWESOME................|||Enable relationship for INSERT and UPDATE

Seems the SQL Server developers were too lazy to say "Enable relationship for INSERT, UPDATE and DELETE. My bad, I read the help screen and found that DELETE is included with this option, however, it doesn't give me the desired result. It actually disables Primary key deletion if Forgein key dependants exist.

Again, do I need to write triggers to accomplish my goal here :confused:

I want all Forgein keys associated with a Primary key to be deleted when I delete the PK record :D|||See attached screenshot.|||Yup, must be an update that I don't have in my v7 version, guess I'll find out what it means to be trigger happy. Thanks blindman.|||'bout time to upgrade, isn't it?|||cascade update\delete is "New" to sql 2000 v :eek:

and you dont have to create database devices anymore.. :D

Cascading Delete Problem

I have three tables that equate to a customer - order - order items
scenario.
The Customer table has 100,000 rows
The Order table has 3 million rows
The Order items has 30 million rows.
I have the primary and foreign keys all defind and the appropriate indexes.
I have cascading deletes on the foreign keys between all three tables.
It takes over a minute to delete a customer. If I remove the cacading
delete, and delete from the order items then orders then customers it takes
seconds.
Why does the cascading deletes take so long?
Using SQL2000 sp3a
Thanks for any insight!
On Thu, 03 Feb 2005 19:35:25 GMT, Howard Carr wrote:

>I have three tables that equate to a customer - order - order items
>scenario.
>The Customer table has 100,000 rows
>The Order table has 3 million rows
>The Order items has 30 million rows.
>I have the primary and foreign keys all defind and the appropriate indexes.
>I have cascading deletes on the foreign keys between all three tables.
>It takes over a minute to delete a customer. If I remove the cacading
>delete, and delete from the order items then orders then customers it takes
>seconds.
>Why does the cascading deletes take so long?
Hi Howard,
Is your primary key (or unique) constraint for the OrderItems table
defined as (OrderID, CustomerID)?
Try changing it to (CustomerID, OrderID). It will speed up the deletion of
a customer, but it will slow down the deletion of an order. If both
deletion happens frequently enough to need good performance, you might try
adding an extra index, so that both orders are present.
Note that changing indexes might affect performance of all other queries
as well.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Cascading Delete Problem

I have three tables that equate to a customer - order - order items
scenario.
The Customer table has 100,000 rows
The Order table has 3 million rows
The Order items has 30 million rows.
I have the primary and foreign keys all defind and the appropriate indexes.
I have cascading deletes on the foreign keys between all three tables.
It takes over a minute to delete a customer. If I remove the cacading
delete, and delete from the order items then orders then customers it takes
seconds.
Why does the cascading deletes take so long?
Using SQL2000 sp3a
Thanks for any insight!On Thu, 03 Feb 2005 19:35:25 GMT, Howard Carr wrote:

>I have three tables that equate to a customer - order - order items
>scenario.
>The Customer table has 100,000 rows
>The Order table has 3 million rows
>The Order items has 30 million rows.
>I have the primary and foreign keys all defind and the appropriate indexes.
>I have cascading deletes on the foreign keys between all three tables.
>It takes over a minute to delete a customer. If I remove the cacading
>delete, and delete from the order items then orders then customers it takes
>seconds.
>Why does the cascading deletes take so long?
Hi Howard,
Is your primary key (or unique) constraint for the OrderItems table
defined as (OrderID, CustomerID)?
Try changing it to (CustomerID, OrderID). It will speed up the deletion of
a customer, but it will slow down the deletion of an order. If both
deletion happens frequently enough to need good performance, you might try
adding an extra index, so that both orders are present.
Note that changing indexes might affect performance of all other queries
as well.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Cascading Delete Problem

I have three tables that equate to a customer - order - order items
scenario.
The Customer table has 100,000 rows
The Order table has 3 million rows
The Order items has 30 million rows.
I have the primary and foreign keys all defind and the appropriate indexes.
I have cascading deletes on the foreign keys between all three tables.
It takes over a minute to delete a customer. If I remove the cacading
delete, and delete from the order items then orders then customers it takes
seconds.
Why does the cascading deletes take so long?
Using SQL2000 sp3a
Thanks for any insight!On Thu, 03 Feb 2005 19:35:25 GMT, Howard Carr wrote:
>I have three tables that equate to a customer - order - order items
>scenario.
>The Customer table has 100,000 rows
>The Order table has 3 million rows
>The Order items has 30 million rows.
>I have the primary and foreign keys all defind and the appropriate indexes.
>I have cascading deletes on the foreign keys between all three tables.
>It takes over a minute to delete a customer. If I remove the cacading
>delete, and delete from the order items then orders then customers it takes
>seconds.
>Why does the cascading deletes take so long?
Hi Howard,
Is your primary key (or unique) constraint for the OrderItems table
defined as (OrderID, CustomerID)?
Try changing it to (CustomerID, OrderID). It will speed up the deletion of
a customer, but it will slow down the deletion of an order. If both
deletion happens frequently enough to need good performance, you might try
adding an extra index, so that both orders are present.
Note that changing indexes might affect performance of all other queries
as well.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Capturing the results from exec command

Hi,

I'm writing a small query where I have a dynamic table name and dynamic condition for the criteria. In order to execute this, I need Exec command.

exec(select count(*) from @.dynamictable where condition = @.dynamiccond)

But here I want to capture the count from the select statement. Could any of you help me capture the results from exec command?

Thanks

2 ways

USE pubs
GO

--sp_executesql
DECLARE @.chvTableName VARCHAR(100),
@.intTableCount INT,
@.chvSQL NVARCHAR(100)

SELECT @.chvTableName = 'Authors'
SELECT @.chvSQL = N'SELECT @.intTableCount = COUNT(*) FROM ' + @.chvTableName

EXEC sp_executesql @.chvSQL, N'@.intTableCount INT OUTPUT', @.intTableCount OUTPUT

SELECT @.intTableCount
GO

--EXEC (SQL)
DECLARE @.chvTableName VARCHAR(100),
@.intTableCount INT,
@.chvSQL NVARCHAR(100)

CREATE TABLE #temp (Totalcount INT)
SELECT @.chvTableName = 'Authors'
SELECT @.chvSQL = 'Insert into #temp Select Count(*) from ' + @.chvTableName

EXEC( @.chvSQL)

SELECT @.intTableCount = Totalcount from #temp

SELECT @.intTableCount

DROP TABLE #temp

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Or:

DECLARE @.chvTableName VARCHAR(100)

CREATE TABLE #temp (Totalcount INT)
SELECT @.chvTableName = 'sysobjects'

insert into #temp(totalCount)
EXEC( 'Select Count(*) from ' + @.chvTableName)

SELECT * from #temp

DROP TABLE #temp

Note: it is generally considered a bad practice to do this sort of thing unless you are building some sort of tool. If this is a production application, it would be better to build a procedure per table:

create procedures count_accounts
as
select count(*) from account
go

Yes, it sounds like a lot of maintenance, but unless you build a large quantity of tables, it shouldn't be a big deal.

capturing cell click event

hi

is there a way i can capture cell click event like may be in report referenced assemblies etc.. so that i can read the cell text in order to open some external applications/windows forms from that..any idea?

regards
farazi'v got an idea to host custom control in the report and then capture the custom control mouse clicnk event.. i'll explore it as well.. but still if someone can give some clue on the original post..|||

If you this will only when the report is exported to HTML and assuming that you use the Report Viewer ASP.NET control, try hooking mouse onclick event in the page body of the hosting page. You should be able to capture the sender using some java script.

The only RS supported feature that you can use to simulate this requirement is assigning a hyperlink to a field using the Navigation tab.

|||hmm.. it seems that we cant do any client side interaction from reports like calling code from client assemblies(from GAC) etc..as its html rendering .. and yeah the only thing left is builtin navigation and i dont think that i can do much from it..actually in simple ways i wanted to show some window from client side assembley on click of specific cells on reports..and i dont see some straight of doing this..

regards
faraz|||

Please consider the option of visualize the reports by means of web service. Once you obtain the report stream in html format (using the render method) you can parse it to attach javascript events you need.

Regards

Maciej Kiewra