Menu
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;
232
Syntax (IF-THEN-ELSIF-ELSE) The syntax for IF-THEN-ELSIF-ELSE in Oracle/PLSQL is: IF condition1 THEN {... statements to execute when condition1 is TRUE...}
It is always legal in PL/SQL programming to nest the IF-ELSE statements, which means you can use one IF or ELSE IF statement inside another IF or ELSE IF statement(s).
PL/SQL IF THEN ELSE statement If the condition evaluates to TRUE, then the statements between THEN and ELSE execute. In case the condition evaluates to FALSE or NULL, the else_statements between ELSE and END IF executes.
The IF-THEN-ELSIF statement allows you to choose between several alternatives. An IF-THEN statement can be followed by an optional ELSIF...ELSE statement. The ELSIF clause lets you add additional conditions.
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.
172
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
