Discussion :

[QuestVba]

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]

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]

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]

.

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]

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

[QuestVba]

Hello,

.

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]

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.