It worked a treat Kolusu, as usual. Thanks much. Today I will test it on another input (adjusting what it need to be adjusted of course). Thanks again.
But it's not clear why you need 2nd test (GT than blank, so you might not have blank there?) and put BBBBBBBB
Final output is out of sync, because I have only 2/3 of the first block, then the first line is completed with 1/3 of the 2nd block and so on. If after Step0100 I completely remove first block (9 lines), final output is perfect, but I am still trying to understand why
Thanks
Joined: 26 Nov 2002 Posts: 12357 Topics: 75 Location: San Jose
Posted: Wed May 17, 2017 11:14 am Post subject:
Fab,
The reason I had to use the A and B with 2 IFTHEN statements was due to the fact that you actually sent the input with the first page missing the header '1page' and your input directly started with the column names. So I had to handle the first missing '1page' header. If your input always has the page header then you do not need the 2 IFTHEN statements.
In the new input you have the '1page' header back again and your 2nd and 3rd group got the same ID of 'B' which messed the resize.
So if your input always begins with page header then here are the updated control cards.
I also optimized the job to dynamically pass the FIRST COLUMN as a symbolic so that you actually only have to change the control cards in one place and not bother about missing any other place.
Here is the updated step0100 control cards, the rest of the job does not need any changes.
Code:
// SET FCOLUMN='DATA_ESTRAZIONE'
//*************************************************************
//* TAG THE RECORDS TO GROUPS AND SORT BASED ON GROUP NUMBER **
//*************************************************************
//STEP0100 EXEC PGM=SORT,PARM='JP1"&FCOLUMN"'
//SYSOUT DD SYSOUT=*
//SORTIN DD DISP=SHR,DSN=Your Input DSNTEP2 DSN
//SORTOUT DD DSN=&&TMP,DISP=(,PASS),SPACE=(CYL,(25,25),RLSE)
//SYSIN DD *
OPTION COPY
INCLUDE COND=(001,005,CH,EQ,C'1PAGE',OR,
001,133,SS,EQ,C'_|',OR,
001,133,SS,EQ,JP1)
Hi Kolusu,
yes header was removed because it starts with the query executed, basically it was my fault, I deleted everything before beginning of data, 1st 1PAGE included. Now with header back again your sort works perfectly with any input, and it is parametric. Perfect. Thanks much Kolusu.
One more thing about this header, I have seen this morning that if query is beyond 1 page, these sorts behave wrong, example:
Code:
1PAGE 1
-- RICHIESTA ESTRAZIONE PER DATI DA ESPORRE SU GAZZETTA UFFICIALE
-- PER CARTO VOBG6
-- ESTRAZIONE FINANZIAMENTI IN ESSERE AL 31.12.2016
-- AVENTI 7 CONVENZIONI ACCORDO QUADRO DEL GRUPPO
-- RICHIESTA :
-- VEDI EMAIL DA xxxxxx GIOVED~ 18 MAGGIO 2017 10:28
-- VEDI EMAIL DA xxxxxxxxxxx GIOVED~ 18 MAGGIO 2017 10:04
-- xxxxxxx xxxxx 18/5/2017
--
***INPUT STATEMENT:
SELECT A.CD_FNZ,A.PR_FNZ,
A.CD_CVZ AS CODICE_CONVENZIONE,
B.DT_ERO ,
CD_FNZ_STA, CASE CD_FNZ_STA
WHEN '00CEN' THEN 'CENSITO '
WHEN '05REQ' THEN 'RICHIESTO '
WHEN '10ANN' THEN 'ANNULLATO '
WHEN '15PRP' THEN 'PROPOSTO PER DELIBERA '
WHEN '20DEL' THEN 'DELIBERATO '
WHEN '25PFN' THEN 'IN PRE-FINANZIAMENTO '
WHEN '30STP' THEN 'STIPULATO '
WHEN '35ERO' THEN 'EROGATO '
WHEN '40PAM' THEN 'IN PRE-AMMORTAMENTO '
WHEN '45AMT' THEN 'IN AMMORTAMENTO '
WHEN '55DUB' THEN 'DUBBIO ESITO '
WHEN '60ICA' THEN 'INCAGLIATO '
WHEN '65SOF' THEN 'IN SOFFERENZA '
WHEN '70SOS' THEN 'SOSPESO '
WHEN '90SCDNOESZ' THEN 'SCADUTO NON ESTINTO '
WHEN '95ESZ' THEN 'ESTINTO '
WHEN '97REVOCA' THEN 'REVOCATO '
WHEN '98ANN' THEN 'ANNULLO LOGICO '
WHEN '99RCLPNO' THEN 'ATTESA RICALCOLO PIANO'
ELSE 'DESCRIZIONE STATO NON TROVATA'
END CASE,
CD_FNZ_STA_MOT, CASE CD_FNZ_STA_MOT
WHEN '0000DEL ' THEN 'DELIBERATO '
WHEN '0003CDZ ' THEN 'CONDIZIONATO '
WHEN '0010PFNREQ' THEN 'RICHIESTA CONTEGGIO EROGAZIONE CONTESTUALE '
WHEN '0015PFNCNF' THEN 'CONFERMA CONTEGGIO EROGAZIONE CONTESTUALE '
WHEN '0020PFNAZZ' THEN 'AUTORIZZAZIONE CONTEGGIO EROGAZIONE CONTESTUALE'
WHEN '0030REQSTP' THEN 'RICHIESTA CONTEGGIO STIPULA '
WHEN '0090STP ' THEN 'STIPULATO '
WHEN '0100EST ' THEN 'EROGAZIONE CONTESTUALE '
WHEN '0105ESTREQ' THEN 'RICHIESTA CONTEGGIO EROGAZIONE CONTESTUALE '
WHEN '0110ESTCNF' THEN 'CONFERMA CONTEGGIO EROGAZIONE CONTESTUALE '
WHEN '0115ESTAZZ' THEN 'AUTORIZZAZIONE EROGAZIONE CONTESTUALE '
WHEN '0120ESTAZS' THEN 'AUTORIZZAZIONE EROGAZIONE DA SVINCOLARE '
WHEN '0125ESTSVN' THEN 'RICHIESTA SVINCOLO '
WHEN '0130ESTSVA' THEN 'AUTORIZZAZIONE SVINCOLO '
WHEN '0150ERTAZZ' THEN 'AUTORIZZAZIONE EROGAZIONE TECNICA '
WHEN '0160ERSREQ' THEN 'RICHIESTA CONTEGGIO EROGAZIONE A SALDO '
WHEN '0165ERSCNF' THEN 'CONFERMA CONTEGGIO EROG. A SALDO '
WHEN '0170ERSAZZ' THEN 'AUTORIZZAZIONE EROGAZIONE A SALDO '
WHEN '0305ESAREQ' THEN 'RICHIESTA CONTEGGIO ESA '
WHEN '0315ESAAZZ' THEN 'AUTORIZZAZIONE ESA '
WHEN '0900RIFBCA' THEN 'REVOCA ISTITUTO '
WHEN '0905RIFCLI' THEN 'RINUNCIA CLIENTE '
WHEN '0910SDF ' THEN 'SUDDIVISIONE FINANZIAMENTO '
1PAGE 2
WHEN '0912UTZREQ' THEN 'RICHIESTA DI UTILIZZO '
WHEN '0917SDS ' THEN 'SUDDIVISIONE A SALDO '
WHEN '0920ICA ' THEN 'PASSAGGIO AD INCAGLIATO '
WHEN '0925RNTICA' THEN 'RIENTRO DA INCAGLIATO '
WHEN '0940SOF ' THEN 'PASSAGGIO A SOFFERENZA '
WHEN '0941RNTSOF' THEN 'RIENTRO DA SOFFERENZA '
WHEN '0945ESZSOF' THEN 'IN SOFFERENZA CON RISOLUZIONE '
WHEN '0950RNC ' THEN 'RINUCIA STIPULA '
WHEN '0960ESZ ' THEN 'ESTINZIONE ANTICIPATA '
WHEN '0961ESZSUR' THEN 'ESTINZIONE SURROGA PASSIVA '
WHEN '0970FRA ' THEN 'FRAZIONAMENTO '
WHEN '0980ERDIGD' THEN 'ARCHIVIAZIONE PRATICA '
WHEN '0983ESZPAG' THEN 'ESTINTO PER PAG. ULT. RATA '
WHEN '0984ESZPRR' THEN 'ESTINTO PER PROROGA '
WHEN '180SPSRAT ' THEN 'INSERIMENTO RATA DI SPESA '
ELSE 'DESCRIZIONE MOTIVO STATO NON TROVATA'
END CASE,
A.DT_INI_VLD_STA_FNZ AS DATA_STATO,
A.FL_FNZ_BLK AS CARTO_SI_NO
FROM DB2A0.TBFNZ A, DB2A0.TBERODFNFN B
WHERE A.CD_BCA='01005'
AND A.CD_FNZ='CF'
AND ((B.DT_ERO < '01.01.2017'
AND CD_FNZ_STA ^='95ESZ' AND A.DT_INI_VLD_STA_FNZ < '01.01.2017')
OR
(A.CD_FNZ_STA = '95ESZ' AND A.DT_INI_VLD_STA_FNZ > '31.12.2016'))
AND A.CD_CVZ IN ('BNPP-PF01','DIP-BNPP01',
'BNPP-PF02','DIP-BNPP02',
'BNPP-PF03','DIP-BNPP03',
'DIP-BNPP04')
AND A.CD_BCA=B.CD_BCA
AND A.CD_FNZ=B.CD_FNZ
AND A.PR_FNZ=B.PR_FNZ
AND B.DT_CTE_SRN='31.12.9999'
I thought that it was fine, header it's there, we have 3 blocks so each line will be 3*131 = 393. At the end of step0100 file is elaborated as follows:
As you can see first block is calculated wrong (well I've checked that all blocks are calculated in the same way, first two parts are calculated as a bigger one). Final line's width is wrongly calculate to 262, instead that 393.
I already used this jcl many times this morning and it was perfect in every run, even with 5 block (each line 131*5). I Wonder why in this case we have wrong calculation. The only difference I see is that query is longer than the other ones I've used and that ugly 3rd block full of blanks. Did I edited the input in a wrong way?
Thanks
Last edited by Fab on Thu May 18, 2017 9:14 am; edited 1 time in total
Joined: 26 Nov 2002 Posts: 12357 Topics: 75 Location: San Jose
Posted: Thu May 18, 2017 4:18 pm Post subject:
Fab,
I am done working on this moving target request. This is my final stab at it. I ran this job with all the different input you have shown so far and everything worked. So if you come up with a different variation, I am not going any spend time to refit the same solution. I actually could have written a COBOL within an hour to handle this kind of request.
Important : The first column to be passed a parm needs a space at the end This is done to avoid any false hits that may stem from the SELECT statement.
Code:
// SET FCOLUMN='CD_FNZ '
//*************************************************************
//* TAG THE RECORDS TO GROUPS AND SORT BASED ON GROUP NUMBER **
//*************************************************************
//STEP0100 EXEC PGM=SORT,PARM='JP1"&FCOLUMN"'
//SYSOUT DD SYSOUT=*
//SORTIN DD DISP=SHR,DSN=KOLUSU.DSNTEP2.OUTPUT.FAB4
//SORTOUT DD DSN=&&TMP,DISP=(,PASS),SPACE=(CYL,(25,25),RLSE)
//SYSIN DD *
OPTION COPY
INCLUDE COND=(001,005,CH,EQ,C'1PAGE',OR,
001,133,SS,EQ,C'_|',OR,
001,133,SS,EQ,JP1)
All times are GMT - 5 Hours Goto page Previous1, 2, 3
Page 3 of 3
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum