[IQ 12.7] Erreur d'une requête

[hittony]

Bonjour,

Je voulais faire un contrôle sur le code année du numéro sécu.

Code :

1
2
3
4
5
6
SELECT * FROM
    (SELECT * FROM table1
    WHERE substr(NoSS, 2, 1) BETWEEN '0' AND '9'
    AND substr(NoSS, 3, 1) BETWEEN '0' AND '9') t1
WHERE year(today()) - 1900 - cast(substr(NoSS, 2, 2) AS integer)
NOT BETWEEN 16 AND 70

ASA Error -1001006: Data exception - data type conversion is not possible.

Code :

1
2
3
4
5
6
SELECT * FROM
    (SELECT * FROM table1
    WHERE substr(NoSS, 2, 1) BETWEEN '0' AND '9'
    AND substr(NoSS, 3, 1) BETWEEN '0' AND '9') t1
WHERE year(today()) - 1900 - cast(substr(NoSS, 2, 2) AS integer) < 16 OR 
year(today()) - 1900 - cast(substr(NoSS, 2, 2) AS integer) > 70

ASA Error -1001006: Data exception - data type conversion is not possible.

Code :

1
2
3
4
5
6
SELECT * FROM
    (SELECT * FROM table1
    WHERE substr(NoSS, 2, 1) BETWEEN '0' AND '9'
    AND substr(NoSS, 3, 1) BETWEEN '0' AND '9') t1
WHERE year(today()) - 1900 - cast(substr(NoSS, 2, 2) AS integer) < 16 OR 
--year(today()) - 1900 - cast(substr(NoSS, 2, 2) as integer) > 70

It works!

Code :

1
2
3
4
5
6
SELECT * FROM
    (SELECT * FROM table1
    WHERE substr(NoSS, 2, 1) BETWEEN '0' AND '9'
    AND substr(NoSS, 3, 1) BETWEEN '0' AND '9') t1
WHERE --year(today()) - 1900 - cast(substr(NoSS, 2, 2) as integer) < 16 or 
year(today()) - 1900 - cast(substr(NoSS, 2, 2) AS integer) > 70

It works too!



par avance

[remi.]

Bonjour,
Iq n'aime pas que l'on cast les types de données.

Souhaites tu sélectionner les gens entre 16 et 70 ans ?
Essayes la requête suivante :

Code :

1
2
3
4
5
6
SELECT * FROM
    (SELECT * FROM table1
    WHERE substr(NoSS, 2, 1) BETWEEN '0' AND '9'
    AND substr(NoSS, 3, 1) BETWEEN '0' AND '9') t1
WHERE
year(current date) - 1900 - convert(smallint, substr(NoSS,2,2))  BETWEEN 16 AND 70

Rémi.

[hittony]

Envoyé par remi.

Souhaites tu sélectionner les gens entre 16 et 70 ans ?

Merci pour ta réponse, mais... je voulais le contraire.

[remi.]

Il suffit donc de rajouter le mot clé NOT devant between.

[hittony]

Envoyé par hittony

Bonjour,

Je voulais faire un contrôle sur le code année du numéro sécu.

Code :

ASA Error -1001006: Data exception - data type conversion is not possible.

C'est exactement ce que j'ai essayé.

[remi.]

Ajoute NOT a ma requête, pas a la tienne, chez moi ca marche.

Code :

1
2
3
4
5
6
SELECT * FROM
    (SELECT * FROM table1
    WHERE substr(NoSS, 2, 1) BETWEEN '0' AND '9'
    AND substr(NoSS, 3, 1) BETWEEN '0' AND '9') t1
WHERE
year(current date) - 1900 - convert(smallint, substr(NoSS,2,2)) NOT BETWEEN 16 AND 70