Case Statements

Can anyone help me with the case statements?
I have customized a new report for our application (vendor software). I want the end user to check 3 boxes to print (they can choose all or one or two).

Below is the case statement I tired in the where clause

Where
STATUS = CASE
WHEN REPORT.SEPARATED = 'y' THEN 'S'
WHEN REPORT.TERMINATED = 'y' THEN 'T'
WHEN REPORT.RETIRED ='y' THEN 'R'
END

I want the user to check one box , two or 3 . Rightnow it is working fine if any one option is checked. Please let me if there is any other way to do this.

many thanks

Quote:

Originally Posted by anishap

Can anyone help me with the case statements?
I have customized a new report for our application (vendor software). I want the end user to check 3 boxes to print (they can choose all or one or two).

Below is the case statement I tired in the where clause

Where
STATUS = CASE
WHEN REPORT.SEPARATED = 'y' THEN 'S'
WHEN REPORT.TERMINATED = 'y' THEN 'T'
WHEN REPORT.RETIRED ='y' THEN 'R'
END

I want the user to check one box , two or 3 . Rightnow it is working fine if any one option is checked. Please let me if there is any other way to do this.

many thanks

i think this would be better if you build the WHERE from your form/gui. you have might have to adjust the returned value of your checkbox. depending on your front-end tool.

something like

queryvar = queryvar + ' WHERE STATUS IN ('
for counter = numbercheckbox
if checkbox is checked
queryvar = queryvar + returnedvalueofcheckbox

queryvar = queryvar + ')'

this is a psedocode. am suggesting the technique, not the syntax. depending on your gui, you're going to have to adjust it accordingly

Case Statement in Stored Procedure ?

I am converting IIF statements from an Access Query into a Stored Procedure
and was doing ok until I got to this one which is a bit more complicated:
CASE WHEN [Field1] = 1 THEN 0 ELSE [Field2] + [Field3] -[Field4] / [Field5]
END
All fields are valid boleen fields
I have tried putting parens around the equation part but still get errors.
How can I get the data into this field? Thanks.CAST as the bit columns as INT such as CAST([Field2] AS INT)
I think that [Field2] + [Field3] -[Field4] / [Field5]
should be ([Field2] + [Field3] -[Field4]) / [Field5]
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:F23EB696-E2B4-43EA-AC88-664FB8B2221E@.microsoft.com...
>I am converting IIF statements from an Access Query into a Stored
>Procedure
> and was doing ok until I got to this one which is a bit more complicated:
> CASE WHEN [Field1] = 1 THEN 0 ELSE [Field2] + [Field3] -[Field4] /
> [Field5]
> END
> All fields are valid boleen fields
> I have tried putting parens around the equation part but still get errors.
> How can I get the data into this field? Thanks.
>|||Please explain what "All fields are valid boleen fields" means.
Also post the error message that you get.
ML|||The Fields I'm trying to perform math on are boolean fields and are already
in place in the stored procedure.
The error says:
ADO Error:Invalid operator for data type. Operator equals add, type equals
bit.
Thanks for helping.
"ML" wrote:

> Please explain what "All fields are valid boleen fields" means.
> Also post the error message that you get.
>
> ML|||>> All fields are valid Boolean fields <<
UNH' Let's get back to the basics of an RDBMS. Rows are not records;
fields are not columns; tables are not files.
Where is the DDL? SQL has no Boolean data types and good programemrs
do not use the proprietary BIT data type.
There is no CASE statement in SQL, either; there is a CASE expression.
So what "field" are you trying to assign this exprsssion to?|||First of all, bit is not boolean, and second - the error message is pretty
much self-explanatory. Why are you adding, subtracting and dividing values
that can either be 1 or 0?
E.g.:
what does this mean to you:
1 + 0 - 1 / 1
or:
1 + 1 - 0 / 1
or worse:
1 + 1 -1 / 0
Please describe what you're trying to achieve. There must be some reason...?
ML|||
"ML" wrote:

> First of all, bit is not boolean, and second - the error message is pretty
> much self-explanatory. Why are you adding, subtracting and dividing values
> that can either be 1 or 0?
> E.g.:
> what does this mean to you:
> 1 + 0 - 1 / 1...this means 0%
> 1 + 0 / 1 + 1 ...this would be 50%
> 1 + 1 / 1 + 1 ...100%
> or:
> 1 + 1 - 0 / 1
> or worse:
> 1 + 1 -1 / 0...checked for and not allowed through business rules
> Please describe what you're trying to achieve. There must be some reason..
.?
>
> ML
I needed to be able to do math on the fields like I can in MS
Access...Changing the bits to integers worked.|||> Where is the DDL? SQL has no Boolean data types and good programemrs
> do not use the proprietary BIT data type.
Good programmers make proper use of approriate dtaa types in any given
situation.
Similary, good programmers don't just follow along with the comment "all
GOTO statements or CURSORs are bad", but take the time to understand why
other programmers prefer to use better alternatives. Cursors can also be
very powerful, but only when used in an appropriate situation where the same
result cannot be equally-well achieved via set-based operations.
If you were defining a table ApplicationUsers, and needed to store (in a
SQL2000 database) a field indicating whether the user was enabled, ( a field
that could only ever have a value of Yes/No), what field would YOU use ...

> There is no CASE statement in SQL, either; there is a CASE expression.
> So what "field" are you trying to assign this exprsssion to?
Whilst I agree with you that the snippet is technically an expression, not a
statement, this value doesn't necessarily need to be assigned to a field.|||I guess if I were an SQL expert I wouldn't need to use this web site and
expose my ignorance of SQL terminology to arrogant know-it-alls who find it
easier to berate my lack of knowledge than just simply give me the answer I
was looking for like Raymond D'Anjou so kindly did. I would appreciate you
not anwering any of my posts in the future.
"--CELKO--" wrote:

> UNH' Let's get back to the basics of an RDBMS. Rows are not records;
> fields are not columns; tables are not files.
> Where is the DDL? SQL has no Boolean data types and good programemrs
> do not use the proprietary BIT data type.
> There is no CASE statement in SQL, either; there is a CASE expression.
> So what "field" are you trying to assign this exprsssion to?
>|||AkAlan,
Don't let Celko intimidate you, and as for him being a SQL expert, well,
perhaps an ANSI 92 expert and thats about as far as it goes.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:0A58D8F7-6E39-4072-9634-2082745BA421@.microsoft.com...
>I guess if I were an SQL expert I wouldn't need to use this web site and
> expose my ignorance of SQL terminology to arrogant know-it-alls who find
> it
> easier to berate my lack of knowledge than just simply give me the answer
> I
> was looking for like Raymond D'Anjou so kindly did. I would appreciate you
> not anwering any of my posts in the future.
> "--CELKO--" wrote:
>

Case Statement in a Stored Procedure

Can anyone tell me if I can use the CASE statement with multiple 'WHEN'
statements in a stored Procedure?
I'm trying to do the following:
CASE
WHEN len(RTRIM(dbo.Deficiencies_Tmp.BLDG_NBR)) > 0 AND
len(RTRIM(dbo.Deficiencies_Tmp.room_nbr)) > 0 THEN
cast(RTRIM(dbo.Deficiencies_Tmp.BLDG_NBR) + '-' +
RTRIM(dbo.Deficiencies_Tmp.room_nbr) AS char(20))
WHEN len(RTRIM(dbo.Deficiencies_Tmp.BLDG_NBR)) > 0 AND
len(RTRIM(dbo.Deficiencies_Tmp.room_nbr)) < 1 THEN cast('BLDG: ' +
RTRIM(dbo.Deficiencies_Tmp.BLDG_NBR) AS char(20))
WHEN len(RTRIM(dbo.Deficiencies_Tmp.BLDG_NBR)) < 1 AND
len(RTRIM(dbo.Deficiencies_Tmp.room_nbr)) > 0 THEN
RTRIM(dbo.Deficiencies_Tmp.room_nbr) AS char(20))
ELSE 'no room number'
END
Is there another way of doing this?
Any help would be appreciated.
Thanks>> Can anyone tell me if I can use the CASE statement with multiple 'WHEN'
statements in a stored Procedure? <<
Yes, you can do it. It is well documented in SQL Server Books Online with
examples too. Did you come across any problems using it?
--
- Anith
( Please reply to newsgroups only )|||HI Berny,
This looks fine, but it's hard to tell if there's an alternative without
knowing what the business rules are, what the tables look like, what the
data within those tables looks like, and what the expected output is
supposed to be. A case statement with multiple WHEN statements will work
fine in a stored procedure, but you might be able to accomplish the same
thing using a UNION and specific WHERE Clauses for each UNION. It might be
better, but then again, it might not be...without knowing all of the
details, who knows...
HTH
--
Regards,
Don R. Watters
Data Group Manager
PhotoWorks, Inc.
"Berny" <BlancoB at msn Dot com> wrote in message
news:OrJK7f4uDHA.2148@.TK2MSFTNGP12.phx.gbl...
> Can anyone tell me if I can use the CASE statement with multiple 'WHEN'
> statements in a stored Procedure?
> I'm trying to do the following:
> CASE
> WHEN len(RTRIM(dbo.Deficiencies_Tmp.BLDG_NBR)) > 0 AND
> len(RTRIM(dbo.Deficiencies_Tmp.room_nbr)) > 0 THEN
> cast(RTRIM(dbo.Deficiencies_Tmp.BLDG_NBR) + '-' +
> RTRIM(dbo.Deficiencies_Tmp.room_nbr) AS char(20))
> WHEN len(RTRIM(dbo.Deficiencies_Tmp.BLDG_NBR)) > 0 AND
> len(RTRIM(dbo.Deficiencies_Tmp.room_nbr)) < 1 THEN cast('BLDG: ' +
> RTRIM(dbo.Deficiencies_Tmp.BLDG_NBR) AS char(20))
> WHEN len(RTRIM(dbo.Deficiencies_Tmp.BLDG_NBR)) < 1 AND
> len(RTRIM(dbo.Deficiencies_Tmp.room_nbr)) > 0 THEN
> RTRIM(dbo.Deficiencies_Tmp.room_nbr) AS char(20))
> ELSE 'no room number'
> END
> Is there another way of doing this?
> Any help would be appreciated.
> 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

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 Statement

Hello

How would I go about using the CASE statement with the WHEN and THEN statements with a scenario like this:

======================================--
CASE PhoneNumber

WHEN 9990000000 THEN City1
WHEN 8030000000 THEN City2

ect...
==============================================

What I want to do is check only the first 3 digits - just the 999. There would be 4 different possible first 3 digits:

999, 997, 998, and 803. Each one of these first 3 digits of the 10 digit phone number identifies a city

What I would like to do is iether use a wild card - like this:

CASE PhoneNumber
WHEN LIKE (999%) THEN City1

OR try something like taking the first 3 numbers and assigning it to a variable then doing the CASE statrement like this:

TAKE PhoneNumber 3 and asign it to CHK_NUM

CASE CHK_NUM
WHEN '999' THEN 'City1'

Any ideas would be greatly appriciated

Thanks!If you know the solution, what is the problem then.|||I don't know the solution. My examples do not have the correct syntax - that's what I'm looking for. What would the correct string look like for the solution?

Thanks|||OK - Never Mind, you were right, I had the solution - it works!

Thanks

Case Sensitive Pattern Match

I am trying to do a select statement with SQL 2000 for all records
containing any number of lowercase letters. I have tried the following
2 statements and they both seem to match both lowercase and uppercase
letters.

SELECT * FROM customers WHERE name LIKE '%[a-z]%'

SELECT * FROM customers WHERE name LIKE
'%[abcdefghijklmnopqrstuvwxyz]%'

Can anybody help?(ozburger@.gmail.com) writes:
> I am trying to do a select statement with SQL 2000 for all records
> containing any number of lowercase letters. I have tried the following
> 2 statements and they both seem to match both lowercase and uppercase
> letters.
> SELECT * FROM customers WHERE name LIKE '%[a-z]%'
> SELECT * FROM customers WHERE name LIKE
> '%[abcdefghijklmnopqrstuvwxyz]%'

SELECT * FROM customers
WHERE name COLLATE Latin1_General_BIN LIKE '%[a-z]%'

You must cast to a binary collation, as in a case-sensitive collation,
a-z ranges something like aBcDC ... Zz. And in a case-insenstive collation
a-z is equivalent to A-Z.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I tried what you said, but it is still matching both lowercase and
uppercase.
e.g. it matches all 3 records below.
CITY SOFTWARE
City Software
city software
I only want to return those records that contain lowercase letters (in
this case the 2nd two records only). Any other suggestions?

Thanks Oz

Erland Sommarskog wrote:
> (ozburger@.gmail.com) writes:
> > I am trying to do a select statement with SQL 2000 for all records
> > containing any number of lowercase letters. I have tried the following
> > 2 statements and they both seem to match both lowercase and uppercase
> > letters.
> > SELECT * FROM customers WHERE name LIKE '%[a-z]%'
> > SELECT * FROM customers WHERE name LIKE
> > '%[abcdefghijklmnopqrstuvwxyz]%'
> SELECT * FROM customers
> WHERE name COLLATE Latin1_General_BIN LIKE '%[a-z]%'
> You must cast to a binary collation, as in a case-sensitive collation,
> a-z ranges something like aBcDC ... Zz. And in a case-insenstive collation
> a-z is equivalent to A-Z.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||(ozburger@.gmail.com) writes:
> I tried what you said, but it is still matching both lowercase and
> uppercase.
> e.g. it matches all 3 records below.
> CITY SOFTWARE
> City Software
> city software
> I only want to return those records that contain lowercase letters (in
> this case the 2nd two records only). Any other suggestions?

I ran this:

CREATE TABLE x (myname varchar(20) NOT NULL)
go
INSERT x(myname) VALUES ('CITY SOFTWARE')
INSERT x(myname) VALUES ('City software')
INSERT x(myname) VALUES ('city software')
go
SELECT * FROM x WHERE myname COLLATE Latin1_General_BIN LIKE '%[a-z]%'

This was the result:

myname
-------
City software
city software

(2 row(s) affected)

If you got any other result there may be other problems with your query.
Can you post it?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You are correct. I tried again this morning and it seems to work fine
now. Maybe I had a typo or something.
Anyway thanks for all of your help on this. Much appreciated.
Ozsql

CASE and INSERT statements

Hi,
I need to write a simple script which i am getting rather on how to
write. Basically, what i need to do is:
If no records exist for a particular condition i.e. TableID=3 and
TableTypeID=4, then insert a row into Tabel1.
I have tried all sorts but I think i have got the order mixed up and that’
s
why it is not working.
This is what I have tried so far:
select * ,
case
when not exists (select * from Table1 where TableID=3 and TableTypeID=14)
then insert into Table1 (TableID, TableTypeID) values (3,14)
end
from Table1
I have tried the above in various different formats but with no success.
Any help/advice much appreciated.
Thanks,
JJens
> If no records exist for a particular condition i.e. TableID=3 and
> TableTypeID=4, then insert a row into Tabel1.
IF NOT EXISTS (SELECT * FROM Table WHERE TableID=3 and TableTypeID=4)
INSERT INTO Table balblabala alaba
ELSE
Do it something else here
"JenC" <JenC@.discussions.microsoft.com> wrote in message
news:57ED03B3-B064-4793-B1AC-C5F4E891EA5E@.microsoft.com...
> Hi,
> I need to write a simple script which i am getting rather on how
> to
> write. Basically, what i need to do is:
> If no records exist for a particular condition i.e. TableID=3 and
> TableTypeID=4, then insert a row into Tabel1.
> I have tried all sorts but I think i have got the order mixed up and that
s
> why it is not working.
> This is what I have tried so far:
> select * ,
> case
> when not exists (select * from Table1 where TableID=3 and TableTypeID=14)
> then insert into Table1 (TableID, TableTypeID) values (3,14)
> end
> from Table1
> I have tried the above in various different formats but with no success.
> Any help/advice much appreciated.
> Thanks,
> J
>|||
This will never evaluate to true
-TableId can=B4t be 3 AND 14 at the same time
select * from Table1 where TableID=3D3 and TableTypeID=3D14
For an inline Query (with an OR rather than the AND which is causing
the above issue)
insert into Table1
(
TableID,
TableTypeID
)
SELECT 3,14
WHERE NOT EXISTS
(
select * from Table1 where TableID=3D3
OR TableTypeID=3D14
)=20
HTH, Jens SUessmeyer.

Case - When - Then - Else?

Thanks in advance for your help!
I have been using very basic SQL statements to return recordsets to my ASP. One that someone gave me a few months ago was the case statement. Until now, I have been using
Case
When this Then that
When day Then night
When wet Then Dry End as whatever.
This has been fine because everything has been this, day or wet. Is there a way to do a Case Else that would capture the very few exceptions that fall outside the norm?

Thanks,
LeeLee,

Why not just attach 'else' to your case expression...

case when 1=1 then 1
else 0 end

--
-oj
http://www.rac4sql.net

Originally posted by clinel
Thanks in advance for your help!
I have been using very basic SQL statements to return recordsets to my ASP. One that someone gave me a few months ago was the case statement. Until now, I have been using
Case
When this Then that
When day Then night
When wet Then Dry End as whatever.
This has been fine because everything has been this, day or wet. Is there a way to do a Case Else that would capture the very few exceptions that fall outside the norm?

Thanks,
Lee|||Thanks for not starting the reply with HI STUPID as I was unaware that it was as simple as that!
Again Thanks,
Lee|||Yep. See below...

Case
When this Then that
When day Then night
When wet Then Dry
Else Foo
End as whatever.

Originally posted by clinel
Thanks in advance for your help!
I have been using very basic SQL statements to return recordsets to my ASP. One that someone gave me a few months ago was the case statement. Until now, I have been using
Case
When this Then that
When day Then night
When wet Then Dry End as whatever.
This has been fine because everything has been this, day or wet. Is there a way to do a Case Else that would capture the very few exceptions that fall outside the norm?

Thanks,
Lee|||Sorry for the repeat post; somehow I had not seen the responses already in the browser. Very confused...

Regards,

Hugh

Originally posted by hmscott
Yep. See below...

Case
When this Then that
When day Then night
When wet Then Dry
Else Foo
End as whatever.

Capturing SQL Statements?

Hi.
I need to be able to identify which SQL Statements are the poorest
performing so that the application developers can improve them. I've setup
Profiler to trace the login names that I want, but it only captures the first
255 characters of the SQL Statement. Is there anyway to capture all of the
SQL that is being executed?
Thanks!
Susan
It can capture the entire text. My guess is that you are viewing the results
in Query Analyzer which has the default visible column length set to 255.
You should be able to change this via the options menu.
Anith
|||On Mon, 5 Feb 2007 09:01:01 -0800, Susan Cooper
<SusanCooper@.discussions.microsoft.com> wrote:
>I need to be able to identify which SQL Statements are the poorest
>performing so that the application developers can improve them. I've setup
>Profiler to trace the login names that I want, but it only captures the first
>255 characters of the SQL Statement. Is there anyway to capture all of the
>SQL that is being executed?
It's all there.
Save it to a database table and it's a text field, you can access it
with substring().
J.

Capturing SQL Statements?

Hi.
I need to be able to identify which SQL Statements are the poorest
performing so that the application developers can improve them. I've setup
Profiler to trace the login names that I want, but it only captures the first
255 characters of the SQL Statement. Is there anyway to capture all of the
SQL that is being executed?
Thanks!
SusanIt can capture the entire text. My guess is that you are viewing the results
in Query Analyzer which has the default visible column length set to 255.
You should be able to change this via the options menu.
--
Anith|||On Mon, 5 Feb 2007 09:01:01 -0800, Susan Cooper
<SusanCooper@.discussions.microsoft.com> wrote:
>I need to be able to identify which SQL Statements are the poorest
>performing so that the application developers can improve them. I've setup
>Profiler to trace the login names that I want, but it only captures the first
>255 characters of the SQL Statement. Is there anyway to capture all of the
>SQL that is being executed?
It's all there.
Save it to a database table and it's a text field, you can access it
with substring().
J.

Capturing SQL Statements?

Hi.
I need to be able to identify which SQL Statements are the poorest
performing so that the application developers can improve them. I've setup
Profiler to trace the login names that I want, but it only captures the firs
t
255 characters of the SQL Statement. Is there anyway to capture all of the
SQL that is being executed?
Thanks!
SusanIt can capture the entire text. My guess is that you are viewing the results
in Query Analyzer which has the default visible column length set to 255.
You should be able to change this via the options menu.
Anith|||On Mon, 5 Feb 2007 09:01:01 -0800, Susan Cooper
<SusanCooper@.discussions.microsoft.com> wrote:
>I need to be able to identify which SQL Statements are the poorest
>performing so that the application developers can improve them. I've setup
>Profiler to trace the login names that I want, but it only captures the fir
st
>255 characters of the SQL Statement. Is there anyway to capture all of the
>SQL that is being executed?
It's all there.
Save it to a database table and it's a text field, you can access it
with substring().
J.

Capture Current SQL Statements

There must be an adhoc command to capture these statements, like they do in
profiler. Does anyone know how?
Thanks
See if these help:
Using fn_get_sql
http://vyaskn.tripod.com/fn_get_sql.htm
Automating Server Side Tracing in SQL Server
http://vyaskn.tripod.com/server_side...sql_server.htm
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"David" <David@.discussions.microsoft.com> wrote in message
news:149937CB-151C-4E52-A001-DA4D052C86AC@.microsoft.com...
> There must be an adhoc command to capture these statements, like they do
in
> profiler. Does anyone know how?
> Thanks
|||Vyas,
Do you think I can run server side tracing SP 24/7 for auditing purposes?
Thanks.
"Narayana Vyas Kondreddi" wrote:

> See if these help:
> Using fn_get_sql
> http://vyaskn.tripod.com/fn_get_sql.htm
> Automating Server Side Tracing in SQL Server
> http://vyaskn.tripod.com/server_side...sql_server.htm
> --
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:149937CB-151C-4E52-A001-DA4D052C86AC@.microsoft.com...
> in
>
>