sql Complexe

 

select      distinct

EXPLOITANT.TYPE_INTERVENANT,  rq_notation_vue_sig.annee, COMMUNE_ASSAINISSEMENT.ANNEEREFERENCEASSAINISSEMENT ,

STATION.CODE_STATION,

COMMUNE.NUMINSEE,

COMMUNE.NOMCOM as Commune,

COMMUNE_ASSAINISSEMENT.NbAbonnesEauPotable as "Eau_potable" ,

COMMUNE_ASSAINISSEMENT.NbAbonnesAssainissement as "Assainissement_collectif" ,

COMMUNE_ASSAINISSEMENT.NbAbonnesEauPotable - COMMUNE_ASSAINISSEMENT.NbAbonnesAssainissement as "Assainissement_non_collectif" ,

Case

When   CodeCommune  IS NOT NULL

Then 'oui'

Else ''

End   as "Commune_100_ANC",

COMMUNE.NOMCOM || ' / ' || station.local_stat as  "Implantation_station",

type_epur.type_epuration as "Filiere_traitement",

STATION.CNEQH   as "Capacite_EH",

  case

  When  RQ_NOTATION_VUE_SIG.NOTATION_STEP is not Null  and  to_char( station.date_mes,'yyyy') <>  to_char(sysdate,'yyyy')  and RQ_NOTATION_VUE_SIG.NOTATION_STEP >=12

  Then 'Bon'

    When  RQ_NOTATION_VUE_SIG.NOTATION_STEP is not Null and  to_char( station.date_mes,'yyyy') <>  to_char(sysdate,'yyyy') and RQ_NOTATION_VUE_SIG.NOTATION_STEP >=10 and RQ_NOTATION_VUE_SIG.NOTATION_STEP <12 

  Then 'Moyen'

    When  RQ_NOTATION_VUE_SIG.NOTATION_STEP is not Null and  to_char( station.date_mes,'yyyy') <>  to_char(sysdate,'yyyy') and  RQ_NOTATION_VUE_SIG.NOTATION_STEP >=8 and RQ_NOTATION_VUE_SIG.NOTATION_STEP <10

  Then 'Mauvais'

    When  RQ_NOTATION_VUE_SIG.NOTATION_STEP is not Null and   to_char( station.date_mes,'yyyy') <>  to_char(sysdate,'yyyy') and  RQ_NOTATION_VUE_SIG.NOTATION_STEP < 8

  Then 'Très mauvais'

   When  RQ_NOTATION_VUE_SIG.NOTATION_STEP is  Null

  Then 'Données insuffisantes'

    else  

   'Nouvelle'

  end

  As "Fonctionnement_station",

  case

  when type_filieresboue.id_filiereboue in (2,15,16,3,1,30,29,27,43,33,34,4,31,18,24,36,54)

  then

  'DESHYDRATATION MECANIQUE'

 when type_filieresboue.id_filiereboue in (46,47,8,11,13,22,23,56)

  then

  'BOUES LIQUIDES'

   when type_filieresboue.id_filiereboue in (9,10,37,38)

  then

  'DESHYDRATATION NATURELLE'

    else

    type_filieresboue.libelle_filiereboue

    end

   as "Traitement_boues",

   case

  when type_filieresboue.id_filiereboue in (29,30,16,43,33,34,40,21,19,48,52,54)

  then

  'CHAULAGE'

 when type_filieresboue.id_filiereboue in (2)

  then

  'SECHAGE THERMIQUE'

   when type_filieresboue.id_filiereboue in (15)

  then

  'SECHAGE SOLAIRE'

    else

    ''

    end

    as "Conditionnement_boues" ,

   case

   When    DESTINATION_BOUE.CODE_DESTINATION='C'

   then  'Compostage'

      When    DESTINATION_BOUE.CODE_DESTINATION ='E2'

   then  'Centre d''enfouissement technique'

      When    DESTINATION_BOUE.CODE_DESTINATION ='D'

   then  'AUTRE'

      When    DESTINATION_BOUE.CODE_DESTINATION ='I'

   then  'Incinération'

      When   DESTINATION_BOUE.CODE_DESTINATION ='S'

     then  'AUTRE'

    When   DESTINATION_BOUE.CODE_DESTINATION='V' or   DESTINATION_BOUE.CODE_DESTINATION ='VR'

   then  'RECYCLAGE AGRONOMIQUE '

     else

     'Destination inconnue, production inconnue ou nulle'

   end

   as "Destination_Principale",

   intervenant.nom_intervenant,

   to_char(intervenant.adresse_documents ) as "Adresse_Maitre_ouvrage",

   intervenant.telephone_intervenant ,

   exploitant.nom_intervenant as "Exploitant",

   to_char( exploitant.adresse_documents) as "Adresse_Exploitant",

   exploitant.telephone_intervenant as "Telephone_Exploitante"

 from

 

  COMMUNE left outer join

  COMMUNE_ASSAINISSEMENT on (COMMUNE_ASSAINISSEMENT.ID_COMMUNE = COMMUNE.CODE_COMMUNE)

Left outer join (

 Station Left outer join

(

   /*les stations et les communes raccordé a ces station*/

     select

     SYSTEMEDECOLLECTE.code_station as codeStation,

     SYSTEMEDECOLLECTERACCORD.ID_OUVRAGE as CodeCommune

     from   SYSTEMEDECOLLECTE inner join SYSTEMEDECOLLECTERACCORD on (SYSTEMEDECOLLECTE.ID_SCL = SYSTEMEDECOLLECTERACCORD.ID_SYSTEMEDECOLLECTE)

)  on (codeStation=Station.code_station)

left outer join RQ_NOTATION_VUE_SIG on (codeStation = RQ_NOTATION_VUE_SIG.code_station)

left outer join (

                  SYNTHESE_DESTINATION  inner join

                  DESTINATION_BOUE on (SYNTHESE_DESTINATION.ID_DESTINATION=DESTINATION_BOUE.ID_DESTINATION)

                )on  (codeStation = SYNTHESE_DESTINATION.CODE_STATION)

left outer join type_filieresboue on (station.id_filiereboue = type_filieresboue.id_filiereboue)

left outer join Intervenant on (station.code_maitreoeuvre = INTERVENANT.ID_INTERVENANT)

left outer join Intervenant Exploitant on (station.code_maitreouvrage = Exploitant.ID_INTERVENANT )

left outer join TYPE_EPUR on (station.CODE_EPURATION=TYPE_EPUR.CODE_EPURATION)             

                )

/*cette condition Faute jointure sur la station D'implantation et pas sattion raccordé on (STATION.CODE_COMMUNE=COMMUNE.CODE_COMMUNE)*/

on (CodeCommune=COMMUNE.CODE_COMMUNE)

 where  

 /*filtre Annee*/

 

    (COMMUNE_ASSAINISSEMENT.ANNEEREFERENCEASSAINISSEMENT LIKE  (to_char(sysdate,'yyyy')-1) or COMMUNE_ASSAINISSEMENT.ANNEEREFERENCEASSAINISSEMENT is NULL)

    and (rq_notation_vue_sig.annee LIKE   (to_char(sysdate,'yyyy')-1) or rq_notation_vue_sig.annee is Null)

    and

  /*filtre Station en exploitation seulement et comminale*/

   STATION.STATUT =0

  and (EXPLOITANT.TYPE_INTERVENANT =1 or EXPLOITANT.TYPE_INTERVENANT is Null)

 and trim(to_char(station.date_mes,'yyyy')) is  Not Null

 and trim(to_char(station.date_mhs,'yyyy')) is Null

 order by commune.nomcom;