sql6

SELECT
KEIYAK.CNSA41
,KEIYAK.CNSB41
,SUM(KEIYAK.CNS038) AS SUMCNS038
,SUM(KEIYAK.CNS131) AS SUMCNS131
,custmr3.CSCTRJ AS CSCTRJ3
,SUM(s1.SJLSRY) AS SUMS1SJLSRY
        ,SUM(s1.SJSTXA) AS SUMS1SJSTXA
        ,SUM(s2.SJLSRY) AS SUMS2SJLSRY
        ,SUM(s2.SJSTXA) AS SUMS2SJSTXA
        ,SUM(s3.SJLSRY) AS SUMS3SJLSRY
        ,SUM(s3.SJSTXA) AS SUMS3SJSTXA
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 custmr3
ON (
custmr3.CSCST1 =
KEIYAK.CNSA41
AND custmr3.CSCST2 = KEIYAK.CNSB41
AND custmr3.CSDLTM = ''
)
WHERE
KEIYAK.CNDLTM=''
AND KEIYAK.CNKNT2!= 0
AND KEIYAK.CNMVDT= 0
AND KEIYAK.CNVDT2=0
AND KEIYAK.CNKNT2 = '8'
AND KEIYAK.CNKNT2 = '8'
AND KEIYAK.CNS038 !=0
AND s1.SJNKSB = '8'
AND
KEIYAK.CNSA41 = '8'



KEIYAK.CNSB41= '8'



KEIYAK.CNBMCD=



TRIM(KEIYAK.CNCNO1) ||
TRIM(KEIYAK.CNCNO2) || TRIM(KEIYAK.CNCNO3)||
TRIM(KEIYAK.CNCNO4) ||
TRIM(KEIYAK.CNCNO5) =  '8'
AND
KEIYAK.CNSLKI ='8'


<
GROUP BY

custmr3.CSCTRJ,
  KEIYAK.CNSA41
,KEIYAK.CNSB41
ORDER BY
KEIYAK.CNSA41 ASC ,KEIYAK.CNSB41 ASC