Requête - Fusion avec condition

**QuestVba** · 29/06/2020, 14h25

Bonjour le Forum

,

J'ai deux requêtes.

La première me permet de récupérer deux colonnes :

Le NISS des personnes étant inscrites officiellement en Belgique.
Le Code Postal

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
select 
  P_OFF.INSS as INSS_OFF
  , nvl(A_OFF.STREET_ZIPCODE,A_OFF.ZIPCODE) AS ZIP_OFF
from 
  PERSONS P_OFF
  , PERIODIC_ADDRESSES PA_OFF
  , ADDRESSES A_OFF
where 
  P_OFF.PERSON_ID = PA_OFF.OFF_ADDR_PERSON_ID
  and PA_OFF.ADDRESS_TYPE = 'OFFICIAL'
  and CURRENT_DATE between PA_OFF.START_DATE and nvl(PA_OFF.END_DATE,'31/12/9999')
  and PA_OFF.ADDRESS_ID = A_OFF.ADDRESS_ID
  and A_OFF.type = 'STRUCTURED'

La seconde me permet de récupérer deux colonnes :

Le NISS des personnes étant inscrites fonctionnellement en Belgique.
Le Code Postal

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
select
    INSS_FUNC
    , ZIP_FUNC
from
(
select  P_FUNC.INSS AS INSS_FUNC
  , nvl(A_FUNC.STREET_ZIPCODE,A_FUNC.ZIPCODE) AS ZIP_FUNC
  , PA_FUNC.START_DATE
  , row_number() over(partition by P_FUNC.INSS order by PA_FUNC.START_DATE desc) as rn
  from
 
  PERSONS P_FUNC
  , PERIODIC_ADDRESSES PA_FUNC
  , ADDRESSES A_FUNC
where 
  P_FUNC.PERSON_ID = PA_FUNC.FUN_ADDR_PERSON_ID
  and PA_FUNC.ADDRESS_TYPE = 'FUNCTIONAL'
  and CURRENT_DATE between PA_FUNC.START_DATE and nvl(PA_FUNC.END_DATE,'31/12/9999')
  and PA_FUNC.ADDRESS_ID = A_FUNC.ADDRESS_ID
  and A_FUNC.type = 'STRUCTURED'
)
where rn=1
;

Pouvez-vous me dire comment je peux fusionner ces deux listes en gardant toutes les données de la liste 1 et en prenant les données de la liste 2 si le NISS de cette liste n'est pas présent dans la liste 1 ?
Ai-je bien été clair ?

**tbc92** · 29/06/2020, 14h50

J'imagine que si une personne est présente dans les 2 listes, elle a le même identifiant INSS. Sinon, on ne peut pas s'en sortir.

Dans ta 2ème requête, tu veux uniquement les personnes 'supplémentaires', uniquement celles qui sont insrites fontionnellement en Belgique, mais pas officiellement.

Donc dans ta 2ème requête, tu peux ajouter une clause du type : and INSS_FUNC not in ( select INSS form persons )Vérifie déjà que cette nouvelle requete marche bien correctement, avec le nombre de lignes voulues et tout et tout.

Ensuite , avec UNION, tu peux faire un regroupement de la requête 1 et de la requête 2 modifiée.

**vanagreg** · 29/06/2020, 15h23

Bonjour,

Tu peux fusionner tes 2 requêtes en une seule et faire un PIVOT pour obtenir ces 2 résultats:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
with address_data
as (select P.INSS as INSS
          , nvl(A.STREET_ZIPCODE,A.ZIPCODE) AS ZIP
          , PA.ADDRESS_TYPE
          , PA.START_DATE
    from 
      PERSONS P
      , PERIODIC_ADDRESSES PA
      , ADDRESSES A
    where 
      P.PERSON_ID = case when PA.ADDRESS_TYPE = 'OFFICIAL' then PA.OFF_ADDR_PERSON_ID else PA.FUN_ADDR_PERSON_ID end
      and PA.ADDRESS_TYPE in ('OFFICIAL', 'FUNCTIONAL')
      and CURRENT_DATE between PA.START_DATE and nvl(PA.END_DATE,to_date('31/12/9999', 'dd/mm/yyyy'))
      and PA.ADDRESS_ID = A.ADDRESS_ID
      and A.type = 'STRUCTURED'
   )
 select inss, zip_off, zip_func
 from address_data
 pivot (max(zip) keep (dense_rank first order by start_date desc) for address_type in ('OFFICIAL' as zip_off, 'FUNCTIONAL' as zip_func))

**QuestVba** · 29/06/2020, 16h08

Envoyé par tbc92

.

En suivant ce que tu m'as dit, voici où j'arrive. Et c'est le bon résultat ! Thanks !

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
-- INSS WITH OFFICIAL ADDRESS
-- IN BELGIUM
select 
  P_OFF.INSS as INSS
  , nvl(A_OFF.STREET_ZIPCODE,A_OFF.ZIPCODE) AS ZIP
from 
  PERSONS P_OFF
  , PERIODIC_ADDRESSES PA_OFF
  , ADDRESSES A_OFF
where 
  P_OFF.PERSON_ID = PA_OFF.OFF_ADDR_PERSON_ID
  and PA_OFF.ADDRESS_TYPE = 'OFFICIAL'
  and CURRENT_DATE between PA_OFF.START_DATE and nvl(PA_OFF.END_DATE,'31/12/9999')
  and PA_OFF.ADDRESS_ID = A_OFF.ADDRESS_ID
  and A_OFF.type = 'STRUCTURED'
 
union
 
-- INSS WITH FUNCTIONAL ADDRESS
-- DELETE IF DBLE ADDRESS OPEN
-- IN BELGIUM
-- NOT in INSS_OFF
select
    INSS_FUNC
    , ZIP_FUNC
from
(
select  P_FUNC.INSS AS INSS_FUNC
  , nvl(A_FUNC.STREET_ZIPCODE,A_FUNC.ZIPCODE) AS ZIP_FUNC
  , PA_FUNC.START_DATE
  , row_number() over(partition by P_FUNC.INSS order by PA_FUNC.START_DATE desc) as rn
  from
 
  PERSONS P_FUNC
  , PERIODIC_ADDRESSES PA_FUNC
  , ADDRESSES A_FUNC
where 
  P_FUNC.PERSON_ID = PA_FUNC.FUN_ADDR_PERSON_ID
  and PA_FUNC.ADDRESS_TYPE = 'FUNCTIONAL'
  and CURRENT_DATE between PA_FUNC.START_DATE and nvl(PA_FUNC.END_DATE,'31/12/9999')
  and PA_FUNC.ADDRESS_ID = A_FUNC.ADDRESS_ID
  and A_FUNC.type = 'STRUCTURED'
 
  AND P_FUNC.INSS NOT IN (
    select 
        P_OFF.INSS as INSS_OFF
    from 
          PERSONS P_OFF
          , PERIODIC_ADDRESSES PA_OFF
          , ADDRESSES A_OFF
    where 
          P_OFF.PERSON_ID = PA_OFF.OFF_ADDR_PERSON_ID
          and PA_OFF.ADDRESS_TYPE = 'OFFICIAL'
          and CURRENT_DATE between PA_OFF.START_DATE and nvl(PA_OFF.END_DATE,'31/12/9999')
          and PA_OFF.ADDRESS_ID = A_OFF.ADDRESS_ID
          and A_OFF.type = 'STRUCTURED'
    )
)
where rn=1
;

**vanagreg** · 29/06/2020, 16h17

Là tu interroges 3 fois les mêmes tables. As-tu essayé la version PIVOT?

**QuestVba** · 29/06/2020, 18h16

Hello,

Envoyé par vanagreg

.

Un grand merci pour ta proposition.

J'a regardé ton sql

et j'avoue que je ne suis pas certain d'avoir bien compris la ligne pivot. Si tu peux m'expliquer un peu plus, ce serait cool. Le pivot permettrait de passer d'une colonne de zip à deux colonnes de zip ?
En ce qui concerne le max et le keep, je suppose que l'on prend le zip en fonction de la date et, à date égale, on prend le max des zip ?

Pour ton info, je te mets ce que je cherche à faire - ce sera plus facile : je dois compter, par Code Postal (ZIPCODE), le nombre de personnes qui sont inscrites de manière officielle ou de manière fonctionnelle et qui reçoivent un paiement.
En ce qui concerne l'inscription :

Soit la personne est inscrite officiellement et là, pas de soucis, car je n'ai qu'une seule adresse possible. Pas de chevauchement ou autre.
Soit la personne est inscrite fonctionnellement et là, je peux avoir plusieurs adresses ouvertes (car le gestionnaire s'est trompé dans la procédure) auquel cas je prends uniquement le ZIP de l'adresse la plus récente.
En plus : une personne est unique et ne possède qu'un seul INSS. Ouf ! Donc, elle peut avoir une inscription officielle et/ou une adresse fonctionnelle. Si on a une adresse officielle ou une adresse officielle et une adresse fonctionnelle, c'est toujours l'officielle qui prime. Sinon, on prend la plus récente des fonctionnelles s'il n'y a que cela. Donc, une même personne peut être connue dans une des deux listes ou dans les deux, mais toujours avec le même numéro dans le dernier cas.

En ce qui concerne le paiement, j'ai ce sql qui me liste tous les enfants (CHILD_2020) qui sont payés à une personne (BENEF_2020) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT DISTINCT
    per.inss AS child_2020
    ,per_al.inss AS benef_2020
FROM
    files fil
    INNER JOIN children chi ON chi.file_number = fil.file_number
    INNER JOIN legal_situation leg ON leg.child_id = chi.actor_id
        AND ((TO_NUMBER(TO_CHAR(CURRENT_DATE,'yyyymm')) - 1) between (leg.start_year * 100 + leg.start_month) AND NVL((leg.end_year * 100 + leg.end_month),999999))
        AND leg.status = 'ACTIVE'
    INNER JOIN childbeneficiarylinks cbl ON cbl.child_id = chi.actor_id
        AND ((TO_NUMBER(TO_CHAR(CURRENT_DATE,'yyyymm')) - 1) between (cbl.start_year * 100 + cbl.start_month) AND NVL((cbl.end_year * 100 + cbl.end_month),999999)) 
    INNER JOIN actors ac ON ac.actor_id = chi.actor_id
    INNER JOIN persons per ON ac.person_id = per.person_id
    INNER JOIN actors ac_al ON ac_al.actor_id = cbl.beneficiary_id
    INNER JOIN persons per_al ON ac_al.person_id = per_al.person_id
    left JOIN periodic_addresses pad on pad.FUN_ADDR_PERSON_ID = per_al.person_id
    left join contactpersons cpe on cpe.ADDRESS_ID = pad.address_id

Le but ultime est de lier les deux requêtes INSS (de la première) et BENEF_2020 (de la seconde) pour connaître le ZIP_CODE et ensuite de faire un Count par ZIP_CODE.

Je pense que tout est expliqué. Mais est-ce bien clair ?

**vanagreg** · 01/07/2020, 12h02

Bonjour,

Le PIVOT permet de convertir des lignes en colonnes. Puisque tu as 2 zips, le but c'était d'avoir une seule ligne avec les 2 zips. La ligne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

max(zip) keep (dense_rank first order by start_date desc)

permet de ne garder que le zip le plus récent, mais pour chacun des zips. Si le but est de ne garder que l'adresse officielle si trouvée, puis la plus récente des fonctionnelles sinon, alors dans la requête tu peux simplement faire dans le SELECT principal:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

select inss, nvl(zip_off, zip_func) zip

Si tu veux joindre le résultat à ta requête benef alors tu peux simplement placer le résultat du pviot dans un autre bloc with pour le référencer dans ton autre requête.

**QuestVba** · 02/07/2020, 16h52

Salut,

Envoyé par vanagreg

.

Un grand merci pour l'explication. Encore un peu moins bête. Cela devient une habitude
Autre bête question : où as-tu appris tout cela ? Tu aurais un lien ou autre.

**vanagreg** · 02/07/2020, 22h44

Envoyé par QuestVba

Autre bête question : où as-tu appris tout cela ? Tu aurais un lien ou autre.

Ca s'apprend avec les années et la motivation. Je suis régulièrement dans les forums Oracle (OTN et MOSC) car là aussi tu apprends beaucoup (mais c'est anglophone). La documentation officielle Oracle est assez bien faite aussi, et il y a des sites et blogs sérieux qui sont très informatifs.

**QuestVba** · 07/07/2020, 08h25

Salut,J'ai essayé dans tous les sens mais je n'arrive pas à mettre les deux sql ensemble ; où benef_2020 = INSS.
Comment mettre plusieurs With ou autres... ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT DISTINCT
    per.inss AS child_2020
    ,per_al.inss AS benef_2020
    --ZIPCODE
    --,count (per_al.inss) 
 
FROM
    files fil
    INNER JOIN children chi ON chi.file_number = fil.file_number
    INNER JOIN legal_situation leg ON leg.child_id = chi.actor_id
        AND ((TO_NUMBER(TO_CHAR(CURRENT_DATE,'yyyymm')) - 1) between (leg.start_year * 100 + leg.start_month) AND NVL((leg.end_year * 100 + leg.end_month),999999))
        AND leg.status = 'ACTIVE'
    INNER JOIN childbeneficiarylinks cbl ON cbl.child_id = chi.actor_id
        AND ((TO_NUMBER(TO_CHAR(CURRENT_DATE,'yyyymm')) - 1) between (cbl.start_year * 100 + cbl.start_month) AND NVL((cbl.end_year * 100 + cbl.end_month),999999)) 
    INNER JOIN actors ac ON ac.actor_id = chi.actor_id
    INNER JOIN persons per ON ac.person_id = per.person_id
    INNER JOIN actors ac_al ON ac_al.actor_id = cbl.beneficiary_id
    INNER JOIN persons per_al ON ac_al.person_id = per_al.person_id
    left JOIN periodic_addresses pad on pad.FUN_ADDR_PERSON_ID = per_al.person_id
    left join contactpersons cpe on cpe.ADDRESS_ID = pad.address_id

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
with address_data
as (select P.INSS as INSS
          , nvl(A.STREET_ZIPCODE,A.ZIPCODE) AS ZIP
          , PA.ADDRESS_TYPE
          , PA.START_DATE
    from 
      PERSONS P
      , PERIODIC_ADDRESSES PA
      , ADDRESSES A
    where 
      P.PERSON_ID = case when PA.ADDRESS_TYPE = 'OFFICIAL' then PA.OFF_ADDR_PERSON_ID else PA.FUN_ADDR_PERSON_ID end
      and PA.ADDRESS_TYPE in ('OFFICIAL', 'FUNCTIONAL')
      and CURRENT_DATE between PA.START_DATE and nvl(PA.END_DATE,to_date('31/12/9999', 'dd/mm/yyyy'))
      and PA.ADDRESS_ID = A.ADDRESS_ID
      and A.type = 'STRUCTURED'
   )
 select inss, nvl(zip_off, zip_func) zip
 from address_data
 pivot (max(zip) keep (dense_rank first order by start_date desc) for address_type in ('OFFICIAL' as zip_off, 'FUNCTIONAL' as zip_func))
;

**tbc92** · 07/07/2020, 09h05

Pour enchainer plusieurs with :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
with X1 as ( 
... 
) , 
X2 as ( 
...
) ,
X3 as (
...
)
select ... from X1,X2,X3

C'est la virgule après la éfinition de X1 ou de X2 qui dit qu'on va continuerà définir des pseudo-tables.
Dans la requete qui définit X2, on peut utiliser X1, et dans la requête qui définit X3, on peut utiliser X1 et/ou X2. C'est un système en cascade.

Pour l'utilisation de la clause PIVOT, je ne vois vraiment pas quel serait l'intérêt de cette clause pour ton besoin.

Certes, tu peux optimiser la requête, en utilisant WITH en particulier ... mais oublie PIVOT. Aucun intérêt ici.

**vanagreg** · 07/07/2020, 09h13

Envoyé par tbc92

Pour l'utilisation de la clause PIVOT, je ne vois vraiment pas quel serait l'intérêt de cette clause pour ton besoin.

Certes, tu peux optimiser la requête, en utilisant WITH en particulier ... mais oublie PIVOT. Aucun intérêt ici.

Pourquoi aucun intérêt? C'est quoi la meilleure solution?

**tbc92** · 07/07/2020, 11h50

La clause Pivot est apparue avec Oracle 11g. Disons que c'est récent.
Avant la 11g, tous les développeurs savaient parfaitement résoudre cette question, sans passer par 'Pivot'. Pivot, c'est bien quand on a une table, avec différentes valeurs (Janvier, Février... ) et qu'on veut une colonne par valeur. Ici la problématique est très différente.

Je ne sais pas ce qu'il en est avec MySQL, PostGre, etc etc, mais je me dis que cette syntaxe 'Pivot' n'est pas totalement standard. En terme de portabilité du code, on met un truc spécifique à Oracle, là où du standard donne le résultat voulu.

En d'autres mots, vu de ma fenêtre : pourquoi faire simple quand on peut faire compliqué.

Je ne vais pas développer plus, c'est un peu comme les questions de goûts et de couleurs, il y a un côté subjectif.

**vanagreg** · 07/07/2020, 12h32

La portabilité du code c'est une utopie. Tu ne tireras jamais profit de la puissance de la base de données si tu n'utilises pas ses fonctionnalités propres. Et Oracle se différencie entres autres de ses concurrents par ces fonctionnalités. Par exemple la possibilité de faire du CONNECT BY, du Outer Partition, d'utiliser la clause MODEL, et maintenant le MATCH_RECOGNIZE. Ce serait dommage de ne pas s'en servir juste pour être sûr de rester "standard SQL". Je suis quasi persuadé qu'aucune application utilise du SQL strictement standard. Donc on oublie aussi toutes les fonctions natives du genre TO_DATE, TO_CHAR, NVL, etc?

Par ailleurs, il me semble que PIVOT n'est pas propre à Oracle, mais je me trompe peut-être. En plus tu disais que "PIVOT n'avait aucun intérêt pour le besoin de QuestVba". Mais là on a plusieurs valeurs, 'OFFICIAL' et 'FONCTIONNAL'. Le PIVOT permet automatiquement d'avoir ces 2 valeurs sur la même ligne pour un INSS donné tout en gérant le fait d'avoir plusieurs occurrences car on prend le max. Ici on peut s'en sortir différemment mais ça reviendrait à interroger plusieurs fois les mêmes tables ce qui est à mon avis plus couteux en termes de performance.

**SQLpro** · 07/07/2020, 18h57

PIVOT n'est effectivement pas normalisé, mais présent dans différents SGBDR avec, hélas, des syntaxes différentes.
MATCH_RECOGNIZE fait par contre partie de la norme SQL (depuis peu).

Pour informations, Oracle travaille à rattraper son retard sur la norme depuis quelques années, du fait qu'ils perdent beaucoup de clients et qu'ils voudrait que certains, insatisfait d'autres SGBDR, reviennent dans leur girond... Quelques exemples et non des moindres parmis d'autres :

L'intégration des CTE, y compris récursives
Le support des collations en remplacement de l'horrible et incomplet NLS
Le support des noms longs (128 caractères) pour les identifiants SQL
Le support du multibase (norme SQL... hé oui !) avec un module payant pompeusement intitulé "multitenant"
L'arrivée de l'opérateur APPLY en complément de LATERAL
Intégration de tout un tas de fonctions et opérateurs normalisés comme COALESCE, CAST, CASE...

A +

**Ikebukuro** · 07/07/2020, 21h11

Envoyé par SQLpro

Pour informations, Oracle travaille à rattraper son retard sur la norme depuis quelques années, du fait qu'ils perdent beaucoup de clients et qu'ils voudrait que certains, insatisfait d'autres SGBDR, reviennent dans leur girond... Quelques exemples et non des moindres parmis d'autres :

Le support du multibase (norme SQL... hé oui !) avec un module payant pompeusement intitulé "multitenant"

Oh oui, belle avancée pour le DBA, d'un seul coup tout est beaucoup plus simple

Et Microsoft, avec SQL Server, ils ne pourraient pas upgrader SSMS, pour éviter notamment de devoir faire 10 refreshs par heure?
Tiens, un truc marrant aussi avec SSMS, j'ai créé une table, inséré des données, j'ai voulu, via les menus SSMS, générer les stats : KO! Il a fallu que je le fasse en T-SQL... je n'en suis toujours pas revenu

Enfin bon, si on veut un vrai SGBD, où le DBA a la main sur quasiment tout, on sait vers où se tourner!

**QuestVba** · 08/07/2020, 15h28

Hello,
Au-delà du petit débat, j'avoue que je reste avec mon problème.
J'ai bien compris que je devais tout intégrer dans un WITH mais je ne vois pas comment faire ? Surtout que j'ai déjà un premier WITH ; comment je peux l'intégrer dans mon WITH global ? Comment le placer comme X1 selon le schéma de tbc92 ?
Une petite piste supplémentaire, pour m'aiguiller...

**SQLpro** · 08/07/2020, 19h47

Envoyé par Ikebukuro

Oh oui, belle avancée pour le DBA, d'un seul coup tout est beaucoup plus simple

Et Microsoft, avec SQL Server, ils ne pourraient pas upgrader SSMS, pour éviter notamment de devoir faire 10 refreshs par heure?
Tiens, un truc marrant aussi avec SSMS, j'ai créé une table, inséré des données, j'ai voulu, via les menus SSMS, générer les stats : KO! Il a fallu que je le fasse en T-SQL... je n'en suis toujours pas revenu

Enfin bon, si on veut un vrai SGBD, où le DBA a la main sur quasiment tout, on sait vers où se tourner!

Vous confondez SSMS qui est un outil client avec SQL Server qui est un SGBDR....
Et les stats sont auto calculées par défaut dans SQL Server. Il est donc inutile de faire ce que vous avez fait !

A +

**SQLpro** · 08/07/2020, 19h50

Envoyé par QuestVba

Hello,
Au-delà du petit débat, j'avoue que je reste avec mon problème.
J'ai bien compris que je devais tout intégrer dans un WITH mais je ne vois pas comment faire ? Surtout que j'ai déjà un premier WITH ; comment je peux l'intégrer dans mon WITH global ? Comment le placer comme X1 selon le schéma de tbc92 ?
Une petite piste supplémentaire, pour m'aiguiller...

Un seul WITH, premier "mot" de la requête, qui indique que vous aller travailler avec une ou plusieurs expression(s) de table.
Pour la syntaxe et des exemples, lisez ce que j'ai écrit à ce sujet.
https://sqlpro.developpez.com/cours/...cursives/#LIII
Chaque expression de table pouvant référencer les expressions précédemment exprimées dans l'ordre de lecture.

C'est du SQL basique dans la norme SQL:1999... ça date de plus de vingt ans !!!!

Et c'est dans tous mes livres sur SQL dans la collection Synthex :
Nom : Couverture SQL Synthex 4e ed - 500.jpg
Affichages : 191
Taille : 77,8 Ko

Nom : Couverture SQL Synthex 4e ed - 500.jpg
Affichages : 191
Taille : 77,8 Ko

A +

**QuestVba** · 09/07/2020, 10h00

Voici où j'arrive :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
with 
address_benef as (
select P.INSS as INSS
          , nvl(A.STREET_ZIPCODE,A.ZIPCODE) AS ZIP
          , PA.ADDRESS_TYPE
          , PA.START_DATE
    from 
      PERSONS P
      , PERIODIC_ADDRESSES PA
      , ADDRESSES A
    where 
      P.PERSON_ID = case when PA.ADDRESS_TYPE = 'OFFICIAL' then PA.OFF_ADDR_PERSON_ID else PA.FUN_ADDR_PERSON_ID end
      and PA.ADDRESS_TYPE in ('OFFICIAL', 'FUNCTIONAL')
      and CURRENT_DATE between PA.START_DATE and nvl(PA.END_DATE,to_date('31/12/9999', 'dd/mm/yyyy'))
      and PA.ADDRESS_ID = A.ADDRESS_ID
      and A.type = 'STRUCTURED'
   ), benef_act as (
SELECT DISTINCT
    per_al.inss AS benef_2020
FROM
    files fil
    INNER JOIN children chi ON chi.file_number = fil.file_number
    INNER JOIN legal_situation leg ON leg.child_id = chi.actor_id
        AND ((TO_NUMBER(TO_CHAR(CURRENT_DATE,'yyyymm')) - 1) between (leg.start_year * 100 + leg.start_month) AND NVL((leg.end_year * 100 + leg.end_month),999999))
        AND leg.status = 'ACTIVE'
    INNER JOIN childbeneficiarylinks cbl ON cbl.child_id = chi.actor_id
        AND ((TO_NUMBER(TO_CHAR(CURRENT_DATE,'yyyymm')) - 1) between (cbl.start_year * 100 + cbl.start_month) AND NVL((cbl.end_year * 100 + cbl.end_month),999999)) 
    INNER JOIN actors ac ON ac.actor_id = chi.actor_id
    INNER JOIN persons per ON ac.person_id = per.person_id
    INNER JOIN actors ac_al ON ac_al.actor_id = cbl.beneficiary_id
    INNER JOIN persons per_al ON ac_al.person_id = per_al.person_id
    left JOIN periodic_addresses pad on pad.FUN_ADDR_PERSON_ID = per_al.person_id
    left join contactpersons cpe on cpe.ADDRESS_ID = pad.address_id
    )
 
select benef_2020, zip
from benef_act
inner join address_benef on address_benef.inss=benef_act.benef_2020
;

C'est pas mal mais pour le moment, je ne vois pas comment insérer cette partie - surtout la 3e ligne qui permet de savoir si la personne à une adresse officielle ou une adresse fonctionnelle et dans ce dernier cas de prendre la plus récente :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
select inss, nvl(zip_off, zip_func) zip
from address_benef
pivot (max(zip) keep (dense_rank first order by start_date desc) for address_type in ('OFFICIAL' as zip_off, 'FUNCTIONAL' as zip_func))

Encore un petit coup de pouce (je préfère

).

Requête - Fusion avec condition

SQL Oracle

Discussions similaires

Partager

Partager