Can this be changed to a CASE construct?

-- First, some DDL:
CREATE TABLE #TEMP (
Requirement varchar (20) NOT NULL,
ExpirationDate datetime NULL
) ON [PRIMARY]
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ1', '2006-01-23')
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ2', '2006-05-29')
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ3', '2007-05-01')
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ4', '2006-05-25')
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ5', '2006-05-26')
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ6', '2005-01-01')
INSERT INTO #TEMP (Requirement)
VALUES ('REQ7')
INSERT INTO #TEMP (Requirement)
VALUES ('REQ8')
/*
What I want to achieve is:
If there are no rows in #TEMP
OR
If any of the expiration dates are NULL
OR
If any of the expiration dates are <= TODAY
return the string 'NOT CLEARED'
ELSE return the string 'CLEARED'
The following works fine:
*/
DECLARE @.ClinicClearanceStatus varchar(12)
SET @.ClinicClearanceStatus = 'CLEARED'
IF
((SELECT COUNT(*) FROM #TEMP) = 0)
OR
((SELECT MIN(ExpirationDate) FROM #TEMP) < GETDATE())
OR
((SELECT COUNT(*) FROM #TEMP WHERE ExpirationDate IS NULL) > 0)
BEGIN
SET @.ClinicClearanceStatus = 'NOT CLEARED'
END
SELECT @.ClinicClearanceStatus AS ClinicClearanceStatus
/*
I'm just curious if the preceding logic could be changed to a CASE
construct; something like
SELECT CASE WHEN < throw the IF statement in here somehow >
THEN 'NOT CLEARED' ELSE 'CLEARED' END AS ClinicClearanceStatus
As always, thanks in advance for all your help.
Carl
*/CREATE TABLE #TEMP (
Requirement varchar (20) NOT NULL,
ExpirationDate datetime NULL
) ON [PRIMARY]
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ1', '2006-01-23')
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ2', '2006-05-29')
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ3', '2007-05-01')
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ4', '2006-05-25')
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ5', '2006-05-26')
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ6', '2005-01-01')
INSERT INTO #TEMP (Requirement)
VALUES ('REQ7')
INSERT INTO #TEMP (Requirement)
VALUES ('REQ8')
SELECT
CASE
WHEN
(
((SELECT COUNT(1) FROM #TEMP) IS NULL) OR
((SELECT MIN(ExpirationDate) FROM #TEMP) < getdate()) OR
((SELECT COUNT(1) FROM #TEMP WHERE ExpirationDate IS NULL) IS NULL )
)
THEN
'NOT CLEARED'
ELSE
'CLEARED' END as status|||Thanks for your reply, Johnny. I checked it out, and it looks like it's
not testing for NULLs correctly; i.e., when I change all of the
expiration dates in REQ1 through REQ6 to '2007-01-01' and leave the two
NULL dates intact, it returns CLEARED. I suspect it's a very minor
change but I don't know where to make it . . .
Thanks --
Carl
Johnny D wrote:
> CREATE TABLE #TEMP (
> Requirement varchar (20) NOT NULL,
> ExpirationDate datetime NULL
> ) ON [PRIMARY]
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ1', '2006-01-23')
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ2', '2006-05-29')
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ3', '2007-05-01')
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ4', '2006-05-25')
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ5', '2006-05-26')
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ6', '2005-01-01')
> INSERT INTO #TEMP (Requirement)
> VALUES ('REQ7')
> INSERT INTO #TEMP (Requirement)
> VALUES ('REQ8')
>
> SELECT
> CASE
> WHEN
> (
> ((SELECT COUNT(1) FROM #TEMP) IS NULL) OR
> ((SELECT MIN(ExpirationDate) FROM #TEMP) < getdate()) OR
> ((SELECT COUNT(1) FROM #TEMP WHERE ExpirationDate IS NULL) IS NULL )
> )
> THEN
> 'NOT CLEARED'
> ELSE
> 'CLEARED' END as status
>|||You can do it, as JohnyD pointed out, but IMO the if then logic is easier to
follow than a case statement in a select.
Of course it depends on where and how you plan on using this return value.
"Carl Imthurn" <nospam@.all.thanks> wrote in message
news:uMy7B9CgGHA.1792@.TK2MSFTNGP03.phx.gbl...
> -- First, some DDL:
> CREATE TABLE #TEMP (
> Requirement varchar (20) NOT NULL,
> ExpirationDate datetime NULL
> ) ON [PRIMARY]
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ1', '2006-01-23')
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ2', '2006-05-29')
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ3', '2007-05-01')
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ4', '2006-05-25')
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ5', '2006-05-26')
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ6', '2005-01-01')
> INSERT INTO #TEMP (Requirement)
> VALUES ('REQ7')
> INSERT INTO #TEMP (Requirement)
> VALUES ('REQ8')
> /*
> What I want to achieve is:
> If there are no rows in #TEMP
> OR
> If any of the expiration dates are NULL
> OR
> If any of the expiration dates are <= TODAY
> return the string 'NOT CLEARED'
> ELSE return the string 'CLEARED'
> The following works fine:
> */
> DECLARE @.ClinicClearanceStatus varchar(12)
> SET @.ClinicClearanceStatus = 'CLEARED'
> IF
> ((SELECT COUNT(*) FROM #TEMP) = 0)
> OR
> ((SELECT MIN(ExpirationDate) FROM #TEMP) < GETDATE())
> OR
> ((SELECT COUNT(*) FROM #TEMP WHERE ExpirationDate IS NULL) > 0)
> BEGIN
> SET @.ClinicClearanceStatus = 'NOT CLEARED'
> END
> SELECT @.ClinicClearanceStatus AS ClinicClearanceStatus
> /*
> I'm just curious if the preceding logic could be changed to a CASE
> construct; something like
> SELECT CASE WHEN < throw the IF statement in here somehow >
> THEN 'NOT CLEARED' ELSE 'CLEARED' END AS ClinicClearanceStatus
> As always, thanks in advance for all your help.
> Carl
> */|||I think it is just a typo.
Change the last "IS NULL" in the case function to " > 0"
"Carl Imthurn" <nospam@.all.thanks> wrote in message
news:e5TMdPDgGHA.2208@.TK2MSFTNGP05.phx.gbl...
> Thanks for your reply, Johnny. I checked it out, and it looks like it's
> not testing for NULLs correctly; i.e., when I change all of the
> expiration dates in REQ1 through REQ6 to '2007-01-01' and leave the two
> NULL dates intact, it returns CLEARED. I suspect it's a very minor
> change but I don't know where to make it . . .
> Thanks --
> Carl
> Johnny D wrote:|||Thanks for the clarification Jim -- that did it.
I appreciate your and Johnny's time.
Carl
Jim Underwood wrote:
> I think it is just a typo.
> Change the last "IS NULL" in the case function to " > 0"
> "Carl Imthurn" <nospam@.all.thanks> wrote in message
> news:e5TMdPDgGHA.2208@.TK2MSFTNGP05.phx.gbl...
>
>
>|||On Thu, 25 May 2006 12:15:22 -0700, Carl Imthurn wrote:
(snip)
>What I want to achieve is:
>If there are no rows in #TEMP
>OR
>If any of the expiration dates are NULL
>OR
>If any of the expiration dates are <= TODAY
>return the string 'NOT CLEARED'
>ELSE return the string 'CLEARED'
Hi Carl,
This can be greatly simplified:
IF (SELECT MIN(COALESCE(ExpirationDate, '19000101') FROM #TEMP) <
CURRENT_TIMESTAMP
SET @.ClinicClearanceStatus = 'NOT CLEARED'
ELSE
SET @.ClinicClearanceStatus = 'CLEARED'
Or, if you really want a CASE:
SET @.ClinicClearanceStatus =
(SELECT CASE WHEN MIN(COALESCE(ExpirationDate, '19000101') <
CURRENT_TIMESTAMP THEN 'NOT CLEARED' ELSE 'CLEARED' END
FROM #TEMP)
(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP|||Hugo --
First, thanks for posting back to me.
I tested your statement with COALESCE and it worked for conditions 2 and
3 ( NULL expiration dates and expiration dates < = today ) but failed on
condition 1 (empty table). In other words, if I have an empty table, the
SELECT statement returns CLEARED where it should return NOT CLEARED.
Here's some DDL if you have a chance (and desire) to pursue this further:
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'dbo.TEST')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.TEST
CREATE TABLE TEST (
Requirement varchar (20) NOT NULL,
ExpirationDate datetime NULL
) ON [PRIMARY]
-- INSERT INTO TEST (Requirement, ExpirationDate)
-- VALUES ('REQ1', '2006-05-31')
-- INSERT INTO TEST (Requirement, ExpirationDate)
-- VALUES ('REQ2', '2006-05-29')
-- INSERT INTO TEST (Requirement, ExpirationDate)
-- VALUES ('REQ3', '2007-05-01')
-- INSERT INTO TEST (Requirement, ExpirationDate)
-- VALUES ('REQ4', '2006-05-25')
-- INSERT INTO TEST (Requirement, ExpirationDate)
-- VALUES ('REQ5', '2006-05-26')
-- INSERT INTO TEST (Requirement, ExpirationDate)
-- VALUES ('REQ6', '2005-01-01')
-- INSERT INTO TEST (Requirement)
-- VALUES ('REQ7')
-- INSERT INTO TEST (Requirement)
-- VALUES ('REQ8')
DECLARE @.ClinicClearanceStatus varchar(20)
IF (SELECT MIN(COALESCE(ExpirationDate, '19000101')) FROM TEST) <
CURRENT_TIMESTAMP
SET @.ClinicClearanceStatus = 'NOT CLEARED'
ELSE
SET @.ClinicClearanceStatus = 'CLEARED'
SELECT @.ClinicClearanceStatus
Thanks for your help Hugo - I appreciate it.
Carl
Hugo Kornelis wrote:
> On Thu, 25 May 2006 12:15:22 -0700, Carl Imthurn wrote:
> (snip)
>
>
> Hi Carl,
> This can be greatly simplified:
> IF (SELECT MIN(COALESCE(ExpirationDate, '19000101') FROM #TEMP) <
> CURRENT_TIMESTAMP
> SET @.ClinicClearanceStatus = 'NOT CLEARED'
> ELSE
> SET @.ClinicClearanceStatus = 'CLEARED'
> Or, if you really want a CASE:
> SET @.ClinicClearanceStatus =
> (SELECT CASE WHEN MIN(COALESCE(ExpirationDate, '19000101') <
> CURRENT_TIMESTAMP THEN 'NOT CLEARED' ELSE 'CLEARED' END
> FROM #TEMP)
> (Untested - see www.aspfaq.com/5006 if you prefer a tested reply)
>|||Carl,
You could change the last section to this...
DECLARE @.ClinicClearanceStatus varchar(20)
IF IsNull((SELECT MIN(COALESCE(ExpirationDate, '19000101')) FROM TEST),
'') <
CURRENT_TIMESTAMP
SET @.ClinicClearanceStatus = 'NOT CLEARED'
ELSE
SET @.ClinicClearanceStatus = 'CLEARED'
SELECT @.ClinicClearanceStatus
HTH
Barry|||Barry --
That did it! Thanks much -- I appreciate your time.
Have a great day.
Carl
Barry wrote:
> Carl,
> You could change the last section to this...
>
> DECLARE @.ClinicClearanceStatus varchar(20)
> IF IsNull((SELECT MIN(COALESCE(ExpirationDate, '19000101')) FROM TEST),
> '') <
> CURRENT_TIMESTAMP
> SET @.ClinicClearanceStatus = 'NOT CLEARED'
> ELSE
> SET @.ClinicClearanceStatus = 'CLEARED'
> SELECT @.ClinicClearanceStatus
>
> HTH
> Barry
>