Solved

Using IF THEN ELSE In SQL Views

Posted on 2009-07-06
10
239 Views
Last Modified: 2012-05-07
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.
0
Comment
Question by:delkent
10 Comments
 
LVL 19

Expert Comment

by:Melih SARICA
ID: 24791794
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
 
LVL 17

Expert Comment

by:pssandhu
ID: 24791821
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
 
LVL 14

Expert Comment

by:shru_0409
ID: 24791945
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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 

Author Comment

by:delkent
ID: 24792018
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
 
LVL 17

Expert Comment

by:pssandhu
ID: 24792046
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
 

Author Comment

by:delkent
ID: 24792076
Thanks but still says invalid column name 'hocode' on save
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24792093
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
 

Author Comment

by:delkent
ID: 24792099
Have changed name and it still errors.
It occurs either when running the query or try to save the query as a view
0
 
LVL 17

Accepted Solution

by:
pssandhu earned 500 total points
ID: 24792128
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
 

Author Closing Comment

by:delkent
ID: 31600482
Has now worked fine. Thank you for your help on this
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS SQL Update query with connected table data 3 62
sql server query 12 32
SQL parsing XML works but want to do it another way 4 21
Error in sql query statment. 21 47
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question