Case Statement With Bigger than?

Hi

How can i create a case statement with a bigger and smaller than sign in it. I keep on getting an error.

Here is the piece of code i'm working on and simply enough, the idea of what i am trying to accomplish.

Code Snippet

SELECT Weight.Weight,

Height.Height,

(Weight.Weight/(Height.Height*Height.Height)) AS BMI,

CASE BMI

WHEN (BMI < 18) THEN 'Under Weight'

WHEN (BMI < 25) THEN 'Healthy Weight'

END AS 'BMI Grouping'

Any Help will be greatly appreciated

Kind Regards

Carel Greaves

Carel:

It looks to me like you have it correct; the only thing you might want to do is add an additonal line after your WHEN statements -- something like

Code Snippet

ELSE 'Over Weight'

Oh the problem is that you cannot reference this as 'BMI'; you need to write out the long version like:

Code Snippet

SELECT Weight.Weight,

Height.Height,

(Weight.Weight/(Height.Height*Height.Height)) AS BMI,

CASE BMI

WHEN ((Weight.Weight/(Height.Height*Height.Height)) < 18) THEN 'Under Weight'

WHEN ((Weight.Weight/(Height.Height*Height.Height)) < 25) THEN 'Healthy Weight'

END AS 'BMI Grouping'

Transact SQL does not have the ability to alias BMI as in your original statement as (I think) Oracle does.

|||

The "case" function has two formats. You can not use both at the same time.

case -- BMI

when BMI < 18 then 'Under Weight'

when BMI < 25 then 'Healthy Weight'

else NULL

end as 'BMI Grouping'

AMB

|||

Carel,

Expanding upon Alejandro's response, CASE works like this:

CASE {ItemToTest}

WHEN {Value} THEN [AlternativeValue}

END

or

CASE

WHEN {ItemToTest} equality {Values} THEN {AlternativeValue}

END

The second alternative has quite a bit of flexibility. Each WHEN can test different {ItemToTest}, and

{AlternativeValue} can return a different datatype that {ItemToTest}.

|||

And you are not allowed to use the Column Aliase name as expression of other column in the same select list..

You have to use the following query,

Code Snippet

SELECT Weight.Weight,

Height.Height,

(Weight.Weight/(Height.Height*Height.Height)) AS BMI,

CASE

WHEN ((Weight.Weight/(Height.Height*Height.Height)) < 18) THEN 'Under Weight'

WHEN ((Weight.Weight/(Height.Height*Height.Height)) < 25) THEN 'Healthy Weight'

END AS 'BMI Grouping'

|||Thanks, yet again i find all the answers that i'm looking for

Thanks Guys

Kind Regards

Carel Greaves

|||

You are welcome; it is always a pleasure working with you, Carel.

Case Statement With Bigger than?

Hi

How can i create a case statement with a bigger and smaller than sign in it. I keep on getting an error.

Here is the piece of code i'm working on and simply enough, the idea of what i am trying to accomplish.

Code Snippet

SELECT Weight.Weight,

Height.Height,

(Weight.Weight/(Height.Height*Height.Height)) AS BMI,

CASE BMI

WHEN (BMI < 18) THEN 'Under Weight'

WHEN (BMI < 25) THEN 'Healthy Weight'

END AS 'BMI Grouping'

Any Help will be greatly appreciated

Kind Regards

Carel Greaves

Carel:

It looks to me like you have it correct; the only thing you might want to do is add an additonal line after your WHEN statements -- something like

Code Snippet

ELSE 'Over Weight'

Oh the problem is that you cannot reference this as 'BMI'; you need to write out the long version like:

Code Snippet

SELECT Weight.Weight,

Height.Height,

(Weight.Weight/(Height.Height*Height.Height)) AS BMI,

CASE BMI

WHEN ((Weight.Weight/(Height.Height*Height.Height)) < 18) THEN 'Under Weight'

WHEN ((Weight.Weight/(Height.Height*Height.Height)) < 25) THEN 'Healthy Weight'

END AS 'BMI Grouping'

Transact SQL does not have the ability to alias BMI as in your original statement as (I think) Oracle does.

|||

The "case" function has two formats. You can not use both at the same time.

case -- BMI

when BMI < 18 then 'Under Weight'

when BMI < 25 then 'Healthy Weight'

else NULL

end as 'BMI Grouping'

AMB

|||

Carel,

Expanding upon Alejandro's response, CASE works like this:

CASE {ItemToTest}

WHEN {Value} THEN [AlternativeValue}

END

or

CASE

WHEN {ItemToTest} equality {Values} THEN {AlternativeValue}

END

The second alternative has quite a bit of flexibility. Each WHEN can test different {ItemToTest}, and

{AlternativeValue} can return a different datatype that {ItemToTest}.

|||

And you are not allowed to use the Column Aliase name as expression of other column in the same select list..

You have to use the following query,

Code Snippet

SELECT Weight.Weight,

Height.Height,

(Weight.Weight/(Height.Height*Height.Height)) AS BMI,

CASE

WHEN ((Weight.Weight/(Height.Height*Height.Height)) < 18) THEN 'Under Weight'

WHEN ((Weight.Weight/(Height.Height*Height.Height)) < 25) THEN 'Healthy Weight'

END AS 'BMI Grouping'

|||Thanks, yet again i find all the answers that i'm looking for

Thanks Guys

Kind Regards

Carel Greaves

|||

You are welcome; it is always a pleasure working with you, Carel.

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.