Hi,

This is what I try to do with a script :

SCRIPT
BEGIN
EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE Vue1 AS
SELECT Valeur
FROM Dim WHERE (Abrev = ''LSP'') WITH DATA';
EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE Vue2 AS
SELECT Sum(Quantite) AS SomQ
FROM ListePerso
WHERE ((CODE LIKE ''A.133.1%'')OR(CODE LIKE ''A.133.2%''))
AND (ETAT = ''PX'') WITH DATA';
EXECUTE IMMEDIATE 'UPDATE Dim SET
Dim.Valeur = Vue1.Valeur+Vue2.SomQ
FROM Vue1,Vue2
WHERE (Dim.Abrev=''SHI'')';
EXECUTE IMMEDIATE 'DROP TABLE Vue1';
EXECUTE IMMEDIATE 'DROP TABLE Vue2';
END

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

Can someone have an idea ?

Thanks,

Bruno

Bruno,

<< This is what I try to do with a script : >>

The problem is that it is impossible to tell what you want to do from just
that script (or the DBISAM script).   How many rows do the Vue1 and Vue2
temporary tables contain before the UPDATE ?

--
Tim Young
Elevate Software
www.elevatesoft.com

There is one row in Vue1 and 0 row in Vue2

Why the clause FROM generate an error ?

If you want, I can send to you the Database

Bruno

Bruno,

<< There is one row in Vue1 and 0 row in Vue2 >>

I would convert this to using a cursor UPDATE like you did in your other
message (UPDATE #3).  The reason is that you're associating row values from
different tables without actually specifying any relationship (you're doing
a cross-join), and thus relying on a certain count of rows and the row
pointer being on a certain row.

<< Why the clause FROM generate an error ? >>

ElevateDB doesn't support joins in UPDATE or DELETE statements - you have to
use correlated sub-queries instead.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

OK, I understand what you said.

Is it possible to do this :

UPDATE LiaisonMetreA SET
PU = (SELECT O_PU
FROM "BIBLE_METREA_BATIMENT".Ouvrages AS Ouvrages
INNER JOIN LiaisonMetreA
ON (LiaisonMetreA.CodeM = Ouvrages.O_CODE)
WHERE (Ouvrages.O_CODE IS NOT NULL))

I use a sub queries to update the field PU but I have this error message :

ElevateDB Error #1011 An error occurred with the query (SELECT ALL "O_PU" AS "O_PU" FROM "LiaisonMetreA" INNER JOIN "BIBLE_METREA_BATIMENT"."Ouvrages" AS "Ouvrages" ON ("Ouvrages"."O_CODE" = "LiaisonMetreA"."CodeM") WHERE ("Ouvrages"."O_CODE" IS NOT NULL)) (A scalar query can only return a single value)

Why ?

Thanks

Bruno

Bruno

If you run the subselect as a stand alone query in EDBManager does it return more than one row?


Roy Lambert [Team Elevate]

Hi Roy,

Yes the subselect return more than one row.

Bruno

Bruno


That's your problem then. It needs to only return one row since you are only updating one row in the table. Try adding a RANGE 1 to 1

Roy Lambert [Team Elevate]