1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158
| IDENTIFICATION DIVISION.
PROGRAM-ID. calcah18.
*programme de calcul de l'ah18
ENVIRONMENT DIVISION.
CONFIGURATION SECTION.
SPECIAL-NAMES.
DECIMAL-POINT IS COMMA.
INPUT-OUTPUT SECTION.
FILE-CONTROL.
* declaratif du fichier de resultat
SELECT ficresah18 ASSIGN TO DISK.
SELECT ficstockcli ASSIGN TO DISK
ORGANIZATION INDEXED
ACCESS DYNAMIC
RECORD KEY idcli.
SELECT ficstockcliinsert ASSIGN TO DISK
ORGANIZATION INDEXED
ACCESS DYNAMIC
RECORD KEY idcli.
DATA DIVISION.
FILE SECTION.
FD ficresah18 LABEL RECORD STANDARD
VALUE OF FILE-ID "/wrk/calcah18.csv".
01 ENR-ficseq.
02 ficseq-age PIC 9(4).
02 sep1 PIC X value ";".
02 ficseq-qte Pic 9(6).
FD ficstockcli LABEL RECORD STANDARD
VALUE OF FILE-ID "/wrk/ficstockcli.csv".
01 ENR-ficix.
02 idcli PIC X(10).
02 sep1 PIC X value ";".
02 age Pic 9(4).
02 sep2 PIC X value ";".
02 qte Pic 9(6).
WORKING-STORAGE SECTION.
* bibliotheque sql pour cobol
Exec sql include sqlca end-exec.
exec sql include '/fr/cobol/fichier/client' end-exec.
exec sql include '/fr/cobol/fichier/comande1' end-exec.
* debut des declaratifs sql
Exec sql begin declare section end-exec.
* wdbase pour ouverture bdd et environnement de travail
01 wdbase pic x.
* pour avoir message erreur avec debugage
01 ws-erreur pic -(6)9.
01 where-error pic x(72).
01 wscli pic X(8) value space.
01 CODE-RET pic s9(9) comp-5 value 0.
* fin de declaratif sql
Exec sql end declare section end-exec.
* declaratif des variables autres que sql
* debut du programme
PROCEDURE DIVISION.
GENERAL.
* ouverture d'environnement de la bdd
MOVE "R" TO WDBASE.
call "opendb" using wdbase.
OPEN output ficresah18.
OPEN output ficstockcli.
* selection des clients avec age et quantite a 1 par defaut
exec sql declare CurseurB cursor for
select
distinct pcle into :idcli ,
trunc(
(
(to_date('20181001','YYYYMMDD')-to_date(pdatnais,'YYYYMMDD'))
/365.25)
,0
) into :age ,
1 into :qte
from fr.client
inner join fr.comande1 on comande1.c1clte = client.pcle
where c1datcde between 20180701 and 20181231
and substr(pdatnais,7,2) not like '00'
and substr(pdatnais,5,2) not like '00'
and substr(pdatnais,1,4) != 9999
and substr(pdatnais,1,4) > 1900
and Pnom3 not like '%DBL%'
and trunc(
(
(to_date('20181001','YYYYMMDD')-to_date(pdatnais,'YYYYMMDD'))
/365.25)
,0
) > 80
group by trunc(
(
(to_date('20181001','YYYYMMDD')-to_date(pdatnais,'YYYYMMDD'))
/365.25)
,0
)
order by trunc(
(
(to_date('20181001','YYYYMMDD')-to_date(pdatnais,'YYYYMMDD'))
/365.25)
,0
) asc
END-EXEC.
exec sql open CurseurB end-exec.
exec sql fetch CurseurB into :ENR-ficix end-exec.
move pcle to idcli.
move trunc(
(
(to_date('20181001','YYYYMMDD')-to_date(pdatnais,'YYYYMMDD'))
/365.25)
,0
) to age.
move 1 to qte.
write ENR-ficix before 1.
* fichier de sortie avec le nombre de client par age
exec sql declare CurseurC cursor for
select age , count(qte)
from ficstockcli
group by age
order by age as
END-EXEC.
exec sql open CurseurC end-exec.
exec sql fetch CurseurC into :ENR-ficseq end-exec.
move age to ficseq-age.
move count(qte) to ficseq-qte.
write ENR-ficix before 1.
FIN-GENERAL.
CLOSE ficresah18 ficstockcli.
STOP RUN. |
Partager