INTERVAL ET SOUS select

Version imprimable

23/09/2011, 12h10
nathieb

INTERVAL ET SOUS select
Bonjour,

Pour les besoins d'une appli on crée une vue en fonction des date
Année N ou année N-1 = f(du mois de l'année)

avant ... statique on avait que N-1
Code:

1 2 3 4 5 6 7 CREATE OR REPLACE FORCE VIEW AS SELECT .. FROM ... WHERE (DATE_OUVERTURE IS NULL OR DATE_OUVERTURE <= TO_DATE('3112'||TO_CHAR((sysdate - INTERVAL '1' YEAR),'YYYY'),'DDMMYYYY'))
cette vue fonctionne bien, mais je dois inclure une condition suplémentaire sur le fameux
décalage en fonction du mois de l'année
Avec un decode, je génére 1 ou 0
Code:

1 2 select decode(sign(TO_NUMBER(TO_CHAR(sysdate -INTERVAL '1' MONTH ,'MM')-8 )),-1,1,0,1,1,0) FROM DUAL
Cela fonctionne aussi, mais il y a un hic quand je remplace le
INTERVAL '1' de la première requête par la deuxième
Code:

1 2 3 4 par INTERVAL ( select decode(sign(TO_NUMBER(TO_CHAR(sysdate -INTERVAL '1' MONTH ,'MM')-8 )),-1,1,0,1,1,0) FROM DUAL )
Est ce possible ???

Olivier

Bonjour,

Si j'ai bien compris :

A partir de la date du jour , tu veux savoir si tu dois retrancher ou non 1 an en fonction du resultat de l'opération num du mois dernier - 8 :
si cette opération ramène un chiffre négatif alors on retranche , sinon on ne retranche pas ?

Dans ce cas là un CASE me semble approprié :
Code:

1 2 3 4 5 6 7 SELECT CASE WHEN sign(TO_NUMBER(TO_CHAR(sysdate -INTERVAL '1' MONTH ,'MM')-8 )) = -1 THEN TO_DATE('3112'||TO_CHAR((sysdate - INTERVAL '1' YEAR),'YYYY'),'DDMMYYYY') WHEN sign(TO_NUMBER(TO_CHAR(sysdate -INTERVAL '1' MONTH ,'MM')-8 )) = 0 THEN TO_DATE('3112'||TO_CHAR((sysdate - INTERVAL '1' YEAR),'YYYY'),'DDMMYYYY') ELSE TO_DATE('3112'||TO_CHAR((sysdate),'YYYY'),'DDMMYYYY') END FROM DUAL

23/09/2011, 13h50
nathieb

Je pense

Bonjour,

effectivement l'idée est là, mais je n'arrivais pas à le traduire en When CASE.
J'avais pris le DECODE en espérant le traduire en TYPE INTERVAL mais cela ne semble pas fonctionner ?

Je pense récrire la solution avec votre(ta) requête pour la création de la vue.

Olivier
23/09/2011, 13h56
nathieb

Résolu

Bonjour,

Effectivement cela me simplifie grandement la requête, j'avoue que je ne suis pas un aficionados des When case / decode

J'avais du mal à respecter les types avec le decode.

Grand Merci

Je vais maintenant voir si cela est conforme, aux attentes utilisateurs. En tout, cas la vue est créé ;):ccool:

Olivier

Bonjour,

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE OR REPLACE FORCE VIEW AS 
SELECT 
..
FROM 
...
WHERE (DATE_OUVERTURE IS NULL 
           OR DATE_OUVERTURE <=  
(SELECT CASE WHEN sign(TO_NUMBER(TO_CHAR(sysdate -INTERVAL '1' MONTH ,'MM')-8 )) = -1 
                 THEN TO_DATE('3112'||TO_CHAR((sysdate - INTERVAL '1' YEAR),'YYYY'),'DDMMYYYY')
            WHEN sign(TO_NUMBER(TO_CHAR(sysdate -INTERVAL '1' MONTH ,'MM')-8 )) = 0  
                 THEN TO_DATE('3112'||TO_CHAR((sysdate - INTERVAL '1' YEAR),'YYYY'),'DDMMYYYY')
            ELSE TO_DATE('3112'||TO_CHAR((sysdate),'YYYY'),'DDMMYYYY')
       END
FROM DUAL
)
(

23/09/2011, 13h59
nathieb

Efficacité

Bonjour,

Comme d'hab simple et efficace, l'équipe Oracle.

Cordialement
olivier
23/09/2011, 14h01
ORA-007

Apres si cette règle de gestion doit être appliquée à plusieurs endroits, il vaut peut être mieux l'encapsuler dans une fonction, ce qui sera plus facile à maintenir.
23/09/2011, 14h10
nathieb

Package

Bonjour,

oui effectivement, j'ai pensé à créer une procédure ou fonction, mais pour l'instant c'est du colmatage urgence.

Olivier

Je trouve que l'implémentation de l'algorithme est trop complexe, et sa mise en pratique pas vraiment optimale non plus.

Vous comparez le mois moins un au mois d'août.
Pourquoi ne pas comparer le mois actuel au mois de septembre ?

Ensuite, il existe des fonctions dates qui permettent d'éviter toutes ces conversions !

Au final, ces deux expressions sont équivalentes, mais la seconde est beaucoup moins lourde en terme de conversion (et en bonus, en terme de syntaxe aussi) :

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
with sr as
(
select add_months(trunc(sysdate, 'yyyy'), level-1) as dt
  from dual
connect by level <= 12
)
select dt
     , case
         when sign(to_number(to_char(dt -interval '1' month ,'mm')-8 )) = -1 
         then to_date('3112'||to_char((dt - interval '1' year),'yyyy'),'ddmmyyyy')
         when sign(to_number(to_char(dt -interval '1' month ,'mm')-8 )) = 0  
         then to_date('3112'||to_char((dt - interval '1' year),'yyyy'),'ddmmyyyy')
       else to_date('3112'||to_char((dt),'YYYY'),'ddmmyyyy')
       end as exp1
     , case
         when extract(month from dt) between 2 and 9
         then trunc(dt, 'yyyy')-1
         else add_months(trunc(dt, 'yyyy')-1, 12)
       end as exp2
  from sr;
 
DT         EXP1       EXP2      
---------- ---------- ----------
01/01/2011 31/12/2011 31/12/2011
01/02/2011 31/12/2010 31/12/2010
01/03/2011 31/12/2010 31/12/2010
01/04/2011 31/12/2010 31/12/2010
01/05/2011 31/12/2010 31/12/2010
01/06/2011 31/12/2010 31/12/2010
01/07/2011 31/12/2010 31/12/2010
01/08/2011 31/12/2010 31/12/2010
01/09/2011 31/12/2010 31/12/2010
01/10/2011 31/12/2011 31/12/2011
01/11/2011 31/12/2011 31/12/2011
01/12/2011 31/12/2011 31/12/2011

23/09/2011, 16h03
nathieb

Merci
Bonjour,
Code:

1 2 3 4 SELECT add_months(trunc(sysdate, 'yyyy'), level-1) AS dt FROM dual connect BY level <= 12
C'est quoi en français ?

Je me réponds, ce sont de requêtes hiérarchiques, bon bah vaincu ;-)

Top, et merci à tout deux, grande leçon.

Olivier