IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

SQL Oracle Discussion :

last() ou first() avec oracle


Sujet :

SQL Oracle

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Expert éminent
    Avatar de tchize_
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Avril 2007
    Messages
    25 482
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 46
    Localisation : Belgique

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Avril 2007
    Messages : 25 482
    Par défaut last() ou first() avec oracle
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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

  2. #2
    Modérateur
    Avatar de Waldar
    Homme Profil pro
    Sr. Specialist Solutions Architect @Databricks
    Inscrit en
    Septembre 2008
    Messages
    8 454
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 47
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Sr. Specialist Solutions Architect @Databricks
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Septembre 2008
    Messages : 8 454
    Par défaut
    La fonction LAST en fait ne s'écrit pas ainsi, la syntaxe est un peu plus absconse :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 !

  3. #3
    Expert éminent
    Avatar de tchize_
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Avril 2007
    Messages
    25 482
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 46
    Localisation : Belgique

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Avril 2007
    Messages : 25 482
    Par défaut
    Citation Envoyé par Waldar Voir le message
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 ^^

  4. #4
    Expert éminent
    Avatar de tchize_
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Avril 2007
    Messages
    25 482
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 46
    Localisation : Belgique

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Avril 2007
    Messages : 25 482
    Par défaut
    ça marche, je vais pouvoir continuer à développer la requête

    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
    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;

  5. #5
    Modérateur
    Avatar de Waldar
    Homme Profil pro
    Sr. Specialist Solutions Architect @Databricks
    Inscrit en
    Septembre 2008
    Messages
    8 454
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 47
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Sr. Specialist Solutions Architect @Databricks
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Septembre 2008
    Messages : 8 454
    Par défaut
    Bravo !
    Attention quand même à la cohérence des types des données, ici par exemple :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    ORDER BY nvl(rank_start,'1/1/1800') DESC
    -- À remplacer par 
    ORDER BY rank_start desc nulls last)

  6. #6
    Expert éminent
    Avatar de tchize_
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Avril 2007
    Messages
    25 482
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 46
    Localisation : Belgique

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Avril 2007
    Messages : 25 482
    Par défaut
    merci pour l'info

    Bon au final, voilà le résultat
    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
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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?

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Code first et Oracle avec odp .net
    Par durnambule dans le forum Entity Framework
    Réponses: 1
    Dernier message: 05/04/2013, 03h26
  2. Problème avec Last() et pas avec First()
    Par Krustig dans le forum Linq
    Réponses: 2
    Dernier message: 12/02/2011, 20h52
  3. Réponses: 14
    Dernier message: 01/09/2003, 15h54
  4. Conseils pour developper une application avec Oracle
    Par belugha dans le forum Langages de programmation
    Réponses: 5
    Dernier message: 02/06/2003, 16h03
  5. [Crystal Report 9] Changer de schéma avec Oracle
    Par sur_uix dans le forum SAP Crystal Reports
    Réponses: 2
    Dernier message: 14/11/2002, 12h19

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo