a where case question

I need to change the criteria of the select so that if @.thstype is 1 then
where a =b else a<> b. This is in a stored proc...
given declare @.thsType as INT
set @.thsType = 1
select *
from t1,t2,t2,...
where u.unvid case @.thsType When 1 then = else <> end @.thsUnvID
and u.unvtype = @.thsunvType
and i1.idxhstdate = @.thsDate1
and i2.idxhstdate = @.thsDate2
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kesYou were so close :-)
Use Northwind
DECLARE @.Test INT
SET @.test = 1
Select *
from customers where customerID =
(CASE WHEN @.Test = 1 THEN 'ALFKI' ELSE '###' END)
HTH, Jens Suessmeyer
http://www.sqlserver2005.de
--
"WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
news:7178EEFF-4304-4871-8138-7F2DF1A2D5BC@.microsoft.com...
>I need to change the criteria of the select so that if @.thstype is 1 then
> where a =b else a<> b. This is in a stored proc...
> given declare @.thsType as INT
> set @.thsType = 1
> select *
> from t1,t2,t2,...
> where u.unvid case @.thsType When 1 then = else <> end @.thsUnvID
> and u.unvtype = @.thsunvType
> and i1.idxhstdate = @.thsDate1
> and i2.idxhstdate = @.thsDate2
> --
> thanks (as always)
> some day i''m gona pay this forum back for all the help i''m getting
> kes|||i'm not sure that will work. I really need something like this
Select *
from customers where
CASE WHEN @.Test = 1 THEN
customerID = 'ALFKI'
ELSE
customerID <> 'ALFKI'
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"Jens Sü?meyer" wrote:

> You were so close :-)
> Use Northwind
> DECLARE @.Test INT
> SET @.test = 1
> Select *
> from customers where customerID =
> (CASE WHEN @.Test = 1 THEN 'ALFKI' ELSE '###' END)
> HTH, Jens Suessmeyer
> --
> http://www.sqlserver2005.de
> --
> "WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
> news:7178EEFF-4304-4871-8138-7F2DF1A2D5BC@.microsoft.com...
>
>|||Try this:
DECLARE @.Test INT
SET @.test = 1
Select *
from customers
where (@.Test = 1 and customerID = 'ALFKI') or
(@.Test <> 1 and customerID <> 'ALFKI' )
Perayu
"WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
news:8CBDCEFA-740D-4DA6-BA4D-18452E54FE1C@.microsoft.com...
> i'm not sure that will work. I really need something like this
> Select *
> from customers where
> CASE WHEN @.Test = 1 THEN
> customerID = 'ALFKI'
> ELSE
> customerID <> 'ALFKI'
> --
> thanks (as always)
> some day i''m gona pay this forum back for all the help i''m getting
> kes
>
> "Jens Smeyer" wrote:
>|||well.........., yes you have an answer and thank you!
However, it turned my mega proc of .5 sec into a 6 second proc.
I can write an if else and have two queries in the proc, but i'd like to
avoide that if i could.
Query below, for what is does it's very fast the condition needs to be in
the joined query at the bottom: (i added the or)
select
s.csistkcsisym,
s.csistksym1,
s.csistkcompany,
case s.csistkExchange when 'OTC' THEN 'Nasdaq' Else s.csistkExchange END as
Exchange,
u.unvName,
isnull(r.unvname, '********') as Sector,
case s.csistkActive when 0 then 'INACTIVE' else 'ACTIVE' END as status,
case h2.stkhstBuySell WHEN '' THEN 'N/A' WHEN 'B' THEN 'Buy' WHEN 'S' then
'Sell' ELSE h2.stkhstBuySell END as PFBuySell,
CASE
WHEN h2.stkhstBuySell = 'B' and h1.stkhstBuySell = 'S' THEN 'gnBK'
WHEN h2.stkhstBuySell = 'S' and h1.stkhstBuySell = 'B' THEN 'rdBK'
ELSE 'wtBK'
END as NEWPFBuySell,
h2.stkhstXO,
CASE
WHEN h2.stkhstXO = 'X' and h1.stkhstxo = 'O' THEN 'gnBK'
WHEN h2.stkhstXO = 'O' and h1.stkhstxo = 'X' THEN 'rdBK'
ELSE 'wtBK'
END as NEWPFXO,
CASE h2.stkhstLine WHEN 'A' THEN 'Above' WHEN 'B' THEN 'Below' ELSE 'N/A'
END as Trend,
CASE
WHEN h2.stkhstLine = 'A' AND h1.stkhstLine = 'B' THEN 'gnBK'
WHEN h2.stkhstLine = 'B' AND h1.stkhstLine = 'A' THEN 'rdBK'
ELSE 'wtBK'
END as NEWPFtrend,
case h2.stkhstRSBS WHEN '' THEN 'N/A' WHEN 'B' THEN 'Buy' WHEN 'S' then
'Sell' ELSE h2.stkhstRSBS END as RSBuySell,
CASE
WHEN h2.stkhstRSBS = 'B' AND h1.stkhstRSBS = 'S' THEN 'gnBK'
WHEN h2.stkhstRSBS = 'S' AND h1.stkhstRSBS = 'B' THEN 'rdBK'
ELSE 'wtBK'
END as NEWRSBuySell,
h2.stkhstRSXO,
CASE
WHEN h2.stkhstRSXO = 'X' AND h1.stkhstRSXO = 'O' THEN 'gnBK'
WHEN h2.stkhstRSXO = 'O' AND h1.stkhstRSXO = 'X' THEN 'rdBK'
ELSE 'wtBK'
END as NEWRSXO,
case When (h2.stkhst10wk - h2.stkhstClose) >= 0 then 'Below' else 'Above'
end as tenBeat,
CASE
WHEN ((h2.stkhst10wk - h2.stkhstClose) >= 0) AND ((h1.stkhst10wk -
h1.stkhstClose) < 0) then 'rdBK'
WHEN ((h1.stkhst10wk - h1.stkhstClose) >= 0) AND ((h2.stkhst10wk -
h2.stkhstClose) < 0) then 'gnBK'
ELSE 'wtBK'
END AS NEWtenBeat,
h2.stkhst10Wk,
h2.stkhstClose,
case
WHEN r.i2idxhstStatus is null
then (dbo.fn_rtnRSStatus(h2.stkhstRSBS,
h2.stkhstRSXO)+dbo.fn_rtnPFStatus(h2.stkhstBuySell, h2.stkhstLine))*2
else ((dbo.fn_rtnBPStatus(r.i2idxhstStatus, r.i2idxhstPosChartPos)*50) +
(dbo.fn_rtnRsRStatus(r.i2idxhstRSBSXO)*25) +
(dbo.fn_rtnBPStatus(r.i2idxhstRSXOStatus, r.i2idxhstRSXOPos)*25) +
(dbo.fn_rtn10Status(r.i2idxhst10Status, r.i2idxhst10ChartPos)*50) +
(dbo.fn_rtnBPStatus(r.i2idxhstStatus, r.i2idxhstPosChartPos)*25) +
(dbo.fn_rtnBPStatus(r.i2idxhstRSXOStatus, r.i2idxhstRSXOPos)*25) )/4+
dbo.fn_rtnRSStatus(h2.stkhstRSBS, h2.stkhstRSXO)
+dbo.fn_rtnPFStatus(h2.stkhstBuySell, h2.stkhstLine)
END as StockRate,
case
WHEN r.i2idxhstStatus is null
then (dbo.fn_rtnRSStatus(h2.stkhstRSBS,
h2.stkhstRSXO)+dbo.fn_rtnPFStatus(h2.stkhstBuySell, h2.stkhstLine))*2
else ((dbo.fn_rtn10Status(r.i2idxhst10Status, r.i2idxhst10ChartPos)*50) +
(dbo.fn_rtnBPStatus(r.i2idxhstStatus, r.i2idxhstPosChartPos)*25) +
(dbo.fn_rtnBPStatus(r.i2idxhstRSXOStatus, r.i2idxhstRSXOPos)*25) )/2+
dbo.fn_rtnRSStatus(h2.stkhstRSBS, h2.stkhstRSXO)
+dbo.fn_rtnPFStatus(h2.stkhstBuySell, h2.stkhstLine)
END as ShortTermRate,
case s.csistkActive when 0 then 'INACTIVE' else 'ACTIVE' END as status
from
csistk s
join unvmem m on m.unvmemCsiId = s.csistkcsisym
join unv u on u.unvID = m.unvmemUnvID
join stkhst h2 on h2.stkhstcsisym = s.csistkcsisym
join stkhst h1 on h1.stkhstcsisym = s.csistkcsisym
left join
(select u.unvname,
u.unvid,
m.unvmemcsiid,
i1.idxhstStatus as i1idxhstStatus,
i2.idxhstStatus as i2idxhstStatus,
i2.idxhstPosChartPos as i2idxhstPosChartPos,
i2.idxhstRSBSXO as i2idxhstRSBSXO,
i2.idxhstRSXOStatus as i2idxhstRSXOStatus,
i2.idxhstRSXOPos as i2idxhstRSXOPos,
i2.idxhst10Status as i2idxhst10Status,
i2.idxhst10ChartPos as i2idxhst10ChartPos
from unv u
join unvmem m on m.unvmemunvid = u.unvid
join idxhst i1 on i1.idxhstidxid = u.unvID
join idxhst i2 on i2.idxhstidxid = u.unvID
where @.thsOne = 1
and u.unvid <> @.thsUnvID
and u.unvtype = @.thsunvType
and i1.idxhstdate = @.thsDate1
and i2.idxhstdate = @.thsDate2
or
@.thsOne<> 1
and u.unvid = @.thsUnvID
and u.unvtype = @.thsunvType
and i1.idxhstdate = @.thsDate1
and i2.idxhstdate = @.thsDate2) r on r.unvmemcsiid = s.csistkcsisym
where u.unvid = @.thsUnvID
and h2.stkhstdate = @.thsDate2
and h1.stkhstdate = @.thsDate1
order by s.csistksym1
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"Perayu" wrote:

> Try this:
> DECLARE @.Test INT
> SET @.test = 1
> Select *
> from customers
> where (@.Test = 1 and customerID = 'ALFKI') or
> (@.Test <> 1 and customerID <> 'ALFKI' )
> Perayu
>
> "WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
> news:8CBDCEFA-740D-4DA6-BA4D-18452E54FE1C@.microsoft.com...
>
>|||On Thu, 8 Sep 2005 13:41:02 -0700, WebBuilder451 wrote:

>well.........., yes you have an answer and thank you!
>However, it turned my mega proc of .5 sec into a 6 second proc.
>I can write an if else and have two queries in the proc, but i'd like to
>avoide that if i could.
>Query below, for what is does it's very fast the condition needs to be in
>the joined query at the bottom: (i added the or)
(snip)
> where @.thsOne = 1
> and u.unvid <> @.thsUnvID
> and u.unvtype = @.thsunvType
> and i1.idxhstdate = @.thsDate1
> and i2.idxhstdate = @.thsDate2
> or
> @.thsOne<> 1
> and u.unvid = @.thsUnvID
> and u.unvtype = @.thsunvType
> and i1.idxhstdate = @.thsDate1
> and i2.idxhstdate = @.thsDate2) r on r.unvmemcsiid = s.csistkcsisym
Hi WebBuilder451,
While this and/or condition will work, it is not very maintainable and
not very efficient either. Please remember that many people do not know
the precedence of evaluation for and and or by head. Just adding
brackets would make this code easier to understand!
But the code below, while equivalent, also has a better chance of being
able to use indexes:
where u.unvtype = @.thsunvType
and i1.idxhstdate = @.thsDate1
and i2.idxhstdate = @.thsDate2
and ((@.thsOne = 1 and u.unvid <> @.thsUnvID)
or (@.thsOne <> 1 and u.unvid = @.thsUnvID))
) r on r.unvmemcsiid = s.csistkcsisym
If that doesn't solve your speed problem and speed is important for you,
than you'll have to duplicate your stored procedure to make two
versions: one for @.thsOne = 1 and one for @.thsOne <> 1. That will allow
SQL Server to create optimized execution plans for both situations.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

a sql statement

This does not display more than 10 rows from the able, varchar(2000) is big
enough to bring more rows, where might the problem mbe?
Declare @.ColList varchar(2000)
Declare @.CrLf varchar(10)
Select @.CrLf=Char(13) + Char(10)
Select @.ColList = COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') +
MyName From MyTable
Select @.ColListworks fine on my end.
JIM.H. wrote:
> This does not display more than 10 rows from the able, varchar(2000) is big
> enough to bring more rows, where might the problem mbe?
> Declare @.ColList varchar(2000)
> Declare @.CrLf varchar(10)
> Select @.CrLf=Char(13) + Char(10)
> Select @.ColList = COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') +
> MyName From MyTable
> Select @.ColList|||On Mon, 24 Jul 2006 06:44:02 -0700, JIM.H. wrote:
>This does not display more than 10 rows from the able, varchar(2000) is big
>enough to bring more rows, where might the problem mbe?
>Declare @.ColList varchar(2000)
>Declare @.CrLf varchar(10)
>Select @.CrLf=Char(13) + Char(10)
>Select @.ColList = COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') +
>MyName From MyTable
>Select @.ColList
Hi Jim,
Since this syntax is not supported, it could be anything. Though I have
to admit that it usually either returns the expected results, or just a
single row. I've just recently had a discussion with Omnibuzz about this
on his blog - check
http://omnibuzz-sql.blogspot.com/2006/07/resolution-for-concatenate-column.html
The most likely reasons for seeing just 10 rows are forgetting to undo a
previous SET ROWCOUNT 10, or your front-end tool deciding not to show
all the data in long string columns. If you're using Query Analyzer, you
can control this through Tools / Options / Results / Maximum characters
per column (defaults to 256; maximum is 8192). In SQL Server Management
Studio, you can control this through Tools / Options / Query Results /
SQL Server / Result to Text (or Result to Grid). The maximum is 8192 for
Result to Text and 65535 for Result to Grid, but AFAIK, line feeds mess
up the Results to Grid display.
--
Hugo Kornelis, SQL Server MVP

a sql statement

This does not display more than 10 rows from the able, varchar(2000) is big enough to bring more rows, where might the problem mbe?

Declare @.ColList varchar(2000)

Declare @.CrLf varchar(10)

Select @.CrLf=Char(13) + Char(10)

Select @.ColList = COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') + MyName From MyTable

Select @.ColList

what is the error message|||

I do not see error, in the query analyzer, I set “Results in text” and run the query, I see first 8 records and (1 row(s) affected) message, it should show at least 50 rows. Is this a query analyzer problem?

|||

how about this

Declare @.CrLf varchar(10)

Select @.CrLf=Char(13) + Char(10)

Select COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') + MyName as nyfield From MyTable

|||

In that case, I see more rows, I am just wondering why I could not get everything although I make @.ColList varchar(5000)

|||

i think QA is displaying it in a very long line

have this a try

Declare @.ColList varchar(2000)

Declare @.CrLf varchar(10)

Select @.CrLf=Char(13) + Char(10)

Select @.ColList = COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') + MyName From MyTable

print @.ColList

|||

JIM.H. wrote:

In that case, I see more rows, I am just wondering why I could not get everything although I make @.ColList varchar(5000)

tried to simulate you can only make it until 4000

i think you should make use of cursor

|||

If you are using SQL 2005 please check the following:

Tools -> Options -> Query Results -> Results to Text Maximum number of characters displayed in each column (the default is 256)

Tools -> Options -> Query Results -> Results to Grid Maximum Characters Received Non XML data (the default is 65536)

In SQL 2000 in QA

Tools -> Options -> Results ->Maximum number of characters per column (the default is 256)

You may need to increase these numbers

|||

i tried this one in northwind

use northwind

Declare @.ColList char(8000)
Declare @.CrLf varchar(2)
Select @.CrLf=Char(13) + Char(10)
Select @.coLlist= COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') + RTRIM(LTRIM(customerid)) From orders

print @.coLlist
select len(@.collist) as txtlength -<- check this out
select datalength(@.collist) as datalenght <-- and this is

here's the result

4000

8000

|||this worked. Thanks.|||there's a limit of byte per row that can be returned, inserted or updated...it's 8096 if i remember correctly...

a sql statement

This does not display more than 10 rows from the able, varchar(2000) is big enough to bring more rows, where might the problem mbe?

Declare @.ColList varchar(2000)

Declare @.CrLf varchar(10)

Select @.CrLf=Char(13) + Char(10)

Select @.ColList = COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') + MyName From MyTable

Select @.ColList

Did you have any SET ROWCOUNT prior to running this SQL> I ran it on my machine and it worked fine for me.|||

If you are using Query Analyzer to execute the query, please go to Tools menu->Options->switch to Results tab->set the 'Maximum characters per column' to max allowed value 8192, then try again.

If you're using Management Studio and you have set to return result as text, please go to Tools->Options->Query Results->SQL Server->Results to Text->set the 'Maximum number of characters displayed in each column' to 8192

|||Thats right. I had changed mine to 1200 sometime back.