SQL 在 Oracle 中使用 IF ELSE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14386881/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using IF ELSE in Oracle
提问by Frankie G
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.
作为 Web 开发人员，我知道如何在多种语言中使用 IF ELSE。但是，我正在学习如何使用 TOAD for Oracle 编写报告。
My question is, how do I properly add an IF ELSE statement?
我的问题是，如何正确添加 IF ELSE 语句？
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)
（VIKKIE to ICKY 是我的主管委派给我帮助我学习的）
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;
回答by Justin Cave
IFis a PL/SQL construct. If you are executing a query, you are using SQL not PL/SQL.
IF是一个 PL/SQL 结构。如果您正在执行查询，则您使用的是 SQL 而不是 PL/SQL。
In SQL, you can use a CASEstatement in the query itself
在 SQL 中，您可以CASE在查询本身中使用语句
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 BYin 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.
由于您没有进行任何聚合，因此您不希望GROUP BY在查询中使用 a。你真的确定你需要DISTINCT吗？人们通常会在缺少连接条件时随意添加或添加它，而不是考虑是否真的需要做额外的工作来识别和删除重复项。
回答by Dileep
You can use Decodeas well:
您也可以使用Decode：
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
