Jointure sur date et max

Bonjour a tous,
Je cherche a faire une requête qui comporte plein de petit problème et au final elle ne marche pas. Voici la structure de ma table
Table1
Nom1
Date
Caractéristique1
Caractéristique2

Table2
Nom1 (clé étrangère de table 1)
Nom 2

Table3
Nom1 (clé étrangère de table 1)
Date (différente de la date de table1)
Statut1
Statut2

Voila pour ce qui est de la structure.
pour ce qui est dedans : la table3 contient énormément de champs(beaucoup plus que la table1) et la Table1 contient des caractéristiques relevé au momment date (de table1 bien sur)

ce que je voudrais afficher :
--Nom1--Nom2--Date--Caractéristique1--Caractéristique2--Statut1--Statut2

la date est celle de la table1
Pour le statut je l'obtient en prenant le dernier statut rentré avant la date de la table1 (en gros la date Maximum parmi toute les date inferieur a date de table 1)
J'arrive a faire cela pour une date et un nom fixe grâce a une sous requête:

Code:

---

1 2 3 4 5 6 7 8 9

select T3.STATUT1, T2.STATUT2 from TABLE3 T3 join TABLE2 T2 on T2.STATUS=T3.STATUT where T3.STATUT_DATE<to_date('010820101110','MM/DDYYYY HH24:MI') and NOM1='nom1' and STATE_DATE=(select max(table3.STATE_DATE) from TABLE3 tab where table3.NOM3='nom1' and table3.DATE<to_date('010820101110','MM/DDYYYY HH24:MI'));

---

Mais je n'arrive pas à insérer cette requête dans ma requête prééxistante (car j'ai déjà une requète qui existe pour obtenir la même chose sans les statuts) pour obtenir ce que je veux et faire le lien avec la table1.

Pour info, j'ai testé avec cette requête :

Code:

---

1 2 3 4 5 6 7 8

select T1.NOM1,T2.NOM2 T3.STATUT1 from Table1 T1 join TABLE2 T2 on T2.NOM1=T1.Nom1 join TABLE3 T3 on T3.NOM1=T1.NOM1 where T3.DATE=(select max(table3.DATE) from TABLE3 T4 where table4.STATE_DATE<T1.DATE) ;

---

Mais la requête tourne vraiment très longtemps sans rien renvoyé (en tout cas je l'arrète avant)

Pourriez vous m'aidez si vous comprenez ce que j'aimerais obtenir.
Merci d'avance pour votre aide

Ce n'est pas simple de manipuler des dates en cherchant le dernier statut. Je ferais ça avec une fonction analytique.

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

select Table1.Nom1, T2.Statut1, T2.Statut2, Table1.Date from ( select distinct Nom1, Statut1, Statut2, Date, dense_rank() over (partition by Nom1 order by Date desc) "position" from Table3, ( select Nom1, Date "day" from Table1 ) T1 where Date<= T1."day" and Nom1 = T1.Nom1 ) T2, Table1 where "position" = 1 and Table1.Nom1 = T2.Nom1

---

Mais je pense qu'il existe une fonction analytique plus adaptée que celle que j'ai utilisée, on va voir si Waldar passe par ici.

Edit:
correction cf plus bas.

Merci beaucoup pour ta formule, effectivement ce n'est pas simple.
Ta formule marche presque parfaitement mais je ne comprend pas pourquoi, pour la faire marcher je suis obliger de supprimer le dernier champs du select (T1.Date), j'ai essayé de le remplacé par T1."Day", rien n'y fait j'ai une erreur invalid indentifier.
As-tu une idée d'où cela peut venir?

Oui, "day" est juste utile dans la sous-requête, pour afficher les éléments de Table1 j'y refais appel plus haut. Si Table1 est une énorme table on peut effectivement faire remonter les informations ainsi :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

SELECT T2.Nom1, T2.Statut1, T2.Statut2, T2."day" FROM ( SELECT DISTINCT Nom1, Statut1, Statut2, Date, dense_rank() over (partition BY Nom1 ORDER BY Date DESC) "position", T1."day" FROM Table3, ( SELECT Nom1, Date "day" FROM Table1 ) T1 WHERE Date<= T1."day" AND Nom1 = T1.Nom1 ) T2 WHERE "position" = 1

---

C'est 2 logiques différentes, la 2e étant potentiellement plus performante mais moins facile à maintenir.

EDIT:
erreur de ma part, ce n'est pas T1.Date qu'il faut dans la première requête mais Table1.Date. Je corrige.

Sa à l'air de marcher impécablement :ccool:, merci beaucoup de ton aide.

J'ai en faite crié victoire un peu vite:? la requète ne me renvoie qu'un nombre très limité de resultat par rapport a ce qu'elle devrait (je ne m'en suit pas rendue compte tout de suite car le nombre de résultat renvoyé reste très grand). Je n'ai pas autant de ligne renvoyé que d'entité dans table1 (ce qui est mon objectif initial).
J'ai essayé de supprimé le distinct mais sa ne change quasiment rien. D'où cela peut-il venir?

Je dirais qu'il existe des Table1.Nom1 qui n'ont pas de correspondance dans Table2 ou Table3. Ou du moins qui n'ont pas de correspondance dans Table3 pour les critères de date spécifiés.

Dans ce cas il suffit de reprendre ma requête avec une jointure ouverte :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

SELECT Table1.Nom1, T2.Statut1, T2.Statut2, Table1.Date FROM Table1 left outer join ( SELECT DISTINCT Nom1, Statut1, Statut2, Date, dense_rank() over (partition BY Nom1 ORDER BY Date DESC) "position" FROM Table3, ( SELECT Nom1, Date "day" FROM Table1 ) T1 WHERE Date<= T1."day" AND Nom1 = T1.Nom1 ) T2 on ( Table1.Nom1 = T2.Nom1 and T2."position" = 1 )

---

Cette fois sa marche vraiment. Merci d'avoir pris le temps de m'aider. Cette requète était vraiment complexe. Merci beaucoup.