Using IF THEN ELSE In SQL Views

I have three different database fields (from different tables) that depending upon the scenario need to be the field that is used to be picked up by reports as a selection criteria.

I need to intgegrate an IF THEN ELSE into a SQL view so that it shows this value regardless. How do i do this? The concept of the query is as follows:

IF order.custA = 'ABC' then order.custB
Else if stock.cust is null then sales.cust
Else stock.cust

In every case it is a string value. Ideally i want to be able to pass that result into Crystal as a parameter. I have tried doing this in Crystal and it kills the performance, so really need to pass this directly from SQL.

Any ideas would be appreciated.

Thanks in advance.
delkentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Melih SARICAOwnerCommented:
select case when order.custa ='ABC' then order.custb when stock.cust is null then sales.cust else stock.cust end column_name from order,sales etc
0
pssandhuCommented:
I am guessing you have all three tables joined in in a single query. In that case you can do something like this in your select statement:
 

SELECT *,
       CASE WHEN order.custA = 'ABC' THEN order.custB
            WHEN stock.cust is null THEN sales.cust
            ELSE stock.cust
       END as Customer
From  Yourtable INNER JOIN OtherTable....

Open in new window

0
shru_0409Commented:
select custA, case when order.custA = 'ABC' THEN order.custB when stock.cust is null then sales.cust when stock.cust = 'xyz' then custC ELSE abc end "order_name"
from order;


try this
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

delkentAuthor Commented:
Thanks, looks promising however it doesn't accept the alias for the Case statement - just says invalid
column name 'hocode'. This is what I have put in:

SELECT CASE WHEN order.custcode = 'WASTE' AND order.job <> 0 THEN order_job.custcode WHEN stock.job IS NULL
                      THEN cust.hocode ELSE stock_job.custcode END AS hocode, dbo.order.ordnum
FROM         dbo.order INNER JOIN
                      dbo.orderdetail ON dbo.order.orderdid = dbo.orderdetail.orderdid INNER JOIN
                      dbo.stock ON dbo.orderdetail.stockid = dbo.stock.stockid INNER JOIN
                      dbo.cust ON dbo.order.custcode = dbo.cust.custcode LEFT OUTER JOIN
                      dbo.job AS order_job ON dbo.ordhed.job = order_job.job LEFT OUTER JOIN
                      dbo.job AS stock_job ON dbo.stock.job = lot_job.job
0
pssandhuCommented:
Try it this way:

SELECT CASE WHEN [order].[custcode] = 'WASTE' AND [order].[job] <> 0 THEN [order_job].[custcode] 
			WHEN [stock].[job] IS NULL THEN [cust].[hocode] 
            ELSE [stock_job].[custcode] 
       END AS 'HOCODE', 
	   [dbo].[order].[ordnum]
FROM   [dbo].[order] INNER JOIN
                      [dbo].[orderdetail] ON [dbo].[order].[orderdid] = [dbo].[orderdetail].[orderdid] INNER JOIN
                      [dbo].[stock] ON [dbo].[orderdetail].[stockid] = [dbo].[stock].[stockid] INNER JOIN
                      [dbo].[cust] ON [dbo].[order].[custcode] = [dbo].[cust].[custcode] LEFT OUTER JOIN
                      [dbo].[job] AS order_job ON [dbo].[ordhed].[job] = [order_job].[job] LEFT OUTER JOIN
                      [dbo].[job] AS stock_job ON [dbo].[stock].[job] = [lot_job].[job]

Open in new window

0
delkentAuthor Commented:
Thanks but still says invalid column name 'hocode' on save
0
pssandhuCommented:
Can you try changing the name to see if it works with a different alias? Also, I see that you get an error when saving. You meant when running the query right?
P.
0
delkentAuthor Commented:
Have changed name and it still errors.
It occurs either when running the query or try to save the query as a view
0
pssandhuCommented:
Try running it without an alias to see if it still errors out. Between the syntax looks fine not sure whats going on. I'll try to do some testing as well.
P.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
delkentAuthor Commented:
Has now worked fine. Thank you for your help on this
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.