Bonjour au Forum,
J'utilise ce sql avec sqldeveloper. Et il fonctionne très bien. Comme vous le voyez j'ai deux variables pour tirer mes données. my_period qui prend le mois en cours et qui retranche my_shift pour trouver le mois que je désire.

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
DEFINE my_shift = 1
DEFINE my_period = TO_NUMBER(TO_CHAR(ADD_MONTHS(TO_DATE(CURRENT_DATE), - &&my_shift),'yyyymm'))
SELECT
    COUNT(CHILD_INSS)
FROM
    (
    SELECT
        p.first_name ||' '|| p.name AS CHILD_NAME,
        p.inss AS CHILD_INSS,
        benef_201912 AS BENEFICIARY_INSS_201912,
        benef_2020 AS BENEFICIARY_INSS_2020,
        CASE
            WHEN benef_201912 = benef_2020 THEN 1
            WHEN benef_201912 <> benef_2020 THEN 0
        END AS Identical_Beneficiary
    FROM
        outgoing_documents od
        INNER JOIN document_conversations dc ON od.document_conversation_id = dc.document_conversation_id
        INNER JOIN actors a ON a.actor_id = dc.concerned_actor_id
        INNER JOIN persons p ON p.person_id = a.person_id
        INNER JOIN
        (
        SELECT DISTINCT
            per.inss AS child_201912
            ,per_al.inss AS benef_201912
        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 (201912 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 (201912 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
        ) ON p.inss = child_201912
        INNER JOIN
        (
        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 (leg.start_year * 100 + leg.start_month) BETWEEN 202001 AND &&my_period
                AND leg.status = 'ACTIVE'
            INNER JOIN childbeneficiarylinks cbl ON cbl.child_id = chi.actor_id
                AND (cbl.start_year * 100 + cbl.start_month) BETWEEN 202001 AND &&my_period
            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
        ) ON p.inss = child_2020
    WHERE od.ad_hoc_type = 'NonDomiciledChild_1'
    )
;
Ma question : j'utilise DBeaver depuis quelques semaines car il est plus stable chez moi mais je n'ai toujours pas réussi à adapter ce sql au niveau des variables. Et surtout éviter qu'il m'envoie un popup de remplissage des variables. Si vous avez des pistes... car j'ai déjà pas mal chercher sur la toile.