在同一查询中使用case语句的Oracle子查询

Question

我在Oracle中有一个case语句;然后我需要一个子查询在同一个查询中使用case语句的结果。我怎样才能做到这一点？

下面是该查询的板块和子查询：

CL.TC_DEFECT_1, 
CASE 
    WHEN CL.TC_DEFECT_1_SELECTION = 10000000 THEN 1 
    WHEN CL.TC_DEFECT_1_SELECTION = 01000000 THEN 2 
    WHEN CL.TC_DEFECT_1_SELECTION = 00100000 THEN 3 
    WHEN CL.TC_DEFECT_1_SELECTION = 00010000 THEN 4 
    WHEN CL.TC_DEFECT_1_SELECTION = 00001000 THEN 5 
    WHEN CL.TC_DEFECT_1_SELECTION = 00000100 THEN 6 
    WHEN CL.TC_DEFECT_1_SELECTION = 00000010 THEN 7 
    WHEN CL.TC_DEFECT_1_SELECTION = 00000001 THEN 8 
END AS "DEFECT_1_SORT_ORDER", 

(SELECT TC_DESCRIPTION_FR FROM TBYE60_DEFECT_CODES DC 
WHERE DC.TC_CODE_DEF = CL.TC_DEFECT_1 
AND DC.TC_SORT_ORDER = DEFECT_1_SORT_ORDER 
AND rownum=1) AS SUB_DEFECT_1_DESC, 

Answer 1

我不知道甲骨文，但你可以看到横向JOIN。 你可以做这样的事情：

  CL.TC_DEFECT_1, 
       "DEFECT_1_SORT_ORDER", 

        (SELECT TC_DESCRIPTION_FR FROM TBYE60_DEFECT_CODES DC WHERE DC.TC_CODE_DEF = CL.TC_DEFECT_1 AND DC.TC_SORT_ORDER = CL.DEFECT_1_SORT_ORDER AND rownum=1) AS SUB_DEFECT_1_DESC 
    ... 
    FROM 
    ... 
    CROSS JOIN TABLE(
    SELECT    CASE 
        WHEN CL.TC_DEFECT_1_SELECTION = 10000000 THEN 1 
        WHEN CL.TC_DEFECT_1_SELECTION = 01000000 THEN 2 
        WHEN CL.TC_DEFECT_1_SELECTION = 00100000 THEN 3 
        WHEN CL.TC_DEFECT_1_SELECTION = 00010000 THEN 4 
        WHEN CL.TC_DEFECT_1_SELECTION = 00001000 THEN 5 
        WHEN CL.TC_DEFECT_1_SELECTION = 00000100 THEN 6 
        WHEN CL.TC_DEFECT_1_SELECTION = 00000010 THEN 7 
        WHEN CL.TC_DEFECT_1_SELECTION = 00000001 THEN 8 
       END AS "DEFECT_1_SORT_ORDER" 
from dual 

    ) a 

OR

  CL.TC_DEFECT_1, 
       "DEFECT_1_SORT_ORDER", 

        (SELECT TC_DESCRIPTION_FR FROM TBYE60_DEFECT_CODES DC WHERE DC.TC_CODE_DEF = CL.TC_DEFECT_1 AND DC.TC_SORT_ORDER = CL.DEFECT_1_SORT_ORDER AND rownum=1) AS SUB_DEFECT_1_DESC 
    ... 
    FROM 
    ... 
    LATERAL (
    SELECT    CASE 
        WHEN CL.TC_DEFECT_1_SELECTION = 10000000 THEN 1 
        WHEN CL.TC_DEFECT_1_SELECTION = 01000000 THEN 2 
        WHEN CL.TC_DEFECT_1_SELECTION = 00100000 THEN 3 
        WHEN CL.TC_DEFECT_1_SELECTION = 00010000 THEN 4 
        WHEN CL.TC_DEFECT_1_SELECTION = 00001000 THEN 5 
        WHEN CL.TC_DEFECT_1_SELECTION = 00000100 THEN 6 
        WHEN CL.TC_DEFECT_1_SELECTION = 00000010 THEN 7 
        WHEN CL.TC_DEFECT_1_SELECTION = 00000001 THEN 8 
       END AS "DEFECT_1_SORT_ORDER" 
from dual 

    ) a 

Answer 2

因为我看你可以很容易地使用，而不是这种情况下，声明INSTR功能我不正是你想要的理解只是先来看看：

CASE 
    WHEN CL.TC_DEFECT_1_SELECTION = 10000000 THEN 1 
    WHEN CL.TC_DEFECT_1_SELECTION = 01000000 THEN 2 
    WHEN CL.TC_DEFECT_1_SELECTION = 00100000 THEN 3 
    WHEN CL.TC_DEFECT_1_SELECTION = 00010000 THEN 4 
    WHEN CL.TC_DEFECT_1_SELECTION = 00001000 THEN 5 
    WHEN CL.TC_DEFECT_1_SELECTION = 00000100 THEN 6 
    WHEN CL.TC_DEFECT_1_SELECTION = 00000010 THEN 7 
    WHEN CL.TC_DEFECT_1_SELECTION = 00000001 THEN 8 
END AS "DEFECT_1_SORT_ORDER" 

可以模拟为：

INSTR(CL.TC_DEFECT_1_SELECTION,'1') as AS "DEFECT_1_SORT_ORDER" 
--you need also to check if CL.TC_DEFECT_1_SELECTION in (above collection) 

希望这会有用。

Answer 3

只需使用子查询。在子查询定义DEFECT_1_SORT_ORDER，然后用它在外部查询：

select . . ., DEFECT_1_SORT_ORDER, 
     (SELECT TC_DESCRIPTION_FR 
     FROM TBYE60_DEFECT_CODES DC 
     WHERE DC.TC_CODE_DEF = CL.TC_DEFECT_1 AND 
       DC.TC_SORT_ORDER = CL.DEFECT_1_SORT_ORDER AND rownum=1 
     ) AS SUB_DEFECT_1_DESC 
from (select . . ., 
      (CASE 
        WHEN CL.TC_DEFECT_1_SELECTION = 10000000 THEN 1 
        WHEN CL.TC_DEFECT_1_SELECTION = 01000000 THEN 2 
        WHEN CL.TC_DEFECT_1_SELECTION = 00100000 THEN 3 
        WHEN CL.TC_DEFECT_1_SELECTION = 00010000 THEN 4 
        WHEN CL.TC_DEFECT_1_SELECTION = 00001000 THEN 5 
        WHEN CL.TC_DEFECT_1_SELECTION = 00000100 THEN 6 
        WHEN CL.TC_DEFECT_1_SELECTION = 00000010 THEN 7 
        WHEN CL.TC_DEFECT_1_SELECTION = 00000001 THEN 8 
       END) AS "DEFECT_1_SORT_ORDER" 
     from . . . 

Answer 4

你可以使用clausole WITH， 在那里你可以有你的SUB_DEFECT_1_DESC， 然后用在那里你会使用SUB_DEFECT_1_DESC的结果为例。