CASE Statement Opinion

I have the following case statement:
CASE (i.idxhstRSXOPos - i2.idxhstRSXOPos)
WHEN 0 THEN 'md2'
ELSE
CASE i.idxhstRSXOLastAct
WHEN 1 then 'mdG'
WHEN 2 then 'mdR'
WHEN 3 then 'mdG'
WHEN 4 then 'mdR'
END
END as rsXOxoflg
It works ok, but i'd like t oknow if there is a better way to do this. It
feels messy, but that doesn't mean there's a better way.
thanks
kes
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kesOr, you could write:
CASE when i.idxhstRSXOPos = i2.idxhstRSXOPos THEN 'md2'
ELSE
CASE i.idxhstRSXOLastAct
WHEN 1 then 'mdG'
WHEN 2 then 'mdR'
WHEN 3 then 'mdG'
WHEN 4 then 'mdR'
END
END as rsXOxoflg
Perayu
"WebBuilder451" wrote:

> I have the following case statement:
> CASE (i.idxhstRSXOPos - i2.idxhstRSXOPos)
> WHEN 0 THEN 'md2'
> ELSE
> CASE i.idxhstRSXOLastAct
> WHEN 1 then 'mdG'
> WHEN 2 then 'mdR'
> WHEN 3 then 'mdG'
> WHEN 4 then 'mdR'
> END
> END as rsXOxoflg
> It works ok, but i'd like t oknow if there is a better way to do this. It
> feels messy, but that doesn't mean there's a better way.
> thanks
> kes
> --
> thanks (as always)
> some day i''m gona pay this forum back for all the help i''m getting
> kes|||When populating a calculated column, typically for reporting purposes, I've
written embedded case expressions more twisted than that. An expression like
that probably won't be indexed by the query optimizer, but so long as you
are not placing it in the where or group by clause, I expect it would only
be a marginal performance hit. One other option would be to return
idxhstRSXOPos, i2.idxhstRSXOPos, and idxhstRSXOLastAct in the query result
and let client application do the calculation.
"WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
news:245254F2-39A7-4DB9-AF86-4B86D8869685@.microsoft.com...
>I have the following case statement:
> CASE (i.idxhstRSXOPos - i2.idxhstRSXOPos)
> WHEN 0 THEN 'md2'
> ELSE
> CASE i.idxhstRSXOLastAct
> WHEN 1 then 'mdG'
> WHEN 2 then 'mdR'
> WHEN 3 then 'mdG'
> WHEN 4 then 'mdR'
> END
> END as rsXOxoflg
> It works ok, but i'd like t oknow if there is a better way to do this. It
> feels messy, but that doesn't mean there's a better way.
> thanks
> kes
> --
> thanks (as always)
> some day i''m gona pay this forum back for all the help i''m getting
> kes|||It looks good to me. Nesting CASE expressions is legal as long as the
data types are correct. If you want to flatten it out, you can:
CASE WHEN (i.idxhstRSXOPos - i2.idxhstRSXOPos) = 0 THEN 'md2'
WHEN i.idxhstRSXOLastAct IN (1, 3) THEN 'mdG'
WHEN i.idxhstRSXOLastAct IN (2, 4) THEN 'mdR'
END AS rsXOxoflg
CASE expressions evaluate the WHEN clauses in order. This will do the
same thing as yours.|||this is what i was looking for.
Not to say the other answers were not good.
Thank You everyone
kes
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"--CELKO--" wrote:

> It looks good to me. Nesting CASE expressions is legal as long as the
> data types are correct. If you want to flatten it out, you can:
> CASE WHEN (i.idxhstRSXOPos - i2.idxhstRSXOPos) = 0 THEN 'md2'
> WHEN i.idxhstRSXOLastAct IN (1, 3) THEN 'mdG'
> WHEN i.idxhstRSXOLastAct IN (2, 4) THEN 'mdR'
> END AS rsXOxoflg
> CASE expressions evaluate the WHEN clauses in order. This will do the
> same thing as yours.
>

CASE Statement not working

Anyone have any idea why the following case statement I am getting a syntax
error near 'when':
, case(m.units when not like '%U%' then m.matl_qty-m.qty_issued
else (m.matl_qty*j.qty_released)-m.qty_issued)endThere are two versions of the CASE syntax, called the "simple" and
"searched" CASEs. For your LIKE expression you need to use the searched
CASE: WHEN has to come before the LIKE expression. See Books Online for
details.
CASE
WHEN m.units NOT LIKE '%U%'
THEN m.matl_qty - m.qty_issued
ELSE (m.matl_qty*j.qty_released)-m.qty_issued
END
--
David Portas
SQL Server MVP
--|||Thanks. That worked good
"David Portas" wrote:
> There are two versions of the CASE syntax, called the "simple" and
> "searched" CASEs. For your LIKE expression you need to use the searched
> CASE: WHEN has to come before the LIKE expression. See Books Online for
> details.
> CASE
> WHEN m.units NOT LIKE '%U%'
> THEN m.matl_qty - m.qty_issued
> ELSE (m.matl_qty*j.qty_released)-m.qty_issued
> END
> --
> David Portas
> SQL Server MVP
> --
>
>

CASE Statement not working

Anyone have any idea why the following case statement I am getting a syntax
error near 'when':
, case(m.units when not like '%U%' then m.matl_qty-m.qty_issued
else (m.matl_qty*j.qty_released)-m.qty_issued)end
There are two versions of the CASE syntax, called the "simple" and
"searched" CASEs. For your LIKE expression you need to use the searched
CASE: WHEN has to come before the LIKE expression. See Books Online for
details.
CASE
WHEN m.units NOT LIKE '%U%'
THEN m.matl_qty - m.qty_issued
ELSE (m.matl_qty*j.qty_released)-m.qty_issued
END
David Portas
SQL Server MVP
|||Thanks. That worked good
"David Portas" wrote:

> There are two versions of the CASE syntax, called the "simple" and
> "searched" CASEs. For your LIKE expression you need to use the searched
> CASE: WHEN has to come before the LIKE expression. See Books Online for
> details.
> CASE
> WHEN m.units NOT LIKE '%U%'
> THEN m.matl_qty - m.qty_issued
> ELSE (m.matl_qty*j.qty_released)-m.qty_issued
> END
> --
> David Portas
> SQL Server MVP
> --
>
>

CASE Statement not working

Anyone have any idea why the following case statement I am getting a syntax
error near 'when':
, case(m.units when not like '%U%' then m.matl_qty-m.qty_issued
else (m.matl_qty*j.qty_released)-m.qty_issued)endThere are two versions of the CASE syntax, called the "simple" and
"searched" CASEs. For your LIKE expression you need to use the searched
CASE: WHEN has to come before the LIKE expression. See Books Online for
details.
CASE
WHEN m.units NOT LIKE '%U%'
THEN m.matl_qty - m.qty_issued
ELSE (m.matl_qty*j.qty_released)-m.qty_issued
END
David Portas
SQL Server MVP
--|||Thanks. That worked good
"David Portas" wrote:

> There are two versions of the CASE syntax, called the "simple" and
> "searched" CASEs. For your LIKE expression you need to use the searched
> CASE: WHEN has to come before the LIKE expression. See Books Online for
> details.
> CASE
> WHEN m.units NOT LIKE '%U%'
> THEN m.matl_qty - m.qty_issued
> ELSE (m.matl_qty*j.qty_released)-m.qty_issued
> END
> --
> David Portas
> SQL Server MVP
> --
>
>

CASE statement in WHERE clause problem

Hello,
I would like to achieve the following within an stored procedure.
SELECT * FROM TableX WHERE ID = 1
OR
SELECT * FROM TableX WHERE ID IS NOT NULL
How can I solve this by using a condition in my WHERE clause?
eg.
//////
CREATE PROCEDURE TestID
@.ID INT
AS
SELECT * FROM TableX
WHERE ID =
CASE
WHEN @.ID IS NOT NULL THEN @.ID
ELSE NOT NULL
END
////
The problem is the ' NOT ' NULL in the ELSE Path
If I skip the ELSE Path then it will be implicitly NULL
Thanks for any help,
RemcoHi,
Try this
SELECT * FROM TableX WHERE ID = 1 OR ID IS NOT NULL
Hth
"Remco" <rembo_r@.hotmail.com> wrote in message
news:eGlfZa3DFHA.1296@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I would like to achieve the following within an stored procedure.
> SELECT * FROM TableX WHERE ID = 1
> OR
> SELECT * FROM TableX WHERE ID IS NOT NULL
> How can I solve this by using a condition in my WHERE clause?
>
> eg.
> //////
> CREATE PROCEDURE TestID
> @.ID INT
> AS
> SELECT * FROM TableX
> WHERE ID =
> CASE
> WHEN @.ID IS NOT NULL THEN @.ID
> ELSE NOT NULL
> END
> ////
> The problem is the ' NOT ' NULL in the ELSE Path
> If I skip the ELSE Path then it will be implicitly NULL
> Thanks for any help,
> Remco
>|||Hello Remco,

> Hello,
> I would like to achieve the following within an stored procedure.
> SELECT * FROM TableX WHERE ID = 1
> OR
> SELECT * FROM TableX WHERE ID IS NOT NULL
> How can I solve this by using a condition in my WHERE clause?
>
if you want criteria:
1. @.ID != null --> ID = @.ID
2. @.ID IS NULL --> ID IS NOT NULL
then:
WHERE
(@.ID IS NOT NULL AND ID = @.ID)
OR (@.ID IS NULL AND ID IS NOT NULL)
Lasse Vgsther Karlsen
http://www.vkarlsen.no/
mailto:lasse@.vkarlsen.no
PGP KeyID: 0x0270466B|||this should get you started on how to do that.
http://www.aspfaq.com/show.asp?id=2501
"Remco" wrote:

> Hello,
> I would like to achieve the following within an stored procedure.
> SELECT * FROM TableX WHERE ID = 1
> OR
> SELECT * FROM TableX WHERE ID IS NOT NULL
> How can I solve this by using a condition in my WHERE clause?
>
> eg.
> //////
> CREATE PROCEDURE TestID
> @.ID INT
> AS
> SELECT * FROM TableX
> WHERE ID =
> CASE
> WHEN @.ID IS NOT NULL THEN @.ID
> ELSE NOT NULL
> END
> ////
> The problem is the ' NOT ' NULL in the ELSE Path
> If I skip the ELSE Path then it will be implicitly NULL
> Thanks for any help,
> Remco
>
>|||Try,
SELECT * FROM TableX
WHERE [ID] = @.id or (@.id is null and [id] is null)
AMB
"Remco" wrote:

> Hello,
> I would like to achieve the following within an stored procedure.
> SELECT * FROM TableX WHERE ID = 1
> OR
> SELECT * FROM TableX WHERE ID IS NOT NULL
> How can I solve this by using a condition in my WHERE clause?
>
> eg.
> //////
> CREATE PROCEDURE TestID
> @.ID INT
> AS
> SELECT * FROM TableX
> WHERE ID =
> CASE
> WHEN @.ID IS NOT NULL THEN @.ID
> ELSE NOT NULL
> END
> ////
> The problem is the ' NOT ' NULL in the ELSE Path
> If I skip the ELSE Path then it will be implicitly NULL
> Thanks for any help,
> Remco
>
>|||
SET ANSI_NULLS ON
SELECT * FROM TableX
WHERE ID =
CASE
WHEN @.ID IS NOT NULL THEN @.ID
ELSE ID
END
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Remco" <rembo_r@.hotmail.com> wrote in message
news:eGlfZa3DFHA.1296@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I would like to achieve the following within an stored procedure.
> SELECT * FROM TableX WHERE ID = 1
> OR
> SELECT * FROM TableX WHERE ID IS NOT NULL
> How can I solve this by using a condition in my WHERE clause?
>
> eg.
> //////
> CREATE PROCEDURE TestID
> @.ID INT
> AS
> SELECT * FROM TableX
> WHERE ID =
> CASE
> WHEN @.ID IS NOT NULL THEN @.ID
> ELSE NOT NULL
> END
> ////
> The problem is the ' NOT ' NULL in the ELSE Path
> If I skip the ELSE Path then it will be implicitly NULL
> Thanks for any help,
> Remco
>|||Reading your post again, I realized that what you want is:
select * from tablex
where ([id] = @.id) or (@.id is null and [id] is not null)
AMB
"Alejandro Mesa" wrote:
> Try,
> SELECT * FROM TableX
> WHERE [ID] = @.id or (@.id is null and [id] is null)
>
> AMB
> "Remco" wrote:
>|||"Remco" <rembo_r@.hotmail.com> wrote in message
news:eGlfZa3DFHA.1296@.TK2MSFTNGP10.phx.gbl...

> eg.
> //////
> CREATE PROCEDURE TestID
> @.ID INT
> AS
> SELECT * FROM TableX
> WHERE ID =
> CASE
> WHEN @.ID IS NOT NULL THEN @.ID
> ELSE NOT NULL
> END
> ////
Possibly (untested):
SELECT * FROM TableX where ID = COALESCE(@.ID,ID)
Good Luck,
Jim|||"James Goodwin" <jim.goodwin@.midmichigan.org> wrote in message
news:1fdb1$420b726d$432498ca$16254@.allth
enewsgroups.com...
> SELECT * FROM TableX where ID = COALESCE(@.ID,ID)
I think that will fail if ID is null. Better is
SELECT * FROM TableX where ISNULL(ID, '') = ISNULL(@.ID,'')|||"Remco" <rembo_r@.hotmail.com> wrote in message
news:eGlfZa3DFHA.1296@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I would like to achieve the following within an stored procedure.
> SELECT * FROM TableX WHERE ID = 1
> OR
> SELECT * FROM TableX WHERE ID IS NOT NULL
SELECT * FROM TableX WHERE ID IS NOT NULL
satisfies this condition, but I'm gathering that's not what you want. :)

Case Statement Help Please

Hello,
I have tried the following syntax:
UPDATE WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF
SET WIND =
CASE
WHEN (WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.OCCUPANCY = 'POULTRY')
THEN WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.WIND =
WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.SHORT_CODE_2
WHERE WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.IDENTIFIER = '072'
AND WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.POLICY_NUMBER =
WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.POLICY_NUMBER
AND WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.POLICY_DATE_TIME =
WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.POLICY_DATE_TIME
AND WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.REFERENCE_NUMBER =
WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.SEQUENCE_NUMBER
END
and it is not working - I get the error of
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '='.
I'm not sure what is missing/wrong on that line?
Thanks!Your syntax is wrong, the THEN clause of a CASE cannot be a boolean
expression -- it must be scalar. Please refer to SQL Server Books Online for
syntax details.
Based on a brief glance through the code, the comparable t-SQL code would be
something along the lines of:
UPDATE stage_phx_fact_policy_rf
SET wind = ( SELECT short_code_2
FROM wh_view_endorsement v1
WHERE v1.policy_number
= stage_phx_fact_policy_rf.policy_number
AND v1.policy_date_time
= stage_phx_fact_policy_rf.policy_date_time
AND v1.reference_number
= stage_phx_fact_policy_rf.sequence_number )
WHERE occupancy = 'poultry'
AND EXISTS ( SELECT *
FROM wh_view_endorsement v1
WHERE v1.policy_number
= stage_phx_fact_policy_rf.policy_number
AND v1.policy_date_time
= stage_phx_fact_policy_rf.policy_date_time
AND v1.reference_number
= stage_phx_fact_policy_rf.sequence_number ) ;
If only unique columns are participating in the correlation, you can use
t-SQL FROM clause as well.
Anith|||Patrice,
You have to put the END statement at the point you are finished with the
CASE clause:
UPDATE WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF
SET WIND =
CASE WHEN (WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.OCCUPANCY = 'POULTRY')
THEN WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.WIND =
WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.SHORT_CODE_2
END
WHERE WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.IDENTIFIER = '072'
AND WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.POLICY_NUMBER =
WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.POLICY_NUMBER
AND WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.POLICY_DATE_TIME =
WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.POLICY_DATE_TIME
AND WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.REFERENCE_NUMBER =
WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.SEQUENCE_NUMBER
John Scragg
"Patrice" wrote:

> Hello,
> I have tried the following syntax:
> UPDATE WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF
> SET WIND =
> CASE
> WHEN (WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.OCCUPANCY = 'POULTRY')
> THEN WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.WIND =
> WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.SHORT_CODE_2
> WHERE WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.IDENTIFIER = '072'
> AND WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.POLICY_NUMBER =
> WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.POLICY_NUMBER
> AND WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.POLICY_DATE_TIME =
> WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.POLICY_DATE_TIME
> AND WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.REFERENCE_NUMBER =
> WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.SEQUENCE_NUMBER
> END
>
>
>
> and it is not working - I get the error of
> Server: Msg 170, Level 15, State 1, Line 6
> Line 6: Incorrect syntax near '='.
>
> I'm not sure what is missing/wrong on that line?
> Thanks!
>

CASE Statement

Hello,
I am trying to understand how to use CASE statements within a CASE
statement. I am using the following but getting the error:
Incorrect syntax near the keyword 'CASE'.
declare @.MyDate DATETIME, @.YearType varchar(255)
set @.MyDate = getdate()
set @.YearType = 'fiscal'
Select
CASE @.YearType
WHEN 'fiscal' THEN
CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q02-' +
Convert(varchar,year(@.MyDate) - 1)
CASE WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q03-' +
Convert(varchar,year(@.MyDate) - 1)
CASE WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q04-' +
Convert(varchar,year(@.MyDate) - 1)
CASE WHEN month(@.MyDate) BETWEEN 10 AND 12 THEN 'Q04' +
Convert(varchar,year(@.MyDate))
WHEN 'calendar' THEN
CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q01-' +
Convert(varchar,year(@.MyDate) )
CASE WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q02-' +
Convert(varchar,year(@.MyDate) )
CASE WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q03-' +
Convert(varchar,year(@.MyDate) )
CASE WHEN month(@.MyDate) BETWEEN 10 AND 12 THEN 'Q04-' +
Convert(varchar,year(@.MyDate))
END
Any help would be appreciated.
--
Thanks in advance,
sck10You are missing END in the inner CASEs.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sck10" <sck10@.online.nospam> wrote in message news:upANGW6XFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I am trying to understand how to use CASE statements within a CASE
> statement. I am using the following but getting the error:
> Incorrect syntax near the keyword 'CASE'.
> declare @.MyDate DATETIME, @.YearType varchar(255)
> set @.MyDate = getdate()
> set @.YearType = 'fiscal'
> Select
> CASE @.YearType
> WHEN 'fiscal' THEN
> CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q02-' +
> Convert(varchar,year(@.MyDate) - 1)
> CASE WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q03-' +
> Convert(varchar,year(@.MyDate) - 1)
> CASE WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q04-' +
> Convert(varchar,year(@.MyDate) - 1)
> CASE WHEN month(@.MyDate) BETWEEN 10 AND 12 THEN 'Q04' +
> Convert(varchar,year(@.MyDate))
> WHEN 'calendar' THEN
> CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q01-' +
> Convert(varchar,year(@.MyDate) )
> CASE WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q02-' +
> Convert(varchar,year(@.MyDate) )
> CASE WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q03-' +
> Convert(varchar,year(@.MyDate) )
> CASE WHEN month(@.MyDate) BETWEEN 10 AND 12 THEN 'Q04-' +
> Convert(varchar,year(@.MyDate))
> END
>
> Any help would be appreciated.
> --
> Thanks in advance,
> sck10
>|||On Mon, 23 May 2005 09:41:35 -0500, sck10 wrote:
>Hello,
>I am trying to understand how to use CASE statements within a CASE
>statement. I am using the following but getting the error:
>Incorrect syntax near the keyword 'CASE'.
Hi sck10,
Tibor already found the missing END's, but he seems to have missed the
extraneous CASE's.
Select
CASE @.YearType
WHEN 'fiscal' THEN
CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q02-' +
Convert(varchar,year(@.MyDate) - 1)
WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q03-' +
Convert(varchar,year(@.MyDate) - 1)
WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q04-' +
Convert(varchar,year(@.MyDate) - 1)
ELSE 'Q04' + Convert(varchar,year(@.MyDate))
END
WHEN 'calendar' THEN
CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q01-' +
Convert(varchar,year(@.MyDate) )
WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q02-' +
Convert(varchar,year(@.MyDate) )
WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q03-' +
Convert(varchar,year(@.MyDate) )
ELSE 'Q04-' + Convert(varchar,year(@.MyDate))
END
END
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Case statement

Is it possible to have multiple expressions, or dml expressions in a case
statement similar to the following?
DECLARE @.path_f1 nvarchar(200)
DECLARE @.path_f2 nvarchar(200)
SELECT
@.path_f1 = output_path_file1,
@.path_f2 = output_path_file2
FROM t1
WHERE t1id = 1
SELECT
CASE
WHEN f1_status = 0 THEN
UPDATE t1
SET f1_status = 1, f1_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f1, null
WHEN f1_status = 1 AND f2_status = 0 THEN
UPDATE t1
SET file2_status = 1, file2_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f2, null
ELSE
RAISERROR ('Error Message', 16,1)
END
FROM t1
WHERE t1id = 1
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1Not the way you're doing it. You can have something along the lines of:
UPDATE t1
SET
f1_status = CASE
WHEN f1_status = 0 THEN 1
ELSE f1_status END
, f1_created = CASE
WHEN f1_status = 0 THEN getdate()
ELSE f1_created END
WHERE t1id = 1
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:5df3ff92c5d28@.uwe...
Is it possible to have multiple expressions, or dml expressions in a case
statement similar to the following?
DECLARE @.path_f1 nvarchar(200)
DECLARE @.path_f2 nvarchar(200)
SELECT
@.path_f1 = output_path_file1,
@.path_f2 = output_path_file2
FROM t1
WHERE t1id = 1
SELECT
CASE
WHEN f1_status = 0 THEN
UPDATE t1
SET f1_status = 1, f1_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f1, null
WHEN f1_status = 1 AND f2_status = 0 THEN
UPDATE t1
SET file2_status = 1, file2_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f2, null
ELSE
RAISERROR ('Error Message', 16,1)
END
FROM t1
WHERE t1id = 1
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1|||The issue would become very straightforward and simple only if one stops
using the phrase 'a CASE statement' because there is no such a thing, and
starts using 'a CASE expression.'
Linchi
"cbrichards via SQLMonster.com" wrote:
> Is it possible to have multiple expressions, or dml expressions in a case
> statement similar to the following?
> DECLARE @.path_f1 nvarchar(200)
> DECLARE @.path_f2 nvarchar(200)
> SELECT
> @.path_f1 = output_path_file1,
> @.path_f2 = output_path_file2
> FROM t1
> WHERE t1id = 1
> SELECT
> CASE
> WHEN f1_status = 0 THEN
> UPDATE t1
> SET f1_status = 1, f1_created = getdate()
> WHERE t1id = 1
> EXEC usp_t1 @.path_f1, null
> WHEN f1_status = 1 AND f2_status = 0 THEN
> UPDATE t1
> SET file2_status = 1, file2_created = getdate()
> WHERE t1id = 1
> EXEC usp_t1 @.path_f2, null
> ELSE
> RAISERROR ('Error Message', 16,1)
> END
> FROM t1
> WHERE t1id = 1
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1
>

CASE Statement

Hello,
I am trying to understand how to use CASE statements within a CASE
statement. I am using the following but getting the error:
Incorrect syntax near the keyword 'CASE'.
declare @.MyDate DATETIME, @.YearType varchar(255)
set @.MyDate = getdate()
set @.YearType = 'fiscal'
Select
CASE @.YearType
WHEN 'fiscal' THEN
CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q02-' +
Convert(varchar,year(@.MyDate) - 1)
CASE WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q03-' +
Convert(varchar,year(@.MyDate) - 1)
CASE WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q04-' +
Convert(varchar,year(@.MyDate) - 1)
CASE WHEN month(@.MyDate) BETWEEN 10 AND 12 THEN 'Q04' +
Convert(varchar,year(@.MyDate))
WHEN 'calendar' THEN
CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q01-' +
Convert(varchar,year(@.MyDate) )
CASE WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q02-' +
Convert(varchar,year(@.MyDate) )
CASE WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q03-' +
Convert(varchar,year(@.MyDate) )
CASE WHEN month(@.MyDate) BETWEEN 10 AND 12 THEN 'Q04-' +
Convert(varchar,year(@.MyDate))
END
Any help would be appreciated.
Thanks in advance,
sck10
You are missing END in the inner CASEs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sck10" <sck10@.online.nospam> wrote in message news:upANGW6XFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I am trying to understand how to use CASE statements within a CASE
> statement. I am using the following but getting the error:
> Incorrect syntax near the keyword 'CASE'.
> declare @.MyDate DATETIME, @.YearType varchar(255)
> set @.MyDate = getdate()
> set @.YearType = 'fiscal'
> Select
> CASE @.YearType
> WHEN 'fiscal' THEN
> CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q02-' +
> Convert(varchar,year(@.MyDate) - 1)
> CASE WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q03-' +
> Convert(varchar,year(@.MyDate) - 1)
> CASE WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q04-' +
> Convert(varchar,year(@.MyDate) - 1)
> CASE WHEN month(@.MyDate) BETWEEN 10 AND 12 THEN 'Q04' +
> Convert(varchar,year(@.MyDate))
> WHEN 'calendar' THEN
> CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q01-' +
> Convert(varchar,year(@.MyDate) )
> CASE WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q02-' +
> Convert(varchar,year(@.MyDate) )
> CASE WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q03-' +
> Convert(varchar,year(@.MyDate) )
> CASE WHEN month(@.MyDate) BETWEEN 10 AND 12 THEN 'Q04-' +
> Convert(varchar,year(@.MyDate))
> END
>
> Any help would be appreciated.
> --
> Thanks in advance,
> sck10
>
|||On Mon, 23 May 2005 09:41:35 -0500, sck10 wrote:

>Hello,
>I am trying to understand how to use CASE statements within a CASE
>statement. I am using the following but getting the error:
>Incorrect syntax near the keyword 'CASE'.
Hi sck10,
Tibor already found the missing END's, but he seems to have missed the
extraneous CASE's.
Select
CASE @.YearType
WHEN 'fiscal' THEN
CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q02-' +
Convert(varchar,year(@.MyDate) - 1)
WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q03-' +
Convert(varchar,year(@.MyDate) - 1)
WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q04-' +
Convert(varchar,year(@.MyDate) - 1)
ELSE 'Q04' + Convert(varchar,year(@.MyDate))
END
WHEN 'calendar' THEN
CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q01-' +
Convert(varchar,year(@.MyDate) )
WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q02-' +
Convert(varchar,year(@.MyDate) )
WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q03-' +
Convert(varchar,year(@.MyDate) )
ELSE 'Q04-' + Convert(varchar,year(@.MyDate))
END
END
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

CASE Statement

Hello,
I am trying to understand how to use CASE statements within a CASE
statement. I am using the following but getting the error:
Incorrect syntax near the keyword 'CASE'.
declare @.MyDate DATETIME, @.YearType varchar(255)
set @.MyDate = getdate()
set @.YearType = 'fiscal'
Select
CASE @.YearType
WHEN 'fiscal' THEN
CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q02-' +
Convert(varchar,year(@.MyDate) - 1)
CASE WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q03-' +
Convert(varchar,year(@.MyDate) - 1)
CASE WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q04-' +
Convert(varchar,year(@.MyDate) - 1)
CASE WHEN month(@.MyDate) BETWEEN 10 AND 12 THEN 'Q04' +
Convert(varchar,year(@.MyDate))
WHEN 'calendar' THEN
CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q01-' +
Convert(varchar,year(@.MyDate) )
CASE WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q02-' +
Convert(varchar,year(@.MyDate) )
CASE WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q03-' +
Convert(varchar,year(@.MyDate) )
CASE WHEN month(@.MyDate) BETWEEN 10 AND 12 THEN 'Q04-' +
Convert(varchar,year(@.MyDate))
END
Any help would be appreciated.
Thanks in advance,
sck10You are missing END in the inner CASEs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sck10" <sck10@.online.nospam> wrote in message news:upANGW6XFHA.3620@.TK2MSFTNGP09.phx.gbl...

> Hello,
> I am trying to understand how to use CASE statements within a CASE
> statement. I am using the following but getting the error:
> Incorrect syntax near the keyword 'CASE'.
> declare @.MyDate DATETIME, @.YearType varchar(255)
> set @.MyDate = getdate()
> set @.YearType = 'fiscal'
> Select
> CASE @.YearType
> WHEN 'fiscal' THEN
> CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q02-' +
> Convert(varchar,year(@.MyDate) - 1)
> CASE WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q03-' +
> Convert(varchar,year(@.MyDate) - 1)
> CASE WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q04-' +
> Convert(varchar,year(@.MyDate) - 1)
> CASE WHEN month(@.MyDate) BETWEEN 10 AND 12 THEN 'Q04' +
> Convert(varchar,year(@.MyDate))
> WHEN 'calendar' THEN
> CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q01-' +
> Convert(varchar,year(@.MyDate) )
> CASE WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q02-' +
> Convert(varchar,year(@.MyDate) )
> CASE WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q03-' +
> Convert(varchar,year(@.MyDate) )
> CASE WHEN month(@.MyDate) BETWEEN 10 AND 12 THEN 'Q04-' +
> Convert(varchar,year(@.MyDate))
> END
>
> Any help would be appreciated.
> --
> Thanks in advance,
> sck10
>|||On Mon, 23 May 2005 09:41:35 -0500, sck10 wrote:

>Hello,
>I am trying to understand how to use CASE statements within a CASE
>statement. I am using the following but getting the error:
>Incorrect syntax near the keyword 'CASE'.
Hi sck10,
Tibor already found the missing END's, but he seems to have missed the
extraneous CASE's.
Select
CASE @.YearType
WHEN 'fiscal' THEN
CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q02-' +
Convert(varchar,year(@.MyDate) - 1)
WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q03-' +
Convert(varchar,year(@.MyDate) - 1)
WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q04-' +
Convert(varchar,year(@.MyDate) - 1)
ELSE 'Q04' + Convert(varchar,year(@.MyDate))
END
WHEN 'calendar' THEN
CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q01-' +
Convert(varchar,year(@.MyDate) )
WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q02-' +
Convert(varchar,year(@.MyDate) )
WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q03-' +
Convert(varchar,year(@.MyDate) )
ELSE 'Q04-' + Convert(varchar,year(@.MyDate))
END
END
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

CASE SQL Construct

I am using the following code to construct an SQL 7.0 View Column

"CASE WHEN [DailyHours] > SUM([TransAmt]) THEN 0 ELSE 1 END"

and it works great!!

However, when I try the same line in SQL 2000, I get the message "The query designer does not support the case SQL construct"

The help screen is no help as all it says is "the syntax you entered is valid but is not supported visually by Query Designer. Be sure the verify your syntax before saving."

It will not let me save, so I'm not sure what to do from here now??You need to write your query in Query Analyzer, not the Query Designer, which is limited in the types of query logic it can represent in its GUI interface.
No self-respecting DBA writes queries in Query Designer. Time to take off the training wheels...|||You are right, I shouldn't be using Query Designer.... here's my problem.

My client is in another city, and I am logging onto their system remotely to copy some code I've written using a VPN they set up for me on my lap top...

The only software I have on my lap top is designer ... which is lame I know.

I have Visual Studio on my primary system...... but no simple way to put this code on their system - ANY thoughts?|||Why don't you use osql?|||No self-respecting DBA writes queries in Query Designer. Time to take off the training wheels...

You taking midol today?|||You taking midol today?
Just my usual grace and elegance.|||Just rename the file to remove the .txt extension|||I opened the file and I get a black screen that looks like an old DOS prompt, which is asking for a password. No matter what I enter it doesn't accept, and a blank simply closes the window....... I'm confused..... what's the password - my windos password or something specific that I have yet to learn??|||at a command window type 'osql.exe /?' it will give you a quick help message on how to use osql. Once you are in to the server, you can execute t-sql commands from there. Look it up in BOL if you need more help.

Case sensitivity error!

Hi,
1] I have a domain user group 'Domain_name \my group' added into my SQL
Server.
2] When I execute the following code ..
if not exists (select * from master.dbo.syslogins where loginname = N'Domain_name\My Group')
exec sp_grantlogin N'Domain_name\My Group'
exec sp_defaultdb N'Domain_name\My Group', N'master'
exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
GO
I receive a error..
Error 15401
Windows NT user or group 'Domain_name\My Group not found.
Check the name again.
I just changed the code where ever *My Group* was there to *my group* then
the query was success.
I have gone through the article below but it dint answer my doubt.
http://support.microsoft.com/kb/q245768/
The server collation is Latin1_General_BIN
What is happening and what is the other way if I dont have to modify the code?
Thanks
ReddiYou have a binary collation (very unusual, btw) which is also case sensitive.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:D0ACD235-3F0D-4D56-BAA5-562E88C22939@.microsoft.com...
> Hi,
> 1] I have a domain user group 'Domain_name \my group' added into my SQL
> Server.
> 2] When I execute the following code ..
> if not exists (select * from master.dbo.syslogins where loginname => N'Domain_name\My Group')
> exec sp_grantlogin N'Domain_name\My Group'
> exec sp_defaultdb N'Domain_name\My Group', N'master'
> exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
> GO
> I receive a error..
> Error 15401
> Windows NT user or group 'Domain_name\My Group not found.
> Check the name again.
> I just changed the code where ever *My Group* was there to *my group* then
> the query was success.
> I have gone through the article below but it dint answer my doubt.
> http://support.microsoft.com/kb/q245768/
> The server collation is Latin1_General_BIN
> What is happening and what is the other way if I dont have to modify the code?
> Thanks
> Reddi
>|||> The server collation is Latin1_General_BIN
I'm not completely sure about this, but it looks as if the BIN collation
means that you're dealing with a SQLServer instance which is case sensitive.
It's rare that this is required, as Windows isn't case sensitive.
As for turning SQLServer from case sensitive to case insensitive, I've never
tried it. I'd be surprised if it were possible though...
Griff|||> As for turning SQLServer from case sensitive to case insensitive, I've never
> tried it. I'd be surprised if it were possible though...
You need to rebuild the system databases using rebuildm.exe, which means you lose all stuff in the
system databases. Also, this doesn't change the collation for the user databases.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Griff" <Howling@.The.Moon> wrote in message news:uiyxm0w8EHA.3988@.TK2MSFTNGP10.phx.gbl...
>> The server collation is Latin1_General_BIN
> I'm not completely sure about this, but it looks as if the BIN collation
> means that you're dealing with a SQLServer instance which is case sensitive.
> It's rare that this is required, as Windows isn't case sensitive.
> As for turning SQLServer from case sensitive to case insensitive, I've never
> tried it. I'd be surprised if it were possible though...
> Griff
>|||Hi Tibor,
Thanks for the response. Correct me if i am off track. If the issue is with
collaltion (case sensitivity), SQL server should not have allowed adding the
Windows NT group with wrong case in the first place as pointed by me in the
URL.
I have other server with same collation settings but it has Windows NT group
as Domain_name\My Group. I need not have to make any code change as what is
mentioned in code is matching with the group.
Thanks
Reddi
"Tibor Karaszi" wrote:
> > As for turning SQLServer from case sensitive to case insensitive, I've never
> > tried it. I'd be surprised if it were possible though...
> You need to rebuild the system databases using rebuildm.exe, which means you lose all stuff in the
> system databases. Also, this doesn't change the collation for the user databases.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Griff" <Howling@.The.Moon> wrote in message news:uiyxm0w8EHA.3988@.TK2MSFTNGP10.phx.gbl...
> >> The server collation is Latin1_General_BIN
> >
> > I'm not completely sure about this, but it looks as if the BIN collation
> > means that you're dealing with a SQLServer instance which is case sensitive.
> >
> > It's rare that this is required, as Windows isn't case sensitive.
> >
> > As for turning SQLServer from case sensitive to case insensitive, I've never
> > tried it. I'd be surprised if it were possible though...
> >
> > Griff
> >
> >
>
>|||You could try rewriting your code to make it case-insensitive. How about
the following (not tested at all):
DECLARE @.ln nvarchar(100)
select @.ln = loginname from master.dbo.syslogins where loginname COLLATE
SQL_Latin1_General_CP1_CI_AS = N'Domain_name\My Group'
if not @.ln is null
BEGIN
exec sp_grantlogin @.ln
exec sp_defaultdb @.ln, N'master'
exec sp_defaultlanguage @.ln, N'us_english'
END
Note that I added BEGIN and END because in the original the second and third
lines were being executed unconditionally.
HTH,
Mike
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:D0ACD235-3F0D-4D56-BAA5-562E88C22939@.microsoft.com...
> Hi,
> 1] I have a domain user group 'Domain_name \my group' added into my SQL
> Server.
> 2] When I execute the following code ..
> if not exists (select * from master.dbo.syslogins where loginname => N'Domain_name\My Group')
> exec sp_grantlogin N'Domain_name\My Group'
> exec sp_defaultdb N'Domain_name\My Group', N'master'
> exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
> GO
> I receive a error..
> Error 15401
> Windows NT user or group 'Domain_name\My Group not found.
> Check the name again.
> I just changed the code where ever *My Group* was there to *my group* then
> the query was success.
> I have gone through the article below but it dint answer my doubt.
> http://support.microsoft.com/kb/q245768/
> The server collation is Latin1_General_BIN
> What is happening and what is the other way if I dont have to modify the
> code?
> Thanks
> Reddi
>

Case sensitivity error!

Hi,
1] I have a domain user group 'Domain_name \my group' added into my SQL
Server.
2] When I execute the following code ..
if not exists (select * from master.dbo.syslogins where loginname =
N'Domain_name\My Group')
exec sp_grantlogin N'Domain_name\My Group'
exec sp_defaultdb N'Domain_name\My Group', N'master'
exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
GO
I receive a error..
Error 15401
Windows NT user or group 'Domain_name\My Group not found.
Check the name again.
I just changed the code where ever *My Group* was there to *my group* then
the query was success.
I have gone through the article below but it dint answer my doubt.
http://support.microsoft.com/kb/q245768/
The server collation is Latin1_General_BIN
What is happening and what is the other way if I dont have to modify the code?
Thanks
Reddi
You have a binary collation (very unusual, btw) which is also case sensitive.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:D0ACD235-3F0D-4D56-BAA5-562E88C22939@.microsoft.com...
> Hi,
> 1] I have a domain user group 'Domain_name \my group' added into my SQL
> Server.
> 2] When I execute the following code ..
> if not exists (select * from master.dbo.syslogins where loginname =
> N'Domain_name\My Group')
> exec sp_grantlogin N'Domain_name\My Group'
> exec sp_defaultdb N'Domain_name\My Group', N'master'
> exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
> GO
> I receive a error..
> Error 15401
> Windows NT user or group 'Domain_name\My Group not found.
> Check the name again.
> I just changed the code where ever *My Group* was there to *my group* then
> the query was success.
> I have gone through the article below but it dint answer my doubt.
> http://support.microsoft.com/kb/q245768/
> The server collation is Latin1_General_BIN
> What is happening and what is the other way if I dont have to modify the code?
> Thanks
> Reddi
>
|||> The server collation is Latin1_General_BIN
I'm not completely sure about this, but it looks as if the BIN collation
means that you're dealing with a SQLServer instance which is case sensitive.
It's rare that this is required, as Windows isn't case sensitive.
As for turning SQLServer from case sensitive to case insensitive, I've never
tried it. I'd be surprised if it were possible though...
Griff
|||> As for turning SQLServer from case sensitive to case insensitive, I've never
> tried it. I'd be surprised if it were possible though...
You need to rebuild the system databases using rebuildm.exe, which means you lose all stuff in the
system databases. Also, this doesn't change the collation for the user databases.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Griff" <Howling@.The.Moon> wrote in message news:uiyxm0w8EHA.3988@.TK2MSFTNGP10.phx.gbl...
> I'm not completely sure about this, but it looks as if the BIN collation
> means that you're dealing with a SQLServer instance which is case sensitive.
> It's rare that this is required, as Windows isn't case sensitive.
> As for turning SQLServer from case sensitive to case insensitive, I've never
> tried it. I'd be surprised if it were possible though...
> Griff
>
|||Hi Tibor,
Thanks for the response. Correct me if i am off track. If the issue is with
collaltion (case sensitivity), SQL server should not have allowed adding the
Windows NT group with wrong case in the first place as pointed by me in the
URL.
I have other server with same collation settings but it has Windows NT group
as Domain_name\My Group. I need not have to make any code change as what is
mentioned in code is matching with the group.
Thanks
Reddi
"Tibor Karaszi" wrote:

> You need to rebuild the system databases using rebuildm.exe, which means you lose all stuff in the
> system databases. Also, this doesn't change the collation for the user databases.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Griff" <Howling@.The.Moon> wrote in message news:uiyxm0w8EHA.3988@.TK2MSFTNGP10.phx.gbl...
>
>
|||You could try rewriting your code to make it case-insensitive. How about
the following (not tested at all):
DECLARE @.ln nvarchar(100)
select @.ln = loginname from master.dbo.syslogins where loginname COLLATE
SQL_Latin1_General_CP1_CI_AS = N'Domain_name\My Group'
if not @.ln is null
BEGIN
exec sp_grantlogin @.ln
exec sp_defaultdb @.ln, N'master'
exec sp_defaultlanguage @.ln, N'us_english'
END
Note that I added BEGIN and END because in the original the second and third
lines were being executed unconditionally.
HTH,
Mike
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:D0ACD235-3F0D-4D56-BAA5-562E88C22939@.microsoft.com...
> Hi,
> 1] I have a domain user group 'Domain_name \my group' added into my SQL
> Server.
> 2] When I execute the following code ..
> if not exists (select * from master.dbo.syslogins where loginname =
> N'Domain_name\My Group')
> exec sp_grantlogin N'Domain_name\My Group'
> exec sp_defaultdb N'Domain_name\My Group', N'master'
> exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
> GO
> I receive a error..
> Error 15401
> Windows NT user or group 'Domain_name\My Group not found.
> Check the name again.
> I just changed the code where ever *My Group* was there to *my group* then
> the query was success.
> I have gone through the article below but it dint answer my doubt.
> http://support.microsoft.com/kb/q245768/
> The server collation is Latin1_General_BIN
> What is happening and what is the other way if I dont have to modify the
> code?
> Thanks
> Reddi
>

Case sensitivity error!

Hi,
1] I have a domain user group 'Domain_name \my group' added into my SQL
Server.
2] When I execute the following code ..
if not exists (select * from master.dbo.syslogins where loginname =
N'Domain_name\My Group')
exec sp_grantlogin N'Domain_name\My Group'
exec sp_defaultdb N'Domain_name\My Group', N'master'
exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
GO
I receive a error..
Error 15401
Windows NT user or group 'Domain_name\My Group not found.
Check the name again.
I just changed the code where ever *My Group* was there to *my group* then
the query was success.
I have gone through the article below but it dint answer my doubt.
http://support.microsoft.com/kb/q245768/
The server collation is Latin1_General_BIN
What is happening and what is the other way if I dont have to modify the cod
e?
Thanks
ReddiYou have a binary collation (very unusual, btw) which is also case sensitive
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:D0ACD235-3F0D-4D56-BAA5-562E88C22939@.microsoft.com...
> Hi,
> 1] I have a domain user group 'Domain_name \my group' added into my SQL
> Server.
> 2] When I execute the following code ..
> if not exists (select * from master.dbo.syslogins where loginname =
> N'Domain_name\My Group')
> exec sp_grantlogin N'Domain_name\My Group'
> exec sp_defaultdb N'Domain_name\My Group', N'master'
> exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
> GO
> I receive a error..
> Error 15401
> Windows NT user or group 'Domain_name\My Group not found.
> Check the name again.
> I just changed the code where ever *My Group* was there to *my group* then
> the query was success.
> I have gone through the article below but it dint answer my doubt.
> http://support.microsoft.com/kb/q245768/
> The server collation is Latin1_General_BIN
> What is happening and what is the other way if I dont have to modify the c
ode?
> Thanks
> Reddi
>|||> The server collation is Latin1_General_BIN
I'm not completely sure about this, but it looks as if the BIN collation
means that you're dealing with a SQLServer instance which is case sensitive.
It's rare that this is required, as Windows isn't case sensitive.
As for turning SQLServer from case sensitive to case insensitive, I've never
tried it. I'd be surprised if it were possible though...
Griff|||> As for turning SQLServer from case sensitive to case insensitive, I've nevern">
> tried it. I'd be surprised if it were possible though...
You need to rebuild the system databases using rebuildm.exe, which means you
lose all stuff in the
system databases. Also, this doesn't change the collation for the user datab
ases.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Griff" <Howling@.The.Moon> wrote in message news:uiyxm0w8EHA.3988@.TK2MSFTNGP10.phx.gbl...[vb
col=seagreen]
> I'm not completely sure about this, but it looks as if the BIN collation
> means that you're dealing with a SQLServer instance which is case sensitiv
e.
> It's rare that this is required, as Windows isn't case sensitive.
> As for turning SQLServer from case sensitive to case insensitive, I've nev
er
> tried it. I'd be surprised if it were possible though...
> Griff
>[/vbcol]|||Hi Tibor,
Thanks for the response. Correct me if i am off track. If the issue is with
collaltion (case sensitivity), SQL server should not have allowed adding the
Windows NT group with wrong case in the first place as pointed by me in the
URL.
I have other server with same collation settings but it has Windows NT group
as Domain_name\My Group. I need not have to make any code change as what is
mentioned in code is matching with the group.
Thanks
Reddi
"Tibor Karaszi" wrote:

> You need to rebuild the system databases using rebuildm.exe, which means y
ou lose all stuff in the
> system databases. Also, this doesn't change the collation for the user dat
abases.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Griff" <Howling@.The.Moon> wrote in message news:uiyxm0w8EHA.3988@.TK2MSFTN
GP10.phx.gbl...
>
>|||You could try rewriting your code to make it case-insensitive. How about
the following (not tested at all):
DECLARE @.ln nvarchar(100)
select @.ln = loginname from master.dbo.syslogins where loginname COLLATE
SQL_Latin1_General_CP1_CI_AS = N'Domain_name\My Group'
if not @.ln is null
BEGIN
exec sp_grantlogin @.ln
exec sp_defaultdb @.ln, N'master'
exec sp_defaultlanguage @.ln, N'us_english'
END
Note that I added BEGIN and END because in the original the second and third
lines were being executed unconditionally.
HTH,
Mike
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:D0ACD235-3F0D-4D56-BAA5-562E88C22939@.microsoft.com...
> Hi,
> 1] I have a domain user group 'Domain_name \my group' added into my SQL
> Server.
> 2] When I execute the following code ..
> if not exists (select * from master.dbo.syslogins where loginname =
> N'Domain_name\My Group')
> exec sp_grantlogin N'Domain_name\My Group'
> exec sp_defaultdb N'Domain_name\My Group', N'master'
> exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
> GO
> I receive a error..
> Error 15401
> Windows NT user or group 'Domain_name\My Group not found.
> Check the name again.
> I just changed the code where ever *My Group* was there to *my group* then
> the query was success.
> I have gone through the article below but it dint answer my doubt.
> http://support.microsoft.com/kb/q245768/
> The server collation is Latin1_General_BIN
> What is happening and what is the other way if I dont have to modify the
> code?
> Thanks
> Reddi
>

Case sensitive problem

You simply need to change the collation in the WHERE clause so that it is
case sensitive. The following example illustrates how the COLLATE clause ca
n
be used to define the collation:
CREATE TABLE [Function]
(
[Function] VARCHAR(100)
)
INSERT [Function] SELECT 'dr'
INSERT [Function] SELECT 'DR'
INSERT [Function] SELECT 'Dr'
SELECT *
FROM [Function]
WHERE [Function] = 'Dr'
Returns:
Function
dr
DR
Dr
(3 row(s) affected)
SELECT *
FROM [Function]
WHERE [Function] = 'Dr' COLLATE LATIN1_General_CS_AS
Function
Dr
(1 row(s) affected)
HTH
- Peter Ward
WARDY IT Solutions
"bad_boyu" wrote:

> Hello,
> I have a very.. very.. very big database that has a table with let's
> say column "Function". I want to do different selects on this column
> "Function". This select must be case sensitive, so if i do a select
> with like "Dr" then the results must contain the Function that have D
> in uppercase and r in lowercase. When the database was created there
> were no constraints concerning column "Function", concern like all
> fields are in uppercase.
> Is there a solution to do this selects(case sensitive) without changing
> the database?
> Thanks,
> BB
>To add on to Peter's response, you can also include the case-insensitive
predicate so that an index on the column can be used:
SELECT *
FROM [Function]
WHERE [Function] = 'Dr' AND
[Function] = 'Dr' COLLATE LATIN1_General_CS_AS
Hope this helps.
Dan Guzman
SQL Server MVP
"bad_boyu" <silaghi.ovidiu@.gmail.com> wrote in message
news:1150245243.474122.145330@.i40g2000cwc.googlegroups.com...
> Hello,
> I have a very.. very.. very big database that has a table with let's
> say column "Function". I want to do different selects on this column
> "Function". This select must be case sensitive, so if i do a select
> with like "Dr" then the results must contain the Function that have D
> in uppercase and r in lowercase. When the database was created there
> were no constraints concerning column "Function", concern like all
> fields are in uppercase.
> Is there a solution to do this selects(case sensitive) without changing
> the database?
> Thanks,
> BB
>|||Hello,
I have a very.. very.. very big database that has a table with let's
say column "Function". I want to do different selects on this column
"Function". This select must be case sensitive, so if i do a select
with like "Dr" then the results must contain the Function that have D
in uppercase and r in lowercase. When the database was created there
were no constraints concerning column "Function", concern like all
fields are in uppercase.
Is there a solution to do this selects(case sensitive) without changing
the database?
Thanks,
BB|||You simply need to change the collation in the WHERE clause so that it is
case sensitive. The following example illustrates how the COLLATE clause ca
n
be used to define the collation:
CREATE TABLE [Function]
(
[Function] VARCHAR(100)
)
INSERT [Function] SELECT 'dr'
INSERT [Function] SELECT 'DR'
INSERT [Function] SELECT 'Dr'
SELECT *
FROM [Function]
WHERE [Function] = 'Dr'
Returns:
Function
dr
DR
Dr
(3 row(s) affected)
SELECT *
FROM [Function]
WHERE [Function] = 'Dr' COLLATE LATIN1_General_CS_AS
Function
Dr
(1 row(s) affected)
HTH
- Peter Ward
WARDY IT Solutions
"bad_boyu" wrote:

> Hello,
> I have a very.. very.. very big database that has a table with let's
> say column "Function". I want to do different selects on this column
> "Function". This select must be case sensitive, so if i do a select
> with like "Dr" then the results must contain the Function that have D
> in uppercase and r in lowercase. When the database was created there
> were no constraints concerning column "Function", concern like all
> fields are in uppercase.
> Is there a solution to do this selects(case sensitive) without changing
> the database?
> Thanks,
> BB
>|||To add on to Peter's response, you can also include the case-insensitive
predicate so that an index on the column can be used:
SELECT *
FROM [Function]
WHERE [Function] = 'Dr' AND
[Function] = 'Dr' COLLATE LATIN1_General_CS_AS
Hope this helps.
Dan Guzman
SQL Server MVP
"bad_boyu" <silaghi.ovidiu@.gmail.com> wrote in message
news:1150245243.474122.145330@.i40g2000cwc.googlegroups.com...
> Hello,
> I have a very.. very.. very big database that has a table with let's
> say column "Function". I want to do different selects on this column
> "Function". This select must be case sensitive, so if i do a select
> with like "Dr" then the results must contain the Function that have D
> in uppercase and r in lowercase. When the database was created there
> were no constraints concerning column "Function", concern like all
> fields are in uppercase.
> Is there a solution to do this selects(case sensitive) without changing
> the database?
> Thanks,
> BB
>|||Thanks a lot for these replies!
But I have another problem, I need that the method LIKE or something
similar to be case-sensitive! The Function column has fields like this:
"Dr Bad Boy", "DR Angelina",
"Prof dr Italian", "Prof Dr Adrian",...
Is there any solution for this kind of problem?
Best regards,
BB
Dan Guzman wrote:
> To add on to Peter's response, you can also include the case-insensitive
> predicate so that an index on the column can be used:
> SELECT *
> FROM [Function]
> WHERE [Function] = 'Dr' AND
> [Function] = 'Dr' COLLATE LATIN1_General_CS_AS
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP|||See if this helps:
http://vyaskn.tripod.com/case_sensi..._sql_server.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"bad_boyu" <silaghi.ovidiu@.gmail.com> wrote in message
news:1150245243.474122.145330@.i40g2000cwc.googlegroups.com...
Hello,
I have a very.. very.. very big database that has a table with let's
say column "Function". I want to do different selects on this column
"Function". This select must be case sensitive, so if i do a select
with like "Dr" then the results must contain the Function that have D
in uppercase and r in lowercase. When the database was created there
were no constraints concerning column "Function", concern like all
fields are in uppercase.
Is there a solution to do this selects(case sensitive) without changing
the database?
Thanks,
BB|||On 14 Jun 2006 02:08:55 -0700, bad_boyu wrote:

>Thanks a lot for these replies!
>But I have another problem, I need that the method LIKE or something
>similar to be case-sensitive! The Function column has fields like this:
>"Dr Bad Boy", "DR Angelina",
>"Prof dr Italian", "Prof Dr Adrian",...
>Is there any solution for this kind of problem?
Hi BB,
SELECT *
FROM Function
WHERE Function LIKE '%Dr%' COLLATE LATIN1_General_CS_AS
Hugo Kornelis, SQL Server MVP|||Thanks for these quick replies!!! I believe it works ;)
You are the best!|||Thanks a lot for these replies!
But I have another problem, I need that the method LIKE or something
similar to be case-sensitive! The Function column has fields like this:
"Dr Bad Boy", "DR Angelina",
"Prof dr Italian", "Prof Dr Adrian",...
Is there any solution for this kind of problem?
Best regards,
BB
Dan Guzman wrote:
> To add on to Peter's response, you can also include the case-insensitive
> predicate so that an index on the column can be used:
> SELECT *
> FROM [Function]
> WHERE [Function] = 'Dr' AND
> [Function] = 'Dr' COLLATE LATIN1_General_CS_AS
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP

Case Problem!

Hi all,
I am trying to create the following stored procedure..
I am still not all that familiar with the Case statement so any help would
be appreciated...!!!
I am getting a syntax errors...
Is there a better way to do this?
CREATE PROCEDURE [dbo].[asmt_v1_ins_card_induction]
@.area VARCHAR(120)
AS
BEGIN
Declare @.last_induction INT
SET @.last_induction = (SELECT last_induction FROM asmt_v1_cards )
CASE
WHEN @.last_induction = NULL THEN INSERT INTO asmt_v1_cards (asmt_1)
VALUES(@.area)
WHEN @.last_induction = 1 THEN INSERT INTO asmt_v1_cards (asmt_2)
VALUES(@.area)
WHEN @.last_induction = 2 THEN INSERT INTO asmt_v1_cards (asmt_3)
VALUES(@.area)
WHEN @.last_induction = 3 THEN INSERT INTO asmt_v1_cards (asmt_4)
VALUES(@.area)
END
Cheers,
AdamCASE is an expression, not a statement; it is not used as a
control-of-flow element. To do what you're attempting to do, you'll
need a series of IF.. ELSE statements.
Of course, just from a cursory glance at your code, it appears that
your data model needs work; what do asmt_1, asmt_2... represent? Are
they different attributes of your entity, or are they simply holders
for value?
Stu|||The data model sucks i know,...
But the request from the powers that be, is that is must be that way..
Thanks for the info..
"Mr Ideas Man" <adam@.pertrain.com.au> wrote in message
news:uoQRTyPOGHA.964@.tk2msftngp13.phx.gbl...
> Hi all,
> I am trying to create the following stored procedure..
> I am still not all that familiar with the Case statement so any help would
> be appreciated...!!!
> I am getting a syntax errors...
> Is there a better way to do this?
> CREATE PROCEDURE [dbo].[asmt_v1_ins_card_induction]
> @.area VARCHAR(120)
> AS
> BEGIN
> Declare @.last_induction INT
> SET @.last_induction = (SELECT last_induction FROM asmt_v1_cards )
> CASE
> WHEN @.last_induction = NULL THEN INSERT INTO asmt_v1_cards (asmt_1)
> VALUES(@.area)
> WHEN @.last_induction = 1 THEN INSERT INTO asmt_v1_cards (asmt_2)
> VALUES(@.area)
> WHEN @.last_induction = 2 THEN INSERT INTO asmt_v1_cards (asmt_3)
> VALUES(@.area)
> WHEN @.last_induction = 3 THEN INSERT INTO asmt_v1_cards (asmt_4)
> VALUES(@.area)
> END
> Cheers,
> Adam
>|||without nagging you about the schema and all...here is the insert without
if/else.
insert asmt_v1_cards(asmt_1,asmt_2,asmt_3,asmt_
4)
select case when @.last_induction is null then @.area end,
case when @.last_induction=1 then @.area end,
case when @.last_induction=2 then @.area end,
case when @.last_induction=3 then @.area end
-oj
"Mr Ideas Man" <adam@.pertrain.com.au> wrote in message
news:uoQRTyPOGHA.964@.tk2msftngp13.phx.gbl...
> Hi all,
> I am trying to create the following stored procedure..
> I am still not all that familiar with the Case statement so any help would
> be appreciated...!!!
> I am getting a syntax errors...
> Is there a better way to do this?
> CREATE PROCEDURE [dbo].[asmt_v1_ins_card_induction]
> @.area VARCHAR(120)
> AS
> BEGIN
> Declare @.last_induction INT
> SET @.last_induction = (SELECT last_induction FROM asmt_v1_cards )
> CASE
> WHEN @.last_induction = NULL THEN INSERT INTO asmt_v1_cards (asmt_1)
> VALUES(@.area)
> WHEN @.last_induction = 1 THEN INSERT INTO asmt_v1_cards (asmt_2)
> VALUES(@.area)
> WHEN @.last_induction = 2 THEN INSERT INTO asmt_v1_cards (asmt_3)
> VALUES(@.area)
> WHEN @.last_induction = 3 THEN INSERT INTO asmt_v1_cards (asmt_4)
> VALUES(@.area)
> END
> Cheers,
> Adam
>

CASE PROBLEM

I am using the following snippet of code:

ACCOUNT =
case left(m.gldebitacct, 6)
when '700048' then '40700-497-00'
when '011195' then '60700-470-05'
else 'error'
end,

I need to alter this code to deal with another case such as:

case left(m.glcreditacct, 6)
when '700048' then '40700-497-00'
when '011195' then '60700-470-05'
else 'error'
end,

I think it should look like the following, but when I try this I get an error:

ACCOUNT =
case left(m.gldebitacct, 6)
when '700048' then '40700-497-00'
when '011195' then '60700-470-05'
else 'error'
end,

case left(m.glcreditacct, 6)
when '700048' then '40700-497-00'
when '011195' then '60700-470-05'
else 'error'
end,

Can anyone help with this?ACCOUNT =
case
when left(m.gldebitacct, 6) = '700048' or left(m.glcreditacct, 6) = '700048' then '40700-497-00'
when left(m.gldebitacct, 6) = '011195' or left(m.glcreditacct, 6) = '011195' then '60700-470-05'
else 'error'
end|||That did the trick.

Thanks

CASE NOT equal

I have the following statement...
CASE
WHEN SRV.srv_package <> PkgtoSRV.Package_SRV
THEN 'Not Equal'
ELSE
'Equal'
END
The value for SRV.srv_package is NULL and the value for
PkgtoSRV.Package_SRV is 2006-05-05. So why does this query return...Equal?
When they are clearly NOT equal. Am I failing to use CASE incorrectly
here? Can I NOT use <>?
Any help would be GREATLY appreciated...
wnfisbaNo, nulls do not come under anything. So, usually nulls are not predictable.
add this to the first line and try
SET ANSI_NULLS OFF
and try it..
if it still doesn't work
then do this... hope this helps.
CASE
WHEN isnull(SRV.srv_package,0) <> isnull(PkgtoSRV.Package_SRV,0)
THEN 'Not Equal'
ELSE
'Equal'
END
"wnfisba" wrote:

> I have the following statement...
> CASE
> WHEN SRV.srv_package <> PkgtoSRV.Package_SRV
> THEN 'Not Equal'
> ELSE
> 'Equal'
> END
> The value for SRV.srv_package is NULL and the value for
> PkgtoSRV.Package_SRV is 2006-05-05. So why does this query return...Equal?
'
> When they are clearly NOT equal. Am I failing to use CASE incorrectly
> here? Can I NOT use <>?
> Any help would be GREATLY appreciated...
> wnfisba|||NULL is unknown. Repeating something I posted yesterday:
<snip>
Nothing will ever = NULL, since the definition of NULL is unknown.
Think about it this way, if I have a form with a field that says "gender"
and I forget to check either male or female, can you say with any certainty
that I am:
(a) male?
(b) female?
(c) not male?
(d) not female?
Further, can you say with any certainty that someone else, who also forgot
to specify their gender, is:
(a) the same gender as me?
(b) the opposite gender from me?
(c) not the same gender as me?
(d) not the opposite gender from me?
</snip>
So, in order to do this comparison, you either need to account for NULLs in
the ELSE, or use COALESCE to allow bogus values into the comparison.
CASE
WHEN SRV.srv_package <> PkgtoSRV.Package_SRV
THEN 'Not Equal'
WHEN SRV.srv_package = PkgtoSRV.Package_SRV
THEN 'Equal'
ELSE 'Unknown - one or both are NULL'
END
-- assuming -1 is not a possible value
-- though, I don't even have an idea what data type you are using
CASE WHEN COALESCE(SRV.srv_package, -1) = COALESCE(PkgtoSRV.Package_SRV, -1)
THEN 'Equal'
ELSE 'Not Equal'
END
"wnfisba" <wnfisba@.discussions.microsoft.com> wrote in message
news:E25E0D4E-0113-401A-A07C-FB7B86C7DC96@.microsoft.com...
>I have the following statement...
> CASE
> WHEN SRV.srv_package <> PkgtoSRV.Package_SRV
> THEN 'Not Equal'
> ELSE
> 'Equal'
> END
> The value for SRV.srv_package is NULL and the value for
> PkgtoSRV.Package_SRV is 2006-05-05. So why does this query
> return...Equal?
> When they are clearly NOT equal. Am I failing to use CASE incorrectly
> here? Can I NOT use <>?
> Any help would be GREATLY appreciated...
> wnfisba

Case in Where Clause

Can you put a Case statement in a Where clause?
I have the following Cursor I am setting up that is giving me the error:
Server: Msg 170, Level 15, State 1, Line 26
Line 26: Incorrect syntax near '>'.
Declare @.SearchCursor Cursor
Set @.SearchCursor = Cursor for Select
CommandText,WhereClause,SearchID,UserID
from CandidateSearches where SearchAgent=1 and SearchAgentActive=1 and
(CASE WHEN NotifyFrequency = 'D' THEN
(DATEDIFF(DAY,SearchAgentLastRun,GetDate
()) > 0)
WHEN NotifyFrequency = 'W' THEN
(DATEDIFF(DAY,SearchAgentLastRun,GetDate
()) >= 7)
WHEN NotifyFrequency = 'M' THEN
(DATEDIFF(DAY,SEarchAgentLastRun,GetDate
()) >= 30) END)
Line 26 is the line the Case Statement is on.
What is the problem with this line?
Thanks,
TomHi Tom
The most important thing to keep in mind is that there is no case STATEMENT
in Transact-SQL. There is a case EXPRESSION, which can be used anywhere you
use an expression. So, you can use a case EXPRESSION in a WHERE, in place of
a value.
Without any more details like the DDL, or a description of what you're
trying to accomplish, my guess is that you want to compare
DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) to one of several different
values, depending on the value of NotifyFrequency. If that is a correct
understanding, you might try something like this in your WHERE clause:
where SearchAgent=1 and SearchAgentActive=1 and
DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) >= CASE NotifyFrequency
WHEN 'D' THEN 0
WHEN 'W' THEN 7
WHEN 'M' THEN 30
END
Also make sure to consider the case where NotifyFrequency is not one of (D,
W, M)
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:eeyXs5cMGHA.3960@.TK2MSFTNGP09.phx.gbl...
> Can you put a Case statement in a Where clause?
> I have the following Cursor I am setting up that is giving me the error:
> Server: Msg 170, Level 15, State 1, Line 26
> Line 26: Incorrect syntax near '>'.
> Declare @.SearchCursor Cursor
> Set @.SearchCursor = Cursor for Select
> CommandText,WhereClause,SearchID,UserID
> from CandidateSearches where SearchAgent=1 and SearchAgentActive=1 and
> (CASE WHEN NotifyFrequency = 'D' THEN
> (DATEDIFF(DAY,SearchAgentLastRun,GetDate
()) > 0)
> WHEN NotifyFrequency = 'W' THEN
> (DATEDIFF(DAY,SearchAgentLastRun,GetDate
()) >= 7)
> WHEN NotifyFrequency = 'M' THEN
> (DATEDIFF(DAY,SEarchAgentLastRun,GetDate
()) >= 30) END)
> Line 26 is the line the Case Statement is on.
> What is the problem with this line?
> Thanks,
> Tom
>|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OQDXBGdMGHA.500@.TK2MSFTNGP15.phx.gbl...
> Hi Tom
> The most important thing to keep in mind is that there is no case
> STATEMENT in Transact-SQL. There is a case EXPRESSION, which can be used
> anywhere you use an expression. So, you can use a case EXPRESSION in a
> WHERE, in place of a value.
> Without any more details like the DDL, or a description of what you're
> trying to accomplish, my guess is that you want to compare
> DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) to one of several different
> values, depending on the value of NotifyFrequency. If that is a correct
> understanding, you might try something like this in your WHERE clause:
Exactly, but how would I do the instance where the case of 'D' is > 0 and
not >= 0?
Thanks,
Tom

> where SearchAgent=1 and SearchAgentActive=1 and
> DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) >= CASE NotifyFrequency
> WHEN 'D' THEN 0
> WHEN 'W' THEN 7
> WHEN 'M' THEN 30
> END
> Also make sure to consider the case where NotifyFrequency is not one of
> (D, W, M)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:eeyXs5cMGHA.3960@.TK2MSFTNGP09.phx.gbl...
>
>|||I think what you are trying to do is this
Declare @.SearchCursor Cursor
Set @.SearchCursor = Cursor for Select
CommandText,WhereClause,SearchID,UserID
from CandidateSearches where SearchAgent=1 and
SearchAgentActive=1 and
((NotifyFrequency = 'D'
and DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) > 0) OR
(NotifyFrequency = 'W'
and DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) >= 7) OR
(NotifyFrequency = 'M'
and DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) >= 30))|||<markc600@.hotmail.com> wrote in message
news:1140022428.091421.209720@.g14g2000cwa.googlegroups.com...
>I think what you are trying to do is this
> Declare @.SearchCursor Cursor
> Set @.SearchCursor = Cursor for Select
> CommandText,WhereClause,SearchID,UserID
> from CandidateSearches where SearchAgent=1 and
> SearchAgentActive=1 and
> ((NotifyFrequency = 'D'
> and DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) > 0) OR
> (NotifyFrequency = 'W'
> and DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) >= 7) OR
> (NotifyFrequency = 'M'
> and DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) >= 30))
That was exactly what I ended up doing. I was just curious as to how to it
(if you can do it) using the Case statement.
Thanks,
Tom|||>> Declare @.SearchCursor Cursor
>That was exactly what I ended up doing. I was just curious as to how to it
>(if you can do it) using the Case statement.
You can do it using CASE, but it doesn't let you do anything that AND,
OR and () don't already let you do. Since it resolves to a values,
you have to use it to set a value that indicates success or failure,
then test that in a comparison.
SELECT CommandText, WhereClause, SearchID, UserID
FROM CandidateSearches
WHERE SearchAgent=1
AND SearchAgentActive=1
AND CASE
WHEN NotifyFrequency = 'D'
AND DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) > 0
THEN 1
WHEN NotifyFrequency = 'W'
AND DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) >= 7
THEN 1
WHEN NotifyFrequency = 'M'
AND DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) >= 30
THEN 1
ELSE 0
END = 1
Roy|||"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:31l7v1dnjf97n234q7ih23mnbq4rtti9u1@.
4ax.com...
> You can do it using CASE, but it doesn't let you do anything that AND,
> OR and () don't already let you do. Since it resolves to a values,
> you have to use it to set a value that indicates success or failure,
> then test that in a comparison.
> SELECT CommandText, WhereClause, SearchID, UserID
> FROM CandidateSearches
> WHERE SearchAgent=1
> AND SearchAgentActive=1
> AND CASE
> WHEN NotifyFrequency = 'D'
> AND DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) > 0
> THEN 1
> WHEN NotifyFrequency = 'W'
> AND DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) >= 7
> THEN 1
> WHEN NotifyFrequency = 'M'
> AND DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) >= 30
> THEN 1
> ELSE 0
> END = 1
>
I was as to what this did at first, but then I realized what it was
doing.
In the "OR" example, I would only get a record if one of the 3 tests (as
well as the SearchAgent and SearchAgentActive) were matched.
In your example, you are testing if the Case statement was "1". It was
clear when I put parens around the case statement. I thought at first you
were setting "End=1". But when I put the parans in, it made sense:
... AND (CASE
WHEN NotifyFrequency = 'D'
AND DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) > 0
THEN 1
WHEN NotifyFrequency = 'W'
AND DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) >= 7
THEN 1
WHEN NotifyFrequency = 'M'
AND DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) >= 30
THEN 1
ELSE 0
END) = 1
I think that is what is happening.
But as you said the OR does the same thing and is a little more clear and
you don't have to do the extra step of setting it to 0 or 1.
Thanks,
Tom
> Roy

CASE in T-SQL

I'm upsizing Access to SQL Server 2005.
I need to convert the following query:
SELECT
tblHistory.AutoNumber,
tblHistory.InputDate,
IIf([tblHistory].[TaxType]="111" Or [tblHistory].[taxtype]="222","ABC",IIf([tblHistory].[taxtype]="AAA","AAA","BBB")) AS 1TaxType FROM tblHistory;

CASE in T SQL:
SELECT tblHistory.AutoNumber,
tblHistory.InputDate,
'1TaxType' =
CASE
WHEN [tblHistory].[TaxType] = '111' THEN 'ABC'
WHEN [tblHistory].[TaxType] = '222' THEN 'ABC'
ELSE
CASE
WHEN [tblHistory].[taxtype] = 'AAA' THEN 'AAA'
ELSE 'BBB'
END
END
FROM tblHistory;

Is this correct?

It is fine. But you can simplify it further like:

SELECT h.AutoNumber,
h.InputDate,
CASE
WHEN h.[TaxType] IN ('111', '222') THEN 'ABC'
WHEN h.[taxtype] = 'AAA' THEN 'AAA'
ELSE 'BBB'
END as [1TaxType]
FROM tblHistory as h;

I also modified the SELECT statement syntax to use table aliases which makes it easier to read and change the proprietary column alias syntax also.