Select SQL Oracle

Version imprimable

Select SQL Oracle

Bonjour à tous,

Je travaille sur une requête SQL pour un KPI à partir d'une table d'historique (ex : HisTable), supposant que cette table contient les données dessous :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 NumSeq || articleID || Statut || Date_Action ______ || _________ || ________ || ___________ 1 || 111 || Submit || 20/12/2017 2 || 111 || Approuve || 28/12/2017 3 || 111 || Approuve || 04/01/2018 4 || 111 || Accept || 10/01/2018 1 || 222 || Submit || 02/01/2018 2 || 222 || Approuve || 03/01/2018 3 || 222 || Accept || 20/01/2018 4 || 222 || Approuve || 24/01/2018 1 || 333 || Submit || 25/12/2017 2 || 333 || Approuve || 07/01/2018 3 || 333 || Approuve || 15/01/2018
Il peut avoir d'autres statuts "Approuve" après l'acceptation sur l'article.

L'objectif est d'afficher pour article, la dernière (récente) date d'approbation avant la date d'acceptation s'elle existe, et la date d'acceptation s'il existe sinon un champs null.

Pour faire simple, y'a deux cas à traiter :
- Si le statut "Accept" pour l'article existe : on affiche articleID + date dernière approbation (Accept) avant la date d'acceptation + la date d'acceptation.
- Si le statut "Accept" n'existe pas encore : on afficher articleId + date dernière approbation + NULL (date acceptation = NULL).
Code:

1 2 3 4 5 articleID || Date_Action_Appr || Date_Action_Accep _________ || ________________ || _________________ 111 || 04/01/2018 || 10/01/2018 222 || 03/01/2018 || 20/01/2018 222 || 15/01/2018 ||
j'ai réussi à afficher les deux articles accepté et je n'ai pas réussi à afficher le troisième qui n'est pas encore accepté.

Toute aide est précieux et je vous en remercie d'avance.

Bonjour,

Tu as fait une erreur dans le résultat attendu, la dernière ligne c'est l'article 333 et pas 222

Code:

1
2
3
4
5
articleID|| Date_Action_Appr || Date_Action_Accep
________ ||_________________|| ______________
111      || 04/01/2018      || 10/01/2018
222      || 03/01/2018      || 20/01/2018
222      || 15/01/2018      ||

Si j'ai bien compris, il faut que l'article ait au moins une ligne 'approuve' ou une ligne 'accept' pour ressortir, et que tu n'as besoin que des données de ces lignes, donc il suffit déjà de limiter à ces 2 états

Ensuite, un simple groupement avec un max suffit (+ decode)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
WITH T AS
(SELECT 1 numseq,  111 idart, 'Submit' action, TO_DATE('20/12/2017', 'DD/MM/RRRR') dteaction FROM DUAL            
UNION ALL SELECT 2 numseq,  111 idart, 'Approuve' action, TO_DATE('28/12/2017', 'DD/MM/RRRR') dteaction FROM DUAL
UNION ALL SELECT 3 numseq,  111 idart, 'Approuve' action, TO_DATE('04/01/2018', 'DD/MM/RRRR') dteaction FROM DUAL
UNION ALL SELECT 4 numseq,  111 idart, 'Accept' action, TO_DATE('10/01/2018', 'DD/MM/RRRR') dteaction FROM DUAL  
UNION ALL SELECT 1 numseq,  222 idart, 'Submit' action, TO_DATE('02/01/2018', 'DD/MM/RRRR') dteaction FROM DUAL  
UNION ALL SELECT 2 numseq,  222 idart, 'Approuve' action, TO_DATE('03/01/2018', 'DD/MM/RRRR') dteaction FROM DUAL
UNION ALL SELECT 3 numseq,  222 idart, 'Accept' action, TO_DATE('20/01/2018', 'DD/MM/RRRR') dteaction FROM DUAL  
UNION ALL SELECT 4 numseq,  222 idart, 'Approuve' action, TO_DATE('24/01/2018', 'DD/MM/RRRR') dteaction FROM DUAL
UNION ALL SELECT 1 numseq,  333 idart, 'Submit' action, TO_DATE('25/12/2017', 'DD/MM/RRRR') dteaction FROM DUAL  
UNION ALL SELECT 2 numseq,  333 idart, 'Approuve' action, TO_DATE('07/01/2018', 'DD/MM/RRRR') dteaction FROM DUAL
UNION ALL SELECT 3 numseq,  333 idart, 'Approuve' action, TO_DATE('15/01/2018', 'DD/MM/RRRR') dteaction FROM DUAL
)
SELECT idart, MAX(DECODE(action, 'Approuve', dteaction)) last_approb, MAX(DECODE(action, 'Accept', dteaction))  last_accept
FROM T
WHERE action IN ('Approuve', 'Accept')
GROUP BY idart
ORDER BY 1

Code:

1
2
3
4
IDART	LAST_APPROB	LAST_ACCEPT
111	04/01/2018	10/01/2018
222	24/01/2018	20/01/2018
333	15/01/2018

Bonjour McM,

Merci pour ta réponse. Effectivement j'ai fais une erreur, c'est bien 333 et pas 222.

En fait, tous les articles ont le statut 'approuve' contrairement au statut 'accept' (par exemple, des commandes qui sont en cours de traitement sont en attente d'acceptation).

Pour ta requête, au niveau de la première partie t'as utilisé des valeurs en dur et ce n'est pas ce que je voulais. La requête que j'ai fais au début qui permet d'afficher juste les articles acceptés est celle ci :
Code:

1 2 3 4 5 select HT1.articleID , HT1.Date_Action as Date_Action_Appr, HT2.Date_Action as Date_Action_Accep from HisTable HT1 join HisTable HT2 on (HT1.articleID = HT2.articleID) where HT1.Statut ='Approuve' and HT2.Statut = 'Accept' and HT1.NumSeq = HT2.NumSeq - 1
Mais je voudrais bien afficher les articles en attente d'acceptation, pas que ceux qui sont acceptés.

Ma première partie (WITH T..) constitue simplement un jeu de données

Avec ce SELECT tu devrais avoir le résultat réel
Code:

1 2 3 4 5 SELECT articleID, MAX(DECODE(Statut , 'Approuve', Date_Action)) Date_Action_Appr, MAX(DECODE(Statut , 'Accept', Date_Action)) Date_Action_Accep FROM HisTable WHERE Statut IN ('Approuve', 'Accept') GROUP BY articleID ORDER BY articleID

07/02/2018, 15h06
zak-mouk

j'ai testé la requête, c'est presque bon, merci.

Il y'a un petit souci au niveau de l'articleid 222, si on fait

Code:

MAX(DECODE(Statut , 'Approuve', Date_Action))

on affiche la date du dernier statut 'Approuve' (numseq = 4) qui vient après l'e statut 'Accept' alors qu'il faut prendre la date du statut 'Approuve' de numseq = 2 (en gros il faut prendre la date du statut 'Approuve' avant l'acceptation == dans le cas au ou l'acceptation est faite).
Le souci est que si on fait

Code:

MIN(DECODE(Statut , 'Approuve', Date_Action))

, au niveau de l'articleid 111 on affichera la date de numseq = 2 au lieu de la date du numseq = 3 :? :?

Essayez ainsi :

Code:

1
2
3
4
5
6
7
8
9
10
    select t1.articleId
         , max(t1.date_action) as Date_Action_Appr
         , t2.date_action      as Date_Action_Accep 
      from HisTable t1
 left join HisTable t2  on t2.articleId    = t1.articleId
                       and t2.date_action >= t1.date_action
                       and t2.Statut       = 'Accept'
     where t1.Statut = 'Approuve'
  group by t1.articleId
         , t2.date_action;

Edit : il y a encore un petit truc à résoudre, j'y reviendrai.

Oups :oops: Je n'avais pas bien lu la demande, et même mon résultat était différent de celui attendu.. désolé.

Je corrige donc

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
WITH HisTable AS
(					SELECT 1 NumSeq,  111 articleID, 'Submit' Statut,	 	TO_DATE('20/12/2017', 'DD/MM/RRRR') Date_Actio FROM DUAL            
UNION ALL SELECT 2 NumSeq,  111 articleID, 'Approuve' Statut, TO_DATE('28/12/2017', 'DD/MM/RRRR') Date_Actio FROM DUAL
UNION ALL SELECT 3 NumSeq,  111 articleID, 'Approuve' Statut, TO_DATE('04/01/2018', 'DD/MM/RRRR') Date_Actio FROM DUAL
UNION ALL SELECT 4 NumSeq,  111 articleID, 'Accept' Statut, 	TO_DATE('10/01/2018', 'DD/MM/RRRR') Date_Actio FROM DUAL  
UNION ALL SELECT 1 NumSeq,  222 articleID, 'Submit' Statut, 	TO_DATE('02/01/2018', 'DD/MM/RRRR') Date_Actio FROM DUAL  
UNION ALL SELECT 2 NumSeq,  222 articleID, 'Approuve' Statut, TO_DATE('03/01/2018', 'DD/MM/RRRR') Date_Actio FROM DUAL
UNION ALL SELECT 3 NumSeq,  222 articleID, 'Accept' Statut, 	TO_DATE('20/01/2018', 'DD/MM/RRRR') Date_Actio FROM DUAL  
UNION ALL SELECT 4 NumSeq,  222 articleID, 'Approuve' Statut, TO_DATE('24/01/2018', 'DD/MM/RRRR') Date_Actio FROM DUAL
UNION ALL SELECT 1 NumSeq,  333 articleID, 'Submit' Statut, 	TO_DATE('25/12/2017', 'DD/MM/RRRR') Date_Actio FROM DUAL  
UNION ALL SELECT 2 NumSeq,  333 articleID, 'Approuve' Statut, TO_DATE('07/01/2018', 'DD/MM/RRRR') Date_Actio FROM DUAL
UNION ALL SELECT 3 NumSeq,  333 articleID, 'Approuve' Statut, TO_DATE('15/01/2018', 'DD/MM/RRRR') Date_Actio FROM DUAL
)
SELECT articleID, MAX(DECODE(Statut, 'Approuve', Date_Actio)) Date_Action_Appr, MAX(DECODE(Statut, 'Accept', Date_Actio)) Date_Action_Accep
FROM HisTable a
WHERE Statut = 'Accept'
	OR (Statut = 'Approuve' AND NumSeq <= NVL((SELECT MAX(b.NumSeq) FROM HisTable b WHERE b.articleID = a.articleID AND b.Statut = 'Accept'), NumSeq)
  		)
GROUP BY articleID
ORDER BY 1

Ca donne

Code:

1
2
3
4
IDART	LAST_APPROB	LAST_ACCEPT
111	04/01/2018	10/01/2018
222	03/01/2018	20/01/2018
333	15/01/2018

Comme McM a bien répondu au besoin, je me suis entraîné à sortir une solution avec un OUTER APPLY (12c minimum).

Je ne suis pas complètement satisfait de la requête, je l'espérais plus simple à écrire, mais elle donne le bon résultat :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
     select t1.articleId
          , coalesce(max(t3.date_action), max(case t1.Statut when 'Approuve' then t1.date_action end)) as Date_Action_Appr
          , max(case t1.Statut when 'Accept' then t1.date_action end) as Date_Action_Accep
       from HisTable t1
outer apply (select max(t2.date_action) as date_action
               from HisTable t2
              where t2.Statut       = 'Approuve'
                and t1.Statut       = 'Accept'
                and t2.articleId    = t1.articleId
                and t2.date_action <= t1.date_action) t3
      where t1.Statut in ('Approuve', 'Accept')
   group by t1.articleId
   order by t1.articleId;

07/02/2018, 20h01
zak-mouk

Bonsoir McM et Waldar,

McM: j'ai fais le test sur 3 éléments comme dans cet exemple et ça marche très BIEN, reste à l'adapter :D, je te remercie beaucoup :merci: :ccool:

Waldar : Je testerai aussi ta requête demain et je te tiens au courant ;), merci beaucoup à toi aussi :ccool:
08/02/2018, 09h04
zak-mouk

Hello Waldar, je viens de tester ta requête mais il ne marche pas, y'a quelque chose qui manque. Merci en tout cas :applo: .
08/02/2018, 11h49
Waldar

Elle ne fonctionne qu'en 12c, mais c'était plus pour l'exercice de OUTER APPLY.
Utilisez simplement la requête de McM qui fonctionnera sur toutes les versions.
09/02/2018, 12h57
McM

Merci Waldar pour la nouveauté OUTER APPLY que je connais pas.. Je vais regarder ça plus en détail.
09/02/2018, 14h58
Scriuiw

De ce que j'ai compris du OUTER APPLY et CROSS APPLY, ce sont des instructions qui servent avant tout à de la compatibilité avec le monde SQL-SERVER
Peu d'intérêt du coup vis-à-vis des jointures standards

Bonjour,

Une petite variante à base de fonctions fenêtrées :

Code:

1
2
3
4
5
6
7
8
9
10
11
 
WITH tmp AS (
SELECT *
	   ,	  MAX(CASE WHEN action = 'Approuve' THEN dteaction END) OVER(PARTITION BY idart ORDER BY dteaction RANGE UNBOUNDED PRECEDING) as Date_Action_Appr
	   ,	  MAX(CASE WHEN action = 'Accept' THEN dteaction END) OVER(PARTITION BY idArt) AS Date_Action_Accep
	   ,	  ROW_NUMBER() OVER(PARTITION BY idart ORDER BY CASE WHEN action = 'Accept' THEN 0 ELSE 1 END, dteaction desc) AS Rn
FROM T
)
SELECT idart, Date_Action_Appr, Date_Action_Accep
FROM tmp
WHERE rn = 1

16/02/2018, 12h12
zak-mouk

Bonjour McM,

J'ai une question par rapport à ta requête.
Tout d'abord j'ai adapté la requête à mon cas, j'ai remplacer MAX par MIN dans deux emplacement :

Code:

MIN(DECODE(Statut, 'Accept', Date_Actio)) Date_Action_Accep

et

Code:

SELECT MIN(b.NumSeq) FROM HisTable b WHERE b.articleID = a.articleID AND b.Statut = 'Accept'

et j'ai rajouté une condition sur la Date_Action_Appr

Code:

DECODE(Statut, 'Approuve', Date_Actio) BETWEEN '02/01/2017' AND '08/01/2017'

dans mon résultat j'ai le champs Date_Action_Accep == NULL
je voudrais savoir si c'est normal ou pas ?
Merci d'avance
16/02/2018, 12h20
McM

C'est bizarre de remplacer le MAX par un MIN alors que ta demande d'origine était "L'objectif est d'afficher pour article, la dernière (récente) date d'approbation avant la date d'acceptation si elle existe"
Reposte ta requête que je vérifie, car je ne comprends pas où tu as mis ce DECODE(Statut, 'Approuve', Date_Actio) BETWEEN '02/01/2017' AND '08/01/2017'
16/02/2018, 13h47
zak-mouk

Hello McM,
Je te remercie pour ton retour rapide.
En effet, l'erreur vient de moi, j'aurai du faire la condition WHERE avec "Date_Actio" tout court et pas avec "DECODE(Statut, 'Approuve', Date_Actio)". C'est faisant les tests que j'ai changé les MAX pour faire marcher la condition "DECODE(Statut, 'Approuve', Date_Actio)" :oops::oops:.
Merci encore :ave::merci:
16/02/2018, 13h57
tbc92

@Scriuiw

Dans certaines configurations, le Cross Apply permet d'améliorer la performance dans des proportions considérables. Temps d'exécution divisé par 10 dans mon cas.
19/02/2018, 16h47
SQLpro

Citation:

Envoyé par Scriuiw

De ce que j'ai compris du OUTER APPLY et CROSS APPLY, ce sont des instructions qui servent avant tout à de la compatibilité avec le monde SQL-SERVER
Peu d'intérêt du coup vis-à-vis des jointures standards

Pas du tout....

En fait la norme SQL avait proposé dans sa version 1999 l'opérateur LATERAL pour effectuer un produit cartésien appliqué à une fonction table. On ne peut pas en effet joindre une fonction table par un JOIN car les données de la fonction table ne sont pas statiques mais générées dynamiquement lors de l'arrivée d'un paramètres dans les arguments de la fonction table, argument qui vient le plus souvent d'une colonne d'une table.
Or pour construire un plan d'exécution, le moteur à besoin d'estimer le nombre de lignes et cela n'est pas possible pour une fonction table qui ne contient rien au moment de l'analyse.

Le seul défaut de l'opérateur LATERAL est qu'il ne renvoyait rien si l'argument n'existe pas (la table dont l'argument est extrait n'a pas de ligne) faisant disparaître du coup les données potentielles de la fonction table.

Devant cette lacune de la norme, Microsoft à proposé de remplacer LATERAL par APPLY (il n'ont d'ailleurs jamais utilisé LATERAL à cause de ce défaut), décliné en deux versions :
CROSS APPLY, le produit cartésien
et
OUTER APPLY, le produit cartésien renvoyant quand même une ligne en cas d'absence de référence des arguments.
La particularité de APPLY est que comme il n'existe pas de stats dessous, une cardinalité constante est utilisée en remplacement (par exemple 100 dans PostgreSQL, 1 dans SQL Server) ce qui peut s'avérer payant si l'on est à peu près sûr de son coût (clin d'œil à Pomalaix)...

Pour information, c'est en cours de normalisation, raison pour laquelle Oracle l'a récemment introduit (tout comme les collations qui viennent enfin d'être introduite sous Oracle (trente ans de retard...) en remplacement de l'imbitable et anormatif NLS...).

A +