Discussion :

[bstevy]

Bonjour à tous,


Voilà, je bloque sur une requête que j'aimerai simplifier, mais je n'arrive pas à bien m'y prendre.
Est ce que vous pourriez m'aider ?



Voici la requete initiale.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SELECT *
  FROM contract_versions CV
WHERE 1 = 1
   AND CV.cv_crc_code NOT IN (12, 13, 14, 15, 16, 102)
   AND cv_number = (SELECT MAX (cv_number)
             FROM contract_versions
            WHERE NOT (cv_status_code = '09' OR cv_status_code = '9')
              AND cv_con_number = CV.cv_con_number)

Comme vous le voyez, on lit deux fois la table contract_versions
et donc ce que je cherche à faire, c'est obtenir le même résultat en un seul passage.
Je suis sur que c'est faisable très facilement, mais j'arrive pas à m'en sortir avec les deux conditions :

D'un point de vue fonctionnel, ca donne cela
D'un coté, j'ai la liste des contrats valides : CV.cv_crc_code NOT IN (12, 13, 14, 15, 16, 102)
De l'autre, j'ai la liste des versions valides : NOT (cv_status_code = '09' OR cv_status_code = '9')
Et je cherche donc les infos (select *) pour la dernière version valide à laquelle le contrat est valide. (je ne ramène pas les contrats qui sont invalidés par leur dernière version)
->Un contract a pu par exemple ête invalidé par une version N, mais si cette version est justement invalidée elle aussi, alors le contrat reste valide.

Vous auriez une idée de comment je pourrais faire ca en évitant d'appeler deux fois la table ?


Pour info, l'idée sur laquelle je partais à la base était d'utiliser une fonction rank pour determiner ma ligne de version max.
Car après, j'ai besoin aussi des info de la version 1, donc le rank m'aurait permi d'avoir une condition du genre "rank = 1 (version max) OR version = 1"
Mais pour le moment, j'ai une couille dans le paté que j'arrive pas à enlever. C'est pas trop loin de ce que je cherche à faire, mais c'est toujours pas ça.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
select	cv_con_number,  
	cv_number, 
	rank() over (partition by cv_con_number order by decode(cv_status_code, '09', 0, '9', 0, cv_number) desc),
	CV.*
FROM contract_versions CV
WHERE 1 = 1
AND CV.cv_crc_code NOT IN (12, 13, 14, 15, 16, 102)

Merci d'avance pour vos idées.


Steven




EDIT : Et je cherche donc les infos (select *) pour la dernière version valide à laquelle le contrat est valide. (je ne ramène pas les contrats qui sont invalidés par leur dernière version)
Je pense que c'est plus clair comme ca.

[bstevy]

Bon, j'annonce qu'a force d'en discuter, j'ai réussi à trouver une solution par moi même.
Néanmoins, j'aimerai bien avoir votre avis et connaitre votre facon de faire.
Et si personne n'a d'idée, je mettrais en résolu avec ma solution demain

[mnitu]

Cherchez sur ce forum First/Last dans leur version agrégée.

[bstevy]

Tu l'as mise en raccourci cette réponse ? genre ctrl+r, ca l'ecrit automatiquement ?

Merci, mais c'est pas du tout la réponse que j'attends... j'ai un problème sur la transformation de mes contions, pas sur la méthode de faire un group by -_-
si t'avais regardé ma deuxième requete, tu aurais peut etre vu qu'il y a qlq chose qui va pas avec les contions que j'utilise, et tu aurais pu m'aider



Au passage, pour aider ceux qui ont un problème avec leur first ... tu devrais mettre un lien vers un message ou y'a l'explication plutot que de demander une recherche qui renvoit sur tes demandes recherches ...

[bstevy]

Cherchez sur ce forum First/Last dans leur version agrégée.

Juste pour confirmer un truc quand même, ce que tu appelles FIRST/LAST en version agrégée, c'est ca :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

min(id) keep(dense_rank first order by kind, id) id

?

[Waldar]

Je l'aurai écrit comme ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
with cte_max_cv_number as
(
  select cv_con_number
       , max(cv_number) as cv_number_max
    from contract_versions
   where cv_status_code not in ('09', '9')
group by cv_con_number
)
select cv.*
  from contract_versions cv
  join cte_max_cv_number cm
    on cm.cv_con_number = cv.cv_con_number
   and cm.cv_number_max = cv.cv_number
 where cv.cv_crc_code not in (12, 13, 14, 15, 16, 102);

[skuatamad]

Juste pour confirmer un truc quand même, ce que tu appelles FIRST/LAST en version agrégée, c'est ca :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

min(id) keep(dense_rank first order by kind, id) id

?

Oui, un lien vers un exemple :
http://lalystar.developpez.com/fonct...tiques/#LIII-I

[bstevy]

Je l'aurai écrit comme ceci :

ca donne juste le même plan que ce que j'ai avant... avec double lecture de la table, ce que je chercher à éviter.

Mais merci quand meme. La requete est jolie

Maintenant, ce qu'il faut que je test, c'est est ce qu'on peut faire du cte de cte...

[bstevy]

Oui, un lien vers un exemple :
http://lalystar.developpez.com/fonct...tiques/#LIII-I

C'est gentil, mais comme je le disais, je sais comment utiliser cette fonction
C'est pas un problème de group by que j'ai XD

[Waldar]

Maintenant, ce qu'il faut que je test, c'est est ce qu'on peut faire du cte de cte...

Oui pas de soucis, il faut les écrire dans des blocs séparés toujours en début de 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
with cte_1 as
(
select * from dual
)
  ,  cte_2 as
(
select * from cte_1
)
  ,  cte_3 as
(
    select *
      from cte_1
cross join cte_2
)
select distinct *
  from cte_3;

[skuatamad]

C'est gentil, mais comme je le disais, je sais comment utiliser cette fonction
C'est pas un problème de group by que j'ai XD

Je répondais juste à ta question :

Juste pour confirmer un truc quand même, ce que tu appelles FIRST/LAST en version agrégée, c'est ca :

Et j'ai ajouté un lien pour que les futurs lecteurs aient un exemple, c'est tout

Par contre, je n'ai pas regardé ton problème, désolé, mais Waldar est sur le coup donc tu auras des réponses pertinentes.

[bstevy]

Bon, si qlq un d'autres idées, qu'il hesite pas.


Ce que j'avais comme problème en fait quand on regarde la deuxième requete que j'ai posté, c'est que je suis censé éliminser des contrats qui ne sont pas valide.
Et je pensais qu'en les décallant dans l'ordre de mon rank, ca serait suffisant, mais en fait, c'etait juste absurde puisque rank me ramènera toujours au moins un rang 1


Et la solution de mnitu me convenait pas non plus car elle fait un group by, donc elle ne m'enlève pas les contrats dont je veux me debarasser...

Mais c'est quand même celle que j'ai prise

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
select 
CV_CON_NUMBER
,max(CV_NUMBER            ) AS	CV_NUMBER
,max(CV_AUTO_RENEWAL      ) keep (dense_rank first order by CV_NUMBER desc) AS CV_AUTO_RENEWAL      
,max(CV_BONUS_CLASS       ) keep (dense_rank first order by CV_NUMBER desc) AS CV_BONUS_CLASS       
,max(CV_CONSTRUCTED_BY    ) keep (dense_rank first order by CV_NUMBER desc) AS CV_CONSTRUCTED_BY    
,max(CV_CONSTRUCTION_TIME ) keep (dense_rank first order by CV_NUMBER desc) AS CV_CONSTRUCTION_TIME 
,max(CV_CRC_CODE          ) keep (dense_rank first order by CV_NUMBER desc) AS CV_CRC_CODE          
,max(CV_CST_CODE          ) keep (dense_rank first order by CV_NUMBER desc) AS CV_CST_CODE          
,max(CV_CUR_ACDNT_TERM    ) keep (dense_rank first order by CV_NUMBER desc) AS CV_CUR_ACDNT_TERM    
,max(CV_DRIVER_FLAG       ) keep (dense_rank first order by CV_NUMBER desc) AS CV_DRIVER_FLAG       
,max(CV_FAMILY_PLUS       ) keep (dense_rank first order by CV_NUMBER desc) AS CV_FAMILY_PLUS       
,max(CV_INTERNET_DISCOUNT ) keep (dense_rank first order by CV_NUMBER desc) AS CV_INTERNET_DISCOUNT 
,max(CV_LADIES_PLUS       ) keep (dense_rank first order by CV_NUMBER desc) AS CV_LADIES_PLUS       
,max(CV_MAIN_MATURITY     ) keep (dense_rank first order by CV_NUMBER desc) AS CV_MAIN_MATURITY     
,max(CV_MD_DATE_OF_BIRTH  ) keep (dense_rank first order by CV_NUMBER desc) AS CV_MD_DATE_OF_BIRTH  
,max(CV_MD_DR_LIC_COLOUR  ) keep (dense_rank first order by CV_NUMBER desc) AS CV_MD_DR_LIC_COLOUR  
,max(CV_MD_FIRST_NAME_KAN ) keep (dense_rank first order by CV_NUMBER desc) AS CV_MD_FIRST_NAME_KAN 
,max(CV_MD_GENDER         ) keep (dense_rank first order by CV_NUMBER desc) AS CV_MD_GENDER         
,max(CV_MD_PREFECTURE     ) keep (dense_rank first order by CV_NUMBER desc) AS CV_MD_PREFECTURE     
,max(CV_MD_SURNAME_KAN    ) keep (dense_rank first order by CV_NUMBER desc) AS CV_MD_SURNAME_KAN    
,max(CV_MGM_DISCOUNT      ) keep (dense_rank first order by CV_NUMBER desc) AS CV_MGM_DISCOUNT      
,max(CV_OW_NATURAL_PERSON ) keep (dense_rank first order by CV_NUMBER desc) AS CV_OW_NATURAL_PERSON 
,max(CV_PAYMENT_DATE      ) keep (dense_rank first order by CV_NUMBER desc) AS CV_PAYMENT_DATE      
,max(CV_PCO_ID            ) keep (dense_rank first order by CV_NUMBER desc) AS CV_PCO_ID            
,max(CV_PET_PLUS          ) keep (dense_rank first order by CV_NUMBER desc) AS CV_PET_PLUS          
,max(CV_PH_FLAG           ) keep (dense_rank first order by CV_NUMBER desc) AS CV_PH_FLAG           
,max(CV_PRE_VERSION_NUMBER) keep (dense_rank first order by CV_NUMBER desc) AS CV_PRE_VERSION_NUMBER
,max(CV_STATUS_CODE       ) keep (dense_rank first order by CV_NUMBER desc) AS CV_STATUS_CODE       
,max(CV_TARIFF_DATE       ) keep (dense_rank first order by CV_NUMBER desc) AS CV_TARIFF_DATE    
FROM japhonie.contract_versions CV
WHERE NOT (cv_status_code = '09' OR cv_status_code = '9')   
group by CV_CON_NUMBER
having max(CV_CRC_CODE) keep (dense_rank first order by CV_NUMBER desc) not IN (12, 13, 14, 15, 16, 102)

Avec ca, c'est juste divisé par 3 le coup de ma requete... Et j'ai un nouveau problème : je n'ai pas assez de tablespace.
Donc là, je vais devoir aller négocier avec les DBA pour qu'ils m'augmentent ca.



En tout ca, merci d'avoir participé
Et si vous avez d'autres commentaires constructif, je suis carrement preneur.

[mnitu]

Tu l'as mise en raccourci cette réponse ? genre ctrl+r, ca l'ecrit automatiquement ?

Merci, mais c'est pas du tout la réponse que j'attends... j'ai un problème sur la transformation de mes contions, pas sur la méthode de faire un group by -_-
si t'avais regardé ma deuxième requete, tu aurais peut etre vu qu'il y a qlq chose qui va pas avec les contions que j'utilise, et tu aurais pu m'aider



Au passage, pour aider ceux qui ont un problème avec leur first ... tu devrais mettre un lien vers un message ou y'a l'explication plutot que de demander une recherche qui renvoit sur tes demandes recherches ...

Et la solution de mnitu me convenait pas non plus car elle fait un group by, donc elle ne m'enlève pas les contrats dont je veux me debarasser...

Mais c'est quand même celle que j'ai prise

Bref c'est juste pour vous dire que j'avais déjà pris la décision de ne plus répondre à aucune de vos interventions. L'exception que j'avais fait ce matin c'était une erreur de ma part.
Bon courage pour la suite

[bstevy]

Bref c'est juste pour vous dire que j'avais déjà pris la décision de ne plus répondre à aucune de vos interventions. L'exception que j'avais fait ce matin c'était une erreur de ma part.
Bon courage pour la suite

Vous êtes un gentil, et vous êtes pas trop rancunié, c'est pas de votre faute si vous vous sentez obligé d'aider même ceux que vous n'aimez pas

Le problème que j'avais était sur l'utilisation de mes filtres, non pas sur le type de group by que je devais utiliser.
C'est cette partie que j'avais mal faite :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

having max(CV_CRC_CODE) keep (dense_rank first order by CV_NUMBER desc) not IN (12, 13, 14, 15, 16, 102)

J'avais tendance à traiter ce cas dans mon max (ou rank) par un genre de case, ou une connerie dans le genre
J'avoue que je bloquais sur un problème de débutant, et c'est en expliquant mon problème à un collègue que j'ai trouvé ma réponse.