where 절의 계산된 필드 사용

where 절의 계산된 필드 사용

계산된 필드를 사용할 수 있는 방법이 있습니까?where조항?

저는 다음과 같은 것을 하고 싶습니다.

SELECT a, b, a+b as TOTAL FROM (
   select 7 as a, 8 as b FROM DUAL
   UNION ALL
   select 8 as a, 8 as b FROM DUAL
   UNION ALL
   select 0 as a, 0 as b FROM DUAL
)
WHERE TOTAL <> 0
;

하지만 이해합니다.ORA-00904: "TOTAL": invalid identifier.

그래서 나는 사용해야 합니다.

SELECT a, b, a+b as TOTAL FROM (
   select 7 as a, 8 as b FROM DUAL
   UNION ALL
   select 8 as a, 8 as b FROM DUAL
   UNION ALL
   select 0 as a, 0 as b FROM DUAL
)
WHERE a+b <> 0
;

논리적으로,select절은 평가된 쿼리의 마지막 부분 중 하나이므로 별칭 및 파생 열을 사용할 수 없습니다. (단,order by논리적으로 마지막에 발생합니다.)

파생 테이블을 사용하는 방법은 다음과 같습니다.

select * 
from (SELECT a, b, a+b as TOTAL FROM ( 
           select 7 as a, 8 as b FROM DUAL 
           UNION ALL 
           select 8 as a, 8 as b FROM DUAL 
           UNION ALL 
           select 0 as a, 0 as b FROM DUAL) 
    )
WHERE TOTAL <> 0 
; 

이 방법은...

select * 
from (SELECT a, b, a+b as TOTAL FROM ( 
           select 7 as a, 8 as b FROM DUAL 
           UNION ALL 
           select 8 as a, 8 as b FROM DUAL 
           UNION ALL 
           select 0 as a, 0 as b FROM DUAL) 
    ) as Temp
WHERE TOTAL <> 0; 

언급URL : https://stackoverflow.com/questions/3884678/use-a-calculated-field-in-the-where-clause