Discussion :

[alex_972]

Bonjour,

J'ai un problème de précision causé par la fonction decode sur un test de nullité d'un timestamp. Auriez vous une idée ?

Ma requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
INSERT INTO table1 
   (SELECT ... 
    FROM table2  
    WHERE timestamp > (SELECT MAX (decode(timestamp, null, (systimestamp - 7), timestamp) FROM table1));

J'ai essayé de conserver la précision en ajoutant des fonctions to_timestamp mais rien n'y fait, je perds les fractions de seconde.

Du coup, j'hésite à faire un script en PL/SQL pour savoir si ma table est null avant le début de la requête.
J'hésitais aussi à faire une requête d'insertion conditionnelle mais dans ce cas elle va tester la nullité de chaque entrée, ce qui est inutile.

merci d'avance !

[McM]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

(SELECT MAX (CASE WHEN timestamp iS NULL then systimestamp - 7 else timestamp end) FROM table1);

[alex_972]

Merci

Je n'avais pas pensé à cette solution !

Est-ce normal la perte de précision avec la fonction décode ?

[McM]

en fait, j'ai refais un test : c'est le systimestamp -7 qui transforme en date
le type de retour du decode est déterminé par la première valeur de retour (notion importante à savoir dans le cas des NULL pour une date par exemple)

J'ai réussi à le faire marcher avec decode

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
SELECT DECODE(SYSTIMESTAMP, NULL, CAST(SYSTIMESTAMP - 7 AS TIMESTAMP), SYSTIMESTAMP) D1, 
		SYSTIMESTAMP -7 d2, 
    CAST(DECODE(SYSTIMESTAMP, NULL, SYSTIMESTAMP - 7, SYSTIMESTAMP) AS TIMESTAMP) d3,
    CASE WHEN SYSTIMESTAMP IS NULL THEN SYSTIMESTAMP - 7 ELSE SYSTIMESTAMP END d4
FROM dual
 
 
D1	D2	D3	D4
27/09/2011 10:57:29.653906	20/09/2011 10:57:29	27/09/2011 10:57:29.000000	27/09/2011 10:57:29.653906 +02:00

Pour le NULL avec date ou chaine dont j'ai parlé

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
SELECT DUMP(DECODE(1, 2, '', SYSDATE)), 
			DUMP(DECODE(1, 2, TO_DATE(NULL), SYSDATE))
FROM dual
 
DUMP(DECODE(1,2,'',SYSDATE))	DUMP(DECODE(1,2,TO_DATE(NULL),SYSDATE))
Typ=1 Len=9: 50,55,45,83,69,80,45,49,49	Typ=13 Len=8: 219,7,9,27,10,59,38,0
 
SELECT DECODE(1, 2, '', SYSDATE),
			DECODE(1, 2, TO_DATE(NULL), SYSDATE)
FROM dual
DECODE(1,2,'',SYSDATE)	DECODE(1,2,TO_DATE(NULL),SYSDATE)
27-SEP-11	27/09/2011 11:01:31

Dans le premier cas on a une chaine de caractère, dans le second une vraie date, essaye de rajouter un jour, ça marchera dans le second, mais pas dans le premier

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
SELECT DECODE(1, 2, TO_DATE(NULL), SYSDATE) + 1
FROM dual
 
DECODE(1,2,TO_DATE(NULL),SYSDATE)+1
28/09/2011 11:02:39
 
SELECT DECODE(1, 2, '', SYSDATE) + 1
FROM dual
ORA-01722: invalid number

[alex_972]

Merci pour l'explication

[laurentschneider]

donc

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

systimestamp - interval '7' day

[Waldar]

Et sinon, vous ne connaissez pas les fonctions NVL / COALESCE ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT MAX(COALECE(timestamp, systimestamp - 7)) FROM table1

[mnitu]

Et sinon, vous ne connaissez pas les fonctions NVL / COALESCE ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT MAX(COALECE(timestamp, systimestamp - 7)) FROM table1

Faite attention aux conversions implicites

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
SQL> select dump(systimestamp) as a, dump(systimestamp - 7) as b, dump(systimestamp - interval '7' day) as c
  2  from dual
  3  /
 
A                    B                    C
-------------------- -------------------- --------------------
Typ=188 Len=20: 7,21 Typ=13 Len=8: 7,219, Typ=188 Len=20: 7,21
9,9,28,10,41,18,32,3 9,21,9,41,18,0       9,9,21,10,41,18,0,31
1,173,25,176,255,0,5                      ,173,25,176,255,0,5,
,32,0,0,50,49                             0,0,0,0,0

[Waldar]

En plus c'était écrit juste au-dessus !

[laurentschneider]

Notez aussi la différence entre coalesce et nvl !

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQL> select nvl(to_date(null),systimestamp  - interval '7' day) from dual;
NVL(TO_DATE(NULL),SYSTIMESTAMP-INTERVAL'7'DAY)
----------------------------------------------
21-SEP-11
SQL> select nvl(sysdate,systimestamp - interval '7' day) from dual;
NVL(SYSDATE,SYSTIMESTAMP-INTERVAL'7'DAY)
----------------------------------------
28-SEP-11
SQL> select coalesce(to_date(null),systimestamp - interval '7' day) from dual;
COALESCE(TO_DATE(NULL),SYSTIMESTAMP-INTERVAL'7'DAY)
---------------------------------------------------
21.09.2011 15:26:19.520283000 +02:00
SQL> select coalesce(sysdate,systimestamp - interval '7' day) from dual;
COALESCE(SYSDATE,SYSTIMESTAMP-INTERVAL'7'DAY)
---------------------------------------------
28.09.2011 15:26:19.000000000 +02:00

[McM]

NVL est du type de la première valeur, COALESCE.. j'en sais rien

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT NVL(CAST(TO_DATE(NULL) AS TIMESTAMP), SYSDATE) as d
FROM dual
d
28/09/2011 15:56:38.000000
 
SELECT NVL(CAST(TO_DATE(NULL) AS TIMESTAMP), 1)
FROM dual
ORA-00932: inconsistent datatypes: expected TIMESTAMP got NUMBER
 
SELECT COALESCE(SYSDATE, SYSTIMESTAMP, 1)
FROM dual
ORA-00932: inconsistent datatypes: expected TIMESTAMP got NUMBER
 
SELECT COALESCE(1, SYSDATE, SYSTIMESTAMP, 1)
FROM dual
ORA-00932: inconsistent datatypes: expected NUMBER got DATE

[laurentschneider]

coalesce apparement convertit vers le type à la meilleure précision, mais j'ai demandé à l'auteur de la documentation SQL de me donner des détails... à suive