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;

What are you exactly trying to do? Use CASE if it's inside the SELECT, or do a proper PL/SQL block. — Plouf, Jan 17 '13 at 19:49

score 27 · Answer 1 · edited Jan 17 '13 at 20:15

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.

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. — Frankie G, Jan 17 '13 at 20:28

score 5 · Answer 2 · edited Jan 17 '13 at 20:16

5

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

edited Jan 17 '13 at 20:16

Jared Harley

8,219
4
39
48

answered Jan 17 '13 at 20:14

Dileep

624
3
10
20

1

In general it's [best to avoid DECODE](http://stackoverflow.com/questions/13712763/are-a-case-statement-and-a-decode-equivalent) the results may not be what you expect. – Ben Jan 18 '13 at 08:58

Using IF ELSE in Oracle

2 Answers2

Linked

Related