Using IF ELSE in Oracle

Question

As a web developer, I know how to use the IF ELSE in multiple languages. However, I am learning how to write reports using TOAD for Oracle.

My question is, how do I properly add an IF ELSE statement?

This is what I am attempting to do. The error is: Command not Properly Ended.

(VIKKIE to ICKY has been tasked to me by my supervisor to help me learn)

SELECT DISTINCT a.item, b.salesman, NVL(a.manufacturer,'Not Set')Manufacturer

FROM inv_items a, arv_sales b
WHERE   a.co = '100'
      AND a.co = b.co
      AND A.ITEM_KEY = b.item_key   
--AND item IN ('BX4C', 'BX8C', 'BX866') --AND salesman ='15'
AND a.item LIKE 'BX%'
AND b.salesman in ('01','15')
AND trans_date BETWEEN to_date('010113','mmddrr')
                         and to_date('011713','mmddrr')


GROUP BY a.item, b.salesman, a.manufacturer
ORDER BY a.item

IF  b.salesman = 'VIKKIE' THEN
a.salesman := 'ICKY';
END IF; 

Yelena

What are you exactly trying to do? Use CASE if it's inside the SELECT, or do a proper PL/SQL block. – Pandolfi

Answer 1

IF is a PL/SQL construct. If you are executing a query, you are using SQL not PL/SQL.

In SQL, you can use a CASE statement in the query itself

SELECT DISTINCT a.item, 
                (CASE WHEN b.salesman = 'VIKKIE'
                      THEN 'ICKY'
                      ELSE b.salesman
                  END), 
                NVL(a.manufacturer,'Not Set') Manufacturer
  FROM inv_items a, 
       arv_sales b
 WHERE  a.co = '100'
   AND a.co = b.co
   AND A.ITEM_KEY = b.item_key   
   AND a.item LIKE 'BX%'
   AND b.salesman in ('01','15')
   AND trans_date BETWEEN to_date('010113','mmddrr')
                      and to_date('011713','mmddrr')
ORDER BY a.item

Since you aren't doing any aggregation, you don't want a GROUP BY in your query. Are you really sure that you need the DISTINCT? People often throw that in haphazardly or add it when they are missing a join condition rather than considering whether it is really necessary to do the extra work to identify and remove duplicates.

Flamen

Thank you for taking the time to not only show a solution, but explaining why I was wrong. Although I found a mistake (on my part of the original code) I was able to fix it and use your example. The mistake was: I used b.salesman instead of a.manufacturer. – Yelena

Answer 2

You can use Decode as well:

SELECT DISTINCT a.item, decode(b.salesman,'VIKKIE','ICKY',Else),NVL(a.manufacturer,'Not Set')Manufacturer
FROM inv_items a, arv_sales b
WHERE a.co = b.co
      AND A.ITEM_KEY = b.item_key
      AND a.co = '100'
AND a.item LIKE 'BX%'
AND b.salesman in ('01','15')
AND trans_date BETWEEN to_date('010113','mmddrr')
                         and to_date('011713','mmddrr')
GROUP BY a.item, b.salesman, a.manufacturer
ORDER BY a.item

Haga

In general it's best to avoid DECODE the results may not be what you expect. – Belie