Showing posts with label sql. Show all posts
Showing posts with label sql. Show all posts

Friday, September 26, 2014

sql7

SELECT
SHBMCD, BNBNA AS SHBMCDNM, SHTNTO, TNBNA AS SHTNTONM, SHSHNO, SHSNT1, SHSNT2, SHSNT3, SHAD01, K1.KUKNMR AS KUKNMR01,
SHCST1, SHCST2, SHHNNA, SHTKCD, PSCUS1, PSCUS2, C1.CSCTRJ AS CSCTRJ01, SHKYYD, SUBSTR(DIGITS(SHS027), 2, 1) AS SHS027,
A1.ABKNMR AS ABKNMR01, SHSA47, SHSB47, C2.CSCTRJ AS CSCTRJ02, C3.CSCTRJ AS CSCTRJ03, NLSA47, NLSB47, C4.CSCTRJ AS CSCTRJ04,
NLA201, NLB201, C5.CSCTRJ AS CSCTRJ05, SHCHOH, K2.KUKNMR AS KUKNMR02, CKST01, K3.KUKNMR AS KUKNMR03, SSTEKC, K4.KUKNMR AS KUKNMR04,
K5.KUKNMR AS KUKNMR05, K6.KUKNMR AS KUKNMR06, SHSHSO, SHZNKA, SHKUBN, SHDAIR,
PSTNS1, T1.TPTPNM AS TPTPNM01,
PSTNS2, T2.TPTPNM AS TPTPNM02,
PSTNS3, T3.TPTPNM AS TPTPNM03,
PSTNS4, T4.TPTPNM AS TPTPNM04,
PSTNS5, T5.TPTPNM AS TPTPNM05,
PSHAM1, PSHAM2, PSHAM3, PSHAM4, PSHAM5,
PSHS11, PSHS12, C11.CSCTRJ AS CSCTRJ11,
PSHS21, PSHS22, C12.CSCTRJ AS CSCTRJ12,
PSHS31, PSHS32, C13.CSCTRJ AS CSCTRJ13,
PSHS41, PSHS42, C14.CSCTRJ AS CSCTRJ14,
PSHS51, PSHS52, C15.CSCTRJ AS CSCTRJ15,
PSTOK1, G1.TTTKNM AS TTTKNM01,
PSTOK2, G2.TTTKNM AS TTTKNM02,
PSTOK3, G3.TTTKNM AS TTTKNM03,
PSTOK4, G4.TTTKNM AS TTTKNM04,
PSTOK5, G5.TTTKNM AS TTTKNM05,
SHS011, SHS015, SHS016, SHS018, SHS019, SHS020, SHS022, SHS01T, SHYDT1, SHYDT3, SHKKIK, SHKAIE, SHKJOK, SHENTI, SHKAIS,
SHKJRT, SHESDS, CMNMRC, NLAMNM, NLKATA, NLOPTD, NLTEMP, NLTOSO, NLUCHI, NLAYOT, NLATYP, SHLKIK, SHVNP1, SHVNC1, SHCSGP, SHCHO3
    FROM SANTEH
        LEFT OUTER JOIN KSANFL ON NLMKN1 = SHSNT1 AND NLMKN2 = SHSNT2 AND NLMKN3 = SHSNT3 AND NLDLTM = ''
        LEFT OUTER JOIN BMNMTR ON BNBCD = SHBMCD AND BNSYMD ='5'AND BNEYMD ='5'AND BNDLTM = ''
        LEFT OUTER JOIN TANTMR ON TNBCD = SHBMCD AND TNTCD = SHTNTO AND TNSYMD ='5' AND TNEYMD ='5' AND TNDLTM = ''
        LEFT OUTER JOIN PRSLMR ON PSSHNO = SHSHNO AND PSDLTM = ''
        LEFT OUTER JOIN SEISKI ON SSSKC1 = PSCUS1 AND SSSKC2 = PSCUS2 AND SSSYMD ='5' AND SSEYMD ='5'AND SSDLTM = ''
        LEFT OUTER JOIN CDTMST ON CMFXID = 'GS005' AND CMCLAS = '2'
        LEFT OUTER JOIN CUSTMR C1 ON C1.CSCST1 = PSCUS1 AND C1.CSCST2 = PSCUS2 AND C1.CSDLTM = ''
        LEFT OUTER JOIN CUSTMR C2 ON C2.CSCST1 = SHSA47 AND C2.CSCST2 = SHSB47 AND C2.CSDLTM = ''
        LEFT OUTER JOIN CUSTMR C3 ON C3.CSCST1 = SHCST1 AND C3.CSCST2 = SHCST2 AND C3.CSDLTM = ''
        LEFT OUTER JOIN CUSTMR C4 ON C4.CSCST1 = NLSA47 AND C4.CSCST2 = NLSB47 AND C4.CSDLTM = ''
        LEFT OUTER JOIN CUSTMR C5 ON C5.CSCST1 = NLA201 AND C5.CSCST2 = NLB201 AND C5.CSDLTM = ''
        LEFT OUTER JOIN TNPOMR T1 ON T1.TPTPCD = PSTNS1 AND T1.TPSYMD ='5' AND T1.TPEYMD ='5' AND T1.TPDLTM = ''
        LEFT OUTER JOIN TNPOMR T2 ON T2.TPTPCD = PSTNS2 AND T2.TPSYMD ='5' AND T2.TPEYMD ='5'AND T2.TPDLTM = ''
        LEFT OUTER JOIN TNPOMR T3 ON T3.TPTPCD = PSTNS3 AND T3.TPSYMD ='5' AND T3.TPEYMD ='5'AND T3.TPDLTM = ''

        LEFT OUTER JOIN CUSTMR C11 ON C11.CSCST1 = PSHS11 AND C11.CSCST2 = PSHS12 AND C11.CSDLTM = ''
        LEFT OUTER JOIN CUSTMR C12 ON C12.CSCST1 = PSHS21 AND C12.CSCST2 = PSHS22 AND C12.CSDLTM = ''
        LEFT OUTER JOIN CUSTMR C13 ON C13.CSCST1 = PSHS31 AND C13.CSCST2 = PSHS32 AND C13.CSDLTM = ''
        LEFT OUTER JOIN CUSTMR C14 ON C14.CSCST1 = PSHS41 AND C14.CSCST2 = PSHS42 AND C14.CSDLTM = ''
        LEFT OUTER JOIN CUSTMR C15 ON C15.CSCST1 = PSHS51 AND C15.CSCST2 = PSHS52 AND C15.CSDLTM = ''
        LEFT OUTER JOIN TKGKMR G1 ON G1.TTTONO = PSTOK1 AND G1.TTSYMD ='5' AND G1.TTEYMD ='5'AND G1.TTDLTM = ''
        LEFT OUTER JOIN TKGKMR G2 ON G2.TTTONO = PSTOK2 AND G2.TTSYMD ='5' AND G2.TTEYMD ='5' AND G2.TTDLTM = ''
        LEFT OUTER JOIN TKGKMR G3 ON G3.TTTONO = PSTOK3 AND G3.TTSYMD ='5'AND G3.TTEYMD ='5'AND G3.TTDLTM = ''
     
        LEFT OUTER JOIN CDKALS ON CKSBSB = 'CL' AND CKFXID = 'KCL0040E' AND CKGMID = '11' AND CKKEY1 = 'CHK005' AND CKKEY2 = SHCHOH AND CKKEY3 = '' AND CKKEY4 = '' AND CKKEY5 = '' AND CKUSEK = '1' AND CKSEQN = 1 AND CKSYMD ='5'AND CKEYMD ='5'
        LEFT OUTER JOIN AKBNMR A1 ON A1.ABKBNC = 'A5' AND SUBSTR(A1.ABDTCD, 2, 1) = SUBSTR(DIGITS(SHS027), 2, 1) AND A1.ABDLTM = '' AND A1.ABSYMD ='5' AND A1.ABEYMD ='5'
    LEFT OUTER JOIN KUBNMR K1 ON K1.KUKBNC = 'B6' AND SUBSTR(K1.KUCODE, 2, 3) = SHAD01 AND K1.KUDLTM = '' AND K1.KUSYMD ='5' AND K1.KUEYMD ='5'
    LEFT OUTER JOIN KUBNMR K2 ON K2.KUKBNC = 'F5' AND SUBSTR(K2.KUCODE, 3, 2) = SHCHOH AND K2.KUDLTM = '' AND K2.KUSYMD ='5' AND K2.KUEYMD ='5'
    LEFT OUTER JOIN KUBNMR K3 ON K3.KUKBNC = 'D2' AND SUBSTR(K3.KUCODE, 3, 2) = SUBSTR(CKST01, 1, 2) AND K3.KUDLTM = '' AND K3.KUSYMD ='5'AND K3.KUEYMD ='5'
    LEFT OUTER JOIN KUBNMR K4 ON K4.KUKBNC = 'D3' AND SUBSTR(K4.KUCODE, 3, 2) = SSTEKC AND K4.KUDLTM = '' AND K4.KUSYMD ='5'AND K4.KUEYMD ='5'
    LEFT OUTER JOIN KUBNMR K5 ON K5.KUKBNC = 'D3' AND SUBSTR(K5.KUCODE, 3, 2) = 'Z2' AND K5.KUDLTM = '' AND K5.KUSYMD ='5'AND K5.KUEYMD ='5'
    LEFT OUTER JOIN KUBNMR K6 ON K6.KUKBNC = 'D3' AND SUBSTR(K6.KUCODE, 3, 2) = 'T1' AND K6.KUDLTM = '' AND K6.KUSYMD ='5' AND K6.KUEYMD ='5'
    WHERE SHSNT1 = '8'
        AND SHSNT2 =  '8'
        AND SHSNT3 = '8'
Posted by at No comments:
Labels:

sql8

SELECT
ac.ANRKNM,
ac.ANTRN5,
ac.ANTRN6,
ac.ANTRN7,
ac.ANTRN8,
ac.ANKIKN,
kb1.KUKNMR as KUKNMR1,
ac.ANKATA,
ac.ANOPTD,
ac.ANTEMP,
ac.ANFIL1,
ac.ANTOSO,
ac.ANUCHI,
ac.ANTKCD,
tk.TLTKNM,
ac.ANEIGK,
eg.EAEINM,
bk.BKBYC1,
bk.BKBYC2,
cs.CSCTRJ,
bl1.BLTHKC,
kb2.KUKNMR as KUKNMR2,
bl1.BLTEKC,
kb3.KUKNMR as KUKNMR3,
bk.BKBYKN,
bl1.BLYKD2,
bk.BKBYD1,
bk.BKBYND,
bk.BKSQNO,
bk.BKCNO1,
bk.BKCNO2,
bk.BKCNO3,
bk.BKCNO4,
bk.BKCNO5,
bk.BKSLKI,
bk.BKATNO,
bk.BKSTXB,
bk.BKBYD2

FROM BAIKAK bk
INNER
JOIN ACNTRA ac
ON(
ac.ANCNO1= bk.BKCNO1 AND
ac.ANCNO2= bk.BKCNO2 AND
ac.ANCNO3= bk.BKCNO3 AND
ac.ANCNO4= bk.BKCNO4 AND
ac.ANCNO5= bk.BKCNO5
AND
ac.ANSLKI= bk.BKSLKI AND
ac.ANDLTM= ''
) LEFT OUTER JOIN KUBNMR kb1
ON(
kb1.KUKBNC = 'V2' AND
RIGHT(TRIM(kb1.KUCODE),INT(kb1.KULENG)) =
ac.ANKIKN AND
kb1.KUDLTM = ' ' AND
kb1.KUSYMD ='7' AND
kb1.KUEYMD ='7'
) LEFT OUTER JOIN TOKYMR tk
ON(
tk.TLTKCD =
ac.ANTKCD AND
tk.TLDLTM = '' AND
tk.TLSYMD ='7' AND
tk.TLEYMD ='7'
) LEFT OUTER JOIN EIGSMR eg
ON(
eg.EATKCD =
ac.ANTKCD AND
eg.EAEIGK = ac.ANEIGK AND
eg.EADLTM = '' AND
eg.EASYMD
='7'
eg.EAEYMD ='7'
)LEFT OUTER JOIN
CUSTMR cs
ON(
cs.CSCST1 = bk.BKBYC1 AND
cs.CSCST2 = bk.BKBYC2 AND
cs.CSDLTM = ''
) LEFT OUTER JOIN BCLTRN bl1
ON(
bl1.BLBYND = bk.BKBYND
AND
bl1.BLSQNO = bk.BKSQNO AND
bl1.BLKIZI = '1' AND
bl1.BLDLTM = ''
) LEFT
OUTER JOIN BCLTRN bl2
ON(
bl2.BLBYND = bk.BKBYND AND
bl2.BLSQNO =
bk.BKSQNO AND
(bl2.BLFL10 != 'K' OR
bl2.BLUPFL != 'U' ) AND
bl2.BLDLTM =
''
) LEFT OUTER JOIN KUBNMR kb2
ON(
kb2.KUKBNC = 'D2' AND
RIGHT(TRIM(kb2.KUCODE),INT(kb2.KULENG)) = bl1.BLTHKC AND
kb2.KUDLTM =
''
AND
kb2.KUSYMD ='7' AND
kb2.KUEYMD ='7'
)
LEFT OUTER JOIN KUBNMR kb3
ON(
kb3.KUKBNC= 'D3' AND
RIGHT(TRIM(kb3.KUCODE),INT(kb3.KULENG))= bl1.BLTEKC AND
kb3.KUDLTM = ''
AND
kb3.KUSYMD ='7'AND
kb3.KUEYMD ='7'
)LEFT OUTER JOIN (
SELECT
sj1.SJCNO1, sj1.SJCNO2, sj1.SJCNO3,
sj1.SJCNO4, sj1.SJCNO5, sj1.SJSLKI
FROM SAIKNJ
sj1
WHERE
(sj1.SJNKSB
='108' OR sj1.SJNKSB ='109' OR sj1.SJNKSB ='110'
OR sj1.SJNKSB ='130')
AND sj1.SJDLTM = ''
GROUP BY sj1.SJCNO1,
sj1.SJCNO2, sj1.SJCNO3,
sj1.SJCNO4, sj1.SJCNO5, sj1.SJSLKI ) sj
ON(
sj.SJCNO1 = bk.BKCNO1
AND
sj.SJCNO2 = bk.BKCNO2
AND sj.SJCNO3 = bk.BKCNO3
AND sj.SJCNO4 =
bk.BKCNO4
AND sj.SJCNO5 = bk.BKCNO5
AND sj.SJSLKI = bk.BKSLKI
) LEFT
OUTER JOIN SAIKNJ sj2
ON(
sj2.SJCNO1 = bk.BKCNO1
AND sj2.SJCNO2 =
bk.BKCNO2
AND sj2.SJCNO3 = bk.BKCNO3
AND sj2.SJCNO4 = bk.BKCNO4
AND
sj2.SJCNO5 = bk.BKCNO5
AND sj2.SJSLKI = bk.BKSLKI
AND sj2.SJNKK2 != 0
AND ( (sj2.SJNKSB = '108')
OR (sj2.SJNKSB ='109')
OR (sj2.SJNKSB ='110')
OR (sj2.SJNKSB ='130') )
AND sj2.SJDLTM= ''
)
WHERE
bk.BKDLTM = ''
AND
bk.BKSLKI = ='7'
AND

bk.BKBYD1 ='7'
AND
bk.BKBYD1 ='7'
AND

ac.ANTKCD ='7'


AND bl2.BLBYND IS NULL AND
sj.SJCNO1 IS NOT NULL AND
sj2.SJCNO1 IS NULL
ORDER BY
bk.BKCNO1 ASC,
bk.BKCNO2 ASC,
bk.BKCNO3 ASC,
bk.BKCNO4 ASC,
bk.BKCNO5 ASC,
bk.BKSLKI ASC,
bk.BKATNO ASC
Posted by at No comments:
Labels:

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
Posted by at No comments:
Labels:

sql5

SELECT
SUM(KEIYAK.CNS030) AS SUMCNS030
,SUM(KEIYAK.CNS129) AS SUMCNS129
,KEIYAK.CNSA33
,KEIYAK.CNSB33
,custmr1.CSCTRJ AS CSCTRJ1
,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 custmr1
ON (
custmr1.CSCST1 = KEIYAK.CNSA33
AND custmr1.CSCST2 = KEIYAK.CNSB33
AND
custmr1.CSDLTM = ''
)
WHERE
KEIYAK.CNDLTM=''
AND KEIYAK.CNKNT2!= 0
AND
KEIYAK.CNMVDT= 0
AND KEIYAK.CNVDT2=0
AND KEIYAK.CNKNT2 &gt;=
#bookingYearMonth1DAT1#
AND KEIYAK.CNKNT2 &lt;= #vSearchEndDate#
AND
KEIYAK.CNS030!=0
AND s1.SJNKSB = '5'

AND


KEIYAK.CNSA33= '5'
AND

KEIYAK.CNSB33='8'

AND

KEIYAK.CNBMCD='9'
AND


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


GROUP BY
custmr1.CSCTRJ,
KEIYAK.CNSA33
,KEIYAK.CNSB33
ORDER BY
KEIYAK.CNSA33 ASC
,KEIYAK.CNSB33 ASC

Posted by at No comments:
Labels:

sql4

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
Posted by at No comments:
Labels:

sql3

SELECT RJRATE as RATE

FROM RINGKJ

WHERE KJCNO1 || KJCNO2 ||
KJCNO3 || KJCNO4 || KJCNO5 = '5'
AND KJSLKI = '5'
AND (KJNKSB =
'101' or KJNKSB = '103')
AND KJDLTM = ''
Posted by at No comments:
Labels:

sql2

SELECT NTSNT1||NTSNT2||NTSNT3 as NTSNT,

NTIJTX,NTTIYC,NTFOIF,NTJOTI,
NTACCD,NTONKB,NTSSCD,NTULCD,
NTSIPK,NTORIT,NTIJKN,NTIJKT,
NTGTLS,NTGTLT,NTMTLS,NTMTLT,
k1.KUKNMR as
TRCDNM,
k2.KUKNMR as JOTINM,
k3.KUKNMR as ACCDNM,
k4.KUKNMR as SSCDNM,
k5.KUKNMR as ULCDNM

FROM NA110T

LEFT OUTER JOIN KUBNMR k1 on
(
k1.KUKBNC = 'B6'
AND
RIGHT(TRIM(k1.KUCODE),INT(k1.KULENG)) = NTTRCD
AND k1.KUSYMD
AND k1.KUDLTM = ''
)

LEFT
OUTER
JOIN KUBNMR k2 on
(
k2.KUKBNC = '0'
AND
RIGHT(TRIM(k2.KUCODE),INT(k2.KULENG)) = NTJOTI

AND k2.KUDLTM = ''
)
LEFT
OUTER JOIN KUBNMR k3 on
(
k3.KUKBNC = 'X5'
AND
RIGHT(TRIM(k3.KUCODE),INT(k3.KULENG)) = NTACCD

AND k3.KUDLTM = ''
)
LEFT
OUTER JOIN
KUBNMR k4 on
(
k4.KUKBNC = 'LK'
AND
RIGHT(TRIM(k4.KUCODE),INT(k4.KULENG)) = NTSSCD

AND k4.KUDLTM = ''
)

LEFT
OUTER JOIN KUBNMR k5 on
(
k5.KUKBNC = 'X5'
AND
RIGHT(TRIM(k5.KUCODE),INT(k5.KULENG)) = NTULCD
AND k5.KUSYMD &lt;=

AND k5.KUDLTM = ''
)
WHERE
TRIM(NTCNO1)||TRIM(NTCNO2)||TRIM(NTCNO3)||TRIM(NTCNO4)||TRIM(NTCNO5) = '8'
AND NTSLKI = '8'
AND
NTDLTM = ''
Posted by at No comments:
Labels:

sql1

SELECT CNCNO1||CNCNO2||CNCNO3||CNCNO4||CNCNO5 as CNNO,
CNSLKI as SLKI,
CNCST1 as CST1,
CNCST2 as CST2,
CSCTRJ as CSNM,
CNTRCD as TRCD,
KUKNMJ as TRNM,
CNDOCC as DOCC,
CNSHKN as SHKN,
CNZNKN as ZNKN,

FROM customerhouse

LEFT OUTER JOIN customer on
CNCST1 = CSCST1
AND CNCST2 = CSCST2
LEFT OUTER JOIN KUBNMR
ON KUKBNC = 'B6'
AND KUDLTM = ''
WHERE
TRIM(CNCNO1)||TRIM(CNCNO2)||TRIM(CNCNO3)||TRIM(CNCNO4)||TRIM(CNCNO5) = '5'
Posted by at No comments:
Labels:
Subscribe to: Posts (Atom)