case statement in where clause

Hello

I want to put a case statement into a where clause but it's not working. Can anybody help, or tell me a better way of doing this

Thanks very much

declare @.param varchar (100)
select @.param = 'mytext

select
colA
,colB
,colC
from
mytable
where
(case
when @.param is null then colA = 'group'
else colA = 'single'
end)I'd suggest using WHERE colA = CASE WHEN @.param IS NULL THEN 'group' ELSE 'single' END-PatP|||No I can't do it like that

I've rewritten what I want, maybe you can hlp with this

declare @.param varchar (100)
select @.param = 'mytext'

select
colA
,colB
,colC
from
mytable
where
(case
when @.param is null then colB = 'group' and colC = 'something'
else colB = 'group' and colC = @.param
end)|||Ok, let's do that dance and move on to:SELECT
colA
, colB
, colC
FROM mytable
WHERE (@.param IS NULL AND colB = 'group' AND colC = 'something')
OR (@.param = colC AND colB = 'group')-PatP

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 / NULL Problem

Hello!
I want to set a value depending on a condition but I can't seem to get it
right when the original value is NULL.
Select bla, bla... FROM bla...
*****************************
DellyDate=CASE DelivDate
WHEN NULL THEN
'1999-12-31'
ELSE
LEFT(DelivDate,4) + '-' + Substring(DelivDate,5,2) + '-' +
RIGHT(DelivDate,2)
END
***********************************'
This code parses successfully, but even when DelivDate actually is NULL,
DellyDate never has the value '1999-12-31'. What am I doing wrong here?You'll need to use IS NULL.
DellyDate=CASE WHEN DelivDate IS NULL
THEN
'1999-12-31'
ELSE
LEFT(DelivDate,4) + '-' + Substring(DelivDate,5,2) + '-' +
RIGHT(DelivDate,2)
END
or alternatively
DellyDate=COALESCE(LEFT(DelivDate,4) + '-' + Substring(DelivDate,5,2) +
'-' +
RIGHT(DelivDate,2) ,'1999-12-31' )|||DellyDate = CASE WHEN DelivDate IS NULL THEN '1999-12-13' ELSE .... END
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Clarkie" <clarkbones@.rock.sendmenot.etmail.com> wrote in message
news:eKVi$lQJGHA.1728@.TK2MSFTNGP09.phx.gbl...
> Hello!
> I want to set a value depending on a condition but I can't seem to get it
> right when the original value is NULL.
> Select bla, bla... FROM bla...
> *****************************
> DellyDate=CASE DelivDate
> WHEN NULL THEN
> '1999-12-31'
> ELSE
> LEFT(DelivDate,4) + '-' + Substring(DelivDate,5,2) + '-' +
> RIGHT(DelivDate,2)
> END
> ***********************************'
> This code parses successfully, but even when DelivDate actually is NULL,
> DellyDate never has the value '1999-12-31'. What am I doing wrong here?
>|||you could also
coalesce(convert(varchar(10), convert(datetme, DelivDate), 120),
'1999-12-31')
"Clarkie" wrote:

> Hello!
> I want to set a value depending on a condition but I can't seem to get it
> right when the original value is NULL.
> Select bla, bla... FROM bla...
> *****************************
> DellyDate=CASE DelivDate
> WHEN NULL THEN
> '1999-12-31'
> ELSE
> LEFT(DelivDate,4) + '-' + Substring(DelivDate,5,2) + '-' +
> RIGHT(DelivDate,2)
> END
> ***********************************'
> This code parses successfully, but even when DelivDate actually is NULL,
> DellyDate never has the value '1999-12-31'. What am I doing wrong here?
>
>|||Read the definition of the CASE expression. You are using a shorthand
for
SELECT ..
FROM ..
WHERE delly_date
=CASE WHEN delly_date = NULL -- always UNKNOWN !!!!
THEN '1999-12-31'
ELSE .. END ;|||Thanks for all the answers!
Problem solved!
//Clarkie
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138581718.965490.84740@.g14g2000cwa.googlegroups.com...
> Read the definition of the CASE expression. You are using a shorthand
> for
> SELECT ..
> FROM ..
> WHERE delly_date
> =CASE WHEN delly_date = NULL -- always UNKNOWN !!!!
> THEN '1999-12-31'
> ELSE .. END ;
>

Capture the SELECT Statement Duration on SQL7

Hello:
I need to give a report of the server performance about
the select statement duration. Does any setup that I can
capture these information into CSV format? How can I do
that?
Does any URL can reference?
Thanks a lot!
Alan
Look at set statistics io on and set statistics time in the BOL
"Alan" <anonymous@.discussions.microsoft.com> wrote in message
news:101501c49978$fc1227b0$a501280a@.phx.gbl...
> Hello:
> I need to give a report of the server performance about
> the select statement duration. Does any setup that I can
> capture these information into CSV format? How can I do
> that?
> Does any URL can reference?
> Thanks a lot!
|||For big production stuf you can do several things.
Capture information using Profiler < the info would be inthe statement
ending row.
You can capture this to a sql table, or to a file and then import it into a
sql table.
You can then export to a flat file using DTS, or bcp , or a select in query
analyzer, then save the results area to a file.
Good luck
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Alan" <anonymous@.discussions.microsoft.com> wrote in message
news:101501c49978$fc1227b0$a501280a@.phx.gbl...
> Hello:
> I need to give a report of the server performance about
> the select statement duration. Does any setup that I can
> capture these information into CSV format? How can I do
> that?
> Does any URL can reference?
> Thanks a lot!

Capacity Planning

Hello
I am very new to sql server, my experience is as a Progrss DBA. I want to
start monitoring the database growth on both all tables and database files
etc within SQL.
In a Progress Environment I could run table analysis job to display each
table, number of records, size/blocks used, min record size, max record site
,
and fragmentation etc.
How can I get similar stats from within SQL?
Many thanks for your help.
JasonHi
SQL Server Profiler is your friend.
http://www.sql-server-performance.c...ofiler_tips.asp
"new_sql_dba" <newsqldba@.discussions.microsoft.com> wrote in message
news:12E4B5A8-A3E6-49C9-9164-6061109E37C4@.microsoft.com...
> Hello
> I am very new to sql server, my experience is as a Progrss DBA. I want to
> start monitoring the database growth on both all tables and database files
> etc within SQL.
> In a Progress Environment I could run table analysis job to display each
> table, number of records, size/blocks used, min record size, max record
> site,
> and fragmentation etc.
> How can I get similar stats from within SQL?
> Many thanks for your help.
> Jason
>
>
>

Capacity Planning

Hello
I am very new to sql server, my experience is as a Progrss DBA. I want to
start monitoring the database growth on both all tables and database files
etc within SQL.
In a Progress Environment I could run table analysis job to display each
table, number of records, size/blocks used, min record size, max record site,
and fragmentation etc.
How can I get similar stats from within SQL?
Many thanks for your help.
Jason
Hi
SQL Server Profiler is your friend.
http://www.sql-server-performance.co...filer_tips.asp
"new_sql_dba" <newsqldba@.discussions.microsoft.com> wrote in message
news:12E4B5A8-A3E6-49C9-9164-6061109E37C4@.microsoft.com...
> Hello
> I am very new to sql server, my experience is as a Progrss DBA. I want to
> start monitoring the database growth on both all tables and database files
> etc within SQL.
> In a Progress Environment I could run table analysis job to display each
> table, number of records, size/blocks used, min record size, max record
> site,
> and fragmentation etc.
> How can I get similar stats from within SQL?
> Many thanks for your help.
> Jason
>
>
>

cant use functions on linked servers??

Hello

I'm having some problems with a couple of linked SQL servers. Basically I can get queries and SPs to work just fine, aklthough there is a little overhead. But I can't get functions to work! Can it really be that linked servers don't support functions? And if so, just out of curiosity, why on earth is it so? Linked servers only handle result sets, not scalar values?

I hope somebody can help me with this
MNJCan you explain "can't get functions to work" in more detail? I've never had it fail, so I'm probably missing something really basic here.

-PatP|||I thought you could never access the SPs and UDFs through a linked server. If you could, I would be interested to know how. Is this syntax valid. Can I execute this from Server 2
linkedserver1.database1.owner1.SP1|||EXECUTE linkedserver.master.dbo.sp_who

Works just fine for me. Does anyone else have trouble with it?

-PatP|||This is working for me:

select * from OPENQUERY ( linkedserver , 'select * from testDB2.dbo.testfunction()' )

but not working:

select * from linkedserver.testDB2.dbo.testfunction()|||Pat,

I get

Server: Msg 7411, Level 16, State 1, Line 1
Server 'QA' is not configured for RPC.|||Well that was easy..

Just go to properties in EM and select that you want to do RPC's

Why would that even be an option...

Wonder what the code is to enable it...|||Originally posted by snail
This is working for me:

select * from OPENQUERY ( linkedserver , 'select * from testDB2.dbo.testfunction()' )

but not working:

select * from linkedserver.testDB2.dbo.testfunction() Can you elaborate just a bit on what constitutes "not working" ?

-PatP|||I think you can't use the next for a function

select * from any_function(params)

Shouldn't this be:

select any_function(params)

Maybe this will solve the problem?|||Welcome Johan!

It depends. If they are using a table valued function, the original syntax would be fine as long as it had at least a two-part name.

-PatP|||Just one question from me ...

though from your post it seems the linked server is to a SQL server ...

is it really to a SQL server or some other db.|||Originally posted by Pat Phelan
Can you elaborate just a bit on what constitutes "not working" ?

-PatP
I got:

Server: Msg 170, Level 15, State 31, Line 1
Line 2: Incorrect syntax near '('.|||Pat: an off-topic question: what's a table valued function?|||Originally posted by jora
Pat: an off-topic question: what's a table valued function? You can think of a table valued function as a view that takes parameters. You can read about them in the CREATE FUNCTION (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_7r1l.asp) documentation, or an article at SQL Team (http://www.sqlteam.com/item.asp?ItemID=1955).

-PatP|||Thanks for the input everybody, and sorry I didn't respond until now. I've been looking further into the thing, and has come to the conclusion that my problem is really about linked servers and INSERTS.

I've started a new thread here:
http://www.dbforums.com/showthread.php?p=3673457#post3673457

Thanks
MNJ