Le portail francophone de la géomatique

SELECT MJ2_PARCEL.CCOCOM || '000' || MJ2_PARCEL.CCOSEC || MJ2_PARCEL.DNUPLA
          AS ID_PARCELLE,
       S1.BATIMENT,
       COUNT (S1."N° invariant") AS "Nb Lots de la copropriété"
  FROM    PLATINI.MJ2_PARCEL MJ2_PARCEL
       INNER JOIN
          (SELECT MJ2_BATI.INVAR AS "N° invariant",
                  MJ2_DESCPEV.DNUPEV AS "N° PEV",
                  MJ2_PROPRIO.DNUPRO AS "N° de propriétaire",
                  MJ2_LOCAL.JDATAT AS "Année de mutation",
                  MJ2_LOCAL.JANNAT AS "Année de construction",
                  MJ2_LOCAL.CCONLC AS "Nature du local",
                  MJ2_DESCPEV.DCAPEC AS "Catégorie",
                  MJ2_DESCPEV.DCETLC AS "Coeff d'entretien",
                  MJ2_HABPRINC.DNBPPR AS "Nb piéces principales",
                  MJ2_HABPRINC.DNBPDC AS "Nb piéces",
                  MJ2_HABPRINC.DSUPDC AS "surface des piéces (hors dépe)",
                  MJ2_BATI.DNUBAT AS batiment,
                  MJ2_BATI.D_ESC AS escalier,
                  MJ2_BATI.DNIV AS niveau,
                  MJ2_BATI.DPOR AS porte,
                  MJ2_BATI.DVOILIB AS voie,
                     MJ2_BATI.CCOCOM
                  || '000'
                  || MJ2_BATI.CCOSEC
                  || MJ2_BATI.DNUPLA
                     AS CODE_PARC,
                  CASE MJ2_DESCPEV.CCTHP
                     WHEN 'L' THEN 'Loué'
                     WHEN 'P' THEN 'PO'
                     WHEN 'V' THEN 'Vacant'
                     ELSE CCTHP
                  END
                     AS OCCUPATION,
                  MJ2_LOCAL.GPDL
             FROM    (   (   (   PLATINI.MJ2_LOCAL MJ2_LOCAL
                              INNER JOIN
                                 PLATINI.MJ2_BATI MJ2_BATI
                              ON (MJ2_LOCAL.INVAR = MJ2_BATI.INVAR))
                          INNER JOIN
                             PLATINI.MJ2_PROPRIO MJ2_PROPRIO
                          ON (MJ2_PROPRIO.CCOCOM = MJ2_BATI.CCOCOM)
                             AND (MJ2_LOCAL.DNUPRO = MJ2_PROPRIO.DNUPRO))
                      INNER JOIN
                         PLATINI.MJ2_DESCPEV MJ2_DESCPEV
                      ON (MJ2_DESCPEV.INVAR = MJ2_BATI.INVAR))
                  INNER JOIN
                     PLATINI.MJ2_HABPRINC MJ2_HABPRINC
                  ON (MJ2_DESCPEV.INVAR = MJ2_HABPRINC.INVAR)
                     AND (MJ2_DESCPEV.DNUPEV = MJ2_HABPRINC.DNUPEV)
            WHERE ( ( (MJ2_DESCPEV.DNUPEV = '001'
                       AND MJ2_DESCPEV.CCOAFF = 'H')
                     AND MJ2_PROPRIO.GTOPER = '1')
                   AND MJ2_LOCAL.GPDL <> '0')) S1
       ON (   MJ2_PARCEL.CCOCOM
           || '000'
           || MJ2_PARCEL.CCOSEC
           || MJ2_PARCEL.DNUPLA = S1.CODE_PARC)
GROUP BY S1.BATIMENT,
         MJ2_PARCEL.CCOCOM || '000' || MJ2_PARCEL.CCOSEC || MJ2_PARCEL.DNUPLA