I think, there is really a bug see below :

Le 21/02/2005 10:52, Philippe Makowski a dit :

> Hello,
> I have 3 tables :
>
> CREATE TABLE CMOTP_RGPCOMS
> (
> SOUS_SECTION CHAR(2) NOT NULL COLLATE ISO8859_1,
> CODE_DEPT CHAR(3) NOT NULL,
> CODE_COMMUNE CHAR(3) NOT NULL,
> ANNEE SMALLINT NOT NULL,
> SOMME_PAYEE DECIMAL(12,1) ,
> SECTION CHAR(1),
> CONSTRAINT PK_CMOTP_RGPCOMS PRIMARY KEY (SOUS_SECTION, CODE_DEPT,
> CODE_COMMUNE, ANNEE));
>
> CREATE TABLE CMOTP_TAUX
> (
> ANNEE SMALLINT NOT NULL,
> CODE_DEPT CHAR(3) NOT NULL,
> CODE_COMMUNE CHAR(3) NOT NULL,
> COMMUNE VARCHAR(35) NOT NULL COLLATE FR_FR,
> CONSTRAINT PK_CMOTP_TAUX PRIMARY KEY (ANNEE, CODE_DEPT, CODE_COMMUNE));
>
> CREATE TABLE CMOTP_NAF
> (
> CODE_NAF CHAR(4) NOT NULL,
> LIBELLE VARCHAR(121) COLLATE FR_FR,
> DIVISION CHAR(2) COLLATE FR_FR,
> SECTION CHAR(1) COLLATE FR_FR,
> SOUS_SECTION CHAR(2) COLLATE FR_FR,
> LIB_DIVISION VARCHAR(66) COLLATE FR_FR,
> LIB_SECTION VARCHAR(62) COLLATE FR_FR,
> LIB_SOUS_SECTION VARCHAR(62) COLLATE FR_FR,
> CONSTRAINT PK_CMOTP_NAF PRIMARY KEY (CODE_NAF)
> );
>
> One view :
>
> CREATE VIEW CMOTP_V_RGPCOMS
> (
> SECTION,
> CODE_DEPT,
> CODE_COMMUNE,
> ANNEE,
> SOMME_PAYEE
> ) AS
>
> SELECT
> SECTION,
> CODE_DEPT,
> CODE_COMMUNE,
> ANNEE,
> SUM(SOMME_PAYEE)
> FROM CMOTP_RGPCOMS
> GROUP BY SECTION,CODE_DEPT,CODE_COMMUNE, ANNEE;
>
> and now :
>
> SELECT * FROM CMOTP_V_RGPCOMS;
> SECTION CODE_DEPT CODE_COMMUNE ANNEE SOMME_PAYEE
> A 093 001 1995 120
> A 093 001 1996 125
> A 093 005 1995 130
> A 093 005 1996 135
>
>
> SELECT CMOTP_V_RGPCOMS.* FROM CMOTP_V_RGPCOMS
> WHERE CMOTP_V_RGPCOMS.CODE_DEPT='093'
> AND CMOTP_V_RGPCOMS.CODE_COMMUNE='001';
> SECTION CODE_DEPT CODE_COMMUNE ANNEE SOMME_PAYEE
> A 093 001 1995 120
> A 093 001 1996 125
>
> SELECT CMOTP_V_RGPCOMS.*, CMOTP_TAUX.COMMUNE FROM CMOTP_V_RGPCOMS
> INNER JOIN CMOTP_TAUX
> ON CMOTP_V_RGPCOMS.ANNEE=CMOTP_TAUX.ANNEE
> AND CMOTP_V_RGPCOMS.CODE_DEPT=CMOTP_TAUX.CODE_DEPT
> AND CMOTP_V_RGPCOMS.CODE_COMMUNE=CMOTP_TAUX.CODE_COMMUNE
> WHERE CMOTP_V_RGPCOMS.CODE_DEPT='093'
> AND CMOTP_V_RGPCOMS.CODE_COMMUNE='001';
> SECTION CODE_DEPT CODE_COMMUNE ANNEE SOMME_PAYEE COMMUNE
> A 093 001 1995 120 BLA
> A 093 001 1996 125 BLA
>
> SELECT CMOTP_V_RGPCOMS.*, CMOTP_NAF.LIB_SECTION, CMOTP_TAUX.COMMUNE FROM
> CMOTP_V_RGPCOMS
> INNER JOIN CMOTP_NAF
> ON CMOTP_V_RGPCOMS.SECTION = CMOTP_NAF.SECTION
> INNER JOIN CMOTP_TAUX
> ON CMOTP_V_RGPCOMS.ANNEE=CMOTP_TAUX.ANNEE
> AND CMOTP_V_RGPCOMS.CODE_DEPT=CMOTP_TAUX.CODE_DEPT
> AND CMOTP_V_RGPCOMS.CODE_COMMUNE=CMOTP_TAUX.CODE_COMMUNE;
> SECTION CODE_DEPT CODE_COMMUNE ANNEE SOMME_PAYEE LIB_SECTION COMMUNE
> A 093 001 1995 120 LIB_A BLA
> A 093 001 1996 125 LIB_A BLA
> A 093 005 1995 130 LIB_A BLA
> A 093 005 1996 135 LIB_A BLA
>
> SELECT CMOTP_V_RGPCOMS.*, CMOTP_NAF.LIB_SECTION, CMOTP_TAUX.COMMUNE FROM
> CMOTP_V_RGPCOMS
> INNER JOIN CMOTP_NAF
> ON CMOTP_V_RGPCOMS.SECTION = CMOTP_NAF.SECTION
> INNER JOIN CMOTP_TAUX
> ON CMOTP_V_RGPCOMS.ANNEE=CMOTP_TAUX.ANNEE
> AND CMOTP_V_RGPCOMS.CODE_DEPT=CMOTP_TAUX.CODE_DEPT
> AND CMOTP_V_RGPCOMS.CODE_COMMUNE=CMOTP_TAUX.CODE_COMMUNE
> WHERE CMOTP_V_RGPCOMS.CODE_DEPT='093'
> AND CMOTP_V_RGPCOMS.CODE_COMMUNE='001';
>
> No Result ?????
>
>

If I add a view :
CREATE VIEW V_SECTION_NAF
(
SECTION,
LIB_SECTION
) AS
select DISTINCT SECTION,LIB_SECTION
from CMOTP_NAF;

Then :
SELECT CMOTP_V_RGPCOMS.*, V_SECTION_NAF.LIB_SECTION, CMOTP_TAUX.COMMUNE
FROM
CMOTP_V_RGPCOMS
INNER JOIN V_SECTION_NAF
ON CMOTP_V_RGPCOMS.SECTION = V_SECTION_NAF.SECTION
INNER JOIN CMOTP_TAUX
ON CMOTP_V_RGPCOMS.ANNEE=CMOTP_TAUX.ANNEE
AND CMOTP_V_RGPCOMS.CODE_DEPT=CMOTP_TAUX.CODE_DEPT
AND CMOTP_V_RGPCOMS.CODE_COMMUNE=CMOTP_TAUX.CODE_COMMUNE
WHERE CMOTP_V_RGPCOMS.CODE_DEPT='093'
AND CMOTP_V_RGPCOMS.CODE_COMMUNE='001';

SECTION CODE_DEPT CODE_COMMUNE ANNEE SOMME_PAYEE LIB_SECTION COMMUNE
A 093 001 1995 120 LIB_A BLA
A 093 001 1996 125 LIB_A BLA

OK