Hi,

Can someone tell me why this script doesn't work :

EXECUTE IMMEDIATE 'CREATE VIEW Vue1 AS SELECT CODE
FROM ListePerso
WHERE (CODE LIKE ''A.11%'') AND (Etat = ''SF'')';

EXECUTE IMMEDIATE 'CREATE VIEW Vue2 AS
SELECT Vue1.code ,ListePerso.Code AS CodePX, SUM(Quantite) AS SUMQ, SUM(VALEUR) AS SumV
FROM ListePerso, Vue1
WHERE (CODE LIKE Vue1.Code + ''%'') And (ListePerso.ETAT=''PX'')
GROUP BY CodePX';

EXECUTE IMMEDIATE 'CREATE VIEW Vue3 AS
SELECT Code, SUM(SumV) AS SOMV2
FROM Vue2
GROUP BY Code';

EXECUTE IMMEDIATE 'UPDATE ListePerso SET
Quantite = NULL,
Valeur = Vue3.SomV  
FROM Vue3
WHERE (ListePerso.CODE LIKE Vue3.Code)And(ListePerso.ETAT=''SF'')';

I always have the same error message :

ElevateDB Error #700 An error was found in the statement at line 19 and column 19 (Expected end of expression but instead found FROM)

I dont' understand why the clause FROM caused an error

Thanks for your help

Bruno Rivere

Hi,

Can someone tell me why this script doesn't work :

EXECUTE IMMEDIATE 'CREATE VIEW Vue1 AS SELECT CODE
FROM ListePerso
WHERE (CODE LIKE ''A.11%'') AND (Etat = ''SF'')';

EXECUTE IMMEDIATE 'CREATE VIEW Vue2 AS
SELECT Vue1.code ,ListePerso.Code AS CodePX, SUM(Quantite) AS SUMQ, SUM(VALEUR) AS SumV
FROM ListePerso, Vue1
WHERE (CODE LIKE Vue1.Code + ''%'') And (ListePerso.ETAT=''PX'')
GROUP BY CodePX';

EXECUTE IMMEDIATE 'CREATE VIEW Vue3 AS
SELECT Code, SUM(SumV) AS SOMV2
FROM Vue2
GROUP BY Code';

EXECUTE IMMEDIATE 'UPDATE ListePerso SET
Quantite = NULL,
Valeur = Vue3.SomV2  
FROM Vue3
WHERE (ListePerso.CODE LIKE Vue3.Code)And(ListePerso.ETAT=''SF'')';

I always have the same error message :

ElevateDB Error #700 An error was found in the statement at line 19 and column 19 (Expected end of expression but instead found FROM)

I dont' understand why the clause FROM caused an error

Thanks for your help

Bruno Rivere

Josselin,

> Can someone tell me why this script doesn't work :

> EXECUTE IMMEDIATE 'UPDATE ListePerso SET
> Quantite = NULL,
> Valeur = Vue3.SomV2  
> FROM Vue3
> WHERE (ListePerso.CODE LIKE Vue3.Code)And(ListePerso.ETAT=''SF'')';

It's simply the wrong syntax:

UPDATE <TableName>
SET <ColumnName> = <Value> [,<Col
[WHERE <FilterCondition>]

Uli

Hi Uli,

I try this syntax

UPDATE ListePerso SET
Quantite = NULL,
Valeur = SOMV2
FROM ListePerso INNER JOIN Vue3
ON (ListePerso.CODE = Vue3.Code1)
WHERE (ListePerso.ETAT='SF')

And it's doesn't work too

This is the error message

ElevateDB Error #700 An error was found in the statement at line 4 and column 2 (Expected end of expression but instead found FROM)

Bruno


Throw away your concepts from DBISAM learn the necessary syntax for ElevateDB. I went through it, I hated it but on the basis I couldn't persuade either Tim or the SQL standards committee to do things the DBISAM way I had to learn.

Since I can't test your query on real data here's two real life examples from my data

UPDATE MailBoxes SET _OnFile = (Select COUNT(*) FROM EMails WHERE _BoxNo = _fkMailBoxes GROUP BY _fkMailBoxes)

UPDATE MailBoxes SET _Archived = (Select COUNT(*) FROM emArchive WHERE _BoxNo = _fkMailBoxes GROUP BY _fkMailBoxes)


Roy Lambert

Josselin,

> I try this syntax
>
> UPDATE ListePerso SET
> Quantite = NULL,
> Valeur = SOMV2
> FROM ListePerso INNER JOIN Vue3
> ON (ListePerso.CODE = Vue3.Code1)
> WHERE (ListePerso.ETAT='SF')

You cannot use joins within an update statement. Instead you have to use
a subquery.

Have a look on these links:

http://www.elevatesoft.com/forums?action=view&category=edb&id=edb_sql&page=1&msg=3929#3929
http://www.elevatesoft.com/forums?action=view&category=edb&id=edb_sql&page=1&msg=2251#2251

Uli

Thanks for the link Uli

I replace my script by this :

CREATE PROCEDURE Test
BEGIN

 DECLARE CustCursor CURSOR WITHOUT RETURN FOR Stmt;
 DECLARE MyTable CURSOR WITHOUT RETURN FOR Stmt2;
 DECLARE Code1 VARCHAR(30);
 DECLARE Code2 VARCHAR(30);
 DECLARE SomV2 FLOAT;


EXECUTE IMMEDIATE 'CREATE VIEW Vue1 AS SELECT CODE
FROM ListePerso
WHERE (CODE LIKE ''A.12%'') AND (Etat = ''SF'')';

EXECUTE IMMEDIATE 'CREATE VIEW Vue2 AS
SELECT Vue1.code AS Code1 ,ListePerso.Code AS CodePX, SUM(Quantite) AS SUMQ, SUM(VALEUR) AS SumV
FROM ListePerso, Vue1
WHERE (CODE LIKE Vue1.Code + ''%'') And (ListePerso.ETAT=''PX'')
GROUP BY CodePX';

EXECUTE IMMEDIATE 'CREATE VIEW Vue3 AS
SELECT Code1, SUM(SumV) AS SOMV2
FROM Vue2
GROUP BY Code1';


 PREPARE Stmt FROM 'SELECT * FROM Vue3';
 PREPARE Stmt2 FROM 'SELECT * FROM ListePerso WHERE (ETAT = ''SF'')';
 OPEN MyTable;
 OPEN CustCursor;
 FETCH FIRST FROM CustCursor (Code1, SomV2) INTO Code1, SomV2;

  WHILE NOT EOF(CustCursor) DO
    FETCH FIRST FROM MyTable (Code) INTO Code2;
    WHILE NOT EOF(MyTable) DO
      IF (Code1 = Code2) THEN
        UPDATE MyTable SET
        Quantite = NULL,
        Valeur = SomV2;
      END IF;
      FETCH NEXT FROM MyTable (Code) INTO Code2;
    END WHILE;
    FETCH NEXT FROM CustCursor (Code1, SomV2) INTO Code1, SomV2;
  END WHILE;    
  CLOSE MyTable;
  CLOSE CustCursor;
END

And it's working