Jointure sur le résultat d'une fonction dépendant d'un paramètre de la table jointe

Version imprimable

08/03/2013, 00h43
gorgonite

Jointure sur le résultat d'une fonction dépendant d'un paramètre de la table jointe
Bonsoir,

J'ai un léger soucis pour concrétiser une requête :
Code:

1 2 3 4 5 6 7 8 WITH a AS ( SELECT id FROM MaFonction() ) SELECT a.id AS "a_id", b.* FROM a CROSS JOIN ( SELECT * FROM SecondeFonction(a.id) ) AS b
le hic étant que je ne peux apparemment pas utiliser a.id dans la définition de b

Actuellement, je suis obligé de passer par du procédural :cry:

:merci: par avance
08/03/2013, 09h42
SQLpro

C'est normal. PostGreSQL ne propose pas d'opérateur pour ce que l'on appelle l'intra jointure.

C'est implémenté sous SQL Server, sous la forme :
CROSS APPLY (pour un produit cartésien)
OUTER APPLY (dans le cas ou aucune ligne ne serait renvoyée par la fonction table).

Vous êtes aux limites de PG !

Et cela pose même des problèmes de perf...
Lisez l'article que j'ai écrit à ce sujet :
http://blog.developpez.com/sqlpro/p9...alles_en_sql_1
Dans ces requêtes, les meilleurs temps de calcul sont obtenus par SQL Server, grace à CROSS APPLY !!!

A +
08/03/2013, 16h03
gorgonite

:merci: pour la réponse

Citation:

Envoyé par SQLpro

C'est normal. PostGreSQL ne propose pas d'opérateur pour ce que l'on appelle l'intra jointure.

Je n'ai pas tellement de choix... si ça grossit trop, j'aurais sûrement moyen d'avoir Oracle au lieu de PostgreSQL si mon proto remplit les besoins fonctionnels :?

Citation:

Envoyé par SQLpro

Vous êtes aux limites de PG !

J'ai du passer par une technique plus procédurale pour casser en deux étapes, ça prend moins de 5min... mais j'aurais préféré un truc propre du premier coup
08/03/2013, 17h42
estofilo
J'imagine que SecondeFonction() renvoie du SET OF quelquechose ou du TABLE(...)

Il me semble que ce tu veux faire s'écrit simplement comme ça:
Code:

1 2 SELECT a.id,SecondeFonction(a.id) FROM (SELECT id FROM MaFonction()) a;
ou si SecondeFonction renvoie un type composite, avec cette variante:
Code:

1 2 SELECT a.id, (SecondeFonction(a.id)).* FROM (SELECT id FROM MaFonction()) a;
Si ça ne passe pas, précise ce que renvoient exactement les fonctions car c'est la clef du problème.
08/03/2013, 22h25
gorgonite

elle renvoie un Set Of

la requête est syntaxiquement correcte, mais horriblement lente comparée au patch procédural que j'utilise pour le moment :?

:merci: pour l'astuce :)
09/03/2013, 10h45
SQLpro

Citation:

Envoyé par gorgonite

:merci: pour la réponse

Je n'ai pas tellement de choix... si ça grossit trop, j'aurais sûrement moyen d'avoir Oracle au lieu de PostgreSQL si mon proto remplit les besoins fonctionnels :?

Oracle ne supporte pas non plus cette forme de jointure. A ma connaissance ceci n'est implémenté que sous MS SQL Server.

A +
13/03/2013, 14h53
Waldar

Citation:

Envoyé par SQLpro

Oracle ne supporte pas non plus cette forme de jointure. A ma connaissance ceci n'est implémenté que sous MS SQL Server.

Ça existe chez Oracle Database depuis la 9i (commercialisée en 2001) :

Code:

select * from table(mafonction(mesparam));

http://docs.oracle.com/cd/E11882_01/...ing.htm#i52954
14/03/2013, 13h58
SQLpro

Waldar, je en conteste pas que les fonctions table existent dans Oracle, mais pas l'intra-jointure avec APPLY, ce que fait SQL Server....

A +

Oracle n'a pas le mot clé APPLY mais il n'en a pas besoin pour faire l'intra jointure avec CROSS JOIN.
Avec le code oracle suivant :

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
create or replace type t_weekday_obj as object (DTE DATE, JOUR VARCHAR2(8))
/
create or replace type t_weekday_table as table of t_weekday_obj;
/
 
CREATE or replace FUNCTION F_WEEKDAYTABLE (A_DATE DATE) 
RETURN t_weekday_table as
  l_date date;
  l_res t_weekday_table := t_weekday_table();
BEGIN 
-- obtention de la date avec heure 0 
   l_date := trunc(a_date); 
-- recherche du lundi 
   WHILE to_char(l_date, 'fmDAY', 'NLS_DATE_LANGUAGE = french') <> 'LUNDI' loop
         l_date := l_date - 1;
   end loop;   
-- insertion des jours de la semaine dans la table 
   l_res.extend;
   l_res(l_res.last) := t_weekday_obj(l_date,'Lundi');
   l_res.extend;
   l_res(l_res.last) := t_weekday_obj(l_date + 1,'Mardi');
   l_res.extend;
   l_res(l_res.last) := t_weekday_obj(l_date + 2,'Mercredi');
   l_res.extend;
   l_res(l_res.last) := t_weekday_obj(l_date + 3,'Jeudi');
   l_res.extend;
   l_res(l_res.last) := t_weekday_obj(l_date + 4,'Vendredi');
   l_res.extend;
   l_res(l_res.last) := t_weekday_obj(l_date + 5,'Samedi');
   l_res.extend;
   l_res(l_res.last) := t_weekday_obj(l_date + 6,'Dimanche');
-- retour   
   RETURN l_res;
END;
/

On obtient :

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
SQL> SELECT *
  2    FROM T_FACTURE_FCT F
  3   CROSS JOIN table(F_WEEKDAYTABLE(f.FCT_DATE)) W
  4   WHERE W.JOUR = 'Jeudi' ;
 
    FCT_ID FCT_DATE     CLI_ID DTE      JOUR
---------- -------- ---------- -------- --------
       145 18/07/05         33 21/07/05 Jeudi
       178 20/07/05         21 21/07/05 Jeudi
       213 22/07/05         47 21/07/05 Jeudi
 
SQL> SELECT FCT_ID, FCT_DATE, CLI_ID,
  2         CASE
  3            WHEN FCT_DATE > W.DTE THEN WW.DTE
  4            ELSE W.DTE
  5         END AS DATE_ENVOI
  6    FROM T_FACTURE_FCT F
  7         CROSS JOIN table(F_WEEKDAYTABLE (F.FCT_DATE)) W
  8         CROSS JOIN table(F_WEEKDAYTABLE (F.FCT_DATE + 7)) WW
  9   WHERE W.JOUR  = 'Jeudi'
 10     AND WW.JOUR = 'Jeudi';
 
    FCT_ID FCT_DATE     CLI_ID DATE_ENV
---------- -------- ---------- --------
       145 18/07/05         33 21/07/05
       178 20/07/05         21 21/07/05
       213 22/07/05         47 28/07/05
 
SQL>

13/05/2013, 23h36
gorgonite

question bête... la jointure latérale de postgresql 9.3 résoudrait-elle mon problème (si j'en crois l'exemple)

Pour infos, il semble que la version avec JOIN LATERAL soit bien plus lente que la version avec CTE :?
Code:

1 2 3 4 5 6 7 With h As ( Select Fonction1, Fonction2(arg,Fonction1) From Fonction1(arg) ) Select h.Fonction1, mv.* From Main_View mv Inner Join h On (id=h.Fonction2) Order By h.Fonction1, mv.Id
vs
Code:

1 2 3 4 5 6 Select h.Fonction1, mv.* From Main_View mv Left Join Lateral ( Select Fonction1, Fonction2(arg,Fonction1) From Fonction1(arg) ) As h On ( id=h.Fonction2 ) Order By h.Fonction1, mv.Id
Fonction1 prend un argument de type T et renvoie un set of bigint
Fonction2 prend un argument de type T ainsi qu'un bigint, et renvoie un set of bigint

C'était juste pour tester... :aie:

30/01/2014, 12h59
Pomalaix

Citation:

Envoyé par skuatamad

Oracle n'a pas le mot clé APPLY ...

Entre temps, Oracle 12c est sortie, et désormais les syntaxes LATERAL, CROSS APPLY et OUTER APPLY sont disponibles.