SQL to work on employees

select MATCLE
from (select ROW_NUMBER() over
(order by (select NULL as noorder)) as RowNum, * from PRDHR.ZY00 where MATCLE like 'M%') as alias
where RowNum between 100 and 200

select b.CLASSI, c.IDESTA, d.CODCAI, e.LIBLON, e.TYCAIS, d.REGIME, f.LIBLON, COUNT(*)
from HR.ZY00 a, HR.ZYCA b, HR.ZY38 c, HR.ZYCS d,
(select za.CDCODE, zb.LIBLON, zc.NUCAIS, zc.TYCAIS from HR.ZD00 za, HR.ZD01 zb, HR.ZDCC zc
where za.NUDOSS = zb.NUDOSS 
and za.NUDOSS = zc.NUDOSS
and za.CDSTCO = 'DRK'
) e,
(select a.CDCODE, b.LIBLON, c.PRTRSQ from HR.ZD00 a, HR.ZD01 b, HR.ZD0B c
where a.NUDOSS = b.NUDOSS 
and a.NUDOSS = c.NUDOSS
and a.CDSTCO = 'DRX') f
where a.NUDOSS = b.NUDOSS
and a.NUDOSS = c.NUDOSS
and a.NUDOSS = d.NUDOSS
and b.DATFIN = '2999-12-31'
and c.DTEN00 = '2999-12-31'
and d.FINCAI = '2999-12-31'
and d.CODCAI = e.CDCODE
and e.TYCAIS in ('A', 'C', 'D') -- pour avoir uniquement l'ARRCO / AGIRC
and d.REGIME = f.CDCODE
group by b.CLASSI, c.IDESTA, d.CODCAI, e.LIBLON, e.TYCAIS, d.REGIME, f.LIBLON

-- Caisses et régimes par salarié
select a.MATCLE, b.CLASSI, c.IDESTA, d.CODCAI, e.LIBLON, e.TYCAIS, d.REGIME, f.LIBLON
from HR.ZY00 a, HR.ZYCA b, HR.ZY38 c, HR.ZYCS d,
(select za.CDCODE, zb.LIBLON, zc.NUCAIS, zc.TYCAIS from HR.ZD00 za, HR.ZD01 zb, HR.ZDCC zc
where za.NUDOSS = zb.NUDOSS 
and za.NUDOSS = zc.NUDOSS
and za.CDSTCO = 'DRK'
) e,
(select ya.CDCODE, yb.LIBLON, yc.PRTRSQ from HR.ZD00 ya, HR.ZD01 yb, HR.ZD0B yc
where ya.NUDOSS = yb.NUDOSS 
and ya.NUDOSS = yc.NUDOSS
and ya.CDSTCO = 'DRX') f
where a.NUDOSS = b.NUDOSS
and a.NUDOSS = c.NUDOSS
and a.NUDOSS = d.NUDOSS
and b.DATFIN = '2999-12-31'
and c.DTEN00 = '2999-12-31'
and d.FINCAI = '2999-12-31'
and d.CODCAI = e.CDCODE
and e.TYCAIS in ('A', 'C', 'D') -- pour avoir uniquement l'ARRCO / AGIRC
and d.REGIME = f.CDCODE

-- Liste des organismes
select a.CDCODE, b.LIBLON, c.NUCAIS, c.TYCAIS from HR.ZD00 a, HR.ZD01 b, HR.ZDCC c
where a.NUDOSS = b.NUDOSS 
and a.NUDOSS = c.NUDOSS
and a.CDSTCO = 'DRK'

-- Liste des régimes
select a.CDCODE, b.LIBLON, c.* from HR.ZD00 a, HR.ZD01 b, HR.ZD0B c
where a.NUDOSS = b.NUDOSS 
and a.NUDOSS = c.NUDOSS
and a.CDSTCO = 'DRX'

   select a.MATCLE, b.DATCON, b.DATFIN, b.TYPCON, c.LIBLON, b.NATCON, d.LIBLON
from PRDHR.ZY00 a, PRDHR.ZYCO b,
(select za.CDCODE, zb.LIBLON
 from PRDHR.ZD00 za, PRDHR.ZD01 zb
 where za.NUDOSS = zb.NUDOSS
 and za.CDSTCO = 'UIP') c,
 (select za.CDCODE, zb.LIBLON
 from PRDHR.ZD00 za, PRDHR.ZD01 zb
 where za.NUDOSS = zb.NUDOSS
 and za.CDSTCO = 'UIQ' and zb.CDLANG = 'F') d
where a.NUDOSS = b.NUDOSS
and b.TYPCON = c.CDCODE
and b.NATCON = d.CDCODE
--and b.NATCON in ('P1', 'P2')
and a.MATCLE like 'M%'