在Oracle中使用IF ELSE [英] Using IF ELSE in Oracle
问题描述
作为一名Web开发人员,我知道如何以多种语言使用IF ELSE.但是,我正在学习如何使用TOAD for Oracle编写报告.
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.
我的问题是,如何正确添加IF ELSE语句?
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已由我的上级主管责成我,以帮助我学习)
(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;
推荐答案
IF
是PL/SQL构造.如果要执行查询,则说明您使用的是SQL,而不是PL/SQL.
IF
is a PL/SQL construct. If you are executing a query, you are using SQL not PL/SQL.
在SQL中,您可以在查询本身中使用CASE
语句
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
由于您没有进行任何汇总,因此您不需要查询中的GROUP BY
.您确定要使用DISTINCT
吗?人们经常随意地添加它,或者在缺少连接条件时添加它,而不是考虑是否真的有必要做额外的工作来识别和删除重复项.
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.
这篇关于在Oracle中使用IF ELSE的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!