ZDDRX Les régimes de cotisation

select a.cdcode "Régime",
replace(b.liblon,'**','NPU ') "Libellé régime",
c.cdrubr "Code rubrique",
replace(d.liblon,'**', 'NPU ') "Libellé rubrique",
c.cdregr "Regroupement",
c.dadeb "Date début",
c.dafin "Date fin",
c.cdform "Code format",
c.memo01 "Mémo 01"
from zd00 a, zd01 b, zdef c,
(select za.cdcode, zb.liblon from zd00 za, zd01 zb where za.nudoss = zb.nudoss and za.cdstco = 'DRC' and zb.cdlang = 'F' ) d
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.cdstco = 'DRX'
and c.cdrubr = d.cdcode
--and a.cdcode in ('HCA', 'ALZ')
order by a.cdcode
;

select a.cdcode "Code",
replace(b.liblon, '**', 'NPU ') "Libellé",
c.codcai "Code contractant",
c.typreg "Type de régime de cotisation",
e.liblon "Libellé type régime",
c.numtec "Code du contrat d'adhésion",
d.liblon "Libellé contrat adhésion",
c.retsal "Modalité d'affiliation - Code option",
c.poprat "Modalité d'affiliation - Pop. rattacht"
from zd00 a, zd01 b, zd0b c,
(select za.cdcode, zb.liblon from zd00 za, zd01 zb where za.nudoss = zb.nudoss and za.cdstco = 'DFG' and zb.cdlang = 'F') d,
(select za.cdcode, zb.liblon from zd00 za, zd01 zb where za.nudoss = zb.nudoss and za.cdstco = 'UIU' and zb.cdlang = 'F') e
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.cdstco = 'DRX'
and b.cdlang = 'F'
and c.numtec = d.cdcode(+)
and c.typreg = e.cdcode(+)
order by a.cdcode ;

select a.CDCODE, b.LIBLON, c.CDRUBR, d.LIBLON, c.DADEB, c.DAFIN, c.CDFORM, c.NBRBAS, c.MTSALA, c.MTPATR
from QUAHR.ZD00 a, QUAHR.ZD01 b, QUAHR.ZDEF c,
(select za.CDCODE, zb.LIBLON from QUAHR.ZD00 za, QUAHR.ZD01 zb where za.NUDOSS = zb.NUDOSS and za.CDSTCO = 'DRC' and zb.CDLANG = 'F') d
where a.CDSTCO = 'DRX' and a.CDCODE in ('P10', 'P11', 'P12')
and a.NUDOSS = b.NUDOSS and a.NUDOSS = c.NUDOSS
and c.CDRUBR = d.CDCODE
and c.DAFIN = '2999-12-31'