This may be easy (becuase I know how to do it in DBISM3), but how to do that in EDB2?
Regards,
Abdulaziz Jasser

Abdulaziz


Depends on exactly what you want to do. For simple updating then

1. set the TEDBQuery to the destination database (ie whatever you've called your in-memory database)
2. preface the table name in the SELECT FROM that you use to get the data with the database name

Apart from the above its just like updating any table from any table.

The SELECT INTO syntax is gone (and sadly missed) but if you look in the extensions ng you should find a script (the subject is "Create a memory table") to help with this.

Roy Lambert [Team Elevate]

Thanks Roy for replying but maybe I should be more specific.  What is wrong with the bellow clause?

UPDATE RAM.tbData D SET

D.ItemNo = (SELECT I.ItemNo     FROM MyDatabase.TB_Items I WHERE I.BranchSysNo = 1 AND   I.YearSysNo   = 4  AND   I.ItemSysNo   = D.ItemSysNo),

D.ItemName_A = (SELECT I.ItemName_A FROM MyDatabase.TB_Items I WHERE I.BranchSysNo = 1 AND   I.YearSysNo   = 4  AND   I.ItemSysNo   = D.ItemSysNo),

D.ItemName_E = (SELECT I.ItemName_E FROM MyDatabase.TB_Items I WHERE I.BranchSysNo = 1 AND   I.YearSysNo   = 4  AND   I.ItemSysNo   = D.ItemSysNo),

D.UnitBarcodeNo = (SELECT I.UnitBarcodeNo FROM MyDatabase.TB_Items I WHERE I.BranchSysNo = 1 AND   I.YearSysNo   = 4  AND   I.ItemSysNo   = D.ItemSysNo)
Regards,
Abdulaziz Jasser

Abdulaziz


I have no idea What error do you get? Which database is the query component pointing to? Do your subselects return a single row or multiple rows?

Roy Lambert [Team Elevate]

Basically I am trying to update some fields in a table in RAM database from another table in another NONE-RAM (Called "MyDatabase")  database.  What kills me is that when I run the same SQL statement from the EDBManager it runs ok but from inside Delphi I get the bellow error:

ElevateDB Error #1011 An error occurred with the query (SELECT ALL "I"."ItemNo" AS "ItemNo" FROM "MyDatabase"."TB_Items" AS "I" WHERE "I"."BranchSysNo" = 1 AND "I"."YearSysNo" = 4 AND "I"."ItemSysNo" = "R"."ItemSysNo") (A scalar query can only return a single value)

NOTES: In the original SQL  statment there is NO "ALL" clause the  in it, but the error message added the "ALL" clause to it.  Any help?
Regards,
Abdulaziz Jasser

Abdulaziz

>Basically I am trying to update some fields in a table in RAM database from another table in another NONE-RAM (Called "MyDatabase") database. What kills me is that when I run the same SQL statement from the EDBManager it runs ok but from inside Delphi I get the bellow error:

What this generally means is that there is some difference between the database(s) you're accessing in EDBManager and inside your program. Remember ElevateDB is simply a Delphi application developed by Tim. There is no magic code inside.

>ElevateDB Error #1011 An error occurred with the query (SELECT ALL "I"."ItemNo" AS "ItemNo" FROM "MyDatabase"."TB_Items" AS "I" WHERE "I"."BranchSysNo" = 1 AND "I"."YearSysNo" = 4 AND "I"."ItemSysNo" = "R"."ItemSysNo") (A scalar query can only return a single value)

This is the important bit. What it means is that that WHERE clause returns more than one row (another indicator that the two databases are different).


>NOTES: In the original SQL statment there is NO "ALL" clause the in it, but the error message added the "ALL" clause to it. Any help?

You can ignore the ALL - its something Tim stuffs in. He can probably explain it but I can't, I just know it can be saely ignored.

Roy Lambert [Team Elevate]

Thanks Roy,

It was a database differences.  The application was querying a database that returns more than one record.  I made some changes to it to force it to return one record.  Thanks again for your reply.
Regards,
Abdulaziz Jasser

Abdulaziz,

You should consider using this syntax instead (apart from your issue with
multiple rows being returned):

UPDATE RAM.tbData D SET (D.ItemNo, D.ItemName_A, D.ItemName_E,
D.UnitBarcodeNo) =
(SELECT I.ItemNo, I.ItemName_A, I.ItemName_E, I.UnitBarcodeNo
 FROM MyDatabase.TB_Items I
 WHERE I.BranchSysNo = 1 AND   I.YearSysNo   = 4  AND   I.ItemSysNo   =
D.ItemSysNo)

This "row value constructor" syntax will be much faster than doing the
columns individually:

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Row_Value_Constructors

--
Tim Young
Elevate Software
www.elevatesoft.com