Oracle에서 Left outer join을 사용하여 쿼리를 작성했지만 쿼리를 실행하면 ORA-00904: "b"."GROSS_DISCOUNT_AMOUNT": invalid identifier
오류가 발생합니다 .TableB에는 net_discount_number 및 gross_discount_amount 열이 포함됩니다.
select
a.GSMNO GSMNO,
a.NET_AMOUNT net,
a.GROSS_AMOUNT gross,
sum(b.net_discount_amount) net_discount, sum(b.gross_discount_amount) gross_discount,
a.code code, a.seq_no
from tableA a
LEFT OUTER JOIN
(select id, code, seq_no, sum(gross_amount) gross_amount, sum(net_discount_amount), sum(gross_discount_amount) from tableB
group by id, code, seq_no)
b ON a.id = b.id and NVL(a.code,b.code) = NVL(b.code,-99) and
NVL(a._seq_no,-99) = NVL(b.seq_no,-99)
and a.gross_amount = b.gross_amount
where a.tvNo like '123% and gsmno ='1111111111'
group by
a.GSMNO,
a.NET_AMOUNT,
a.GROSS_AMOUNT,
a.code, a.seq_no
집계 합계에 대한 별칭을 지정하지 않았습니다. 다음과 같아야합니다.
select
a.GSMNO GSMNO,
a.NET_AMOUNT net,
a.GROSS_AMOUNT gross,
sum(b.net_discount_amount) net_discount, sum(b.gross_discount_amount) gross_discount,
a.code code, a.seq_no
from tableA a
LEFT OUTER JOIN
(select id, code, seq_no,
sum(gross_amount) gross_amount,
sum(net_discount_amount) net_discount_amount,
sum(gross_discount_amount) gross_discount_amount
from tableB
group by id, code, seq_no)
b ON a.id = b.id and NVL(a.code,b.code) = NVL(b.code,-99) and
NVL(a._seq_no,-99) = NVL(b.seq_no,-99)
and a.gross_amount = b.gross_amount
where a.tvNo like '123% and gsmno ='1111111111'
group by
a.GSMNO,
a.NET_AMOUNT,
a.GROSS_AMOUNT,
a.code, a.seq_no
이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.
침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제
몇 마디 만하겠습니다