Discussion :

[OBI-ONE]

Bonjour à tous,

J'utilise Oracle 10 sous unix (solaris) en production.

Je dois écrire une requete qui me rendra un certains chiffre, mais je dois l'adapter aux exigences d'unix.

cela fait 3 jours que je me bat mais sans succes contre cette requete, et j'ai la pression de mes chefs.

En fait, c'est lorsqu'il faut faire la difference entre 2 dates que tout capote, je ne sais pas aussi si quelque par il y a d'autres erreur, mais voici la fameuse requete

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
create table CHU as
select CODECLT
from DT.TBLCLT
where (todate(trandate+3600000),'yyyymmdd') BETWEEN (todate (to_number(trandate+3600000))-7776000000),'yyyymmdd') && (todate(trandate+3600000),'yyyymmdd'))
and SAL>0
and CLTCPTE=1
group by CODECLT,
CODECLT
order by CODECLT
;

J'ai besoin de votre claircoyance pour entrevoir l'avenir avec sérénité, sinon c'est l'hécatombe.


merci


Prince

[skuatamad]

todate => to_date
GROUP BY CODECLT,CODECLT => Une seule fois c'est suffisant, et comme il n'y a pas de fonction de regroupement DISTINCT à la place de GROUP BY est utilisable.

Si trandate est une date il est probable que les conversions soient inutiles.
Une explication du besoin et les messages d'erreurs renvoyés seraient appréciables.

mais je dois l'adapter aux exigences d'unix

Non quelque soit l'OS c'est transparent niveau SQL.

[Pozzo]

Salut,

Expliquer ce que ça doit faire serait assez bon en effet.

Le parenthésage me paraît incertain.
Le && dans l'opération between aussi

Tu es bien sous Oracle n'est-ce pas ?

Pozzo

[OBI-ONE]

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
CREATE TABLE CHU AS
SELECT CODECLT
FROM DT.TBLCLT
WHERE (todate(trandate+3600000),'yyyymmdd') BETWEEN (todate (to_number(trandate+3600000))-7776000000),'yyyymmdd') && (todate(trandate+3600000),'yyyymmdd'))
AND SAL>0
AND CLTCPTE=1
GROUP BY CODECLT,
CODECLT
ORDER BY CODECLT
;

En fait, le code doit me servir a calculer le nombre de client n'ayant effectuer aucune transaction en 90 jours d'intervalle.

j'ai un peu remodeler et formater le code comme ceci :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
CREATE TABLE chu AS
SELECT codeclt
FROM dt.tblclt
WHERE to_date(trandate+3600000,'yyyymmdd')
BETWEEN to_date(7776000000 - to_number(trandate+3600000),'yyyymmdd')
AND to_date(trandate+3600000,'yyyymmdd')
AND sal > 0
AND cltcpte=1
GROUP BY codeclt,
codeclt
ORDER BY codeclt;

je pense que le vrai probleme se trouve dans la difference entre les dates, trandate étant la date de la transaction, et 7776000000 represente 90 jours en millisecondes, trandate étant elle aussi en milliseconde, 3600000 represente 1 heure de temps en milliseconde, je dois toujours ajouter 1 heure pour retrouver l'heure exacte de la transaction car je suis a GMT+1.

Code :

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

1
2
3
4
5
 
WHERE to_date(trandate+3600000,'yyyymmdd')
                      *
ERROR at line 4:
ORA-01843: not a valid month

c'est l'erreur qu'il genere actuellement

Prince

[Pozzo]

Coté syntaxe :
Si trandate est une date il n'y a pas besoin de convertir avec des to_date et d'ajouter des 1/1000.
La date c'est trandate
1 heure après c'est trandate + 1/24
90 jours avant c'est trandate - 90

Coté conception :
Le clause where s'applique à chaque ligne.
Sur chaque ligne tu compares la date trandate à elle même diminuée de 90j ou augmentée d'une heure.
Je ne pense pas que cela réponde au problème qui est de mesurer des différences de date entre deux enregistrement, ou plus exactement pour une ligne de vérifier qu'il n'y a pas d'autre ligne dans les 90 derniers jours.

C'est plutôt une clause where not exists vers laquelle je m'orienterais.
Je cherche une ligne pour laquelle il n'existe pas de ligne après trandate - 90

Accessoirement :
Si toutes les dates sont décalées d'une heure et qu'on fait des comparaisons de date l'ajout d'une heure n'est pas utile car on mesure des intervales.

Pozzo

[OBI-ONE]

Après avoir lu le dernier post de Pozzo, j'ai réfléchi effectivement sur le fond et la forme du code, après avoir supprimer beaucoup d'imperfections, la requete tourne effectivement sous oracle, mais malheureusement, ne me donne pas les bons résultats, voici la requete

Code :

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

1
2
3
4
5
6
7
8
9
 
create table chu as
select codeclt
from dt.codeclt
where to_char(todate(trandate+3600000),'yyyymmdd')
between (select '20110212'-90 from dual) and '20110212'
and sal > 0
and cltcpte in (1,3)
group by codeclt;

[Waldar]

Que contient comme valeur la colonne trandate ?

Si vous pouviez fournir un jeu de test petit mais fidèle à votre problématique, c'est le plus simple.

[Pozzo]

Bonjour OBI-ONE,

Si vous travaillez sur les intervalles vous ne vous en tirerez qu'en faisant apparaître la table deux fois dans votre requête afin de comparer une ligne aux autres.

Voici un exemple qui illustre le propos avec une table des clients et une table des commandes.
Création de deux tables :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
-- Les clients (pour avoir des noms)
Create Table cli_client (
cli_id Number Not Null,
nom Varchar2 (50) Not Null
);
-- Les commandes des clients
Create Table cmd_commande (
cmd_id Number Not Null,
cli_id Number Not Null,
dat_commande Date Not Null
);

Renseignement des tables :

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
 
Delete cli_client;
Insert Into cli_client (cli_id, nom) Values (1, 'Client1');
Insert Into cli_client (cli_id, nom) Values (2, 'Client2');
Insert Into cli_client (cli_id, nom) Values (3, 'Client3');
 
Delete cmd_commande;
Insert Into cmd_commande (cmd_id, cli_id, dat_commande) 
Values (1,  1, to_date('01/01/2010', 'DD/MM/YYYY'));
Insert Into cmd_commande (cmd_id, cli_id, dat_commande) 
Values (2,  1, to_date('01/02/2010', 'DD/MM/YYYY'));
Insert Into cmd_commande (cmd_id, cli_id, dat_commande) 
Values (3,  1, to_date('01/06/2010', 'DD/MM/YYYY'));
Insert Into cmd_commande (cmd_id, cli_id, dat_commande) 
Values (4,  1, to_date('01/11/2010', 'DD/MM/YYYY'));
 
Insert Into cmd_commande (cmd_id, cli_id, dat_commande) 
Values (5,  2, to_date('01/01/2010', 'DD/MM/YYYY'));
Insert Into cmd_commande (cmd_id, cli_id, dat_commande) 
Values (6,  2, to_date('01/03/2010', 'DD/MM/YYYY'));
 
Insert Into cmd_commande (cmd_id, cli_id, dat_commande) 
Values (7,  2, to_date('01/05/2010', 'DD/MM/YYYY'));
Insert Into cmd_commande (cmd_id, cli_id, dat_commande) 
Values (8,  2, to_date('01/07/2010', 'DD/MM/YYYY'));
 
Insert Into cmd_commande (cmd_id, cli_id, dat_commande) 
Values (9,  3, to_date('01/08/2010', 'DD/MM/YYYY'));
Insert Into cmd_commande (cmd_id, cli_id, dat_commande) 
Values (10, 3, to_date('01/12/2010', 'DD/MM/YYYY'));
Commit;

Deux requêtes. Je suppose qu'on travaille au 31/12/2010. Si on travaille avec la date du jour on peut utiliser sysdate.

Qui n'a pas commandé dans les 90 derniers jours ?

Code :

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

1
2
3
4
5
6
7
8
9
 
Select cli.cli_id, cli.nom
  From cli_client cli
 Where Not
        cli_id In
        (Select cmd.cli_id
           From cmd_commande cmd
          Where cmd.dat_commande >= to_date('31/12/2010', 'DD/MM/YYYY') - 90)
Order By cli_id, nom;

CLI_ID NOM
---------- --------------------------------------------------
2 Client2

Qui parmi les clients ayant commandé, a connu une période de 90 j sans commande et quand (hormis la première commande qui fatalement ne suit pas une autre commande) ?

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
Select cmd.cli_id, cmd.dat_commande
  From cmd_commande cmd
 Where Not Exists (Select cmd2.cli_id, cmd2.dat_commande
          From cmd_commande cmd2
         Where cmd2.cli_id = cmd.cli_id
           And cmd2.dat_commande < cmd.dat_commande
           And cmd2.dat_commande >= cmd.dat_commande - 90)
   And Not (cmd.cli_id, cmd.dat_commande) In
        (Select cmd3.cli_id, Min(cmd3.dat_commande)
              From cmd_commande cmd3
             Group By cmd3.cli_id);

CLI_ID DAT_COMMANDE
---------- ------------
3 01/12/2010
1 01/11/2010
1 01/06/2010

En espérant que vous êtes encore par là.
Pozzo

[OBI-ONE]

Merci encore très cher Pozzo,

Ta démarche m'a inspiré, j'ai eu un moyen efficace de faire la difference entre date et jour.

En fait quand je fait date - 90, Oracle me renvoit bien sure une date, mais pas avec 90 jours de moins, c'était souvent avec 30 ou 28 jours de moins, j'ai compris que mon pb ne setrouvait pas là, mais surtout a cause d'unix, pour rappel ma BD est installé sous unix.

Pour se faire, j'ai trouvé une requete qui ne rate pas sa cible :

Code :

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

1
2
 
select substr(to_char((select to_timestamp('20110221 00:00:00','yyyymmdd HH24:MI:SS') + numtodsinterval(-90,'DAY') from dual)),1,9) from dual;

il suffit juste après de l'imbriquer dans la requete principale.