我有一个视图,其中包含一列的强制转换语句。此强制转换语句包含一个案例语句。此声明有效。此语句的结果为 1、2 或 3。
从这里开始,我需要使用上一个 case 语句的结果(我使用了 WITH 语句,但它不起作用(来确定列的值。一个简单的 case 语句,将 yes、no 或 null 分配给上述语句的值(1、2 或 3(
任何帮助不胜感激。谢谢。
使用伪代码的示例:
CAST (
WITH case_output
AS(
SELECT
CASE
WHEN EXISTS
(select from table where blah blah)
THEN
(select column from that table)
ELSE
(select from some another table)
END
)
CASE
WHEN case_output = 1
THEN 'Yes'
WHEN case_output = 2
THEN 'No'
else
NULL
AS VARCHAR2 (10))
column_name,
.... [rest of query]
混淆了WITH
子句的查询名称和列名。例如,它是
WITH my_query AS (SELECT c1 AS my_column FROM t1)
SELECT my_column FROM my_query;
其次,在 Oracle 的 SQL 中,你总是需要一个FROM
子句。使用虚拟表DUAL
作为替身:
SELECT CASE WHEN ... THEN END AS my_column
FROM DUAL;
最小工作示例:
CREATE TABLE t1 (c1 INT);
CREATE TABLE t2 (c2 INT);
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (2);
WITH case_query AS (
SELECT CASE WHEN EXISTS (SELECT * FROM t1 WHERE c1=100)
THEN (SELECT c1 FROM t1)
ELSE (SELECT c2 FROM t2)
END AS case_output
FROM dual)
SELECT CASE case_output
WHEN 1 THEN 'Yes'
WHEN 2 THEN 'No'
ELSE NULL
END second_case_output
FROM case_query;