|
각각의 쿼리는 조회가 되는데 union all 사용하면 Error: SQL0199 - 키워드 UNION가 예상한 키워드가 아님. 이라고 나옵니다.
뭘 잘못한걸까요..아시는분 있으시면 답변 부탁드립니다.
select * from (
select distinct(a.wi31ai) as item, b.wa25ad as name, a.wi31ap as in_date,
a.wi31an as bl_no, (case a.wi31aj when 'N2' then '1' when 'N3' then '2' when 'N4' then '3' end) as revision,
a.wi31ar as qty, a.wi31av as out_date, a.wi31at as inv, a.wi31au as con_qty,
--S-T Act 1
coalesce(sum(case when c.ib12ae = 'JB24' then c.ib12bi end),0) as JB24,
coalesce(sum(case when c.ib12ae = 'JB14' then c.ib12bi end),0) as JB14,
coalesce(sum(case when c.ib12ae = 'JB13' then c.ib12bi end),0) as JB13,
coalesce(sum(case when c.ib12ae = 'JB12' then c.ib12bi end),0) as JB12,
coalesce(sum(case when c.ib12ae = 'JB17' then c.ib12bi end),0) as JB17,
--Total(S-T Act 1)
(coalesce(sum(case when c.ib12ae = 'JB24' then c.ib12bi end),0) +
coalesce(sum(case when c.ib12ae = 'JB14' then c.ib12bi end),0) +
coalesce(sum(case when c.ib12ae = 'JB13' then c.ib12bi end),0) +
coalesce(sum(case when c.ib12ae = 'JB12' then c.ib12bi end),0) +
coalesce(sum(case when c.ib12ae = 'JB17' then c.ib12bi end),0)) as act1,
--S-T Act 2
coalesce(sum(case when c.ib12ae = 'JB11' then c.ib12bi end),0) as JB11,
coalesce(sum(case when c.ib12ae = 'JD04' then c.ib12bi end),0) as JD04,
'' as ct10,
coalesce(sum(case when c.ib12ae = 'JC11' then c.ib12bi end),0) as JC11,
coalesce(sum(case when c.ib12ae = 'JC12' then c.ib12bi end),0) as JC12,
coalesce(sum(case when c.ib12ae = 'JC01' then c.ib12bi end),0) as JC01,
coalesce(sum(case when c.ib12ae = 'JB25' then c.ib12bi end),0) as JB25,
--Total(S-T Act 2)
coalesce(sum(case when c.ib12ae = 'JB11' then c.ib12bi end),0) +
coalesce(sum(case when c.ib12ae = 'JD04' then c.ib12bi end),0) +
coalesce(sum(case when c.ib12ae = 'JC11' then c.ib12bi end),0) +
coalesce(sum(case when c.ib12ae = 'JC12' then c.ib12bi end),0) +
coalesce(sum(case when c.ib12ae = 'JC01' then c.ib12bi end),0) +
coalesce(sum(case when c.ib12ae = 'JB25' then c.ib12bi end),0) as act2,
--vat(S-T Act 2)
((coalesce(sum(case when c.ib12ae = 'JB11' then c.ib12bi end),0) +
coalesce(sum(case when c.ib12ae = 'JD04' then c.ib12bi end),0) +
coalesce(sum(case when c.ib12ae = 'JC11' then c.ib12bi end),0) +
coalesce(sum(case when c.ib12ae = 'JC12' then c.ib12bi end),0) +
coalesce(sum(case when c.ib12ae = 'JC01' then c.ib12bi end),0) +
coalesce(sum(case when c.ib12ae = 'JB25' then c.ib12bi end),0)) / 10) as vat,
--Total Result
(coalesce(sum(case when c.ib12ae = 'JB11' then c.ib12bi end),0) +
coalesce(sum(case when c.ib12ae = 'JD04' then c.ib12bi end),0) +
coalesce(sum(case when c.ib12ae = 'JC11' then c.ib12bi end),0) +
coalesce(sum(case when c.ib12ae = 'JC12' then c.ib12bi end),0) +
coalesce(sum(case when c.ib12ae = 'JC01' then c.ib12bi end),0) +
coalesce(sum(case when c.ib12ae = 'JB25' then c.ib12bi end),0))+
((coalesce(sum(case when c.ib12ae = 'JB11' then c.ib12bi end),0) +
coalesce(sum(case when c.ib12ae = 'JD04' then c.ib12bi end),0) +
coalesce(sum(case when c.ib12ae = 'JC11' then c.ib12bi end),0) +
coalesce(sum(case when c.ib12ae = 'JC12' then c.ib12bi end),0) +
coalesce(sum(case when c.ib12ae = 'JC01' then c.ib12bi end),0) +
coalesce(sum(case when c.ib12ae = 'JB25' then c.ib12bi end),0)) / 10) as total
,SUBSTR(CHAR(a.wi31bf),5,2) as end_date
FROM ldbcfs.twi31m a,
ldbcfs.twa25m b,
ldbcom.tib12m c
WHERE a.wi31bf between 20070125 and 20070225
and c.ib12aw='1208653367'
and a.wi31ai = b.wa25ab
AND a.wi31aa = c.ib12aa
AND a.wi31ab = c.ib12ab
AND a.wi31ac = c.ib12ac
and a.wi31ad = c.ib12ad
and a.wi31ae = c.ib12af
and a.wi31an = c.ib12ag
and a.wi31ao = c.ib12ah
and a.wi31ak = c.ib12ai
and a.wi31al = c.ib12aj
and a.wi31am = c.ib12ak
group by b.wa25ad, a.wi31ap,
a.wi31an, a.wi31aj,
a.wi31ar, a.wi31av, a.wi31at, a.wi31au, a.wi31ai, a.wi31bf
order by a.wi31an
UNION all
select distinct(a.wi31ai), '' as name, a.wi31ap as in_date,
a.wi31an as bl_no, (case a.wi31aj when 'N2' then '1' when 'N3' then '2' when 'N4' then '3' end) as revision,
a.wi31ar as qty, a.wi31av as out_date, a.wi31at as inv, a.wi31au as con_qty,
'' as JB24, '' as JB14, '' as JB13, '' as JB12, '' as JB17, '' as act1, '' as JB11,
'' as JD04, coalesce(sum(case when f.ut50af = 'CT10' then f.ut50ao end),0) as ct10,
'' as JC11, '' as JC12, '' as JC01, '' as JB25, '' act2, '' as vat, '' as total,
SUBSTR(CHAR(a.wi31bf),5,2) as end_date
from ldbcfs.twi31m a, ldbtrs.tum03m d, ldbtrs.tuc02t e, ldbtrs.tut50t f
where f.ut50ae > 0
AND a.wi31bf between 20070125 and 20070225
and a.wi31ak = d.um03aa
and a.wi31al = d.um03ab
and a.wi31am = d.um03ac
and e.uc02aa = f.ut50aa
and e.uc02ab = f.ut50ab
and e.uc02ac = f.ut50ac
and e.uc02ad = f.ut50ad
and e.uc02ao = d.um03ad
and d.um03ag = e.uc02ai
and d.um03ah = e.uc02aj
group by a.wi31ap, a.wi31an, a.wi31aj,
a.wi31ar, a.wi31av, a.wi31at, a.wi31au, a.wi31ai, a.wi31bf
order by a.wi31an )
|
