SELECT
KEIYAK.CNCNO1
,KEIYAK.CNCNO2
,KEIYAK.CNCNO3
,KEIYAK.CNCNO4
,KEIYAK.CNCNO5
,KEIYAK.CNSLKI
,ACNTRA.ANRKNM
,ACNTRA.ANTRN5
,ACNTRA.ANTRN6
,ACNTRA.ANTRN7
,ACNTRA.ANTRN8
,KEIYAK.CNS030
,KEIYAK.CNS129
,KEIYAK.CNSA33
,KEIYAK.CNSB33
,custmr1.CSCTRJ AS CSCTRJ1
,KEIYAK.CNS034
,KEIYAK.CNS130
,KEIYAK.CNSA37
,KEIYAK.CNSB37
,custmr2.CSCTRJ AS CSCTRJ2
,KEIYAK.CNS038
,KEIYAK.CNS131
,KEIYAK.CNSA41
,KEIYAK.CNSB41
,CUSTMR3.CSCTRJ AS CSCTRJ3
,KEIYAK.CNKNT2
,BMNMTR.BNBNA
,s1.SJNKD2 AS MAXS1SJNKD2
,KEIYAK.CNKIKN
,s1.SJLSRY AS SUMS1SJLSRY
,s1.SJSTXA AS SUMS1SJSTXA
,s2.SJLSRY AS SUMS2SJLSRY
,s2.SJSTXA AS SUMS2SJSTXA
,s3.SJLSRY AS SUMS3SJLSRY
,s3.SJSTXA AS SUMS3SJSTXA
,s1.SJNKSB AS SJNKSB1
FROM
KEIYAK
LEFT OUTER JOIN (
SELECT
SJCNO1
,SJCNO2
,SJCNO3
,SJCNO4
,SJCNO5
,SJSLKI
,SJNKSB
,MAX(SJNKD2) AS SJNKD2
,SUM(SJLSRY) AS SJLSRY
,SUM(SJSTXA) AS SJSTXA
,SJDLTM
FROM
SAIKNJ
GROUP BY
SJCNO1
,SJCNO2
,SJCNO3
,SJCNO4
,SJCNO5
,SJSLKI
,SJNKSB
,SJDLTM
) s1
ON (
s1.SJCNO1 = KEIYAK.CNCNO1
AND s1.SJCNO2 = KEIYAK.CNCNO2
AND s1.SJCNO3 = KEIYAK.CNCNO3
AND s1.SJCNO4 = KEIYAK.CNCNO4
AND s1.SJCNO5 = KEIYAK.CNCNO5
AND s1.SJSLKI = KEIYAK.CNSLKI
AND s1.SJDLTM = ''
)
LEFT OUTER JOIN (
SELECT
SJCNO1
,SJCNO2
,SJCNO3
,SJCNO4
,SJCNO5
,SJSLKI
,SJNKSB
,SUM(SJLSRY) AS SJLSRY
,SUM(SJSTXA) AS SJSTXA
,SJDLTM
,SJNKK2
FROM
SAIKNJ
GROUP BY
SJCNO1
,SJCNO2
,SJCNO3
,SJCNO4
,SJCNO5
,SJSLKI
,SJNKSB
,SJDLTM
,SJNKK2
) s2
ON (
s2.SJCNO1 = KEIYAK.CNCNO1
AND s2.SJCNO2 = KEIYAK.CNCNO2
AND s2.SJCNO3 = KEIYAK.CNCNO3
AND s2.SJCNO4 = KEIYAK.CNCNO4
AND s2.SJCNO5 = KEIYAK.CNCNO5
AND s2.SJSLKI = KEIYAK.CNSLKI
AND s2.SJNKSB = s1.SJNKSB
AND s2.SJDLTM = ''
AND s2.SJNKK2 != 0
)
LEFT OUTER JOIN (
SELECT
SJCNO1
,SJCNO2
,SJCNO3
,SJCNO4
,SJCNO5
,SJSLKI
,SJNKSB
,SUM(SJLSRY) AS SJLSRY
,SUM(SJSTXA) AS SJSTXA
,SJDLTM
,SJNKK2
FROM
SAIKNJ
GROUP BY
SJCNO1
,SJCNO2
,SJCNO3
,SJCNO4
,SJCNO5
,SJSLKI
,SJNKSB
,SJDLTM
,SJNKK2
) s3
ON (
s3.SJCNO1 = KEIYAK.CNCNO1
AND s3.SJCNO2 = KEIYAK.CNCNO2
AND s3.SJCNO3 = KEIYAK.CNCNO3
AND s3.SJCNO4 = KEIYAK.CNCNO4
AND s3.SJCNO5 = KEIYAK.CNCNO5
AND s3.SJSLKI = KEIYAK.CNSLKI
AND s3.SJNKSB = s1.SJNKSB
AND s3.SJDLTM = ''
AND s3.SJNKK2 = 0
)
LEFT OUTER JOIN ACNTRA
ON (
ANCNO1 = KEIYAK.CNCNO1
AND ANCNO2 = KEIYAK.CNCNO2
AND ANCNO3 = KEIYAK.CNCNO3
AND ANCNO4 = KEIYAK.CNCNO4
AND ANCNO5 = KEIYAK.CNCNO5
AND ANSLKI = KEIYAK.CNSLKI
AND ANDLTM = ''
)
LEFT OUTER JOIN CUSTMR custmr1
ON (
custmr1.CSCST1 = KEIYAK.CNSA33
AND custmr1.CSCST2 = KEIYAK.CNSB33
AND custmr1.CSDLTM = ''
)
LEFT OUTER JOIN CUSTMR custmr2
ON (
custmr2.CSCST1 = KEIYAK.CNSA37
AND custmr2.CSCST2 = KEIYAK.CNSB37
AND custmr2.CSDLTM = ''
)
LEFT OUTER JOIN CUSTMR custmr3
ON (
custmr3.CSCST1 = KEIYAK.CNSA41
AND custmr3.CSCST2 = KEIYAK.CNSB41
AND custmr3.CSDLTM = ''
)
LEFT OUTER JOIN BMNMTR
ON (
BMNMTR.BNBCD = KEIYAK.CNBMCD
AND BMNMTR.BNSYMD = '5'
AND BMNMTR.BNEYMD ='5'
AND BMNMTR.BNDLTM = ''
)
WHERE
KEIYAK.CNDLTM=''
AND KEIYAK.CNKNT2!=0
AND KEIYAK.CNMVDT=0
AND
KEIYAK.CNVDT2=0
AND
KEIYAK.CNKNT2
ORDER BY
KEIYAK.CNKNT2 ASC
,KEIYAK.CNCNO1 ASC
,KEIYAK.CNCNO2 ASC
,KEIYAK.CNCNO3 ASC
,KEIYAK.CNCNO4 ASC
,KEIYAK.CNCNO5 ASC
,KEIYAK.CNSLKI ASC
No comments:
Post a Comment