WHERE statements based on IF-THEN-ELSE conditions

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

WHERE statements based on IF-THEN-ELSE conditions

Author

Topic

bharath.ak
Starting Member

3 Posts

Posted - 2011-05-20 : 10:42:34

Hello,

Please help me out with this scenario:

I have 2 tables - One is GROUP and another is LIBUSER.
GROUP contains AdGroup and Library variables;
LIBUSER contains Library and AdUser variables.

Sample data for GROUP is:
-------------------
AdGroup Library
-------------------
ABC Sales
ABC Cost
ABC Price
HNK Sales
HNK Cost
HNK Price
USA Sales
USA Cost
USA Price
IND Sales
IND Cost
IND Price
-------------------

Sample data for LIBUSER is:
--------------------
Library AdUser
--------------------
Sales Bharath
Sales Ajay
Sales Vijay
Cost Sekhar
Cost Ajay
Cost Ram
Cost Arjun
Price ALL
--------------------

Here, I need to write a query such that
If AdUser = 'ALL' in the LIBUSER table, then run one WHERE statement in my SQL Select statement.
if AdUser NOT = 'ALL' in the LIBUSER table, then run another WHERE statement in my SQL select statement.

Which means I have only one SELECT statement but based on the valus for AdUser in LIBUSER table, I need SQL to run that specific WHERE condition.

Please help me how to go about it. Any help would be greatly appreciated.

Thanks,
Bharath.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-05-20 : 13:30:18

I'm not following what you want to do so, can you tell us what you want for output based on your sample data?

This link might also help you prepare your DDL, DML and question:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

bharath.ak
Starting Member

3 Posts

Posted - 2011-05-20 : 13:42:52

If this helps,

1. If the LIBUSER.AdUser = 'ALL', then I should create the TEMP table as

create table temp as 
select distinct a.adgroup, d.aduser 
from group a, libuser d 
where a.library=d.library

2. If the LIBUSER.AdUser NOT = 'ALL', then I should create the TEMP table as

create table temp as 
select distinct a.adgroup, d.aduser 
from group a, libuser d 
where a.library=d.library and userlogin = d.libuser

Here we get userlogin value from some other source (can be manageable) And these should be accomplished in a single select statement.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-05-20 : 13:47:36

I dont know why you want this to be incorporated in Where Statement. Its a condition and you can use proper IF ELSE statement like

If @LIBUSERAdUser = 'ALL'
BEgin

select distinct a.adgroup, d.aduser
from group a, libuser d
where a.library=d.library

End

Else IF @LIBUSERAdUser <> 'ALL'
BEGIN
select distinct a.adgroup, d.aduser
from group a, libuser d
where a.library=d.library and userlogin = d.libuser
END

Cheers
MIK

bharath.ak
Starting Member

3 Posts

Posted - 2011-05-20 : 13:51:26

I would have done that if I am writing this code in SAS editor...

I have to put this SAS Code in a C Sharp program used for web interface. And It should be written in a single select statement in a C Sharp program...

latch
Yak Posting Veteran

62 Posts

Posted - 2011-05-20 : 14:21:41

Try something like these:

declare @var varchar(50),@var1 varchar(50),@var2 varchar(60)
Declare @SQL VarChar(1000)
set @var1='Ajay'
set @var2='Arjun'
select @var=case AdUser when 'ALL' then @var1
else @var2
end
from Libuser
Select @SQL = 'SELECT * FROM Libuser '
select @SQL=@SQL+'where AdUser = ''' + @var + ''''
exec (@SQL)

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-05-20 : 16:02:20

Why do you want to bind the SQL code in C-sharp? Any specific reason.

It is not considered to be a wise approach to bind the SQL code in the application code. Its better if you crate a stored procedure for this code and then just call that stored procedure from your application code. This will do the same as binding the SQL code in application code.

Cheers
MIK

kazi
Starting Member

8 Posts

Posted - 2011-05-21 : 03:06:31

You can get this as follows

"
select distinct a.adgroup, b.aduser
from adgroup a, libuser b
where a.library=b.library and b.aduser in
(select distinct(case userlogin when 'All' then aduser else userlogin end) from libuser) and b.aduser<> 'All'
"

Kazi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources