zOs/SQL/WM005J
//A540769W JOB (CP00,KE50),'DB2 REO', 00010000
// MSGCLASS=T,TIME=1440, 00020000
// NOTIFY=&SYSUID,REGION=0M 00030000
//P00 EXEC PGM=IKJEFT01,
// DYNAMNBR=20
//SYSTSIN DD *
DSN SYSTEM (DBAF)
RUN PROGRAM (DSNTIAUL) PARMS('SQL')
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSPUNCH DD DUMMY
//SYSREC00 DD DSN=A540769.TMP.DREI,
// DISP=(,CATLG),SPACE=(CYL,(10,10),RLSE),
// RECFM=FB,LRECL=809
//*YSREC00 DD SYSOUT=*
SELECT *
FROM OA1A.TWM005A1
WHERE D$GUEB > CURRENT TIMESTAMP
AND CTYP = 'EG13';
//
//P00 EXEC PGM=IKJEFT01,
// DYNAMNBR=20
//SYSTSIN DD *
DSN SYSTEM (DBAF)
RUN PROGRAM (DSNTEP2)
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSPUNCH DD DUMMY
//*YSREC00 DD DSN=A540769.TMP.OUT,
//* DISP=(,CATLG),SPACE=(CYL,(10,10),RLSE),
//* RECFM=FB,LRECL=809
//SYSIN DD *
SELECT *
FROM OA1A.TWM005A1
WHERE D$GUEB > CURRENT TIMESTAMP
AND CTYP = 'EG13';
//
SELECT * FROM OA1T.TWM005A1
WHERE D$GUEB > CURRENT TIMESTAMP
AND CTYP = 'CARG';
//
SELECT * FROM OA1T.TWM005A1
WHERE D$GUEB > CURRENT TIMESTAMP
AND IS40KEY > ''
and NDEP > '0000000000000000'
AND NDEP < '9999999999999999'
ORDER BY NDEP, IS40KEY, CSTAT;
//
UPDATE OA1T.TWM005A1
SET D$GUEB = '2011-03-15-13.25.34.000000'
,TAUFTRG = 'F540657'
,PLMUT = 'A629518'
,D$LMUT = '2011-03-15-13.25.34.000001'
WHERE CTYP = 'SAP3'
AND TWM1 = '0008';
//
SELECT CKAT, SUBSTR(';',1,1),
CTYP, SUBSTR(';',1,1),
D$ERF, SUBSTR(';',1,1),
PERF, SUBSTR(';',1,1),
D$LMUT, SUBSTR(';',1,1),
PLMUT, SUBSTR(';',1,1),
TGRUND, SUBSTR(';',1,1),
TAUFTRG, SUBSTR(';',1,1),
D$GUEA, SUBSTR(';',1,1),
D$GUEB, SUBSTR(';',1,1),
CSTAT, SUBSTR(';',1,1),
CDEFAULT, SUBSTR(';',1,1),
NCIF, SUBSTR(';',1,1),
NDEP, SUBSTR(';',1,1),
TWMD1, SUBSTR(';',1,1),
TWMD5, SUBSTR(';',1,1),
TWM6, SUBSTR(';',1,1),
TWM1, SUBSTR(';',1,1),
TWM2, SUBSTR(';',1,1),
TWM3, SUBSTR(';',1,1),
TWM4, SUBSTR(';',1,1),
NCIF2
FROM OA1T.TWM005A1
WHERE D$GUEB > CURRENT TIMESTAMP
AND CTYP IN ('PSNE','PSNK','PSNI')
;
//
SELECT *
FROM OA1t.TWM005A1
WHERE D$GUEB > CURRENT TIMESTAMP
and ncif = '083509104606'
;
//
SELECT CD.CD100001
FROM OA1T.TCD100A1 CD
WHERE NOT EXISTS
(SELECT *
FROM OA1T.TDM001A1 DM
WHERE CD.CD100001 = DM.DM001007);
//
SELECT distinct
OA1T.TWM005A1.CTYP, SUBSTR(';',1,1),
OA1T.TWM005A1.NCIF, SUBSTR(';',1,1),
OA1T.TWM005A1.NDEP, SUBSTR(';',1,1),
substr(OA1T.TWM005A1.TWM1,1,3), SUBSTR(';',1,1),
OA1T.TDM001A1.DM001110
FROM OA1T.TWM005A1, OA1T.TDM001A1
WHERE OA1T.TWM005A1.D$GUEB > CURRENT TIMESTAMP
AND DATE(OA1T.TWM005A1.D$GUEA) < '01.02.2011'
AND OA1T.TWM005A1.CTYP IN ('SLLA', 'SLVV', 'SLQI')
AND OA1T.TWM005A1.NCIF = OA1T.TDM001A1.DM001007
AND OA1T.TDM001A1.DM001110 IN ('0011', '0012');
//
SELECT WM.NDEP
FROM OA1T.TWM005A1 WM
WHERE D$GUEB > CURRENT TIMESTAMP
AND CTYP = 'BURA'
AND NOT EXISTS
(SELECT *
FROM OA1T.TDM001A1 DM
WHERE NDEP = DM001006);
//
SELECT CKAT,SUBSTR(';',1,1),CTYP,
SUBSTR(';',1,1),CURRENT TIMESTAMP
FROM OA1T.TWM004A1
WHERE D$GUEB > CURRENT TIMESTAMP
AND CKAT <> 'ZZZZ'
ORDER BY 1,2;
//
SELECT COUNT (*)
FROM OA1T.TWM005A1 WM
,OA1T.TCD100A1 CD
WHERE WM.CTYP = 'BUAL'
AND WM.D$GUEB > CURRENT TIMESTAMP
AND WM.NCIF = CD.CD100001
AND CD.CD100004 IN ('0020','0021','0023','0041',
'0050','0051','0099');
//
SELECT WM.NCIF,' ',DM.DM001110
SELECT *
FROM OA1T.TWM005A1 WM
,OA1T.TDM001A1 DM
WHERE NDEP > ' '
//
SELECT NCIF
,SUBSTR(';',1,1)
,NDEP
FROM OA1T.TWM005A1
WHERE D$GUEA <= CURRENT TIMESTAMP
AND D$GUEB >= CURRENT TIMESTAMP
AND CSTAT BETWEEN 'A' AND 'A'
AND CDEFAULT BETWEEN 'C' AND 'C'
AND CTYP = 'EG13';
//
ORDER BY NCIF;
SELECT A.NDEP
FROM OA1T.TWM005A1 A
,OA1T.TWM005A1 B
WHERE A.D$GUEB > CURRENT TIMESTAMP
AND A.CTYP = 'BUAL'
AND A.NDEP
(SELECT *
FROM OA1T.TWM004A1 A
WHERE A.D$GUEB > CURRENT TIMESTAMP
AND A.NDEP = B.NDEP)
GROUP BY B.CTYP;
//
SELECT *
FROM OA1T.TWM005A1
WHERE CTYP = 'JFDD'
AND D$GUEB > CURRENT TIMESTAMP;
//
SELECT *
FROM OA1T.TWM005A1 A1
WHERE A1.CTYP ='BUCA'
AND A1.D$GUEB > CURRENT TIMESTAMP
AND EXISTS
(SELECT * FROM OA1T.TWM005A1 A2
WHERE A2.D$GUEB > CURRENT TIMESTAMP
AND A2.NDEP = A1.NDEP
AND A2.CTYP = 'BUCP');
//
SELECT *
FROM OA1T.TWM005A1
WHERE CTYP ='PSNI'
AND D$GUEB > CURRENT TIMESTAMP
AND NOT EXISTS
(SELECT * FROM OA1T.TDM001A1
WHERE NDEP = DM001006);
//
SELECT * FROM OA1T.TWM005A1
WHERE TGRUND = 'INITIAL LOAD SAVAS'
AND CTYP = 'SAP3'
AND DATE(D$ERF) = '06.10.2009';
//
SELECT *
FROM OA1T.TWM005A1
WHERE D$GUEB > CURRENT TIMESTAMP
AND CTYP <>'BURA'
AND NDEP IN
('0065064131075000',
'0835063006595000');
//
SELECT C.KAT1,C.TYP1,CHAR(DECIMAL(C.CNT1)) AS CNT2
FROM
(SELECT DISTINCT B.CKAT AS KAT1,A.TYP AS TYP1,A.CNT AS CNT1
FROM
(SELECT CTYP AS TYP
,COUNT(*) AS CNT
FROM OA1P.TWM005A1
WHERE D$GUEB > CURRENT TIMESTAMP
AND NDEP <> 'ZZZZ'
GROUP BY CTYP
ORDER BY CNT DESC) AS A
,OA1P.TWM005A1 AS B
WHERE A.TYP = B.CTYP) AS C
ORDER BY CNT2 DESC;
//
SELECT B.NDEP
FROM OA1P.TWM005A1 B
WHERE B.D$GUEB > CURRENT TIMESTAMP
AND B.CTYP = 'EG13'
AND EXISTS
(SELECT A.NDEP
FROM OA1P.TWM005A1 A
WHERE A.D$GUEB > CURRENT TIMESTAMP
AND A.CTYP = 'EG15'
AND A.NDEP = B.NDEP);
//
SELECT CHAR(COUNT(*))
FROM OA1P.TWM005A1
WHERE D$GUEB > CURRENT TIMESTAMP
AND CTYP = 'BUAL'
AND PERF = 'S107153'
AND DATE(D$LMUT) BETWEEN '01.10.2008' AND '31.10.2008'
GROUP BY CTYP
;
//
AND PERF NOT IN ('KC02','DM04','ROT')
SELECT DISTINCT SUBSTR(NVALOR,1,15)
,SUBSTR(';',1,1)
,SUBSTR(CTYP,1,4)
FROM OA1P.TWM005A1
WHERE D$GUEB > CURRENT TIMESTAMP
AND CKAT = 'INRE'
ORDER BY 1,2;
//
SELECT SUBSTR(CKAT,1,4)
,SUBSTR(';',1,1)
,SUBSTR(CTYP,1,4)
,SUBSTR(';',1,1)
GROUP BY CKAT,CTYP,NCIF,NDEP,NVALOR,TWMD1,D$LMUT
SELECT SUBSTR(B.CKAT,1,4)
,SUBSTR(';',1,1)
,SUBSTR(B.CTYP,1,4)
,SUBSTR(';',1,1)
,SUBSTR(B.CSS,1,6)
,SUBSTR(';',1,1)
,SUBSTR(B.CRC,1,5)
,SUBSTR(';',1,1)
,SUBSTR(VALUE(A1.TYP,' '),1,4)
FROM OA1P.TWM004A1 B
LEFT OUTER JOIN
(SELECT DISTINCT A.CTYP AS TYP
FROM OA1P.TWM006A1 A
WHERE A.D$GUEB > CURRENT TIMESTAMP) AS A1
ON B.CTYP = A1.TYP
WHERE B.D$GUEB > CURRENT TIMESTAMP;
//
AND CTYP = 'SAP3'
AND PERF = 'F623870';
SELECT NDEP,CHAR(COUNT(*)) AS Z
FROM OA1P.TWM005A1
WHERE D$GUEB > CURRENT TIMESTAMP
GROUP BY NDEP
ORDER BY Z DESC
SELECT CTYP, SUBSTR(' ',1,1),CHAR(COUNT(*))
FROM OA1P.TWM006A1
WHERE D$GUEB > CURRENT TIMESTAMP
GROUP BY CTYP;
;
//
SELECT B.CTYP, SUBSTR(' ',1,1),CHAR(COUNT(*))
FROM OA1P.TWM005A1 B
WHERE B.D$GUEB > CURRENT TIMESTAMP
AND B.NBU > ' '
AND B.CTYP IN
(SELECT A.CTYP
FROM OA1P.TWM004A1 A
WHERE A.D$GUEB > CURRENT TIMESTAMP
AND A.NBU_CF <> 'G')
GROUP BY B.CTYP;
//
SELECT NDEP,CHAR(COUNT(*)) AS Z
FROM OA1P.TWM005A1
WHERE D$GUEB > CURRENT TIMESTAMP
GROUP BY NDEP
ORDER BY Z DESC
;