Hi,

I'm trying to do an update of a table with a script, and I have an error message

This is my script :

SCRIPT ()
BEGIN
EXECUTE IMMEDIATE 'CREATE VIEW Vue1 AS
SELECT Sum(Valeur) AS SomV
FROM Dim WHERE (Code LIKE ''SHI.%'') AND (Etat = ''DI'')';
EXECUTE IMMEDIATE 'UPDATE Dim SET
Valeur = SomV ,
Quantite = SomV
FROM Vue1, Dim
WHERE (Dim.Abrev = ''SHI'')';
EXECUTE IMMEDIATE 'DROP VIEW Vue1';
END

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

I can't see what I do wrong ! Can someone help me

Thanks

Bruno

PS : I use this script with the ElevateDB Manager

Bruno



Try this


EXECUTE IMMEDIATE 'UPDATE Dim SET
Valeur = (SELECT Sum(Valeur) AS SomV FROM Dim WHERE (Code LIKE ''SHI.%'') AND (Etat = ''DI'')),
Quantite = (SELECT Sum(Valeur) AS SomV FROM Dim WHERE (Code LIKE ''SHI.%'') AND (Etat = ''DI''))'

Untested so no guarantees

Roy Lambert [Team Elevate]

Thanks Roy, your Query is working good.

But this is what I do with DBISAM :

SELECT Valeur
INTO "\Memory\Tmp1"
FROM Dim WHERE (Abrev = 'LSP');

SELECT Sum(Quantite) AS SomQ
INTO "\Memory\Tmp2"
FROM ListePerso
WHERE ((CODE LIKE 'A.133.1%')OR(CODE LIKE 'A.133.2%')) AND (ETAT = 'PX');

UPDATE Dim SET
Valeur = "\Memory\Tmp1".Valeur+"\Memory\Tmp2".SomQ
FROM Dim,"\Memory\Tmp1","\Memory\Tmp2"
WHERE (Abrev='SHI');

DROP TABLE "\Memory\Tmp1";
DROP TABLE "\Memory\Tmp2";

I would like to do the same thing with ElevateDB

Bruno

Bruno


DBISAM and ElevateDB are not the same. The SQL is different, and to a different standard. One major area of difference is scripts. DBISAM scripts were essentially just a few sql statements run one after the other. ElevateDB scripts are far more powerful but don't do some of the things DBISAM scripts could do.

If it were possible you could just run the statements one after the other using simple queries BUT the statements you have are not compatible with ElevateDB. eg SELECT .. INTO is no longer viable. Its painful (I know from personal experience) but you have to learn the new SQL.

Roy Lambert [Team Elevate]

I know that my DBISAM statement is not compatible with ElevateDB, and I know that SELECT...INTO is no longer viable.

I just want to translate my DBISAM query in ElevateDB query so how can I do that ?

Josselin


You said that the query I posted works. What's wrong with using that?

Roy Lambert

Hi Roy

It's because I have a lot of other Query in DBISAM more complicated than this query and I want to convert them in ElevateDB.

What is the more efficient in ElevateDB, executing query one by one or create a script ?
Is it better to create temporary table or create view to replace  SELECT...INTO in DBISAM ?

Thanks

Bruno

Josselin


>It's because I have a lot of other Query in DBISAM more complicated than this query and I want to convert them in ElevateDB.

And why does that prevent you from using the code I provided?

>What is the more efficient in ElevateDB, executing query one by one or create a script ?
>Is it better to create temporary table or create view to replace SELECT...INTO in DBISAM ?

The way you're doing it there's no difference (ok maybe a little bit since you don't have to create/free components).

DBISAM scripts were simply a way to execute multiple simple sql statements without having to do a lot of Close - SQL := .. and ExecSQLs, ElevateDB scripts are a very powerful programming language in their own right, simply using multiple lines with EXECUTE IMMEDIATE is the same as DBISAM scripts and basically just running simple sql statements one after the other.

Roy Lambert [Team Elevate]

Please look my other post "UPDATE #3 With SCRIPT" and tell me what I can do

thanks a lot

Bruno Riviere

Josselin

>Please look my other post "UPDATE #3 With SCRIPT" and tell me what I can do

Same as I had to. Read the manual

Roy Lambert

ps I hated the new syntax when I started. I had finally learnt how to do it the DBISAM way and now had to learn a new set of methods and practices.