Simplification de requete pour éviter une double lecture

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:

---

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:

---

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.

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

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

:ptdr: 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 ...

Citation:

---

Envoyé par mnitu

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:

---

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

---

?

Je l'aurai écrit comme ceci :

Code:

---

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

---

Citation:

---

Envoyé par bstevy

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

Code:

---

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

Citation:

---

Envoyé par Waldar

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

Citation:

---

Envoyé par skuatamad

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

Citation:

---

Envoyé par bstevy

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:

---

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;

---

Citation:

---

Envoyé par bstevy

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 :

Citation:

---

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.

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:

---

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.

Citation:

---

Envoyé par bstevy

:ptdr: 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 ...

---

Citation:

---

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

Citation:

---

Envoyé par mnitu

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:

---

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.