Using Select Case statement within another Select Case

[OutInLeftField]

Can
Select case when ....(if this is true then do ) then
select case (....do this select case)
else
select case (do this select case)
end

from .....

?????

Thanks

 

[hneal98]

Are you asking this for VB or SQL?

 

[OutInLeftField]

Many Apologies...

SQL 2000...

 

[hneal98]

Then the command would not be as you wrote it, it would be like this:

Select case when ....(if this is true then do ) then
case when (....do this select case)
else
case when (do this select case)
end

from .....


Not sure why you need this, but it should be possible.

 

[OutInLeftField]

I am trying to work around not being able to use If's and Declares to do 3 different comparisions...

Is this possible to do?

 

[JamesLean]

Bottom line: nesting CASE expressions is perfectly fine.

If you're struggling with a particular scenario then post the details and we can try and help.

--James

 

[OutInLeftField]

Ok...

Here is the code for two different procedures:

1st batch


declare @resolved int

if (Select issuetype
FROM ContactIssue
where ContactIdnt = 99999999
and IssueClearedByIdnt is null
and Active > '5') = 'INTENT TO DENY'
set @resolved = 1
else
set @resolved = 0
if @resolved = 1
select 1
else
select 0




and second

if (select count(*) from CredentialSchedule where CredentialIdnt in (
select distinct ParentCredentialIdnt from CredentialRelationship
where RelationshipTypeIdnt in (
select RelationshipTypeIdnt from RelationshipType where Description like 'deleg%')
and ChildCredentialIdnt = 999999999 and Active > '5')) > 0
begin
declare @S209 int
declare @S036 int
if exists (select ParentCredentialIdnt from CredentialRelationship where RelationShipTypeIdnt in (select RelationshipTypeIdnt from CredentialRelationship where RelationshipTypeIdnt in (select RelationshipTypeIdnt from RelationshipType where [Description] like 'controlled%') and ChildCredentialIdnt = 99999999 and Active > '5'))
set @s209 = 1
else
set @s209 = 0
if exists (select ParentCredentialIdnt from CredentialRelationship where RelationshipTypeIdnt in (
select RelationshipTypeIdnt from CredentialRelationship where RelationshipTypeIdnt in (
select RelationshipTypeIdnt from RelationshipType where Description like 'delegating%')
and ChildCredentialIdnt = 99999999 and Active > '5'))
set @s036 = 1
else
set @s036 = 0
if (@s209 = 1) and (@s036 =1)
select 1
else
select 0
end
else
begin
if exists (select ParentCredentialIdnt from CredentialRelationship where RelationshipTypeIdnt in (
select RelationshipTypeIdnt from CredentialRelationship
where RelationshipTypeIdnt in (
select RelationshipTypeIdnt from
RelationshipType where [Description] like 'controlled%')
and ChildCredentialIdnt = 99999999 and Active > '5'))
select 1
else
select 0
end

Good Luck