last() ou first() avec oracle [Résolu]

[tchize_]

Bonjour, j'essaie de réaliser depuis tout a l'heure un requete SQL qui me semblait simple, mais je m'arrache les cheveux sur oracle.

J'ai une table, appelée RANK. Grosso modo

Code x :

1
2
3
4
5
EMP_ID Number,
RANK_ID Number primary key,
RANK_START timestamp,
RANK_END timestamp
X,Y,Z données diverses que je cheche à extraire.

J'ai besoin de faire la requete suivante:retourner un tableau, groupé par employé, et dont les colonnes sont les valeurs les plus "récentes" au sens de la paire [RANK_START, RANK_END] (en fait, les dernière dans l'ordre de tri)

J'arrive à faire du order by suivant mon critère, j'arrive à mettre mes closes WHERE pour filter les lignes dont le range START/END n'inclue pas aujourd'hui, mais je n'arrive par à prendre la "dernière row" pour chaque EMP_ID.

D'après ce que je trouve sur le net, en SQL, c'est aussi simple que de faire

Code sql :

1
2
3
4
SELECT EMP_ID, last(RANK_ID), last(X), last(Y), last(Z) 
FROM RANK 
ORDER BY RANK_START DESC ,RANK_END DESC
GROUP BY EMP_ID

seulement voilà, oracle butte sur last (invalid identifier) qu'il ne reconnait pas comme fonction d'aggrégation

Donc pour faire simple, j'ai dans ma table

Code x :

1
2
3
4
5
6
7
EMP_ID RANK_ID, RANK_START, RANK_END, X , Y , Z
1      1       1/1/2000 1/1/2002 A  A  A
1      2       1/1/2001 1/1/2012 A  B  A
2      3       1/1/2000 1/1/2013 A  B  A
3      4       1/1/2001 1/1/2012 A  B  A
3      5       1/1/1998 1/1/2001 A  B  A

et je veux obtenir

Code x :

1
2
3
4
EMP_ID RANK_ID, RANK_START, RANK_END, X , Y , Z
1      2       1/1/2001 1/1/2012 A  B  A
2      3       1/1/2000 1/1/2013 A  B  A
3      4       1/1/2001 1/1/2012 A  B  A

Si quelqu'un peut m'aider. A noter que la requete rentrera en tant que sous requete comme partie droite dans une left outer join, le but final étant d'aplatire les tables Employé et rank pour avoir un tableau avec les données de l'employe et le "rank actuel". Donc si quelqu'un a une solution qui fait l'applatissement directement, c'est bon aussi. Pour le moment je part sur ce format

Code sql :

FROM (<sous requete liant deux/trois TABLES pour former l employé>) e LEFT OUTER JOIN (<sous requete que j essaie ici de construire>) r ON e.emp_id=r.emp_id

[Waldar]

La fonction LAST en fait ne s'écrit pas ainsi, la syntaxe est un peu plus absconse :

Code :

1
2
3
4
5
6
7
8
9
  SELECT emp_id,
         max(rank_id) keep (dense_rank last ORDER BY rank_start DESC) AS rank_id,
         max(rank_start) AS rank_start,
         max(rank_end)   AS rank_end  ,
         max(x) keep (dense_rank last ORDER BY rank_start DESC) AS x,
         max(y) keep (dense_rank last ORDER BY rank_start DESC) AS y,
         max(z) keep (dense_rank last ORDER BY rank_start DESC) AS z
    FROM rank
GROUP BY emp_id;

Une autre solution, plus lisible à mes yeux :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
WITH SR AS
(
SELECT emp_id, rank_id,
       rank_start, rank_end,
       x, y, z,
       row_number() over(partition BY emp_id ORDER BY rank_start DESC) AS rn
  FROM rank
)
SELECT emp_id, rank_id,
       rank_start, rank_end,
       x, y, z
  FROM SR
 WHERE rn = 1;

Pour "l’aplatissement", il y a certainement moyen de le faire en une passe, mais il faudrait toute la requête !

[tchize_]

Envoyé par Waldar

La fonction LAST en fait ne s'écrit pas ainsi, la syntaxe est un peu plus absconse :

J'avais vu que ca servait aussi de fonction analytique, mais comme j'y connais rien en méthodes analytiques...., j'avais rien compris à la notation (en fait, j'ai rien compris à votre exemple, mais je suppose que ça marche, je testerais
Question subsidiaire, j'ai un WHERE qui élimine avant applatissement des lignes sur certains critères d'acceptation, je les met dans la clause where ou je les mets dans la condition analytique? Exemple:

Code :

1
2
3
4
5
6
7
8
9
10
  SELECT emp_id,
         max(rank_id) keep (dense_rank last ORDER BY rank_start DESC) AS rank_id,
         max(rank_start) AS rank_start,
         max(rank_end)   AS rank_end  ,
         max(x) keep (dense_rank last ORDER BY rank_start DESC) AS x,
         max(y) keep (dense_rank last ORDER BY rank_start DESC) AS y,
         max(z) keep (dense_rank last ORDER BY rank_start DESC) AS z
    FROM rank
WHERE RANK_START<sysdate AND RAND_END>sysdate
GROUP BY emp_id;

Une autre solution, plus lisible à mes yeux :

Autant j'arrivais plus ou moins a comprendre la première, autant celle là j'y comprend quedalle, donc je vais éviter :p

Pour "l’aplatissement", il y a certainement moyen de le faire en une passe, mais il faudrait toute la requête !

Houlà, vous avez du courrage. LE problème, c'est que j'ai sur la même requete 3 ou 4 one-to-many à applatir, et en outer join qui plus est, car il peux ne rien y avoir dans la requete. + une série d'autres tables à joindre entre elles, J'ai mal aux yeux rien qu'à voir la requete actuellement développer qui n'a pas encore d'applatissement

Si vous avez envie de la partie sportive, je peux toujours vous la filer mardi ^^

[tchize_]

ça marche, je vais pouvoir continuer à développer la requête

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
WITH FLAT_EMPLOYEE AS
       (SELECT ee.person_id, ee.EMP_NAT_REG_NUMBER, pp.birthdate, pp.LAST_NAME, pp.first_name,
              EE.EMP_ADMIN_MATRICULE,
              pp.LANGUAGE, 
              home.address_number AS home_number, home.street AS home_street, home.zip AS home_zip, home.city AS home_city, 
 
              ee.emp_status_2, ee.emp_admin_state, pp.nationality, PP.GENDER AS gender_id
        FROM RH_EMPLOYEE ee,RH_PERSON pp, RH_ADDRESS home
        WHERE ee.PERSON_ID = pp.PERSON_ID 
              AND  pp.home_address = home.address_id
              AND  ee.EMP_ADMIN_STATE IN (13160,13161,13162,13163) ),
 
    CURRENT_CAREER AS 
       (SELECT r.emp_id,
          max(r.career_id) keep (dense_rank last ORDER BY nvl(rank_start,'1/1/1800') DESC) AS career_id,
          max(r.rank_start) keep (dense_rank last ORDER BY nvl(rank_start,'1/1/1800') DESC) AS rand_start,
          max(r.rank_end)   keep (dense_rank last ORDER BY nvl(rank_start,'1/1/1800') DESC) AS rank_end ,
          max(g.pcode) keep (dense_rank last ORDER BY nvl(rank_start,'1/1/1800') DESC) AS grade_code,
          max(g.glevel) keep (dense_rank last ORDER BY nvl(rank_start,'1/1/1800') DESC) AS grade_level
        FROM RH_CAREER_RANK r
        LEFT OUTER JOIN RH_GRADE g
          ON g.GRADE_ID = r.GRADE_ID
        WHERE nvl(rank_start,'1/1/1800') < sysdate AND nvl(rank_end,'1/1/2200') > sysdate
        GROUP BY emp_id)
SELECT e.*,
     nat.alpha3 AS "nationality", 
    gender.pcode AS "gender", lang.pcode AS LANGUAGE, statsit.pcode AS "statsit todo",
    substr(adminstate.pcode,0,1) AS "posadmin"
 
FROM FLAT_EMPLOYEE e
LEFT OUTER JOIN RH_COUNTRY nat
ON e.nationality = nat.alpha2
LEFT OUTER JOIN RH_SELECT_LIST gender
ON gender.entry_id = e.GENDER_ID
LEFT OUTER JOIN RH_SELECT_LIST lang
ON lang.entry_id = e.LANGUAGE
LEFT OUTER JOIN RH_SELECT_LIST statsit
ON statsit.entry_id = e.emp_status_2
LEFT OUTER JOIN CURRENT_CAREER c
ON c.emp_id = e.person_id
LEFT OUTER JOIN RH_SELECT_LIST adminstate
ON adminstate.entry_id = e.emp_admin_state
 
ORDER BY e.LAST_NAME, E.FIRST_NAME;

[Waldar]

Bravo !
Attention quand même à la cohérence des types des données, ici par exemple :

Code :

1
2
    nvl(rank_start,'1/1/1800') < sysdate
AND nvl(rank_end  ,'1/1/2200') > sysdate

'1/1/1800' est juste une chaîne de caractères, il faut la convertir explicitement en date si vous voulez éviter les mauvaises surprises :

Code :

1
2
    nvl(rank_start, to_date('1/1/1800', 'dd/mm/yyyy')) < sysdate
AND nvl(rank_end  , to_date('1/1/2200', 'dd/mm/yyyy')) > sysdate

Ou mieux encore, rien ne sert de coder des dates par défaut :

Code :

1
2
    (rank_start < sysdate OR rank_start IS NULL)
AND (rank_end   > sysdate OR rank_end   IS NULL)

Idem dans les fonctions de fenêtrage :

Code :

1
2
3
ORDER BY nvl(rank_start,'1/1/1800') DESC
-- À remplacer par 
ORDER BY rank_start DESC nulls last)

[tchize_]

merci pour l'info

Bon au final, voilà le résultat

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
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
CREATE VIEW PERCON_INTERMEDIATE AS 
WITH FLAT_EMPLOYEE AS
       (SELECT ee.person_id, ee.EMP_NAT_REG_NUMBER, pp.birthdate, pp.LAST_NAME, pp.first_name,
              EE.EMP_ADMIN_MATRICULE, EE.EMP_WORKING_PERCENT AS TAUX_ACTIV, 
              EE.EMP_MEDICAL_NUMBER AS MEDNR, EE.EMP_IN_DATE AS DATIN,
              EE.EMP_SALARY_IN_DATE AS ANCPEC, 
              home.address_number AS home_number, home.street AS home_street, home.zip AS home_zip, home.city AS home_city, 
              office.phone AS office_phone, EE.EMP_EMAIL AS email, 
 
              pp.LANGUAGE, ee.emp_status_2, ee.emp_admin_state, pp.nationality, PP.GENDER AS gender_id, ee.NOK_ID AS nok_id,
              home.country AS home_country_id
        FROM RH_EMPLOYEE ee,RH_PERSON pp, RH_ADDRESS home, RH_ADDRESS office
        WHERE ee.PERSON_ID = pp.PERSON_ID 
              AND  pp.home_address = home.address_id
              AND  ee.emp_office_address = office.address_id
              AND  ee.EMP_ADMIN_STATE IN (13160,13161,13162,13163)
              AND  ee.emp_status_1 = 17265
       ),
 
    CURRENT_CAREER AS 
       (SELECT r.emp_id,
          max(r.career_id) keep (dense_rank last ORDER BY rank_start DESC nulls last) AS career_id,
          max(r.rank_start) keep (dense_rank last ORDER BY rank_start DESC nulls last) AS rand_start,
          max(r.rank_end)   keep (dense_rank last ORDER BY rank_start DESC nulls last) AS rank_end ,
          max(g.pcode) keep (dense_rank last ORDER BY rank_start DESC nulls last) AS grade_code,
          max(g.glevel) keep (dense_rank last ORDER BY rank_start DESC nulls last) AS grade_level,
          max(path.junior) keep (dense_rank last ORDER BY rank_start DESC nulls last) AS path_jrcode
        FROM RH_CAREER_RANK r
        LEFT OUTER JOIN RH_GRADE g
          ON g.GRADE_ID = r.GRADE_ID
        LEFT OUTER JOIN RH_SELECT_LIST path
          ON path.entry_id = r.career_path
        WHERE (rank_start < sysdate OR rank_start IS NULL)
          AND (rank_end   > sysdate OR rank_end   IS NULL)
        GROUP BY emp_id
       ),
    CURRENT_SALARY AS 
       (SELECT r.emp_id,
          max(r.salary_start) keep (dense_rank last ORDER BY salary_start  DESC nulls last) AS salary_start,
          max(r.salary_end)   keep (dense_rank last ORDER BY salary_start  DESC nulls last) AS salary_end ,
          max(r.salary_scale) keep (dense_rank last ORDER BY salary_start  DESC nulls last) AS salary_scale
        FROM RH_SALARY_RANK r
        WHERE (salary_start < sysdate OR salary_start IS NULL)
          AND (salary_end   > sysdate OR salary_end   IS NULL)
        GROUP BY emp_id
       ),
    CURRENT_CONTRACT AS
       (SELECT c.emp_id,
          max(c.CT_START_DATE) keep (dense_rank last ORDER BY CT_START_DATE DESC nulls last) AS contract_start,
          max(c.CT_END_DATE)   keep (dense_rank last ORDER BY CT_START_DATE DESC nulls last) AS contract_end ,
          max(compt.entry_name_nl) keep (dense_rank last ORDER BY CT_START_DATE DESC nulls last) AS contract_comptability
        FROM RH_CONTRACT c
        LEFT OUTER JOIN RH_SELECT_LIST compt
          ON compt.ENTRY_ID = c.CT_COMPT_SECTION
        WHERE (CT_START_DATE < sysdate OR CT_START_DATE IS NULL)
          AND (CT_END_DATE   > sysdate OR CT_END_DATE   IS NULL)
        GROUP BY emp_id
       )
SELECT 
    '11' AS TRENSFP, contract.contract_end AS DRENSFP, e.EMP_ADMIN_MATRICULE AS MATRICNR, e.EMP_NAT_REG_NUMBER AS NAT_NUM,
    extract(YEAR FROM e.birthdate) AS DATENAISS, e.last_name AS NOM, e.first_name AS prenom, e.home_street ||' ' || e.home_number AS RUE,
    e.home_zip AS PCOD, nat.alpha3 AS nat, gender.pcode AS gender, 
    lang.pcode AS RLING, 
    case 
      when e.emp_status_2 IS NULL then '4'
      else statsit.junior
    end AS statsit,
    substr(c.grade_code,-1) AS GRORG,  
    case 
       when c.grade_level = 's' OR  c.grade_level = 'S' then 'W'
       else  c.grade_level
    end AS NIVEAU,
    substr(adminstate.pcode,0,1) AS posadmin,
    e.TAUX_ACTIV, e.MEDNR, e.DATIN, e.ANCPEC,
    salary.salary_scale AS BAREM,
    CASE 
      when nok.nok_haardtoelage = 'Y' THEN 1
      when nok.nok_standplaatstoelage = 'Y' THEN 2
      else 0
    END AS FOYER_RES,
    contract.contract_comptability AS NUMART, 'K' AS ORGANISME,
    e.home_city AS COMMUNE, e.office_phone AS PHONE, e.email,
    c.path_jrcode AS FAMILLEFONCTION_NR,
    home_country.alpha3 AS PAYS_ADRESSE
 
 
FROM FLAT_EMPLOYEE e
LEFT OUTER JOIN RH_COUNTRY nat
ON e.nationality = nat.alpha2
LEFT OUTER JOIN RH_COUNTRY home_country
ON e.home_country_id = home_country.alpha2
LEFT OUTER JOIN RH_SELECT_LIST gender
ON gender.entry_id = e.GENDER_ID
LEFT OUTER JOIN RH_SELECT_LIST lang
ON lang.entry_id = e.LANGUAGE
LEFT OUTER JOIN RH_SELECT_LIST statsit
ON statsit.entry_id = e.emp_status_2
LEFT OUTER JOIN CURRENT_CAREER c
ON c.emp_id = e.person_id
LEFT OUTER JOIN RH_SELECT_LIST adminstate
ON adminstate.entry_id = e.emp_admin_state
LEFT OUTER JOIN CURRENT_SALARY salary
ON salary.emp_id = e.person_id
LEFT OUTER JOIN RH_NEXTOFKIN nok
ON nok.NOK_ID = e.NOK_ID
LEFT OUTER JOIN CURRENT_CONTRACT contract
ON contract.emp_id = e.person_id
 
ORDER BY e.LAST_NAME, E.FIRST_NAME;

D'autres remarques?


PS: question subsidiaire. Je pensait passer le contenu de cette vue dans une table tierce, qui contient beaucoup plus de colonnes, dont les autres colonnes resteront vide. Les colonnes communes ont le même nom.

Oracle me refuse

Code :

1
2
INSERT INTO percon_2
SELECT * FROM percon_intermediate;

sous motif "nombre de valeur inssufisant".

Suis-je obligé de tout énuméré ou y a-t-il moyen de dire à oracle de faire correspondre les colonnes de même nom entre la table du select et celle du insert?

[mnitu]

Est-vous certain que l'order by doit faire partie de la vue ?
Mettez des valeurs par défaut pour vos colonnes ; mais utiliser des inserts nommés : insert into table (col1, col2, …) select … c'est une très bonne pratique.

[tchize_]

l'order by, non, pas besoin dans la vue, il servait quand je faisait uniquement les selects au départ, après, la vue semble vivre très bien avec

j'avais justement espéré éviter de me coltiner l'énumération de 30 noms de colonnes
C'est peut être une bonne pratique, mais il s'agit ici de transferts one-shot dans le cadre d'une migration de donnée, donc je peux garantir que les deux tables on des colonnes de même noms Tant pis. ca m'aurait fait gagner 10 minutes ^ ^

Sur ce merci bien et je clôture ce sujet