Discussion :

[loic20h28]

bonjour tout le monde,

J'ai une base de données sous FrontBase et je souhaite réaliser une requête.

La structure de ma base de données :
Article (id, libellé, prix, id_TypeArticle#)
TypeArticle (id, libellé)
AttributDuTypeArticle (id, nom, javaClasse, id_TypeArticle#)
AttributParArticle(id_Article#, id_Attribut#)
AttributParArticleEntier(id_Article#, id_Attribut#, valeur)
AttributParArticleTexte(id_Article#, id_Attribut#, valeur)
AttributParArticleDate(id_Article#, id_Attribut#, valeur)
=> champ souligné corresponde au clé primaire.
=> champ accompagné d'un # correspond au clé étrangère.

Voici la requête :

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
select *
from
((
select distinct a.libelle AS Article, AttEnt.valeur AS Entier, AttTexte.valeur  AS Texte, '' AS "Date"
from Article AS a, AttributParArticleEntier AS AttEnt, AttributParArticleTexte AS AttTexte
where a.id = AttEnt.id_Article
and a.id = AttTexte.id_Article
and AttTexte.id_Attribut = 4
)
union
(
select distinct a.libelle AS Article, AttEnt.valeur AS Entier, AttTexte.valeur AS Texte, '' AS "Date"
from Article AS a, AttributParArticleEntier AS AttEnt, AttributParArticleTexte AS AttTexte
where a.id = AttEnt.id_Article
and a.id = AttTexte.id_Article
and AttTexte.id_Attribut = 1
)
union
(
select distinct a.libelle AS Article, AttEnt.valeur AS Entier, AttTexte.valeur || ' - ' || AttTexte2.valeur AS Texte, AttDate.valeur AS "Date"
from Article AS a, AttributParArticleEntier AS AttEnt, AttributParArticleTexte AS AttTexte, AttributParArticleTexte AS AttTexte2, AttributParArticleDate AS AttDate
where a.id = AttEnt.id_Article
and a.id = AttTexte.id_Article
and a.id = AttTexte2.id_Article
and AttTexte2.id_Attribut = 6
and AttTexte.id_Attribut = 5
and a.id = AttDate.id_Article 
))
AS tmp

Voici l'erreur qui est généré :

Semantic error 217. Datatypes are not comparable or don't match.
Semantic error 485. Near: SELECT * FROM (((SELECT DISTINCT "a"."libelle" AS "Article", "AttEnt"."valeur" AS "Entier", "AttTexte"."valeur" AS "Texte", '' AS "Date" FROM "Article" AS "a","AttributParArticleEntier" AS "AttEnt","AttributParArticleTexte" AS "AttTexte" WHERE ("a"."id"="AttEnt"."id_Article") AND ("a"."id"="AttTexte"."id_Article") AND ("AttTexte"."id_Attribut"=4))UNION(SELECT DISTINCT "a"."libelle" AS "Article", "AttEnt"."valeur" AS "Entier", "AttTexte"."valeur" AS "Texte", '' AS "Date" FROM "Article" AS "a","AttributParArticleEntier" AS "AttEnt","AttributParArticleTexte" AS "AttTexte" WHERE ("a"."id"="AttEnt"."id_Article") AND ("a"."id"="AttTexte"."id_Article") AND ("AttTexte"."id_Attribut"=1)))UNION(SELECT DISTINCT "a"."libelle" AS "Article", "AttEnt"."valeur" AS "Entier", "AttTexte"."valeur" || ' - ' || "AttTexte2"."valeur" AS "Texte", "AttDate"."valeur" AS "Date" FROM "Article" AS "a","AttributParArticleEntier" AS "AttEnt","AttributParArticleTexte" AS "AttTexte","AttributParArticleTexte" AS "AttTexte2","AttributParArticleDate" AS "AttDate" WHERE ("a"."id"="AttEnt"."id_Article") AND ("a"."id"="AttTexte"."id_Article") AND ("a"."id"="AttTexte2"."id_Article") AND ("AttTexte2"."id_Attribut"=6) AND ("AttTexte"."id_Attribut"=5) AND ("a"."id"="AttDate"."id_Article"))) AS "tmp";.
Semantic error 485. Near: "AttributParArticleDate".
Exception 363 (40:000). Transaction rollback.

Comment puis-je faire??

Merci d'avance

[Snipah]

Salut,

As tu vérifié tes requetes une par une pour voir si ça colle?

Ensuite, c'est manifestement un probleme de type incompatible, or tu ne nous précises pas les types de tes champs.
Peut être la concaténation dans ta troisième requete, ou, plus probable, la chaine de caractère vide dans les deux premières pour ton champs "Date", essaie avec NULL ou une date remarquable comme '31/12/9999'.

Enfin, je crois que :

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 DISTINCT a.libelle AS Article, AttEnt.valeur AS Entier, AttTexte.valeur  AS Texte, '' AS "Date"
FROM Article AS a, AttributParArticleEntier AS AttEnt, AttributParArticleTexte AS AttTexte
WHERE a.id = AttEnt.id_Article
AND a.id = AttTexte.id_Article
AND AttTexte.id_Attribut = 4
)
union
(
SELECT DISTINCT a.libelle AS Article, AttEnt.valeur AS Entier, AttTexte.valeur AS Texte, '' AS "Date"
FROM Article AS a, AttributParArticleEntier AS AttEnt, AttributParArticleTexte AS AttTexte
WHERE a.id = AttEnt.id_Article
AND a.id = AttTexte.id_Article
AND AttTexte.id_Attribut = 1
)

est équivalent à :

Code :

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

1
2
3
4
5
6
 
SELECT DISTINCT a.libelle AS Article, AttEnt.valeur AS Entier, AttTexte.valeur  AS Texte, '' AS "Date"
FROM Article AS a, AttributParArticleEntier AS AttEnt, AttributParArticleTexte AS AttTexte
WHERE a.id = AttEnt.id_Article
AND a.id = AttTexte.id_Article
AND AttTexte.id_Attribut IN (4,1)

[loic20h28]

As tu vérifié tes requêtes une par une pour voir si ça colle?

J'ai fais requête par requête et elle fonctionne parfaitement.

AttributParArticleEntier(id_Article#, id_Attribut#, valeur)
=> valeur est de type Integer
AttributParArticleTexte(id_Article#, id_Attribut#, valeur)
=> valeur est de type varchar(30)
AttributParArticleDate(id_Article#, id_Attribut#, valeur)
=> valeur est de type date.

Si je met Null, j'obtiens comme erreur :

Semantic error 218. NULL not allowed here.
Semantic error 218. NULL not allowed here.
Exception 363 (40:000). Transaction rollback.

Si je met une date du style '9999-12-31', voici la requête que j'obtiens :

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
SELECT *
FROM
((
SELECT DISTINCT a.libelle AS Article, AttEnt.valeur AS Entier, AttTexte.valeur  AS Texte, date'9999-12-31' AS "Date"
FROM Article AS a, AttributParArticleEntier AS AttEnt, AttributParArticleTexte AS AttTexte
WHERE a.id = AttEnt.id_Article
AND a.id = AttTexte.id_Article
AND AttTexte.id_Attribut = 4
)
union
(
SELECT DISTINCT a.libelle AS Article, AttEnt.valeur AS Entier, AttTexte.valeur AS Texte, date'9999-12-31' AS "Date"
FROM Article AS a, AttributParArticleEntier AS AttEnt, AttributParArticleTexte AS AttTexte
WHERE a.id = AttEnt.id_Article
AND a.id = AttTexte.id_Article
AND AttTexte.id_Attribut = 1
)
union
(
SELECT DISTINCT a.libelle AS Article, AttEnt.valeur AS Entier, AttTexte.valeur || ' - ' || AttTexte2.valeur AS Texte, AttDate.valeur AS "Date"
FROM Article AS a, AttributParArticleEntier AS AttEnt, AttributParArticleTexte AS AttTexte, AttributParArticleTexte AS AttTexte2, AttributParArticleDate AS AttDate
WHERE a.id = AttEnt.id_Article
AND a.id = AttTexte.id_Article
AND a.id = AttTexte2.id_Article
AND AttTexte2.id_Attribut = 6
AND AttTexte.id_Attribut = 5
AND a.id = AttDate.id_Article 
))
AS tmp

cela fonctionne mais moi je souhaite qu'à la place de 9999-12-31 on est rien, cela est-possible??

Cordialement

[Az2000]

essaie avec NULL ou une date remarquable comme '31/12/9999'.

Arghh, t'as répondu trop vite ... la suite en dessous...

[loic20h28]

J'ai essayé regarde :

Si je met Null, j'obtiens comme erreur :
Citation:
Semantic error 218. NULL not allowed here.
Exception 363 (40:000). Transaction rollback.

la requête ça donne :

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
select *
from
(( 
SELECT DISTINCT a.libelle AS Article, AttEnt.valeur AS Entier, AttTexte.valeur  AS Texte, null AS DateDePublication
FROM Article AS a, AttributParArticleEntier AS AttEnt, AttributParArticleTexte AS AttTexte
WHERE a.id = AttEnt.id_Article
AND a.id = AttTexte.id_Article
AND AttTexte.id_Attribut IN (4,1)
)
union
(
SELECT DISTINCT a.libelle AS Article, AttEnt.valeur AS Entier, AttTexte.valeur || ' - ' || AttTexte2.valeur AS Texte, AttDate.valeur AS "Date"
FROM Article AS a, AttributParArticleEntier AS AttEnt, AttributParArticleTexte AS AttTexte, AttributParArticleTexte AS AttTexte2, AttributParArticleDate AS AttDate
WHERE a.id = AttEnt.id_Article
AND a.id = AttTexte.id_Article
AND a.id = AttTexte2.id_Article
AND AttTexte2.id_Attribut = 6
AND AttTexte.id_Attribut = 5
AND a.id = AttDate.id_Article 
))
AS tmp

j'ai même essayé :

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
select *
from
(( 
SELECT DISTINCT a.libelle AS Article, AttEnt.valeur AS Entier, AttTexte.valeur  AS Texte, '0000-00-00' AS DateDePublication
FROM Article AS a, AttributParArticleEntier AS AttEnt, AttributParArticleTexte AS AttTexte
WHERE a.id = AttEnt.id_Article
AND a.id = AttTexte.id_Article
AND AttTexte.id_Attribut IN (4,1)
)
union
(
SELECT DISTINCT a.libelle AS Article, AttEnt.valeur AS Entier, AttTexte.valeur || ' - ' || AttTexte2.valeur AS Texte, AttDate.valeur AS "Date"
FROM Article AS a, AttributParArticleEntier AS AttEnt, AttributParArticleTexte AS AttTexte, AttributParArticleTexte AS AttTexte2, AttributParArticleDate AS AttDate
WHERE a.id = AttEnt.id_Article
AND a.id = AttTexte.id_Article
AND a.id = AttTexte2.id_Article
AND AttTexte2.id_Attribut = 6
AND AttTexte.id_Attribut = 5
AND a.id = AttDate.id_Article 
))
AS tmp

voici l'erreur que cela me donne :

Semantic error 217. Datatypes are not comparable or don't match.
Semantic error 485. Near: SELECT * FROM ((SELECT DISTINCT "a"."libelle" AS "Article", "AttEnt"."valeur" AS "Entier", "AttTexte"."valeur" AS "Texte", '0000-00-00' AS "DateDePublication" FROM "Article" AS "a","AttributParArticleEntier" AS "AttEnt","AttributParArticleTexte" AS "AttTexte" WHERE ("a"."id"="AttEnt"."id_Article") AND ("a"."id"="AttTexte"."id_Article") AND (("AttTexte"."id_Attribut")=ANY (VALUES 4, 1)))UNION(SELECT DISTINCT "a"."libelle" AS "Article", "AttEnt"."valeur" AS "Entier", "AttTexte"."valeur" || ' - ' || "AttTexte2"."valeur" AS "Texte", "AttDate"."valeur" AS "Date" FROM "Article" AS "a","AttributParArticleEntier" AS "AttEnt","AttributParArticleTexte" AS "AttTexte","AttributParArticleTexte" AS "AttTexte2","AttributParArticleDate" AS "AttDate" WHERE ("a"."id"="AttEnt"."id_Article") AND ("a"."id"="AttTexte"."id_Article") AND ("a"."id"="AttTexte2"."id_Article") AND ("AttTexte2"."id_Attribut"=6) AND ("AttTexte"."id_Attribut"=5) AND ("a"."id"="AttDate"."id_Article"))) AS "tmp";.
Semantic error 485. Near: "AttributParArticleDate".
Exception 363 (40:000). Transaction rollback.

Cordialement

[Az2000]

Essaie de mettre NULL, en commençant ton union par le dernier select (celui où il n'y a pas Null).

Sinon, il faut que tu "cast" Null en date, en espérant que tu connaisses la syntaxe, car je ne connais pas ta BDD.

[Snipah]

Oui loic, j'ai supprimé mon message quand j'ai relu ta précédente réponse, mais manifestement tu l'avais déjà lu, désolé
J'ai fait quelques tests entre temps, j'ai pas ce probleme sous Oracle.

[loic20h28]

Réponse à Az2000 :
Tout d'abord merci d'avoir pris de ton temps pour me répondre.
J'ai essayé avec NULL en commençant par la requête sans null mais ça fonctionné pas.
Ensuite, je savais pas du tout comment caster null en date, j'ai regardé sur internet en tappant sur google : "oracle : cast null en date" et j'ai trouvé.
Et oui, étrange FrontBase prend des choses comme oracle.
Voici ce que cela me donne :

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
select *
from
(( 
SELECT DISTINCT a.libelle AS Article, AttEnt.valeur AS Entier, AttTexte.valeur || ' - ' || AttTexte2.valeur AS Texte, AttDate.valeur AS "Date"
FROM Article AS a, AttributParArticleEntier AS AttEnt, AttributParArticleTexte AS AttTexte, AttributParArticleTexte AS AttTexte2, AttributParArticleDate AS AttDate
WHERE a.id = AttEnt.id_Article
AND a.id = AttTexte.id_Article
AND a.id = AttTexte2.id_Article
AND AttTexte2.id_Attribut = 6
AND AttTexte.id_Attribut = 5
AND a.id = AttDate.id_Article
)
union
(
SELECT DISTINCT a.libelle AS Article, AttEnt.valeur AS Entier, AttTexte.valeur  AS Texte, cast(null as date) AS DateDePublication
FROM Article AS a, AttributParArticleEntier AS AttEnt, AttributParArticleTexte AS AttTexte
WHERE a.id = AttEnt.id_Article
AND a.id = AttTexte.id_Article
AND AttTexte.id_Attribut IN (4,1)
))
AS tmp

Réponse à Snipah :
Un grand merci pour ton aide quand même.

Merci à vous deux!!