Discussion :

[Marc_27]

Bonjour à tous,

J'ai un tout petit soucis dans une requete AS400 (c'est en fait une requete SQL direct faite dans Access) qui j'essaye de faire:

J'ai un champ dtfin au format aaaammjj, et j'essaye de faire une clause WHERE comme la suivant:

Code sql :

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

where  cast(substr(dtfin, 1, 4)||'-'||substr(dtfin, 5, 2)||'-'||substr(dtfin, 7, 2) as date)>current date

Normalement ça marche bien, mais dans cette table j'ai des donnés '99999999' qui veulent dire "à la vie", et donc j'ai l'erreur "SQL0181: Une valeur de la chaine date, heure ou horodatage est incorrect" (au moins je crois qui ça soit la cause de l'erreur...)

Est-ce que c'est possible de contourner ce problème avec un jor de

Code sql :

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

WHERE CASE ... WHEN ...

?

Merci d'avance pour vos reponses...

[SQLpro]

Une telle date 9999999999 n'existant pas vous ne pouvez pas la convertir. Prenez une vraie date, par exemple 99991231.

A +

[Marc_27]

Merci beaucoup SQLpro.

En fait j'étais en train de essayer ça avec

Code :

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

Replace(dtfin, '99999999', '99991231')

dans mon condition WHERE.

Maintenant je ne comprends plus rien . Je m'explique:
Voilà la condition WHERE de ma requete:

Code sql :

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

1
2
3
4
5
6
WHERE (ntlrp in(select noclie from pack10pdta.agposp0s where codde1='EUR' and etacpt<>3 and nacmpt in ('205','210','211','212','213','214','215','216','260','261','262','263','264','265','266','267','268')) 
or ntlrp in (SELECT  noclco FROM pack10pdta.fccotp0s where nocmpt in (select nocmpt from pack10pdta.fcgcop0s where etacpt<>'3' and nacmpt in ('213','214','216','262','264')))
or ntlrp in (SELECT  noclma FROM pack10pdta.fcmanp0s where nocmpt in (select nocmpt from pack10pdta.fcgcop0s where etacpt<>'3' and nacmpt in ('205','210','211','212','213','214','215','216','260','261','262','263','264','265','266','267','268')))
or ntlrp in (SELECT notdes FROM pack10pdta.ptit)
or ntlrp in (SELECT garant FROM pack10pdta.EPSGDEP000 where rfgara in (select rfgara  from pack10pdta.epsgdcp000  where  dtfin>'20100505')) 
or ntlrp in (SELECT garant FROM pack10pdta.EPSGDGP000 where rfgara in(select rfgara from pack10pdta.epsgdcp000 where dtfin>'20100505')))

Et elle marche très bien.

Après changer (dans la ligne avant-dernière)

Code sql :

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

SELECT garant FROM pack10pdta.EPSGDEP000 where rfgara in (select rfgara  from pack10pdta.epsgdcp000  where  dtfin>'20100505'

par

Code sql :

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

SELECT garant FROM pack10pdta.EPSGDEP000 where rfgara in (select rfgara  from pack10pdta.epsgdcp000  where  cast(substr(replace(dtfin, '99999999', '99991231'), 1, 4)||'-'||substr(replace(dtfin, '99999999', '99991231'), 5, 2)||'-'||substr(replace(dtfin, '99999999', '99991231'), 7, 2) as date)>current date

Elle donne l'erreur 0181.

[Marc_27]

Le truc c'est qui si je fais la requete

Code sql :

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

SELECT garant FROM pack10pdta.EPSGDEP000 WHERE rfgara IN (SELECT rfgara  FROM pack10pdta.epsgdcp000  WHERE  cast(substr(REPLACE(dtfin, '99999999', '99991231'), 1, 4)||'-'||substr(REPLACE(dtfin, '99999999', '99991231'), 5, 2)||'-'||substr(REPLACE(dtfin, '99999999', '99991231'), 7, 2) AS date)>current date

toute seule, sans la requete principale, elle marche bien...

[CinePhil]

Elle donne l'erreur 0181.

Quel SGBD ?

Ne serait-ce pas CURRENT_DATE avec underscore plutôt que CURRENT DATE en deux mots ?

Le truc c'est qui si je fais la requete toute seule, sans la requete principale, elle marche bien...

Donc l'erreur ne semble venir de là.

Plutôt que ce WHERE hyper complexe et probablement peu performant, ce ne serait pas plus simple de faire des jointures ?
Qu'y a t-il avant le WHERE dans la requête ?

[Marc_27]

Merci de ton réponse CinePhil,


En fait c'était pas moi qui a fait le requete, je viens de la prendre pour y ameliorer un tout petit peu, car je n'ai pas aussi beaucoup de connaissances sur les tables de cette base AS400...

J'ai essayé avec le "_" et ça ne change rien, dans la requet ecomplexe et même dans la requete simple.

Voilà ma requete complete (la version qui ne marche pas ):

Code sql :

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

1
2
3
4
5
6
7
8
9
SELECT cast(ntlrp as int) as tiers, cdqual as qualite, cdtitr as titre, nmrsoc as nom, nmprst prenom, nmjf as epouse , cast(substr(dsnais*1000000+danais*10000+dmnais*100+djnais, 1, 4)||'-'||substr(dsnais*1000000+danais*10000+dmnais*100+djnais, 5, 2)||'-'||substr(dsnais*1000000+danais*10000+dmnais*100+djnais, 7, 2) as date) as date_nais,  lbcnai as lieu, cast(cdprof as smallint) as CSP, cdsitf as sit_f, cdrmat as r_mat, cast(substr(dscrea*1000000+dacrea*10000+dmcrea*100+djcrea, 1, 4)||'-'||substr(dscrea*1000000+dacrea*10000+dmcrea*100+djcrea, 5, 2)||'-'||substr(dscrea*1000000+dacrea*10000+dmcrea*100+djcrea, 7, 2) as date) as date_cre, cast(substr(dsmaj*1000000+damaj*10000+dmmaj*100+djmaj, 1, 4)||'-'||substr(dsmaj*1000000+damaj*10000+dmmaj*100+djmaj, 5, 2)||'-'||substr(dsmaj*1000000+damaj*10000+dmmaj*100+djmaj, 7, 2) as date)  as date_maj, cdgsui as gstr, cdpnat as NAT, cdprsd as RSD, notldo as TEL_1, notlbu as TEL_2, notelf as FAX, notelx as AUTRE_TEL, cdvip as PPE
FROM pack10pdta.ptip
WHERE (ntlrp in(select noclie from pack10pdta.agposp0s where codde1='EUR' and etacpt<>3 and nacmpt in ('205','210','211','212','213','214','215','216','260','261','262','263','264','265','266','267','268')) 
or ntlrp in (SELECT  noclco FROM pack10pdta.fccotp0s where nocmpt in (select nocmpt from pack10pdta.fcgcop0s where etacpt<>'3' and nacmpt in ('213','214','216','262','264')))
or ntlrp in (SELECT  noclma FROM pack10pdta.fcmanp0s where nocmpt in (select nocmpt from pack10pdta.fcgcop0s where etacpt<>'3' and nacmpt in ('205','210','211','212','213','214','215','216','260','261','262','263','264','265','266','267','268')))
or ntlrp in (SELECT notdes FROM pack10pdta.ptit)
or ntlrp in (SELECT garant FROM pack10pdta.EPSGDEP000 where rfgara in (select rfgara  from pack10pdta.epsgdcp000  where  cast(substr(replace(dtfin, '99999999', '99991231'), 1, 4)||'-'||substr(replace(dtfin, '99999999', '99991231'), 5, 2)||'-'||substr(replace(dtfin, '99999999', '99991231'), 7, 2) as date)>current date)) 
or ntlrp in (SELECT garant FROM pack10pdta.EPSGDGP000 where rfgara in(select rfgara from pack10pdta.epsgdcp000 where dtfin>'20100505')))
ORDER BY ntlrp

Ce qui je ne comprends pas c'est qui si les deux requetes A et B donnent les mêmes resultats, pour quoi je ne pourrais pas changer A pour B dans la requete principale...

[CinePhil]

C'est une horreur cette requête !

Il faudrait voir du côté des fonctions de dates du SGBD s'il y a moyen de simplifier ces CAST à rallonge et de transformer cette date sous format texte 'aaaammjj' en date SQL 'aaaa-mm-jj'.

Je ne vois pas trop l'intérêt non plus de CASTer par exemple la colonne cdprof (code professionnel semble t-il) en SMALLINT alors que c'est un code et qu'on ne fera probablement pas de calculs dessus.

Quant au WHERE complexe, je confirme qu'il doit être possible d'utiliser des jointures externes avec les tables impliquées dans les sous-requêtes, ce qui devrait être plus performant.

Pas le temps de m'y pencher maintenant.

Si quelqu'un veut s'amuser, j'ai remis en forme la requête pour la rendre lisible. Du coup elle fait presque 100 lignes !
Je serais curieux de connaître le temps de réponse d'un tel machin, et sur quel volume de données, ainsi que son plan d'exécution.

Pour ton problème de message d'erreur, essaie avec le WHERE CASE que j'ai mis dans la requête remise en forme ci-dessous :

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
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
SELECT cast(ntlrp AS int) AS tiers, 
  cdqual AS qualite, 
  cdtitr AS titre, 
  nmrsoc AS nom, 
  nmprst AS prenom, 
  nmjf AS epouse, 
  cast(
    substr(dsnais * 1000000 + danais * 10000 + dmnais * 100 + djnais, 1, 4)
    ||'-'||
    substr(dsnais * 1000000 + danais * 10000 + dmnais * 100 + djnais, 5, 2)
    ||'-'||
    substr(dsnais * 1000000 + danais * 10000 + dmnais * 100 + djnais, 7, 2) 
    AS date
  ) AS date_nais,  
  lbcnai AS lieu, 
  cast(cdprof AS smallint) AS CSP, 
  cdsitf AS sit_f, 
  cdrmat AS r_mat, 
  cast(
    substr(dscrea * 1000000 + dacrea * 10000 + dmcrea * 100 + djcrea, 1, 4)
    ||'-'||
    substr(dscrea * 1000000 + dacrea * 10000 + dmcrea * 100 + djcrea, 5, 2)
    ||'-'||
    substr(dscrea * 1000000 + dacrea * 10000 + dmcrea * 100 + djcrea, 7, 2) 
    AS date
  ) AS date_cre, 
  cast(
    substr(dsmaj * 1000000 + damaj * 10000 + dmmaj * 100 + djmaj, 1, 4)
    ||'-'||
    substr(dsmaj * 1000000 + damaj * 10000 + dmmaj * 100 + djmaj, 5, 2)
    ||'-'||
    substr(dsmaj * 1000000 + damaj * 10000 + dmmaj * 100 + djmaj, 7, 2) 
    AS date
  )  AS date_maj, 
  cdgsui AS gstr, 
  cdpnat AS NAT, 
  cdprsd AS RSD, 
  notldo AS TEL_1, 
  notlbu AS TEL_2, 
  notelf AS FAX, 
  notelx AS AUTRE_TEL, 
  cdvip AS PPE
FROM pack10pdta.ptip
WHERE 
(
  ntlrp IN (
    SELECT noclie 
    FROM pack10pdta.agposp0s 
    WHERE codde1 = 'EUR' 
      AND etacpt <> 3 
      AND nacmpt IN ('205','210','211','212','213','214','215','216','260','261','262','263','264','265','266','267','268')
  ) 
  OR ntlrp IN (
    SELECT  noclco 
    FROM pack10pdta.fccotp0s 
    WHERE nocmpt IN (
      SELECT nocmpt 
      FROM pack10pdta.fcgcop0s 
      WHERE etacpt <> '3' 
    AND nacmpt IN ('213','214','216','262','264')
    )
  )
  OR ntlrp IN (
    SELECT  noclma 
    FROM pack10pdta.fcmanp0s 
    WHERE nocmpt IN (
      SELECT nocmpt 
      FROM pack10pdta.fcgcop0s 
      WHERE etacpt <> '3' 
    AND nacmpt IN ('205','210','211','212','213','214','215','216','260','261','262','263','264','265','266','267','268')
    )
  )
  OR ntlrp IN (
    SELECT notdes 
    FROM pack10pdta.ptit
  )
  OR ntlrp IN (
    SELECT garant 
    FROM pack10pdta.EPSGDEP000 
    WHERE rfgara IN (
      SELECT rfgara  
      FROM pack10pdta.epsgdcp000  
      WHERE  
    CASE 
      WHEN dtfin <> '99999999' THEN
        cast(substr(dtfin, 1, 4)||'-'||substr(dtfin, 5, 2)||'-'||substr(dtfin, 7, 2) AS date) > current_date
    END
    )
  ) 
  OR ntlrp IN (
    SELECT garant 
    FROM pack10pdta.EPSGDGP000 
    WHERE rfgara IN (
      SELECT rfgara 
      FROM pack10pdta.epsgdcp000 
      WHERE dtfin > '20100505')
    )
  )

[Marc_27]

Je te remercie vraiment pour l'aide, CinePhil.

J'ai essayé la requete (toute simple):

Code :

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

1
2
3
4
5
6
select rfgara, dtfin  
from pack10pdta.epsgdcp000 
where
CASE 
     WHEN dtfin <> '99999999' THEN  cast(substr(dtfin, 1, 4)||'-'||substr(dtfin, 5, 2)||'-'||substr(dtfin, 7, 2) AS date) > current date
END

mais elle ne marche pas, il y a quelque chose qui ne va pas dans ce code...

Aurais-tu une idée du pour quoi?
Merci encore une fois

[CinePhil]

mais elle ne marche pas

Symptôme ?
- message d'erreur ?
- mauvais résultat ?
- pas de résultat ?

[Marc_27]

J'ai ODBC connection failed
Comme j'avais dit, c'est une requete SQL Direct d'Access vers AS400.

j'ai contourné l eproblème en faisant:

Code sql :

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

WHERE (dtfin='99999999') OR (NOT dtfin='99999999' AND cast(substr(dtfin, 1, 4)||'-'||substr(dtfin, 5, 2)||'-'||substr(dtfin, 7, 2) AS date) > current date)

Mais j'ai toujours le même soucis:
Cette requete marche bien mais lors que je la mets dans ma requete principale j'ai toujours l'erreur 0181 en disant qu'il y a un soucis avec les dates...

[Waldar]

Il y avait une erreur syntaxique, il fallait finir la case avant d'effectuer la comparaison :

Code :

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

1
2
3
4
CASE 
  WHEN dtfin <> '99999999'
  THEN  cast(substr(dtfin, 1, 4)||'-'||substr(dtfin, 5, 2)||'-'||substr(dtfin, 7, 2) AS date)
END > current_date

[CinePhil]

Résumons...

Quand tu exécutes la requête complète sans la comparaison à CURRENT_DATE mais en comparant à une valeur de date spécifique et en format texte, elle fonctionne.

Quand tu exécutes la requête complète avec comparaison à CURRENT_DATE, message d'erreur 0181.

Quand tu exécutes la sous-requête contenant la comparaison à CURRENT_DATE, elle fonctionne.

C'est ça ?

D'autre part, ta requête est exécutée via Access vers un AS400 dis-tu. Je connais AS400 comme étant une machine IBM mais pas comme un SGBD. C'est quel SGBD qui est interogé sur l'AS400 ?

[Marc_27]

Il semble un peu bete vu comme celle-la mais oui c'est bien ça...
J'ai une grande requete avec une sous-requete A qui marche bien.
Si je change ce sous-requete A pour une autre sous-requete B (que si toute seule marche bien aussi), ma grande requete donne l'erreur 181 ci-dessus.

C'est important de dire que les resultats de A et B sont identiques...
Cette une base IBM-DB2, c'est ça que tu voudrais savoir?

Et bien sure, merci beaucoup pour tout ce temps que tu donnes à mon problème...

[K2R400]

Pour ta Date, au lieu d'utiliser

Code :

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

cast(substr(dtfin, 1, 4)||'-'||substr(dtfin, 5, 2)||'-'||substr(dtfin, 7, 2) AS date) > current date)

Utilises plutôt

Code :

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

Where Date( INSERT(INSERT(dtfin, 5, 0, '-'), 8, 0, '-') ) > current date)

[Marc_27]

Merci K2R400 (encore une fois )

J'ai changé les requetes, mais j'ai toujours le même erreur:
les requetes

Code sql :

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

SELECT garant FROM pack10pdta.EPSGDEP000 where rfgara in (select rfgara  from pack10pdta.epsgdcp000  where  ((dtfin='99999999') or (not dtfin='99999999' and Date( INSERT(INSERT(dtfin, 5, 0, '-'), 8, 0, '-') ) > current date))

Code sql :

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

SELECT garant FROM pack10pdta.EPSGDGP000 where  ((dtfin='99999999') or (not dtfin='99999999' and Date( INSERT(INSERT(dtfin, 5, 0, '-'), 8, 0, '-') ) > current date))

marchent bien mais lorsque je les ai mis dans ma requete principale j'ai l'erreur 181: Une valeur de la chaîne date, heure ou horodatage es incorrect.


Est-ce que je suis en train de perdre quelque chose ici?
Merci encore une fois...

[bernard59139]

Bonjour

comme dtfin n'est pas une date, et comme on ne peut pas etre sur à 100% de l'ordre d'interprétation des ordres sql,
je pense qu'il est souhaitable de travailler sur la "CURRENT DATE" , ce qui devrait donner:

Code :

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

1
2
3
4
 
dtfin > SUBSTR(CHAR(CURRENT DATE,ISO),1,4)!!       
          SUBSTR(CHAR(CURRENT DATE,ISO),6,2)!!       
          SUBSTR(CHAR(CURRENT DATE,ISO),2,2)

code pas testé...

[Marc_27]

Bonjour bernard59139 et merci de ta réponse.
Il a marché!!!


Je suis vraiment loin de comprendre le soucis, mais l'important est-ce que tout marche bien maintenant.

Merci encore une fois à toi et bien sure aux autres que m'ont vraiment aidé.

[bernard59139]

Le soucis est qu'en interne, tu ne sauras jamais ce que db2 fait vraiment.
par exemple:

Code :

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

dtfin='99999999' or date(dtfin)>'2010-01-01'

quel morceau db2 va évaluer en 1er? ou alors, évaluation des 2 morceaux puis analyse du résultat?
Et même si cela fonctionne avec une requête, un changement quelconque (version, ptf, ré-écriture de la requete, ..) pourra produire une erreur.

bon week-end

[Marc_27]

Bonjour et merci pour tes précisions bernard59139.
Normalement je travaille avec Access/ SQL Server, et donc je n'ai pas trop de connaissances de DB2.