Subqueries in Case statement

30 Sep 2005 9:59 PM

Terri

Can I use a subquery in a case statement.

Current code.

CASE SECURITY.SEC_TYP_CD
WHEN 'MUNI'
THEN do action A here
WHEN 'CB'
THEN do action A here
WHEN 'CONB'
THEN do action A here
ELSE
do action B here
END

Rather than checking every security type I'd rather use a subquery and see
if the security type exists in a particular SELECT statement. Is this
possible?

30 Sep 2005 10:26 PM

Jerry Spivey

Terri,

A sample for pubs:

USE PUBS
GO

SELECT (CASE WHEN EXISTS (SELECT * FROM AUTHORS WHERE AU_ID =
'172-32-1176')THEN 1 ELSE 0 END)

HTH

Jerry
Show quote

"Terri" <te***@cybernets.com> wrote in message
news:dhkdng$cqn$1@reader2.nmix.net...
> Can I use a subquery in a case statement.
>
> Current code.
>
> CASE SECURITY.SEC_TYP_CD
> WHEN 'MUNI'
> THEN do action A here
> WHEN 'CB'
> THEN do action A here
> WHEN 'CONB'
> THEN do action A here
> ELSE
> do action B here
> END
>
> Rather than checking every security type I'd rather use a subquery and see
> if the security type exists in a particular SELECT statement. Is this
> possible?
>
>

30 Sep 2005 10:48 PM

Hugo Kornelis

On Fri, 30 Sep 2005 15:59:08 -0600, Terri wrote:

Show quote

>Can I use a subquery in a case statement.
>
>Current code.
>
>CASE SECURITY.SEC_TYP_CD
>WHEN 'MUNI'
>THEN do action A here
>WHEN 'CB'
>THEN do action A here
>WHEN 'CONB'
>THEN do action A here
>ELSE
>do action B here
>END
>
>Rather than checking every security type I'd rather use a subquery and see
>if the security type exists in a particular SELECT statement. Is this
>possible?
>

Hi Terri,

CASE is an expression, not a statement. You can't use it for
control-of-flow. That's what IF and ELSE are for.

The following would be about equivalent to your code above

DECLARE @SEC_TYP_ID varhcar(10)
SET @SEC_TYP_ID = (SELECT SECURITY.SEC_TYP_ID
FROM ...
WHERE ...)
IF @SEC_TYP_CD IN ('MUNI', 'CB', 'CONB')
BEGIN
do action A here
END
ELSE
BEGIN
do action B here
END

Best, Hugo

--

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

30 Sep 2005 11:10 PM

Jerry Spivey

Terri,

I think it really depends on what you are trying to do....change the data
returned...then CASE is great. Perform some ACTION then use IF as Hugo has
suggested.

As a test to change the data returned:

CREATE TABLE SECURITY
(SID INT NOT NULL,
SEC_TYP_CD VARCHAR(5))

INSERT SECURITY
VALUES (1, 'MUNI')
INSERT SECURITY
VALUES (2, 'CB')
INSERT SECURITY
VALUES (3, 'CONB')
INSERT SECURITY
VALUES (4, 'NA')

SELECT CASE SECURITY.SEC_TYP_CD
WHEN 'MUNI' THEN 'DESCRIPTION 1'
WHEN 'CB' THEN 'DESCRIPTION 2'
WHEN 'CONB' THEN 'DESCRIPTION 3'
ELSE
'DESCRIPTION 4' END AS 'RESULT'
FROM SECURITY

HTH

Jerry
Show quote

"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:81grj1hl36vhrj7cp0qr042nitsimpuap1@4ax.com...
> On Fri, 30 Sep 2005 15:59:08 -0600, Terri wrote:
>
>>Can I use a subquery in a case statement.
>>
>>Current code.
>>
>>CASE SECURITY.SEC_TYP_CD
>>WHEN 'MUNI'
>>THEN do action A here
>>WHEN 'CB'
>>THEN do action A here
>>WHEN 'CONB'
>>THEN do action A here
>>ELSE
>>do action B here
>>END
>>
>>Rather than checking every security type I'd rather use a subquery and see
>>if the security type exists in a particular SELECT statement. Is this
>>possible?
>>
>
> Hi Terri,
>
> CASE is an expression, not a statement. You can't use it for
> control-of-flow. That's what IF and ELSE are for.
>
> The following would be about equivalent to your code above
>
> DECLARE @SEC_TYP_ID varhcar(10)
> SET @SEC_TYP_ID = (SELECT SECURITY.SEC_TYP_ID
> FROM ...
> WHERE ...)
> IF @SEC_TYP_CD IN ('MUNI', 'CB', 'CONB')
> BEGIN
> do action A here
> END
> ELSE
> BEGIN
> do action B here
> END
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)

1 Oct 2005 1:13 PM

David Portas

Yes you can use subqueries in a CASE expression...

> Rather than checking every security type I'd rather use a subquery and see
> if the security type exists in a particular SELECT statement.

.... but I would use a join to do that. Typically I think a join would be
more efficient than a subquery in this context.

--
David Portas
SQL Server MVP
--

Show quote

"Terri" <te***@cybernets.com> wrote in message
news:dhkdng$cqn$1@reader2.nmix.net...
> Can I use a subquery in a case statement.
>
> Current code.
>
> CASE SECURITY.SEC_TYP_CD
> WHEN 'MUNI'
> THEN do action A here
> WHEN 'CB'
> THEN do action A here
> WHEN 'CONB'
> THEN do action A here
> ELSE
> do action B here
> END
>
> Rather than checking every security type I'd rather use a subquery and see
> if the security type exists in a particular SELECT statement. Is this
> possible?
>
>