If else statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

If else statement

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-09-21 : 09:48:47

This statement works until I get to the else section. If you are in 'S41' then the else statement runs as well and shows all of the S41.

So both of these are running:

if @doc in ('V01', 'S67', 'S41', 'S66')
Begin
select * from pendingdds
where doc in ('V01', 'S67', 'S41', 'S66')
End

else

Select FO,
DOC,
Clm,
COS,
ddsrcpt,
FileDate = max(FileDate),
Age = Datediff(day,max(filedate), getdate()),
Title = case when min(Title) <> max(Title) then 'Concurr' else min(Title) end
From pendingdds
where doc =@doc
Group By FO, DOC, CLM, COS, ddsrcpt

Here's the entire query:


@doc varchar(3)

as


if @doc = 'ALL' 
begin
Select FO,
	DOC, 
    Clm,
	COS,
ddsrcpt,
	FileDate = max(FileDate),
	Age = Datediff(day,max(filedate), getdate()),
	Title = case when min(Title) <> max(Title) then 'Concurr' else min(Title) end
From pendingdds
where doc in ('S09', 'S0B','S0L','S11', 'S23', 'S41', 'S66','S67','S10','S53','S92','S93','S55','S91')
Group By FO, DOC, CLM, COS, ddsrcpt
end

if @doc in ('V01', 'S67', 'S41', 'S66')
Begin 
select * from pendingdds
where doc in ('V01', 'S67', 'S41', 'S66')
End

 if @doc in ('S88', 'S53', 'S10', 'S92', 'S93','S0L')
Begin
select * from pendingdds
where doc in ('S88', 'S53', 'S10', 'S92', 'S93','S0L')
end 


 if @doc in ('V12', 'S55', 'S91')
Begin
select * from pendingdds
where doc in ('V12', 'S55', 'S91')
end 

else


Select  FO, 
	DOC, 
        Clm,
	COS,
        ddsrcpt,
	FileDate = max(FileDate),
	Age = Datediff(day,max(filedate), getdate()),
	Title = case when min(Title) <> max(Title) then 'Concurr' else min(Title) end
From pendingdds
where doc =@doc
Group By FO, DOC, CLM, COS, ddsrcpt

How do I get it to only run the first statement? If it's too confusing to understand I will send some table info.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-21 : 10:21:54

better to show how you populate @docno

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rajarajan
Starting Member

48 Posts

Posted - 2011-09-21 : 10:31:39

ITS JUST SAMPLE
LET ME KNOW YOUR THOUGHTS

declare @doc varchar (80)
set @doc ='s41'

if @doc in ('V01', 'S67', 'S41', 'S66')
Begin
select Getdate()
End

else
begin
SELECT CONVERT(VARCHAR(10),GETDATE() ,101)
END

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-09-21 : 11:45:53

when you run all of the below you will see how S41 produces two results. I only want it to come out in the if statement not the else.

Here's the table and insert info:



CREATE TABLE [dbo].[PDTest](
	[fo] [varchar](3) NOT NULL,
	[doc] [varchar](4) NOT NULL,
	[clms] [char](6) NOT NULL,
	[cos] [char](6) NOT NULL,
	[FileDate] [char](30) NULL,
	[DDSRcpt] [char](30) NULL,
	[Title] [varchar](3) NOT NULL)



insert into PDTest
select '007', 'S67', '193568', '193568', '09/02/2010', '09/02/2010', 'T16' union all
select '008', 'S67', '569842', '569842', '10/27/2010', '09/14/2010', 'T2' union all
select '031', 'S66', '166789', '166789', '12/30/2010', '12/21/2010', 'T2' union all
select '111', 'S67', '597326', '597326', '08/05/2011', '12/30/2010', 'T2' union all
select '111', 'S67', '120569', '120569', '05/03/2011', '12/06/2010', 'T16' union all
select '172', 'S66', '199546', '199546', '02/09/2011','11/05/2010', 'T16' union all
select '172', 'S66', '175896', '175896', '07/14/2011', '01/05/2011', 'T2'  union all
select '194', 'S66', '055896', '055896', '03/10/2011', '01/04/2011', 'T2'  union all
select '203', 'S41', '046895', '046895', '09/02/2011', '10/01/2010', 'T16' union all
select '203', 'S41', '178569', '178569', '07/13/2011', '08/05/2010', 'T2'  union all
select '203', 'S41', '199325', '199325', '10/05/2010', '08/10/2010', 'T16' union all
select '204', 'S41', '019865', '019865', '05/18/2011', '12/15/2010', 'T2'  union all
select '208', 'S41', '184569', '184569', '01/05/2011', '01/03/2011', 'T2'  union all
select '208', 'S41', '206458', '206458', '08/24/2011', '08/22/2010', 'T16' union all
select '220', 'S41', '164566', '164566', '01/03/2011', '12/29/2010', 'T16'

Then run this query and use S41 so you will see what I mean:


create procedure ddstest1 --'S41'


@doc varchar(3)

as


if @doc = 'ALL' 
begin
Select FO,
	DOC, 
    Clms,
	COS,
ddsrcpt,
	FileDate = max(FileDate),
	Age = Datediff(day,max(filedate), getdate()),
	Title = case when min(Title) <> max(Title) then 'Concurr' else min(Title) end
From pdtest
where doc in ('S09', 'S0B','S0L','S11', 'S23', 'S41', 'S66','S67','S10','S53','S92','S93','S55','S91')
Group By FO, DOC, CLMs, COS, ddsrcpt
end


if @doc in ('V01', 'S67', 'S41', 'S66')
Begin 
Select FO, 
	DOC, 
    Clms,
	COS,
	ddsrcpt,
	FileDate = max(FileDate),
	Age = Datediff(day,max(filedate), getdate()),
	Title = case when min(Title) <> max(Title) then 'Concurr' else min(Title) end
from pdtest
where doc in ('V01', 'S67', 'S41', 'S66')
Group By FO, DOC, CLMs, COS, ddsrcpt
End

 if @doc in ('S88', 'S53', 'S10', 'S92', 'S93','S0L')
Begin
Select FO, 
	DOC, 
    Clms,
	COS,
	ddsrcpt,
	FileDate = max(FileDate),
	Age = Datediff(day,max(filedate), getdate()),
	Title = case when min(Title) <> max(Title) then 'Concurr' else min(Title) end
from pdtest
where doc in ('S88', 'S53', 'S10', 'S92', 'S93','S0L')
Group By FO, DOC, CLMs, COS, ddsrcpt
end 


 if @doc in ('V12', 'S55', 'S91')
Begin
Select FO, 
	DOC, 
    Clms,
	COS,
	ddsrcpt,
	FileDate = max(FileDate),
	Age = Datediff(day,max(filedate), getdate()),
	Title = case when min(Title) <> max(Title) then 'Concurr' else min(Title) end
from pdtest
where doc in ('V12', 'S55', 'S91')
Group By FO, DOC, CLMs, COS, ddsrcpt
end 

else


Select FO, 
	DOC, 
    Clms,
	COS,
	ddsrcpt,
	FileDate = max(FileDate),
	Age = Datediff(day,max(filedate), getdate()),
	Title = case when min(Title) <> max(Title) then 'Concurr' else min(Title) end
From pdtest
where doc =@doc
Group By FO, DOC, CLMs, COS, ddsrcpt

Thanks!

Rajarajan that wouldn't work because I have several other numbers that would go in the else statement.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-21 : 12:21:40

One option would be to convert the [ELSE] statement to an [IF] statement with a NOT IN filter which comprises all the previous conditions.

HTH.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-21 : 12:22:57

the reason is simple. you've add them as individual ifs so else is coupled with last if and since that if evaluates to false its getting executed causing second resultset. just do small modification as below and see


create  procedure ddstest1 --'S41'


@doc varchar(3)

as


if @doc = 'ALL' 
begin
Select FO,
	DOC, 
    Clms,
	COS,
ddsrcpt,
	FileDate = max(FileDate),
	Age = Datediff(day,max(filedate), getdate()),
	Title = case when min(Title) <> max(Title) then 'Concurr' else min(Title) end
From pdtest
where doc in ('S09', 'S0B','S0L','S11', 'S23', 'S41', 'S66','S67','S10','S53','S92','S93','S55','S91')
Group By FO, DOC, CLMs, COS, ddsrcpt
end


else  if @doc in ('V01', 'S67', 'S41', 'S66')
Begin 
Select FO, 
	DOC, 
    Clms,
	COS,
	ddsrcpt,
	FileDate = max(FileDate),
	Age = Datediff(day,max(filedate), getdate()),
	Title = case when min(Title) <> max(Title) then 'Concurr' else min(Title) end
from pdtest
where doc in ('V01', 'S67', 'S41', 'S66')
Group By FO, DOC, CLMs, COS, ddsrcpt
End

else if @doc in ('S88', 'S53', 'S10', 'S92', 'S93','S0L')
Begin
Select FO, 
	DOC, 
    Clms,
	COS,
	ddsrcpt,
	FileDate = max(FileDate),
	Age = Datediff(day,max(filedate), getdate()),
	Title = case when min(Title) <> max(Title) then 'Concurr' else min(Title) end
from pdtest
where doc in ('S88', 'S53', 'S10', 'S92', 'S93','S0L')
Group By FO, DOC, CLMs, COS, ddsrcpt
end 


else if @doc in ('V12', 'S55', 'S91')
Begin
Select FO, 
	DOC, 
    Clms,
	COS,
	ddsrcpt,
	FileDate = max(FileDate),
	Age = Datediff(day,max(filedate), getdate()),
	Title = case when min(Title) <> max(Title) then 'Concurr' else min(Title) end
from pdtest
where doc in ('V12', 'S55', 'S91')
Group By FO, DOC, CLMs, COS, ddsrcpt
end 

else


Select FO, 
	DOC, 
    Clms,
	COS,
	ddsrcpt,
	FileDate = max(FileDate),
	Age = Datediff(day,max(filedate), getdate()),
	Title = case when min(Title) <> max(Title) then 'Concurr' else min(Title) end
From pdtest
where doc =@doc
Group By FO, DOC, CLMs, COS, ddsrcpt

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-09-21 : 13:35:48

Wow thanks so much!!! So putting the else in front of the if makes them all on statement. Thanks for explaning too!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-21 : 13:38:31

yep..that will do the trick
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-09-21 : 14:19:52

Thanks again!

I'm back...thought I was done huh

Concurr isn't working for S67

Add these four records to the existing table:

insert into PDTest
select '007', 'S67', '193444', '193444', '09/02/2010', '09/02/2010', 'T16' union all
select '007', 'S67', '193444', '193444', '08/18/2010', '09/02/2010', 'T2' union all
select 'C85', 'S66', '094689', '094689', '01/07/2011', '01/07/2011', 'T16' union all
select 'C85', 'S66', '094689', '094689', '12/13/2010', '12/15/2010', 'T2'

Then run this stored procedure and go down to the bottom of the table and look how C85 Title should be Concurr.

If you run S67 (as the doc instead of S57) you will see it comes up as concurr



alter procedure ddstest1 --'S67'


@doc varchar(3)

as


if @doc = 'ALL' 
begin
Select FO,
	DOC, 
    Clms,
	COS,
ddsrcpt,
	FileDate = max(FileDate),
	Age = Datediff(day,max(filedate), getdate()),
	Title = case when min(Title) <> max(Title) then 'Concurr' else min(Title) end
From pdtest
where doc in ('S09', 'S0B','S0L','S11', 'S23', 'S41', 'S66','S67','S10','S53','S92','S93','S55','S91')
Group By FO, DOC, CLMs, COS, ddsrcpt
end


else if @doc in ('V01', 'S67', 'S41', 'S66')
Begin 
Select FO, 
	DOC, 
    Clms,
	COS,
	ddsrcpt,
	FileDate = max(FileDate),
	Age = Datediff(day,max(filedate), getdate()),
	Title = case when min(Title) <> max(Title) then 'Concurr' else min(Title) end
from pdtest
where doc in ('V01', 'S67', 'S41', 'S66')
Group By FO, DOC, CLMs, COS, ddsrcpt
End

else if @doc in ('S88', 'S53', 'S10', 'S92', 'S93','S0L')
Begin
Select FO, 
	DOC, 
    Clms,
	COS,
	ddsrcpt,
	FileDate = max(FileDate),
	Age = Datediff(day,max(filedate), getdate()),
	Title = case when min(Title) <> max(Title) then 'Concurr' else min(Title) end
from pdtest
where doc in ('S88', 'S53', 'S10', 'S92', 'S93','S0L')
Group By FO, DOC, CLMs, COS, ddsrcpt
end 


else if @doc in ('V12', 'S55', 'S91')
Begin
Select FO, 
	DOC, 
    Clms,
	COS,
	ddsrcpt,
	FileDate = max(FileDate),
	Age = Datediff(day,max(filedate), getdate()),
	Title = case when min(Title) <> max(Title) then 'Concurr' else min(Title) end
from pdtest
where doc in ('V12', 'S55', 'S91')
Group By FO, DOC, CLMs, COS, ddsrcpt
end 

else


Select FO, 
	DOC, 
    Clms,
	COS,
	ddsrcpt,
	FileDate = max(FileDate),
	Age = Datediff(day,max(filedate), getdate()),
	Title = case when min(Title) <> max(Title) then 'Concurr' else min(Title) end
From pdtest
where doc =@doc
Group By FO, DOC, CLMs, COS, ddsrcpt

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-21 : 22:25:12

C85 Title wont be Concurr as its max(title) and min(title) are same. Thats because you've included ddsrcpt also in grouping and its values are different. use modified code below and see the change


alter procedure ddstest1 --'S67'


@doc varchar(3)

as


if @doc = 'ALL' 
begin
Select FO,
	DOC, 
    Clms,
	COS,
ddsrcpt,
	FileDate = max(FileDate),
	Age = Datediff(day,max(filedate), getdate()),
	Title = case when min(Title) <> max(Title) then 'Concurr' else min(Title) end
From pdtest
where doc in ('S09', 'S0B','S0L','S11', 'S23', 'S41', 'S66','S67','S10','S53','S92','S93','S55','S91')
Group By FO, DOC, CLMs, COS, ddsrcpt
end


else if @doc in ('V01', 'S67', 'S41', 'S66')
Begin 
Select FO, 
	DOC, 
    Clms,
	COS,
	-- ddsrcpt,
	FileDate = max(FileDate),
	Age = Datediff(day,max(filedate), getdate()),min(Title),max(Title),
	Title = case when min(Title) <> max(Title) then 'Concurr' else min(Title) end
from pdtest
where doc in ('V01', 'S67', 'S41', 'S66')
Group By FO, DOC, CLMs, COS--, ddsrcpt
End

else if @doc in ('S88', 'S53', 'S10', 'S92', 'S93','S0L')
Begin
Select FO, 
	DOC, 
    Clms,
	COS,
	ddsrcpt,
	FileDate = max(FileDate),
	Age = Datediff(day,max(filedate), getdate()),
	Title = case when min(Title) <> max(Title) then 'Concurr' else min(Title) end
from pdtest
where doc in ('S88', 'S53', 'S10', 'S92', 'S93','S0L')
Group By FO, DOC, CLMs, COS, ddsrcpt
end 


else if @doc in ('V12', 'S55', 'S91')
Begin
Select FO, 
	DOC, 
    Clms,
	COS,
	ddsrcpt,
	FileDate = max(FileDate),
	Age = Datediff(day,max(filedate), getdate()),
	Title = case when min(Title) <> max(Title) then 'Concurr' else min(Title) end
from pdtest
where doc in ('V12', 'S55', 'S91')
Group By FO, DOC, CLMs, COS, ddsrcpt
end 

else


Select FO, 
	DOC, 
    Clms,
	COS,
	ddsrcpt,
	FileDate = max(FileDate),
	Age = Datediff(day,max(filedate), getdate()),
	Title = case when min(Title) <> max(Title) then 'Concurr' else min(Title) end
From pdtest
where doc =@doc
Group By FO, DOC, CLMs, COS, ddsrcpt

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-09-22 : 08:21:36

Thanks for your reply I tried it but I need that column. Is there another way? If not it's just one record and I will keep it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-22 : 11:49:03

whats the requirment by the way? it clearly has two different titles so should be concurr. why should you want it be considered as concurr then?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-09-22 : 13:29:25

if the title is T16 and T2 and they are both have the same clms number then they should be called concurr.

Look at clm - 193444 it comes out as Concurr

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-22 : 13:36:02

thats because as suggested earlier their ddsrcpt are same and here its different. so including it in group splits it up. or if you just want to return one of ddsrcpt values in random and make it single record you can do this.


.....
else if @doc in ('V01', 'S67', 'S41', 'S66')
Begin 
Select FO, 
	DOC, 
    Clms,
	COS,
	MIN(ddsrcpt) AS ddsrcpt,
	FileDate = max(FileDate),
	Age = Datediff(day,max(filedate), getdate()),min(Title),max(Title),
	Title = case when min(Title) <> max(Title) then 'Concurr' else min(Title) end
from pdtest
where doc in ('V01', 'S67', 'S41', 'S66')
Group By FO, DOC, CLMs, COS
End
....

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-09-22 : 14:21:07

Okay thanks I like that. I put it in the other if statements so I wouldn't have that problem again. Thanks again!!!!

So when I want distinct records don't include the field I want distinct records in the group by clause?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-22 : 22:22:34

depends on value in field. if value in field is not same for all in group they will come as multiple records

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources