ZDDRE Le répertoire des établissements

select a.cdcode, b.liblon,
c.cdsire || c.cdnics as "SIRET",
d.idcy00,
e.liblon
from zd00 a, zd01 b,  zdag c, zd39 d,
(select za.cdcode, zb.liblon from zd00 za, zd01 zb where za.nudoss = zb.nudoss and za.cdstco = 'UAA') e
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.nudoss = d.nudoss
and a.cdstco = 'DRE'
and d.idcy00 = e.cdcode ;

select d.extc28, c.idcy00, f.liblon, a.cdcode, b.liblon, e.zonadc
from zd00 a, zd01 b, zd39 c, zd9u d, zd0f e,
( select xa.cdcode, xb.liblon from zd00 xa, zd01 xb where xa.nudoss = xb.nudoss and xa.cdstco = 'UAA') f
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.nudoss = d.nudoss
and a.nudoss = e.nudoss
and a.cdstco = 'DRE'
and c.idcy00 = f.cdcode(+)
order by d.extc28, c.idcy00, a.cdcode ;