Case statement with multiple conditions

Obviously the below Case expression does not work. I have conditions on two
fields. If it is true then proceed.
CASE WHEN tbl.FieldA = 1 AND tbl.FieldB = NULL THEN
GETDATE()
ELSE
tbl.end_dt
END
Please Help,
Culammy bad,
Solution:
CASE WHEN su.deleted_flag = 1
THEN
CASE WHEN su.end_dt IS NULL THEN
CONVERT(VARCHAR(11), GETDATE() - 1, 101)
ELSE su.end_dt
END
ELSE
su.end_dt
END
"culam" wrote:

> Obviously the below Case expression does not work. I have conditions on t
wo
> fields. If it is true then proceed.
>
> CASE WHEN tbl.FieldA = 1 AND tbl.FieldB = NULL THEN
> GETDATE()
> ELSE
> tbl.end_dt
> END
> Please Help,
> Culam|||> Obviously the below Case expression does not work. I have conditions
> on two fields. If it is true then proceed.
> CASE WHEN tbl.FieldA = 1 AND tbl.FieldB = NULL THEN
> GETDATE()
> ELSE
> tbl.end_dt
> END
> Please Help,
> Culam
Apart from = NULL, this should work like you expect it to.
Lasse Vgsther Karlsen
http://usinglvkblog.blogspot.com/
mailto:lasse@.vkarlsen.no
PGP KeyID: 0x2A42A1C2

case statement with an sql query statement

a case statement in VB is ment for a string or numeric expression. if i place a sql parameter query statement it shows type mismatch. what do i do??Originally posted by ONIL
a case statement in VB is ment for a string or numeric expression. if i place a sql parameter query statement it shows type mismatch. what do i do??

can you show the statement you are trying to execute? Or a better understanding of what you are after?|||ok here's the issue
i have the following: -
1. a access table: telephone_directory (fields are first name, last name, extension_no, building_name)
2. a form with menu find and sub menus "by extension number", "by first name", "by last name". ALSO another form named PF with a text box and a listbox

3. a sql parameter query as: -
cq.SQL = "PARAMETERS something1 INTEGER; SELECT * FROM TELEPHONE_DIRECTORY" & _
" WHERE LEFT(EXTENSION_NO,1) LIKE [something1] " & _
" OR LEFT(EXTENSION_NO,2) LIKE [something1] " & _
" OR LEFT(EXTENSION_NO,3) LIKE [something1] " & _
" OR LEFT(EXTENSION_NO,4) LIKE [something1] " & _
" OR LEFT(EXTENSION_NO,5) LIKE [something1] " & _
" OR LEFT(EXTENSION_NO,6) LIKE [something1] " & _
" OR EXTENSION_NO LIKE [something1] " & _
" ORDER BY EXTENSION_No; "
4. a bas file with the parameter query wherein case 1 is for extension number case 2 is for last names wherein the sql query is : -

cq.SQL = "PARAMETERS something1 text; SELECT * FROM TELEPHONE_DIRECTORY" & _
" WHERE LEFT(LAST_NAME,1)LIKE [something1] " & _
" OR LEFT(LAST_NAME,2)LIKE [something1] " & _
" OR LEFT(LAST_NAME,3)LIKE [something1] " & _
" OR LEFT(LAST_NAME,4)LIKE [something1] " & _
" OR LEFT(LAST_NAME,5)LIKE [something1] " & _
" OR LEFT(LAST_NAME,6)LIKE [something1] " & _
" OR LAST_NAME LIKE [something1] " & _
" ORDER BY LAST_NAME; "
.... and so on

5. now how do i call for the "case" in the PF form so that the text box takes the input and listbox displays result for all types of find. I am succesful with different forms for each FIND but i want to use ONLY ONE form.|||Since it's an Access database I don't know if IIf() or Switch() would help at all.sql

CASE problems with select statement.

I'm trying to build a select statement using the CASE expression. All
I want is to build the WHERE piece of the select statement based on a
parameter value.
I want to somehow CASE the WHERE clause based on a parameter of let's
say, @.DateType
SELECT *
FROM <table>
WHERE <columnA> >= @.BeginDate AND <columnA> <= @.EndDate
SELECT *
FROM <table>
WHERE <columnB> >= @.BeginDate AND <columnB> <= @.EndDate
I know there is something simple i'm over looking.
thanks folks...
-fdAre you talking about something like this? Note this sample works in the
AdventureWorks sample database:
DECLARE @.start DATETIME;
DECLARE @.end DATETIME;
DECLARE @.dateType VARCHAR(5);
SELECT @.start = '2001-08-01',
@.end = '2001-08-15',
@.dateType = 'Ship';
SELECT *
FROM Sales.SalesOrderHeader
WHERE CASE @.dateType WHEN 'Order' THEN OrderDate
WHEN 'Due' THEN DueDate
WHEN 'Ship' THEN ShipDate
END BETWEEN @.start AND @.end;
"forest demon" <mete.hanap@.gmail.com> wrote in message
news:588a87ea-7d70-45c8-83dc-ac06df09f331@.e25g2000prg.googlegroups.com...
> I'm trying to build a select statement using the CASE expression. All
> I want is to build the WHERE piece of the select statement based on a
> parameter value.
> I want to somehow CASE the WHERE clause based on a parameter of let's
> say, @.DateType
> SELECT *
> FROM <table>
> WHERE <columnA> >= @.BeginDate AND <columnA> <= @.EndDate
> SELECT *
> FROM <table>
> WHERE <columnB> >= @.BeginDate AND <columnB> <= @.EndDate
> I know there is something simple i'm over looking.
> thanks folks...
> -fd|||On Mar 6, 10:23=A0pm, "Mike C#" <x...@.xyz.com> wrote:
> Are you talking about something like this? Note this sample works in the
> AdventureWorks sample database:
> DECLARE @.start DATETIME;
> DECLARE @.end DATETIME;
> DECLARE @.dateType VARCHAR(5);
> SELECT @.start =3D '2001-08-01',
> =A0 @.end =3D '2001-08-15',
> =A0 @.dateType =3D 'Ship';
> SELECT *
> FROM Sales.SalesOrderHeader
> WHERE CASE @.dateType WHEN 'Order' THEN OrderDate
> =A0 WHEN 'Due' THEN DueDate
> =A0 WHEN 'Ship' THEN ShipDate
> =A0 END BETWEEN @.start AND @.end;
> "forest demon" <mete.ha...@.gmail.com> wrote in message
> news:588a87ea-7d70-45c8-83dc-ac06df09f331@.e25g2000prg.googlegroups.com...
>
> > I'm trying to build a select statement using the CASE expression. All
> > I want is to build the WHERE piece of the select statement based on a
> > parameter value.
> > I want to somehow CASE the WHERE clause based on a parameter of let's
> > say, @.DateType
> > SELECT *
> > FROM <table>
> > WHERE =A0 <columnA> =A0 >=3D =A0 @.BeginDate =A0AND =A0 <columnA> =A0 <==3D @.EndDate
> > SELECT *
> > FROM <table>
> > WHERE =A0 <columnB> =A0 >=3D =A0 @.BeginDate =A0AND =A0 <columnB> =A0 <==3D @.EndDate
> > I know there is something simple i'm over looking.
> > thanks folks...
> > -fd- Hide quoted text -
> - Show quoted text -
sorry, i should have added a little more info. i use this in a stored
procedure and
i have the following thus far.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spDataReaderStoredProcedure]
(
-- Add the parameters for the stored procedure here
@.BeginDate datetime,
@.EndDate datetime,
@.DateType varchar(50)
)
AS
BEGIN
SET NOCOUNT ON;
SELECT *
FROM Permits
WHERE CASE @.dateType
WHEN 'S_DATE' THEN 'S_DATE >=3D @.BeginDate AND S_DATE <=3D @.EndDate'
WHEN 'G_DATE' THEN 'G_DATE >=3D @.BeginDate AND G_DATE <=3D @.EndDate'
END
END
the DateType comes in as one of two different date types. DateType
either comes
in as 'G_DATE' or 'S_DATE'. the columns in the table are also G_DATE
and S_DATE.
thanks for your time...|||On Mar 6, 11:01=A0pm, forest demon <mete.ha...@.gmail.com> wrote:
> On Mar 6, 10:23=A0pm, "Mike C#" <x...@.xyz.com> wrote:
>
>
> > Are you talking about something like this? Note this sample works in the=
> > AdventureWorks sample database:
> > DECLARE @.start DATETIME;
> > DECLARE @.end DATETIME;
> > DECLARE @.dateType VARCHAR(5);
> > SELECT @.start =3D '2001-08-01',
> > =A0 @.end =3D '2001-08-15',
> > =A0 @.dateType =3D 'Ship';
> > SELECT *
> > FROM Sales.SalesOrderHeader
> > WHERE CASE @.dateType WHEN 'Order' THEN OrderDate
> > =A0 WHEN 'Due' THEN DueDate
> > =A0 WHEN 'Ship' THEN ShipDate
> > =A0 END BETWEEN @.start AND @.end;
> > "forest demon" <mete.ha...@.gmail.com> wrote in message
> >news:588a87ea-7d70-45c8-83dc-ac06df09f331@.e25g2000prg.googlegroups.com...=
> > > I'm trying to build a select statement using the CASE expression. All
> > > I want is to build the WHERE piece of the select statement based on a
> > > parameter value.
> > > I want to somehow CASE the WHERE clause based on a parameter of let's
> > > say, @.DateType
> > > SELECT *
> > > FROM <table>
> > > WHERE =A0 <columnA> =A0 >=3D =A0 @.BeginDate =A0AND =A0 <columnA> =A0 <==3D @.EndDate
> > > SELECT *
> > > FROM <table>
> > > WHERE =A0 <columnB> =A0 >=3D =A0 @.BeginDate =A0AND =A0 <columnB> =A0 <==3D @.EndDate
> > > I know there is something simple i'm over looking.
> > > thanks folks...
> > > -fd- Hide quoted text -
> > - Show quoted text -
> sorry, i should have added a little more info. =A0i use this in a stored
> procedure and
> i have the following thus far.
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE [dbo].[spDataReaderStoredProcedure]
> (
> =A0 =A0 =A0 =A0 -- Add the parameters for the stored procedure here
> =A0 =A0 =A0 =A0 @.BeginDate datetime,
> =A0 =A0 =A0 =A0 @.EndDate datetime,
> =A0 =A0 =A0 =A0 @.DateType varchar(50)
> )
> AS
> BEGIN
> =A0 =A0 =A0 =A0 SET NOCOUNT ON;
> SELECT *
> FROM Permits
> WHERE CASE @.dateType
> =A0 WHEN 'S_DATE' THEN 'S_DATE >=3D @.BeginDate AND S_DATE <=3D @.EndDate'
> =A0 WHEN 'G_DATE' THEN 'G_DATE >=3D @.BeginDate AND G_DATE <=3D @.EndDate'
> END
> END
> the DateType comes in as one of two different date types. DateType
> either comes
> in as 'G_DATE' or 'S_DATE'. =A0the columns in the table are also G_DATE
> and S_DATE.
> thanks for your time...- Hide quoted text -
> - Show quoted text -
i finally figured it out...damn, i hate making silly mistakes....
thanks for your input mike...
-fd|||There is case, But there is also Between
Select * from table where DateColumn Between @.BeginDate and @.enddate
Case should not be used in a where.
Better to create a Table Variable and load from an index of Quailifing
Primary keys.
Then Join on the Table variable.
!0 fold performance increase.
-R
"forest demon" <mete.hanap@.gmail.com> wrote in message
news:588a87ea-7d70-45c8-83dc-ac06df09f331@.e25g2000prg.googlegroups.com...
> I'm trying to build a select statement using the CASE expression. All
> I want is to build the WHERE piece of the select statement based on a
> parameter value.
> I want to somehow CASE the WHERE clause based on a parameter of let's
> say, @.DateType
> SELECT *
> FROM <table>
> WHERE <columnA> >= @.BeginDate AND <columnA> <= @.EndDate
> SELECT *
> FROM <table>
> WHERE <columnB> >= @.BeginDate AND <columnB> <= @.EndDate
> I know there is something simple i'm over looking.
> thanks folks...
> -fd|||"Randy Pitkin" <RandyPitkin@.ydpages.com> wrote in message
news:eVT$m35gIHA.5088@.TK2MSFTNGP02.phx.gbl...
> There is case, But there is also Between
> Select * from table where DateColumn Between @.BeginDate and @.enddate
> Case should not be used in a where.
> Better to create a Table Variable and load from an index of Quailifing
> Primary keys.
> Then Join on the Table variable.
> !0 fold performance increase.
BETWEEN replaces the >= AND <= but doesn't do anything for the OP's question
of dynamically determining which column to use to limit rows. Whether or
not he needs a performance increase probably depends on how much data he's
querying, etc.sql

CASE problems with select statement.

I'm trying to build a select statement using the CASE expression. All
I want is to build the WHERE piece of the select statement based on a
parameter value.
I want to somehow CASE the WHERE clause based on a parameter of let's
say, @.DateType
SELECT *
FROM <table>
WHERE <columnA> >= @.BeginDate AND <columnA> <= @.EndDate
SELECT *
FROM <table>
WHERE <columnB> >= @.BeginDate AND <columnB> <= @.EndDate
I know there is something simple i'm over looking.
thanks folks...
-fd
Are you talking about something like this? Note this sample works in the
AdventureWorks sample database:
DECLARE @.start DATETIME;
DECLARE @.end DATETIME;
DECLARE @.dateType VARCHAR(5);
SELECT @.start = '2001-08-01',
@.end = '2001-08-15',
@.dateType = 'Ship';
SELECT *
FROM Sales.SalesOrderHeader
WHERE CASE @.dateType WHEN 'Order' THEN OrderDate
WHEN 'Due' THEN DueDate
WHEN 'Ship' THEN ShipDate
END BETWEEN @.start AND @.end;
"forest demon" <mete.hanap@.gmail.com> wrote in message
news:588a87ea-7d70-45c8-83dc-ac06df09f331@.e25g2000prg.googlegroups.com...
> I'm trying to build a select statement using the CASE expression. All
> I want is to build the WHERE piece of the select statement based on a
> parameter value.
> I want to somehow CASE the WHERE clause based on a parameter of let's
> say, @.DateType
> SELECT *
> FROM <table>
> WHERE <columnA> >= @.BeginDate AND <columnA> <= @.EndDate
> SELECT *
> FROM <table>
> WHERE <columnB> >= @.BeginDate AND <columnB> <= @.EndDate
> I know there is something simple i'm over looking.
> thanks folks...
> -fd
|||On Mar 6, 10:23Xpm, "Mike C#" <x...@.xyz.com> wrote:
> Are you talking about something like this? Note this sample works in the
> AdventureWorks sample database:
> DECLARE @.start DATETIME;
> DECLARE @.end DATETIME;
> DECLARE @.dateType VARCHAR(5);
> SELECT @.start = '2001-08-01',
> X @.end = '2001-08-15',
> X @.dateType = 'Ship';
> SELECT *
> FROM Sales.SalesOrderHeader
> WHERE CASE @.dateType WHEN 'Order' THEN OrderDate
> X WHEN 'Due' THEN DueDate
> X WHEN 'Ship' THEN ShipDate
> X END BETWEEN @.start AND @.end;
> "forest demon" <mete.ha...@.gmail.com> wrote in message
> news:588a87ea-7d70-45c8-83dc-ac06df09f331@.e25g2000prg.googlegroups.com...
>
>
>
>
>
> - Show quoted text -
sorry, i should have added a little more info. i use this in a stored
procedure and
i have the following thus far.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spDataReaderStoredProcedure]
(
-- Add the parameters for the stored procedure here
@.BeginDate datetime,
@.EndDate datetime,
@.DateType varchar(50)
)
AS
BEGIN
SET NOCOUNT ON;
SELECT *
FROM Permits
WHERE CASE @.dateType
WHEN 'S_DATE' THEN 'S_DATE >= @.BeginDate AND S_DATE <= @.EndDate'
WHEN 'G_DATE' THEN 'G_DATE >= @.BeginDate AND G_DATE <= @.EndDate'
END
END
the DateType comes in as one of two different date types. DateType
either comes
in as 'G_DATE' or 'S_DATE'. the columns in the table are also G_DATE
and S_DATE.
thanks for your time...
|||On Mar 6, 11:01Xpm, forest demon <mete.ha...@.gmail.com> wrote:
> On Mar 6, 10:23Xpm, "Mike C#" <x...@.xyz.com> wrote:
>
>
>
>
>
>
>
>
>
> sorry, i should have added a little more info. Xi use this in a stored
> procedure and
> i have the following thus far.
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE [dbo].[spDataReaderStoredProcedure]
> (
> X X X X -- Add the parameters for the stored procedure here
> X X X X @.BeginDate datetime,
> X X X X @.EndDate datetime,
> X X X X @.DateType varchar(50)
> )
> AS
> BEGIN
> X X X X SET NOCOUNT ON;
> SELECT *
> FROM Permits
> WHERE CASE @.dateType
> X WHEN 'S_DATE' THEN 'S_DATE >= @.BeginDate AND S_DATE <= @.EndDate'
> X WHEN 'G_DATE' THEN 'G_DATE >= @.BeginDate AND G_DATE <= @.EndDate'
> END
> END
> the DateType comes in as one of two different date types. DateType
> either comes
> in as 'G_DATE' or 'S_DATE'. Xthe columns in the table are also G_DATE
> and S_DATE.
> thanks for your time...- Hide quoted text -
> - Show quoted text -
i finally figured it out...damn, i hate making silly mistakes....
thanks for your input mike...
-fd
|||There is case, But there is also Between
Select * from table where DateColumn Between @.BeginDate and @.enddate
Case should not be used in a where.
Better to create a Table Variable and load from an index of Quailifing
Primary keys.
Then Join on the Table variable.
!0 fold performance increase.
-R
"forest demon" <mete.hanap@.gmail.com> wrote in message
news:588a87ea-7d70-45c8-83dc-ac06df09f331@.e25g2000prg.googlegroups.com...
> I'm trying to build a select statement using the CASE expression. All
> I want is to build the WHERE piece of the select statement based on a
> parameter value.
> I want to somehow CASE the WHERE clause based on a parameter of let's
> say, @.DateType
> SELECT *
> FROM <table>
> WHERE <columnA> >= @.BeginDate AND <columnA> <= @.EndDate
> SELECT *
> FROM <table>
> WHERE <columnB> >= @.BeginDate AND <columnB> <= @.EndDate
> I know there is something simple i'm over looking.
> thanks folks...
> -fd
|||"Randy Pitkin" <RandyPitkin@.ydpages.com> wrote in message
news:eVT$m35gIHA.5088@.TK2MSFTNGP02.phx.gbl...
> There is case, But there is also Between
> Select * from table where DateColumn Between @.BeginDate and @.enddate
> Case should not be used in a where.
> Better to create a Table Variable and load from an index of Quailifing
> Primary keys.
> Then Join on the Table variable.
> !0 fold performance increase.
BETWEEN replaces the >= AND <= but doesn't do anything for the OP's question
of dynamically determining which column to use to limit rows. Whether or
not he needs a performance increase probably depends on how much data he's
querying, etc.

CASE function result with result expression values (for IN keyword)

I am trying to code a WHERE xxxx IN ('aaa','bbb','ccc') requirement but it the return values for the IN keyword changes according to another column, thus the need for a CASE function.

WHERE
GROUP.GROUP_ID = 2
AND DEPT.DEPT_ID = 'D'
AND WORK_TYPE_ID IN
(
CASE DEPT_ID
WHEN 'D' THEN 'A','B','C' <- ERROR
WHEN 'F' THEN 'C','D
ELSE 'A','B','C','D'
END
)

I kept on getting errors, like

Msg 156, Level 15, State 1, Line 44
Incorrect syntax near the keyword 'WHERE'.

which leads me to assume that the CASE ... WHEN ... THEN statement does not allow mutiple values for result expression. Is there a way to get the SQL above to work or code the same logic in a different manner in just one simple SQL, and not a procedure or T-SQL script.

AND

(

(CASE DEPT_ID = 'D' AND WORK_TYPE_ID IN ('A','B','C'))

OR
(CASE DEPT_ID = 'F' AND WORK_TYPE_ID IN ('A','B','C'))

OR
(CASE DEPT_ID != 'D' AND CASE DEPT_ID != 'F' AND

WORK_TYPE_ID IN ('A','B','C'))

)

Though this could lead to bad performance :-(

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||Hi Jens,

Thanks for the reply . It works, and I agree with you that it could lead to performance degradation.

However, if the number of records involved are filtered and limited to, say under 1000 rows, it would still be managable ? Just a feeling, I know it is hard to quantify the expense of a query by just the row count alone.

Kenny

Case Expression within a Stored Proc

Is it possible? I have a request to create a stored proc that will
dynamically add a range to a WHERE clause based on a numeric value of a
comment type. If the incoming comment type request is say 10, the
where clause needs to be set to IN(10,11,12,13,14,15,16,17,18,19)OR if
a 20 is passed in the clause would read IN(20,21....)
So I was thinking that a CASE expression within the proc would be the
best way to go, but have had no luck in finding an example or any other
related information regarding CASE exp in a proc.
TIA
BillOn 8 Sep 2004 06:44:18 -0700, Bill Willyerd wrote:

>Is it possible? I have a request to create a stored proc that will
>dynamically add a range to a WHERE clause based on a numeric value of a
>comment type. If the incoming comment type request is say 10, the
>where clause needs to be set to IN(10,11,12,13,14,15,16,17,18,19)OR if
>a 20 is passed in the clause would read IN(20,21....)
>So I was thinking that a CASE expression within the proc would be the
>best way to go, but have had no luck in finding an example or any other
>related information regarding CASE exp in a proc.
>TIA
>Bill

Hi Bill,

In this case (no pun intended), I'd simply write it like this:

WHERE MyColumn BETWEEN @.parameter AND @.parameter + 9

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||On 8 Sep 2004 08:17:57 -0700, Bill Willyerd wrote:

>Sorry I didn't add that if a request for specfic comment type comes in
>like 22 we only return the type 22's.
>I do like the BETWEEN stmt though I haven't seen that before, I will
>remember that one.
>Thx, Bill

Hi Bill,

Is the request for "a specific comment type" passed in through a seperate
parameter? And the other parameter is used to get a range of comment
types?

CREATE PROC MyProc @.Specific int,
@.RangeStart int
AS
IF (@.Specific IS NULL AND @.RangeStart IS NULL)
OR (@.Specific IS NOT NULL AND @.RangeStart IS NOT NULL)
RAISERROR ('Supply exactly one of the two parameters', 16, 1)
ELSE
IF @.Specific IS NOT NULL
SELECT Column List
FROM YourTable
WHERE MyColumn = @.Specific
ELSE
SELECT Column List
FROM YourTable
WHERE MyColumn BETWEE @.RangeStart AND @.RangeStart + 9
go
(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||On 8 Sep 2004 16:28:26 -0700, Bill Willyerd wrote:

>At present it comes as a single request.
(snip)

Hi Bill,

How do you know if the request is for a specific comment type or for a
range? Surely, there has to be some way to distinguish a request for
comment type '20' (meaning just 20) from a request for comment type '20'
(meaning all values 20 through 29).

Maybe this is a good time to explain what information should be included
in newsgroup postings to maximise the chance to get a useful reply:

* Table structure, posted as DDL (CREATE TABLE statements, omitting
irrelevant columns but including all constraints);
* Sample data, posted as INSERT statements (and please verify that the
CREATE TABLE and INSERT statements you post work properly in an empty test
database!);
* Expected output, based on sample data;
* The SQL code you already got (if any), plus the results these give you
and the reason why that is wrong. If you get an error message, copy and
paste the full message;
* A short, concise description of the business problem you're trying to
solve.

Check out these sites as well:
http://www.aspfaq.com/etiquette.asp?id=5006
http://vyaskn.tripod.com/code.htm#inserts

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

case expression stor proc, need some help

ALTER PROCEDURE dbo.TEST_TOTALCALLS
(
@.varDate as varchar (255),
@.StartDate as datetime,
@.EndDate as datetime
)
AS

SELECT
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
WHEN 'Month' Then DATENAME(mm, CALLSTARTTIME)
END,
COUNT(*) as 'Total Calls'
FROM CALLMASTER
WHERE (COMMERCIALS = '1') AND (CALLSTARTTIME >= @.StartDate) AND (CALLENDTIME <= @.EndDate)

GROUP BY
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
WHEN 'Month' Then DATEPART(mm, CALLSTARTTIME), DATENAME(mm, CALLSTARTTIME) ' <--this part gave me an error, because of the comma,
END
ORDER BY
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
WHEN 'Month' Then DATEPART(mm, CALLSTARTTIME)
END

The month case is giving me an error. I think it has to do with two expressions in one line.
Anyone know how to combine that into 1 expression? or is there away to work around it?
As I would like to display the month as Name, but group and sort by number.
Thx!~Are you saying you don't get the data you want if you remove the datename part from your group by clause? You are getting the month name out by the select part of your procedure and I don't see the need to also group by it if you only want to group by number.|||well, I am trying to get data displayed in the name of the month, but not in ABC order.
i.e.

January
Feb
March

instead of ABC order,
April
December
Febuary

I did the sql before grouping them together.
And this worked,

SELECT
DATENAME(mm, CALLSTARTTIME),
COUNT(*) as 'Total Calls'
FROM CALLMASTER
WHERE (COMMERCIALS = '1') AND (CALLSTARTTIME >= @.StartDate) AND (CALLENDTIME <= @.EndDate)
GROUP BY
DATEPART(mm, CALLSTARTTIME), DATENAME(mm, CALLSTARTTIME)
ORDER BY
DATEPART(mm, CALLSTARTTIME)

Yet, the GROUP BY clause consist oftwoexpressions for it to function, (from my understanding)
and I don't know how to make that clause work in a CASE expression.

Thx in advance~|||oh yea, this doesn't work from my understanding:

SELECT
DATENAME(mm, CALLSTARTTIME),
COUNT(*) as 'Total Calls'
FROM CALLMASTER
WHERE (COMMERCIALS = '1') AND (CALLSTARTTIME >= @.StartDate) AND (CALLENDTIME <= @.EndDate)
GROUP BY
DATENAME(mm, CALLSTARTTIME)
ORDER BY
DATEPART(mm, CALLSTARTTIME)|||How about this:-

GROUP BY
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
WHEN 'Month' Then DATEPART(mm, CALLSTARTTIME)
END,
CASE @.varDate
WHEN 'Year' Then ??
WHEN 'Quarter' Then ??
WHEN 'Month' Then DATENAME(mm, CALLSTARTTIME)
END

I think it would only work if you could put something in for the Year and Quarter too (where the ?? are). Might not work at all.
The only other thing would be to perhaps use a sql if to have 2 different selects, one for month with case no longer needed and one for the other 2 using case:-

if @.varDate='Month'
begin
SELECT DATENAME(mm, CALLSTARTTIME), COUNT(*) as 'Total Calls'
FROM CALLMASTER
WHERE (COMMERCIALS = '1') AND (CALLSTARTTIME >= @.StartDate) AND (CALLENDTIME <= @.EndDate)
GROUP BY
DATEPART(mm, CALLSTARTTIME), DATENAME(mm, CALLSTARTTIME)
ORDER BY
DATEPART(mm, CALLSTARTTIME)
end
else
begin
SELECT
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
END,
COUNT(*) as 'Total Calls'
FROM CALLMASTER
WHERE (COMMERCIALS = '1') AND (CALLSTARTTIME >= @.StartDate) AND (CALLENDTIME <= @.EndDate)

GROUP BY
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
END
ORDER BY
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
END
end|||Thank you Brian. That will work.

Side Question: is there a way to name the heading by case?

e.g.
SELECT
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME) <--This will have As 'Year'
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)<--This will have As 'Quarter'
WHEN 'Month' Then DATENAME(mm, CALLSTARTTIME) <--This will have as 'Month'
END,|||Yes there is:-

SELECT
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME) <--This will have As 'Year'
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)<--This will have As 'Quarter'
WHEN 'Month' Then DATENAME(mm, CALLSTARTTIME) <--This will have as 'Month'
END as myheadingname|||SELECT
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME) <--This will have As 'Year'
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)<--This will have As 'Quarter'
WHEN 'Month' Then DATENAME(mm, CALLSTARTTIME) <--This will have as 'Month'
END as myheadingname

Can myheadingname varies by case? like can it be a parameter/variable,and it will display different headings depending on the case selected.

e.g.
When @.varDate = 'Year', the column header will be 'Year'.
When @.varDate = 'Month', the column header will be 'Month'.
When @.varDate = 'Quarter', the column header will be 'Quarter'

the above example, as myheadingname is a generalized header. It will not differ no matter what @.varDate is.|||I don't think you can but no harm in trying something like that:-

SELECT
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME) As 'Year'
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME) As 'Quarter'
WHEN 'Month' Then DATENAME(mm, CALLSTARTTIME) as 'Month'
END

You will probably get an error if it doesn't work.|||yup, got an error.|||Well other than that you can expand the if for each possibility and do without the case.

case expression plus outer join in ole db source

I'm trying to generate the data for a 2-column table, where both columns are defined as NOT NULL and the second column is a uniqueidentifier.

In SQL Server Management Studio, this works fine:

insert into table_3(column_a, column_b)

select table_1.column_a, (case when table_2.column_b is NULL then newid() else table_2.column_b end) as column_b

from table_1 left outer join table_2 on table_1.column_c = table_2.column_c

That is, column_b of the SELECT result has no NULL values, and all 35,986 rows are successfully inserted into a previously empty table_3. (If I comment out the INSERT INTO clause and project table_2.column_b instead of "(case ... end) as column_b", the SELECT result includes 380 rows with a NULL in column_b, so I know the case expression plus the outer join are working as expected.)

But when I use the SELECT query as the SQL command in an OLE DB Source component that is connected directly to the OLE DB Destination for the result table, I get this error:

There was an error with input column "column_b" (445) on input "OLE DB Destination Input" (420

The column status returned was: "The value violated the integrity constraints for the column.".

And sure enough, when I modify the result table to allow NULL in column_b, truncate it, and re-run the data flow, it inserts the exact same 380 rows with a NULL in column_b among the 35,986 rows.

So what is SSIS doing to screw up the results of the SELECT command?

Kevin,

Can you see the values of column_b when you use the 'preview' of the OLE DB source?

Did you check twice the mapping tab in your OLE DB destination to make sure nothing is missing?

Have you used a data view right before the OLE DB Destination to check the values of column_b are shown correctly?

If the answer to those 3 questions is yes, I am affraid I could not help you.

Rafael Salas

|||Could there be an issue in executing the newid() function?|||

Argh, the problem was due to operator error: somehow the source component SQL statement had gotten out of sync with the corresponding variable. How embarrassing!

But many thanks to Rafael and Phil, who got me to examine the data flow task closely enough to discover my error.

case expression in where clause and null's

Using SQL Server 2005
I have a simple table example with two columns: FirstName varchar(20) and
LastName varchar(20)
I am doing something like this in a stored procedure where @.firstname and
@.lastname are passed in and @.lastname could be null
select * from table where FirstName = @.firstname and LastName =
COALESCE(@.LastName, LastName)
I am also using "set ansi_nulls off".
The query should give names where LastName is null if @.Lastname = null but
that's not happening. Why?
John DalbergBecause COALESCE returns the first non-NULL value. If @.LastName is null,
then it won't return @.LastName, it will Return LastName (the column, not the
variable).
Essentially making your stmt:
select * from table where FirstName = @.firstname and LastName = LastName
or rather
select * from table where FirstName = @.firstname
"John Dalberg" <nospam@.nospam.sss> wrote in message
news:20060405191639.387$oV@.newsreader.com...
> Using SQL Server 2005
> I have a simple table example with two columns: FirstName varchar(20) and
> LastName varchar(20)
> I am doing something like this in a stored procedure where @.firstname and
> @.lastname are passed in and @.lastname could be null
> select * from table where FirstName = @.firstname and LastName =
> COALESCE(@.LastName, LastName)
> I am also using "set ansi_nulls off".
> The query should give names where LastName is null if @.Lastname = null but
> that's not happening. Why?
> John Dalberg|||>The query should give names where LastName is null if @.Lastname = null but
>that's not happening. Why?
It sounds like this might be confusion over how NULL works.
If table.LastName IS NULL, and @.LastName IS NULL, then
COALESCE(@.LastName, LastName) will resolve to NULL. In that situation
the test:
LastName = COALESCE(@.LastName, LastName)
resolves to:
NULL = NULL
Which comparison will never resolve to TRUE. NULL is never equal to
anything, including another NULL. Consider these comparisons:
NULL = 'banana'
NULL <> 'banana'
NULL = NULL
NULL <> NULL
None of those can ever be resolved as TRUE, because equality (or
inequality) can only result from comparing something to something.
NULL is nothing, and can not be compared at all.
Roy Harvey
Beacon Falls, CT
On 05 Apr 2006 23:06:02 GMT, nospam@.nospam.sss (John Dalberg) wrote:

>Using SQL Server 2005
>I have a simple table example with two columns: FirstName varchar(20) and
>LastName varchar(20)
>I am doing something like this in a stored procedure where @.firstname and
>@.lastname are passed in and @.lastname could be null
>select * from table where FirstName = @.firstname and LastName =
>COALESCE(@.LastName, LastName)
>I am also using "set ansi_nulls off".
>The query should give names where LastName is null if @.Lastname = null but
>that's not happening. Why?
>John Dalberg|||Roy Harvey <roy_harvey@.snet.net> wrote:
> It sounds like this might be confusion over how NULL works.
> If table.LastName IS NULL, and @.LastName IS NULL, then
> COALESCE(@.LastName, LastName) will resolve to NULL. In that situation
> the test:
> LastName = COALESCE(@.LastName, LastName)
> resolves to:
> NULL = NULL
> Which comparison will never resolve to TRUE. NULL is never equal to
> anything, including another NULL. Consider these comparisons:
> NULL = 'banana'
> NULL <> 'banana'
> NULL = NULL
> NULL <> NULL
> None of those can ever be resolved as TRUE, because equality (or
> inequality) can only result from comparing something to something.
> NULL is nothing, and can not be compared at all.
But when you have set ansi_nulls off and run:
select * from table where lastname = null, it returns rows where lastname=
null
Doesn't that statement translate to:
select * from table where null = null ?
and when you set ansi_nulls on
one needs to write it as: select * from table where lastname is null ?
John Dalberg|||take a look at this
declare @.v1 int,@.v2 int
select @.v1 = null,@.v2 = null
if @.v1 =@.v2
print 'equal'
else
print 'not equal'
go
declare @.v1 int,@.v2 int
select @.v1 = null,@.v2 = null
if @.v1 is null and @.v2 is null
print 'Both null'
else
print 'both not null'
go
set ansi_nulls off
go
declare @.v1 int,@.v2 int
select @.v1 = null,@.v2 = null
if @.v1 =@.v2
print 'equal'
else
print 'not equal'
go
set ansi_nulls on
go
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||"Paul Wehland" <PaulWe@.REMOVE-ME.Avanade.com> wrote:
> Because COALESCE returns the first non-NULL value. If @.LastName is null,
> then it won't return @.LastName, it will Return LastName (the column, not
> the variable).
> Essentially making your stmt:
> select * from table where FirstName = @.firstname and LastName =
> LastName or rather
> select * from table where FirstName = @.firstname
What does COALESCE return in this case when there's no none null values?
(not that it makes sense)
select * from table where FirstName = @.firstname and LastName =
COALESCE(@.LastName, @.LastName)
Would that translate to:
select * from table where FirstName = @.firstname and LastName = null ?
Doesn't 'set ansi_null off' make 'null =null' evaluate to true?
Anyways, I need the where clause to include the lastname if @.lastname has a
value and return null lastnames rows if @.lastname is null. I couldn't find
a way to do it in a CASE expression. I can do it in a dynamic sql.
John Dalberg
> "John Dalberg" <nospam@.nospam.sss> wrote in message
> news:20060405191639.387$oV@.newsreader.com...|||>>What does COALESCE return in this case when there's no none null values?
(not that it makes sense)
COALESCE will return the first non NULL value
Here is an example
declare @.v1 int,@.v2 int,@.v3 int,@.v4 int
select @.v1 = null,@.v2 = null,@.v3 =4,@.v4 =8
select coalesce(@.v1,@.v2,@.v3,@.v4)
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||"SQL" <denis.gobo@.gmail.com> wrote:
> take a look at this
> declare @.v1 int,@.v2 int
> select @.v1 = null,@.v2 = null
> if @.v1 =@.v2
> print 'equal'
> else
> print 'not equal'
> go
> declare @.v1 int,@.v2 int
> select @.v1 = null,@.v2 = null
> if @.v1 is null and @.v2 is null
> print 'Both null'
> else
> print 'both not null'
> go
> set ansi_nulls off
> go
> declare @.v1 int,@.v2 int
> select @.v1 = null,@.v2 = null
> if @.v1 =@.v2
> print 'equal'
> else
> print 'not equal'
> go
> set ansi_nulls on
> go
I understand the principles. That's why I included set ansi_nulls off in my
clarification.|||It is easier to communicate using DDL. I've added DDL at the end of this tex
t so we can use that
from here on.
Let me see if I can re-phrase your question:
You have some rows which has NULL on the lastname column. You want to find t
hem using ANSI_NULLS OFF
and by passing in NULL in the @.lastname parameter of your stored procedure.
So, in the query, you
have the following condition:
AND LastName = COALESCE(@.LastName, LastName)
So, if if you pass NULL for the @.lastname parameter, the condition will tran
slate to:
AND LastName = LastName
And you wonder why that will not return the rows where you have NULL in the
lastname column. Is that
correct?
If so, read in Books Online about SET ANSI_NULLS OFF. It only comments about
comparsions between a
column and NULL, not between two columns where each has NULL. I guess that t
his is how Sybase
defined it some 20 years ago, and MS has kapt this behavior. You could do a
BOL feedback and ask
them to clarify this in the 2005 BOL.
DDL:
USE tempdb
CREATE TABLE t(firstname varchar(30) not null, lastname varchar(30) null)
insert into t (firstname, lastname)
VALUES('John', 'Dalberg')
insert into t (firstname, lastname)
VALUES('Franz', NULL)
SET ANSI_NULLS OFF
GO
CREATE PROC p
@.firstname varchar(30), @.lastname varchar(30)
AS
SELECT firstname, lastname
from t
where FirstName = @.firstname
AND LastName = COALESCE(@.LastName, LastName)
GO
EXEC p 'John', 'Dalberg'
EXEC p 'John', NULL
EXEC p 'Franz', NULL
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John Dalberg" <nospam@.nospam.sss> wrote in message news:20060406121819.765$iB@.newsreader.c
om...
> Roy Harvey <roy_harvey@.snet.net> wrote:
> But when you have set ansi_nulls off and run:
> select * from table where lastname = null, it returns rows where lastname=
> null
> Doesn't that statement translate to:
> select * from table where null = null ?
> and when you set ansi_nulls on
> one needs to write it as: select * from table where lastname is null ?
> John Dalberg|||"Paul Wehland" <PaulWe@.REMOVE-ME.Avanade.com> wrote:
> Because COALESCE returns the first non-NULL value. If @.LastName is null,
> then it won't return @.LastName, it will Return LastName (the column, not
> the variable).
> Essentially making your stmt:
> select * from table where FirstName = ffirstname and LastName =
> LastName or rather
Right and that's the way it should work so when you have ansi_nulls off,
lastname = lastname will return rows where lastname is null and it will not
return these rows if set ansi_nulls on. I am missing what was wrong in my
statement.
so:
set ansi_nulls off;select * from table where FirstName = firstname and
LastName =lastname
&
select * from table
both return the same # of rows regardless whether lastname is null or not.
John Dalbergsql

Case Expression in Where Claus

I there a way to differ the filtering field through a Case expression in the Where claus?

ex:

Where
Case
When @.StuNum = ''
Then S.SSN = @.SSN
Else
S.StuNum = @.StuNum
End

And ...

The actual field to filter by differs, but I can't seem to find the right syntax to do this.

Any help is appreciated : )

The answer to your question is yes... But it is unwieldy. Try this instead:

WHERE (@.StuNum='' ANDS.SSN=@.SSN) OR (@.StuNum<>'' AND S.StuNum=@.StuNum)

|||

Hi,

I am not sure what you want... so I am sending my answer on some guess :) if it is not the thing you are looking for then please explain your problem a bit more. Anyways please find the query below.

DECLARE @.EmpIDINT

SET @.EmpID=2

SELECT*

FROM HumanResources.Employee

WHERE

(SELECTCASE

WHEN @.EmpID=1THEN

ContactID

ELSE

NationalIDNumberEND)=(SELECTCASE

WHEN @.EmpID=1THEN

1209

ELSE

253022876END)

Hope it Helps!

Bhaskar!

|||

I think you hit the nail on the head. I have my syntax listed as below now. Seems the "=" sign was being interpreted as part of the Case statement and needed to be separated out. Thanks to both of yo for your input.

Where

(Case

When (@.StuNum = ' ' or @.StuNum = '' or @.StuNum = null)

Then S.SSN

Else

S.StuNum

End) = (Case

When (@.StuNum = ' ' or @.StuNum = '' or @.StuNum = null)

Then @.SSN

Else

@.StuNum

End)

Case Expression in SQL Server 2000

I have to use the Case expression in my query, so I search arround the web and got the following:

SELECT title, price,
Budget = CASE price
WHEN price > 20.00 THEN 'Expensive'
WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN price < 10.00 THEN 'Inexpensive'
ELSE 'Unknown'
END,
FROM titles

It should run OK base on my research in the internet. But my SQL Server gave me error:

syntax error arround '>'.

I did several search and many people can use the ">" sign or "<" sign in the Case expression, but I just can't use it in my SQL Server, I can't even use any boolean expression, I can only use values.

can anyone help me out? My SQL Server Version is SQL Server 2000 Sevice Pack 4.

Thanks!

Try this:

SELECT title, price, CASE price AS Budget
WHEN > 20.00 THEN 'Expensive'
WHEN BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN < 10.00 THEN 'Inexpensive'
ELSE 'Unknown'
END,
FROM titles

|||

come out error too.

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '>'.

|||

SELECT title, price,

CASE

WHEN price > 20.00 THEN 'Expensive'
WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN price < 10.00 THEN 'Inexpensive'
ELSE 'Unknown'
END AS Budget
FROM titles

|||

The case expression has 2 formats:

Simple CASE function:

CASEinput_expression
WHENwhen_expressionTHENresult_expression
[ ...n]
[
ELSEelse_result_expression
]
END

Searched CASE function:

CASE
WHENBoolean_expression THENresult_expression
[ ...n]
[
ELSEelse_result_expression
]
END

So you're using the simple CASE function when you add 'price' column following CASE keyword. Note this will evaluateinput_expression, and then, in the order specified, evaluatesinput_expression=when_expression for each WHEN clause. That's why CASE price WHEN price>N doesn't work. For more information about CASE, you can take a look at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_5t9v.asp

|||

Great, Thanks so much for you answer! funny thing is I searched so many web sites, they all have bad Case expression..

Case / Switch function help

Starting to play around with SQL server at work and this is my question:

In the query design mode in access I can make one of the fields an expression that is driven by a built-in switch function.
i.e. Switch([CategoryName] Like "Beverage","Drink",[CategoryName] Like "Cheese","Dairy")
This results in the additional column field I created to display "Drink" for each record that has the CategoryName = "Beverage", and "Dairy" for "Cheese".

Can I do something like this in SQL server in the view designer itself, or do I need to make a user defined function and call it?

Thanks in advance for any help.Use the CASE statement in SQL Server.|||Can I do this in the view design mode or do I need to make a user defined function?
Any sample code would be really appreciated.|||CASE WHEN [CategoryName] = 'Beverage' THEN 'Drink'
WHEN [CategoryName] = 'Cheese' THEN 'Dairy'
ELSE 'Unknown'
END AS DerivedColumnName

carrying values from a subreport to a main report

How can I carry a value over from a subreport to a main report?

If I have a total in a sub report, I want to use that total in expression on the main report.

Any ideas?

You can't. You can only pass parameters from the main report to the subreport.|||IS there any way around this? some other method perhaps?|||

Here is something you can try. Create a NET assembly referenced by both reports (master and subreport). In the assembly, define a static collection to store values on per user and report basis (so users can't overwrite each other values), e.g. collection key consists of the user name (User!UserID) and report name (Globals!ReportName(. Next, try adding/changing values from the subreport and see if the master can get them. SSRS 2005 changed the order in which expressions are evaluated so I don't know what will happen but you asked for another way...

If you decide to try this, please share the results here.

carrying values from a subreport to a main report

How can I carry a value over from a subreport to a main report?

If I have a total in a sub report, I want to use that total in expression on the main report.

Any ideas?

You can't. You can only pass parameters from the main report to the subreport.|||IS there any way around this? some other method perhaps?|||

Here is something you can try. Create a NET assembly referenced by both reports (master and subreport). In the assembly, define a static collection to store values on per user and report basis (so users can't overwrite each other values), e.g. collection key consists of the user name (User!UserID) and report name (Globals!ReportName(. Next, try adding/changing values from the subreport and see if the master can get them. SSRS 2005 changed the order in which expressions are evaluated so I don't know what will happen but you asked for another way...

If you decide to try this, please share the results here.

Capture Grouping Values

I have two groupings defined in my table. I group by Owner and then by Priority. I'm struggling with the expression to capture the string values at the appropriate scope. Using the table below, I want to capture "Critical, High" for "Jack Daniels" and "Medium, Low, Informational" for "Jim Beam". The table has "grpOwner" and "grpPriority" defined on Fields!Owner.Value and Fields!Priority.Value respectively. Any help would be greatly appreciated!

Owner

Priority

Jack Daniels

Critical

High

Jim Beam

Medium

Low

Informational

You should be able to concatenate the values in code as explained here.|||I opened a ticket with MSFT and they recommended creating a custom assembly with a function that retrieves the values based on the grouping level.