DBeaver - Variable

Version imprimable

DBeaver - Variable

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:

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.

TO_DATE(CURRENT_DATE) ? Aïe, ça me pique les yeux.. jamais de to_date sur une date

Sinon, ça te sert à quoi cette requête ? Tu ne peux pas passer par du plsql ?

Code:

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
DECLARE
v NUMBER;
v_shift NUMBER := 1;
v_my_period NUMBER := TO_NUMBER(TO_CHAR(ADD_MONTHS(CURRENT_DATE, - v_shift ),'yyyymm'));
begin
SELECT
    COUNT(CHILD_INSS) INTO v
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 v_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 v_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'
dbms_putput.put_line('resultat:'|| v);
END;

18/12/2020, 10h48
13thFloor
Hello,
le 1er lien de google te donne la réponse.

Citation:

Window -> Preferences -> Database -> Editors -> SQL Editor -> SQL Processing. There is a block on Parameters with settings you can change. See the Dynamic Parameter binding section on the wiki.

Il faut cocher Anonymous sql parameters.

J'ai fais un test avec :
Code:

1 2 @set days = 1 select to_char(sysdate-:days,'dd/mm/rr hh24:mi:ss') from dual;
Et ça marche.
18/12/2020, 11h14
QuestVba

@McM

Pourquoi ce sql ? Cela concerne les enfants bénéficiaires sans domicile. Quand on applique la nouvelle législation en 01/2020, certains enfants ont perdu leur droit mais, dans certaines conditions, le droit a été rouvert après 01/2020. C'est donc ce nombre d'enfants que je cherche et trouve.

PL/SQL n'est malheureusement pas une option.
18/12/2020, 11h20
QuestVba
@13thFloor
Effectivement, j'ai bien coché cette option.

mon but était d'utiliser une variable dans une autre variable. Comme ceci (qui ne fonctionne évidemment pas):
Code:

1 2 3 4 5 6 7 @set days = 125 @set my_period = to_char(sysdate-:days,'dd/mm/rr hh24:mi:ss') select :days ,:my_period FROM dual ;