oraclesql中带查询的cast和case语句

camsedfj 于 2021-08-13 发布在 Java

关注(0)|答案(3)|浏览(495)

我目前正在编辑一个视图，并尝试将字段“doctorate”转换为yes、no或unknown，基于一个case语句，该语句由两个表连接，然后连接到视图。
在from子句“expecting”或“as”附近有一个错误，但添加这些字符时仍然会出错。感谢您的帮助！谢谢
请看下面我的代码摘录：

CAST (
            CASE 
                WHEN D.DEGREE_HIERARCHY >= 40 
                    THEN 'Yes'
                WHEN D.DEGREE_HIERARCHY < 40 
                    THEN 'No'
                WHEN D.DEGREE_HIERARCHY IS NULL
                    THEN 'Unknown'
                ELSE
                    NULL
            END
        FROM DEGREE_CROSSWALK D, VPAA V
            WHERE
                V.FACULTY_DEGREE_CODE = D.DEGREE_CODE
                   AND V.PERSON_SKEY = SRC.PERSON_SKEY
                   AND V.CURRENT_DEGREE = '1'
        AS VARCHAR2 (50))
                DOCTORATE,

sql casting oracle case

来源：https://stackoverflow.com/questions/62069592/cast-and-case-statement-with-query-in-oracle-sql

3条答案

按热度按时间

ovfsdjhp1#

我不知道你为什么在里面使用整个查询 CAST ，但如果您使用 SELECT 以及 () 如下：（参见代码中的内联注解）

SELECT CAST (
            (SELECT CASE -- added ( and SELECT here
                WHEN D.DEGREE_HIERARCHY >= 40 
                    THEN 'Yes'
                WHEN D.DEGREE_HIERARCHY < 40 
                    THEN 'No'
                WHEN D.DEGREE_HIERARCHY IS NULL
                    THEN 'Unknown'
                ELSE
                    NULL
            END
        FROM DEGREE_CROSSWALK D, VPAA V
            WHERE
                V.FACULTY_DEGREE_CODE = D.DEGREE_CODE
                   AND V.PERSON_SKEY = SRC.PERSON_SKEY
                   AND V.CURRENT_DEGREE = '1') -- added ending bracket ) here
        AS VARCHAR2 (50)) FROM DUAL

展开查看全部

赞(0）回复(0）举报 2021-08-13

vfhzx4xs2#

我相信你需要这个：

select CASE WHEN D.DEGREE_HIERARCHY >= 40 THEN 
                 cast('Yes' as varchar(20))
            WHEN D.DEGREE_HIERARCHY < 40 THEN 
                 cast('No' as varchar(20))
            WHEN D.DEGREE_HIERARCHY IS NULL THEN 
                 cast('Unknown' as varchar(20))
            ELSE
                 NULL
       END name_of_column -- Here the case clause ends and you can name the column
FROM DEGREE_CROSSWALK D --add other tables and join condition here
WHERE --add other conditions of the where clause

另外，我建议您不要这样连接表：

FROM DEGREE_CROSSWALK D, VPAA V
WHERE V.FACULTY_DEGREE_CODE = D.DEGREE_CODE

但就像这样（只是一个例子）：

FROM DEGREE_CROSSWALK D
JOIN VPAA V ON V.FACULTY_DEGREE_CODE = D.DEGREE_CODE

展开查看全部

赞(0）回复(0）举报 2021-08-13

syqv5f0l3#

首先将cast函数强制转换为数据类型，或者删除该强制转换。

SELECT CAST((SELECT CASE WHEN D.DEGREE_HIERARCHY >= 40 
                         THEN 'Yes'
                         WHEN D.DEGREE_HIERARCHY < 40 
                         THEN 'No'
                         WHEN D.DEGREE_HIERARCHY IS NULL
                         THEN 'Unknown'
                         ELSE NULL
                         END
             FROM DEGREE_CROSSWALK D, VPAA V
             WHERE V.FACULTY_DEGREE_CODE = D.DEGREE_CODE
             AND V.PERSON_SKEY = SRC.PERSON_SKEY
             AND V.CURRENT_DEGREE = '1') AS VARCHAR2 (50)) AS DOCTORATE
FROM DUAL

赞(0）回复(0）举报 2021-08-13

我来回答

oraclesql中带查询的cast和case语句

3条答案

相关问题

热门标签

最新问答