Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 5 of 5 total |
migrate a DBISAM query in an ElevateDB query |
Tue, Jan 24 2012 11:35 AM | Permanent Link |
Josselin | Hi,
with DBISAM, it's possible to do a query like this for update a table : UPDATE Minutes SET Libelle = Ouvrages.O_LIBELLE, Unite = Ouvrages.O_UNITE, PU = Ouvrages.O_PU, Total = Q*PU, Fournis = Ouvrages.Fournis, Poser = Ouvrages.Poser, Debourser = Ouvrages.Debourser, Pose = Ouvrages.Pose, Fourniture = Ouvrages.Fourniture FROM Minutes INNER JOIN Ouvrages ON (Minutes.MetreA = Ouvrages.O_CODE) WHERE (Ref = 'A') OR (Ref = 'B') In ElevateDB this query is not authorized, how can I do to make the same query with ElevateDB Thanks for your help Bruno |
Tue, Jan 24 2012 11:40 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Josselin
You have to use a subselect in each place you are now using the joined table >UPDATE Minutes SET >Libelle = (SELECT O_LIBELLE FROM Ouvrages WHERE Minutes.MetreA = Ouvrages.O_CODE), .... .... FROM Minutes WHERE (Ref = 'A') OR (Ref = 'B') You'll probably have to mess around with the WHERE clause in the subselect to make it product a scalar result. [Team Elevate] |
Wed, Jan 25 2012 9:27 AM | Permanent Link |
Josselin | Thanks Roy, it's working good
I thought I had already tried that....or maybe not... lol Greetings. Bruno |
Wed, Jan 25 2012 11:09 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< You have to use a subselect in each place you are now using the joined table >> Not entirely correct - you can use a row value constructor to do the whole thing in one shot: UPDATE Minutes SET (Libelle, Unite, PU, Total, Fournis, Poser, Debourser, Pose, Fourniture) = (SELECT Ouvrages.O_LIBELLE, Ouvrages.O_UNITE, Ouvrages.O_PU, Q*PU, Ouvrages.Fournis, Ouvrages.Poser, Ouvrages.Debourser, Ouvrages.Pose, Ouvrages.Fourniture FROM Ouvrages WHERE (Ouvrages.O_CODE = Minutes.MetreA) AND (Ref = 'A') OR (Ref = 'B')) It's kind of weird, but EDB allows a sub-select enclosed within an UPDATE/DELETE statement to refer to columns in the outer statement, so the (Q*PU) expression still works fine. -- Tim Young Elevate Software www.elevatesoft.com |
Thu, Jan 26 2012 4:09 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
I agree - its kind of weird Roy Lambert |
This web page was last updated on Tuesday, May 7, 2024 at 02:40 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |