Discussion :

[Bruno2r]

Bonjour,
Base : Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit

Dans une table j'ai 4 champs de type Date :
MH_DDEB - Date de début - toujours renseignée exemple 02/01/2006
MH_HDEB - Heure de début - toujours renseignée exemple 01/01/1900 12:10:00
MH_DFIN - Date de fin - null ou renseignée exemple 10/01/2006
MH_HFIN - Heure de fin - null ou renseignée exemple 01/01/1900 13:46:00

Evidemment, je cherche à obtenir une DateHeure de début et de fin dans deux nouveaux champs

DateHeure de début pas de problème :

Code :

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

1
2
to_date((to_char(MH_DDEB,'yyyymmdd')||' '||
to_char(MH_HDEB,'hh24:mi:ss')),'yyyymmdd hh24:mi:ss') as DH_DEB

DateHeure de fin avec gestion de null avec un decode :

Code :

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

1
2
3
decode(MH_DFIN, null, null, 
to_date((to_char(MH_DFIN,'yyyymmdd')||' '||
to_char(MH_HFIN,'hh24:mi:ss')),'yyyymmdd hh24:mi:ss')) as DH_FIN

Mon problème : impossible de récupérer avec les champs comportant des null la partie Time lorsqu'elle est renseignée

Pour un événement donné (voici en ligne pour faciliter la lecture) les résultats obtenus :

EVENEMENT 105017079

Avec les données de début j'obtiens ce que je veux :
MH_DDEB 02/01/2006
MH_HDEB 01/01/1900 12:10:00
DH_DEB 02/01/2006 12:10:00

Pas avec les données de fin :
MH_DFIN 10/01/2006
MH_HFIN 01/01/1900 13:46:00
DH_FIN 10/01/06

[Z3phur]

Salut, si j'ai bien compris ce que tu attends c'est quand


MH_DFIN (pas renseigné)
MH_HFIN 01/01/1900 13:46:00
DH_FIN 13:46:00

Si c'est bien cela que tu veux, c'est normal que tu ne récupère rien car ton décode te retourne NULL quand MH_DFIN est null.

De plus n'est il pas préférable d'utiliser le CASE au lieu du DECODE en 10g???

ton code modifié :

Code :

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

1
2
3
4
5
(case
       when MH_DFIN is null then null
               else to_date((to_char(MH_DFIN,'yyyymmdd')||' '||
                     to_char(MH_HFIN,'hh24:mi:ss')),'yyyymmdd hh24:mi:ss')
end) as DH_FIN

Mais pour ton résultat attendu :

Code :

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

1
2
3
4
5
6
7
(case
       when MH_DFIN is null and MH_HFIN is null then null
       when MH_DFIN is null then MH_HFIN
       else to_date((to_char(MH_DFIN,'yyyymmdd')||' '||
             to_char(MH_HFIN,'hh24:mi:ss')),'yyyymmdd hh24:mi:ss')
end) as DH_FIN
end) as DH_fin

Cela répond il à ta question?

[Bruno2r]

Bonjour et merci pour ta réponse.

Oui, l'utisation de CASE résoud le problème.

Cela dit,

• Je ne récupérais pas null avec mon decode lorsque la date et l'heure existent
• Je récupérais la date seule dépourvue d'heure. (voir exemple dans premier message)

Et j'aimerais bien comprendre pourquoi .... ?
Avec decode je pouvais contourner le problème en donnant une date bidon en cas de null et, dès lors, les dates renseignées affichaient bien l'heure

Code :

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

1
2
3
4
decode(MH_DFIN, null, to_date('29990101 00:00:00','yyyymmdd hh24:mi:ss'), 
to_date((to_char(MH_DFIN,'yyyymmdd')||' '||
to_char(MH_HFIN,'hh24:mi:ss')),'yyyymmdd hh24:mi:ss')) as DH_FIN

Techiquement, est-ce que quelqu'un sait ce qui différencie le CASE du DECODE dans la façon de procéder ?

En tout cas Merci pour l'idée.

[Z3phur]

Pour mon information personnel tu as choisi le quel cas de case le premier ou la deuxième solution?

sinon je vais investiguer pour essayer d'avoir une réponse à :

Techiquement, est-ce que quelqu'un sait ce qui différencie le CASE du DECODE dans la façon de procéder ?

[Bruno2r]

Pour mon information personnel tu as choisi le quel cas de case le premier ou la deuxième solution?

j'ai choisi le premier :

Code :

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

1
2
3
4
(case when V.MH_DFIN IS NULL then NULL
else to_date((to_char(V.MH_DFIN,'yyyymmdd')||' '||
to_char(V.MH_HFIN,'hh24:mi:ss')),'yyyymmdd hh24:mi:ss')end) AS DH_FIN,

Le second ne présente pas d'intérêt dans ce cas précis puisque

• soit MH_DIN et MH_HFIN sont null
• soit ils sont renseignés tous les deux

Merci encore

[Antoun]

Techiquement, est-ce que quelqu'un sait ce qui différencie le CASE du DECODE dans la façon de procéder ?

une piste : j'ai déjà eu des pb de division par zéro qui se résolvaient par CASE et pas par DECODE. Mon hypothèse avait été que DECODE est une fonction, et donc que tous ses arguments sont évalués, qu'ils soient utiles ou non. A l'inverse, CASE est une structure programmatique, qui n'évalue que les éléments nécessaires.

[Bruno2r]

Bonjour et merci pour ta réponse.

Oui, l'utisation de CASE résoud le problème.

Cela dit,

• Je ne récupérais pas null avec mon decode lorsque la date et l'heure existent
• Je récupérais la date seule dépourvue d'heure. (voir exemple dans premier message)

Et j'aimerais bien comprendre pourquoi .... ?
Avec decode je pouvais contourner le problème en donnant une date bidon en cas de null et, dès lors, les dates renseignées affichaient bien l'heure

Code :

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

1
2
3
4
decode(MH_DFIN, null, to_date('29990101 00:00:00','yyyymmdd hh24:mi:ss'), 
to_date((to_char(MH_DFIN,'yyyymmdd')||' '||
to_char(MH_HFIN,'hh24:mi:ss')),'yyyymmdd hh24:mi:ss')) as DH_FIN

Techiquement, est-ce que quelqu'un sait ce qui différencie le CASE du DECODE dans la façon de procéder ?

En tout cas Merci pour l'idée.

une piste : j'ai déjà eu des pb de division par zéro qui se résolvaient par CASE et pas par DECODE. Mon hypothèse avait été que DECODE est une fonction, et donc que tous ses arguments sont évalués, qu'ils soient utiles ou non. A l'inverse, CASE est une structure programmatique, qui n'évalue que les éléments nécessaires.

C'est clair qu'avec le CASE on passe par la "branche" adéquate sans évaluer le traitement de l'autre.
On peut même faire encore plus simple :

Code :

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

1
2
3
4
5
6
(case when V.MH_DFIN IS NOT NULL 
then 
to_date(
(to_char(V.MH_DFIN,'yyyymmdd')||' '||
to_char(V.MH_HFIN,'hh24:mi:ss')),'yyyymmdd hh24:mi:ss')
end) AS DH_FIN,

ce que ne permet pas le decode.
Ce qui reste mystérieux c'est le résultat du décode lorsque la date est renseignée selon le traitement choisi pour la date absente.
si date null alors null sinon date et heure = date sans heure
si date null alors 01/01/2999 sinon date et heure = date et heure

[McM]

J'avais lu dans le magasine Oracle pourquoi :
Parce que le type de retour du DECODE est le type du 1er élément retourné.

donc essayes avec

Code :

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

1
2
3
4
decode(MH_DFIN, NULL, 
to_date(NULL), 
to_date((to_char(MH_DFIN,'yyyymmdd')||' '||
to_char(MH_HFIN,'hh24:mi:ss')),'yyyymmdd hh24:mi:ss')) AS DH_FIN

Une autre méthode pour avoir la date + l'heure sans to_char et to_date.

Code :

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

mh_dfin + (mh_hfin - trunc(mh_hfin))

[Bruno2r]

Très astucieux

Merci McM
ça me rappelle de bons souvenirs (l'été dernier sur ce même forum)

[Snipah]

Laquelle des deux solutions as-tu utilisée?
J'aimerai savoir si le 'to_date(NULL)' a résolu le problème de ta requête initiale, merci

[Antoun]

si date null alors null sinon date et heure = date sans heure
si date null alors 01/01/2999 sinon date et heure = date et heure

Je ne sais pas si ça répond à ta question, mais sous Oracle la concaténation ne suit pas la règle de propagation des NULL :

Code :

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

1
2
3
4
select 'aa' || null 
from dual ;
 
-- résultat : aa

[laurentschneider]

moi je n'employerais pas de to_char

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
SQL> create table t (MH_DDEB date, MH_HDEB date);
Table created.
SQL> insert into t values (
  to_date('02/01/2006','DD/MM/YYYY') , 
  to_date('01/01/1900 12:10:00','DD/MM/YYYY HH24:MI:SS'));
1 row created.
SQL> select MH_DDEB + (MH_HDEB - date '1900-01-01') from t;
MH_DDEB+(MH_HDEB-DATE
---------------------
02/01/2006 12:10:00

Mais ça ne fait sûrement pas avancer le schmillblick

[McM]

C'est ce que j'avais dit..

Une autre méthode pour avoir la date + l'heure sans to_char et to_date.
Code :
mh_dfin + (mh_hfin - trunc(mh_hfin))

Pour le chimili, Bruno2r n'a pas répondu

J'aimerai savoir si le 'to_date(NULL)' a résolu le problème de ta requête initiale, merci

[Bruno2r]

C'est ce que j'avais dit..

Une autre méthode pour avoir la date + l'heure sans to_char et to_date.
Code :
mh_dfin + (mh_hfin - trunc(mh_hfin))

J'aime bien

Pour le chimili, Bruno2r n'a pas répondu

J'aimerai savoir si le 'to_date(NULL)' a résolu le problème de ta requête initiale, merci

A vrai dire j'ai choisi le Case qui me convient très bien
Depuis, je suis passé à autre chose (beaucoup trop de demandes en attente)
En termes de performance ça m'aurait bien plus de comparer les différentes méthodes mais toujours pas le temps.

Merci à tous

[McM]

Vous m'obligez à tester

J'ai mis 3 cas (tout renseigné, sans heure, sans rien)
Et un to_date(null), un NULL et un SYSDATE pour voir le format automatique

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
17
18
19
20
21
WITH t AS (
	SELECT TO_DATE('10.01.2006', 'DD.MM.RRRR') AS mh_dfin,
  		TO_DATE('01.01.1900 13:46:00', 'DD.MM.RRRR HH24:MI:SS') AS mh_hfin
  FROM DUAL
UNION
	SELECT TO_DATE('10.01.2006', 'DD.MM.RRRR') AS mh_dfin,
  		TO_DATE(NULL) AS mh_hfin
  FROM DUAL
UNION
	SELECT TO_DATE(NULL) AS mh_dfin,
  		TO_DATE(NULL) AS mh_hfin
  FROM DUAL
 	)
SELECT SYSDATE,
        DECODE(MH_DFIN, NULL, NULL, 
		TO_DATE((TO_CHAR(MH_DFIN,'yyyymmdd')||' '||
		TO_CHAR(MH_HFIN,'hh24:mi:ss')),'yyyymmdd hh24:mi:ss')) AS DH_FIN, 
        DECODE(MH_DFIN, NULL, TO_DATE(NULL), 
		TO_DATE((TO_CHAR(MH_DFIN,'yyyymmdd')||' '||
		TO_CHAR(MH_HFIN,'hh24:mi:ss')),'yyyymmdd hh24:mi:ss')) AS DH_FIN_2
FROM t

Code :

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

1
2
3
4
SYSDATE			DH_FIN		DH_FIN_2
04/09/2008 09:39:12	10-JAN-06	10/01/2006 13:46:00
04/09/2008 09:39:12	10-JAN-06	10/01/2006
04/09/2008 09:39:12

On remarque le format particulier du decode sans le to_date(null).
Il y a donc une conversion implicite en CHAR

[Vincent Rogier]

... hum ...

la même requête sur mon env. (sans préciser le nls_date_format) :

SYSDATE DH_FIN DH_FIN_2
-------- -------- --------
04/09/08 10/01/06 10/01/06
04/09/08 10/01/06 10/01/06
04/09/08

la même requête sur mon env. (en précisant le nls_date_format) :

alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';

SYSDATE DH_FIN DH_FIN_2
------------------- ------------------- -------------------
04-09-2008 11:15:07 10-01-2006 13:46:00 10-01-2006 13:46:00
04-09-2008 11:15:07 10-01-2006 00:00:00 10-01-2006 00:00:00
04-09-2008 11:15:07

[McM]

Bon, j'avais testé sous TOAD..

Histoire de montrer le type :

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
17
18
19
20
21
WITH t AS (
	SELECT TO_DATE('10.01.2006', 'DD.MM.RRRR') AS mh_dfin,
  		TO_DATE('01.01.1900 13:46:00', 'DD.MM.RRRR HH24:MI:SS') AS mh_hfin
  FROM DUAL
UNION
	SELECT TO_DATE('10.01.2006', 'DD.MM.RRRR') AS mh_dfin,
  		TO_DATE(NULL) AS mh_hfin
  FROM DUAL
UNION
	SELECT TO_DATE(NULL) AS mh_dfin,
  		TO_DATE(NULL) AS mh_hfin
  FROM DUAL
 	)
SELECT DUMP(SYSDATE),
        DUMP(DECODE(MH_DFIN, NULL, NULL, 
		TO_DATE((TO_CHAR(MH_DFIN,'yyyymmdd')||' '||
		TO_CHAR(MH_HFIN,'hh24:mi:ss')),'yyyymmdd hh24:mi:ss'))) AS DH_FIN, 
    DUMP(    DECODE(MH_DFIN, NULL, TO_DATE(NULL), 
		TO_DATE((TO_CHAR(MH_DFIN,'yyyymmdd')||' '||
		TO_CHAR(MH_HFIN,'hh24:mi:ss')),'yyyymmdd hh24:mi:ss'))) AS DH_FIN_2
FROM t

Code :

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

1
2
3
4
5
 
DUMP(SYSDATE)					DH_FIN				DH_FIN_2
Typ=13 Len=8: 216,7,9,4,12,39,38,0	Typ=1 Len=9: 49,48,45,74,65,78,45,48,54	Typ=13 Len=8: 214,7,1,10,13,46,0,0
Typ=13 Len=8: 216,7,9,4,12,39,38,0	Typ=1 Len=9: 49,48,45,74,65,78,45,48,54	Typ=13 Len=8: 214,7,1,10,0,0,0,0
Typ=13 Len=8: 216,7,9,4,12,39,38,0	NULL					NULL

Le type de la colonne n'est pas le même

J'ai un peu de mal avec les type : Le 1 correspondrait à une conversion en char (et non pas à une chaine de caractère)

Code :

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

1
2
3
4
5
SELECT DUMP(1), DUMP('1'), DUMP(TO_CHAR(1))
FROM dual
 
DUMP(1)			DUMP('1')	DUMP(TO_CHAR(1))
Typ=2 Len=2: 193,2	Typ=96 Len=1: 49	Typ=1 Len=1: 49

[Vincent Rogier]

le type 1 est bien une chaine de caractère...

Par contre je suis surpris par le code 13 pour la date car cela devrait être 12 !!! le code 13 n'est pas documenté... (peut être une variante interne au SQLT_DAT... Mais Oracle renvoi toujours un SQLT_DAT ou un SQLT_ODT quand on décrit une colone avec OCI)

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
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
 
#define SQLT_CHR  1                        /* (ORANET TYPE) character string */
#define SQLT_NUM  2                          /* (ORANET TYPE) oracle numeric */
#define SQLT_INT  3                                 /* (ORANET TYPE) integer */
#define SQLT_FLT  4                   /* (ORANET TYPE) Floating point number */
#define SQLT_STR  5                                /* zero terminated string */
#define SQLT_VNU  6                        /* NUM with preceding length byte */
#define SQLT_PDN  7                  /* (ORANET TYPE) Packed Decimal Numeric */
#define SQLT_LNG  8                                                  /* long */
#define SQLT_VCS  9                             /* Variable character string */
#define SQLT_NON  10                      /* Null/empty PCC Descriptor entry */
#define SQLT_RID  11                                                /* rowid */
#define SQLT_DAT  12                                /* date in oracle format */
#define SQLT_VBI  15                                 /* binary in VCS format */
#define SQLT_BFLOAT 21                                /* Native Binary float*/
#define SQLT_BDOUBLE 22                             /* NAtive binary double */
#define SQLT_BIN  23                                  /* binary data(DTYBIN) */
#define SQLT_LBI  24                                          /* long binary */
#define SQLT_UIN  68                                     /* unsigned integer */
#define SQLT_SLS  91                        /* Display sign leading separate */
#define SQLT_LVC  94                                  /* Longer longs (char) */
#define SQLT_LVB  95                                   /* Longer long binary */
#define SQLT_AFC  96                                      /* Ansi fixed char */
#define SQLT_AVC  97                                        /* Ansi Var char */
#define SQLT_IBFLOAT  100                           /* binary float canonical */
#define SQLT_IBDOUBLE 101                          /* binary double canonical */
#define SQLT_CUR  102                                        /* cursor  type */
#define SQLT_RDD  104                                    /* rowid descriptor */
#define SQLT_LAB  105                                          /* label type */
#define SQLT_OSL  106                                        /* oslabel type */
 
#define SQLT_NTY  108                                   /* named object type */
#define SQLT_REF  110                                            /* ref type */
#define SQLT_CLOB 112                                       /* character lob */
#define SQLT_BLOB 113                                          /* binary lob */
#define SQLT_BFILEE 114                                   /* binary file lob */
#define SQLT_CFILEE 115                                /* character file lob */
#define SQLT_RSET 116                                     /* result set type */
#define SQLT_NCO  122      /* named collection type (varray or nested table) */
#define SQLT_VST  155                                      /* OCIString type */
#define SQLT_ODT  156                                        /* OCIDate type */
 
/* datetimes and intervals */
#define SQLT_DATE                      184                      /* ANSI Date */
#define SQLT_TIME                      185                           /* TIME */
#define SQLT_TIME_TZ                   186            /* TIME WITH TIME ZONE */
#define SQLT_TIMESTAMP                 187                      /* TIMESTAMP */
#define SQLT_TIMESTAMP_TZ              188       /* TIMESTAMP WITH TIME ZONE */
#define SQLT_INTERVAL_YM               189         /* INTERVAL YEAR TO MONTH */
#define SQLT_INTERVAL_DS               190         /* INTERVAL DAY TO SECOND */
#define SQLT_TIMESTAMP_LTZ             232        /* TIMESTAMP WITH LOCAL TZ */
 
#define SQLT_PNTY   241              /* pl/sql representation of named types */

[McM]

Je suis en 9.2.0.6
J'ai aussi testé sur une 8.1.7 c'est pareil.
13 pour sysdate

Je vais voir sur Apex qui est en 10g je crois


Edit : Apex est en 11g
(13 donc pour le dump)

[Vincent Rogier]

Je suis 10g donc, t'embêtes pas à tester...

Le code 13 n'est pas documenté mais doit être une variante du 12 ou alors ...

Sinon j'ai testé d'autres type avec dump() et cela ressort bien le code oracle corresposdant.