Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 8 of 8 total |
UPDATE #2 with a script |
Tue, Jun 8 2010 6:15 AM | Permanent Link |
Josselin | 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 |
Tue, Jun 8 2010 11:10 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Wed, Jun 9 2010 3:26 AM | Permanent Link |
Josselin | 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 |
Wed, Jun 9 2010 1:41 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Thu, Jun 10 2010 4:16 AM | Permanent Link |
Josselin | 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 |
Thu, Jun 10 2010 6:20 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Bruno
If you run the subselect as a stand alone query in EDBManager does it return more than one row? Roy Lambert [Team Elevate] |
Thu, Jun 10 2010 11:39 AM | Permanent Link |
Josselin | Hi Roy,
Yes the subselect return more than one row. Bruno |
Thu, Jun 10 2010 12:24 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |