CODEWITHSUNDEEP
sql
user2953363
user2953363

Reputation: 11

Can a case statement have if then else and an or clause

Good Day To All I'm a newbie to SQL and want to perform the following in SQL. I've done it in Crystal but do not know if can be done in SQl. Here is how I done it in Crystal can this be done in SQL:

{@Adjust Code} and {@Adjust Code WH} are 2 variables that were checked for null values if null put the work 'Blank' in the field

Can the below code be put in the select statement :

if {RV_CLAIM_DETAILS.NET} <> 0
    then {RV_CLAIM_DETAILS.NET}

    else if ({@Adjust Code} in ["#C", "A2", "24"] or
            {@Adjust Code} = "23" and {@Adjust Code WH} = "24")
             then {RV_CLAIM_DETAILS.CONTRVAL}

Regards, DAvid

Upvotes: 0

Views: 67

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

The standard SQL way of representing this logic is to use a case statement:

(case when RV_CLAIM_DETAILS.NET <> 0
      then RV_CLAIM_DETAILS.NET
      when "@Adjust Code" in ('#C', 'A2', '24') or
           "@Adjust Code" = '23' and "@Adjust Code WH" = '24'
      then RV_CLAIM_DETAILS.CONTRVAL
 end) as myValue

This has exactly the same semantics as your statement. If the first clause matches, then that value is returned. If none match, then NULL is returned.

Some notes. First, SQL uses single quotes, not double quotes, to delimit strings. That said, some database engines support double quotes for that purpose.

Second, in general column names starting with '@' are not allowed, unless they are escaped. One way to escape names is using double quotes. That said, some database engines use square braces or back quotes for the same purpose. I also removed the curly braces, which are not needed for SQL.

Upvotes: 2

Eddie Martinez
Eddie Martinez

Reputation: 13910

Simple CASE expression: 

CASE input_expression 
     WHEN when_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END 
Searched CASE expression:
CASE
     WHEN Boolean_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END

Example:

SELECT   ProductNumber, Category =
      CASE ProductLine
         WHEN 'R' THEN 'Road'
         WHEN 'M' THEN 'Mountain'
         WHEN 'T' THEN 'Touring'
         WHEN 'S' THEN 'Other sale items'
         ELSE 'Not for sale'
      END,
   Name
FROM Production.Product
ORDER BY ProductNumber;
GO

Upvotes: 1

Related Questions