Hi,

I have created a query with two tables and connected it to a datagrid (SMDBGrid).
Also an UpdateSQL (ModifySQL) query has been created.

However when I click on on a field in the grid I can't change it.

Did I forget to set some settings or is it just not possible to edit a grid field?

Mike

<<I have created a query with two tables and  . . . >>

Muli-table queries are not updatable.

You can use query in the SELECT column list which converts a 2 table query into a single table query.  For example,

SELECT
 SupplierID,
 (SELECT Supplier FROM Suppliers WHERE Suppliers.ID = Parts.SupplierID) AS Supplier
 FROM Parts


The Elevate DB SQL Manual explains the conditions for a updatable query.


Result Set Cursor Sensitivity
-----------------------------------------

Queries that retrieve data from a single table will generate a sensitive result set provided that:

1) The user or developer requests a sensitive result set cursor. Please see the DECLARE statement for
more information on requesting a a sensitive or insensitive result set cursors in SQL/PSM routines, and
your product-specific manual for requesting sensitive or insensitive cursors in client applications.

2) There is no DISTINCT keyword in the SELECT statement.

3) All SELECT expressions are either a column reference or a computed column that does not contain any
aggregate functions (MIN, MAX, SUM, etc.). Computed columns are read-only in the sensitive result set
cursor and cannot be modified.

4) There is no GROUP BY clause in the SELECT statement.

5) There is no ORDER BY clause in the SELECT statement, or there is an ORDER BY clause that minimally
matches the columns, and the collations defined for the columns, in an existing index in the source table.

6) There are no correlated sub-queries in the WHERE clause of the SELECT statement.


Richard

Mike


If what Richard suggests doesn't work your alternative is to create an in-memory table and edit that.

First make sure you have an in-memory database then use the CREATE TABLE xxx AS yyyy WITH DATA

If you need indices you'll have to add those as well

Roy Lambert

Thank you for the explanation!

I have decided to go for an in-memory table.

Mike,

In this situation I use a ClientDataSet (I don't know whether you are developing in Delphi) as an intermediate data-management component. It works very well and allows easy editing of EDB's read-only datasets.

Mike,

Depending on the query, it might also be possible to replace your query with 2 tables with a updatable one...
Could you post the query please ?

--
Fernando Dias
[Team Elevate]

@Fernando: Sure here it is.

SELECT revaluation.*, object.city, object_model.objectnaam FROM revaluation
INNER JOIN object
ON object.object_id=revaluation.object_id
INNER JOIN object_model
ON object_model.object_model_id=object.model_id

Mike

That's easy to change into the new syntax

SELECT
*,
(SELECT city FROM object WHERE object._id = revaluation.object_id),
(SELECT objectnaam  FROM object_model WHERE object_model.object_model_id= (SELECT object.model_id FROM object WHERE object._id = revaluation.object_id))
FROM revaluation

I think without testing. BUT, and there are some big buts here

1. try running it in EDBManager and compare the speed with the JOINed version. Even with the right indices I think you'll find it quite a bit slower.
2. you must be able to guarantee that the two subselects will each produce a simple scalar (I think its the right word) result
3. you won't be able to edit city or objectnamm anyway

Roy Lambert

Roy and Mike,

It looks good, should work.
About the speed, if there are indexes on Object.ObjectId and Object_model.Object_model_id it will be fast, because it's going to produce a live result. If not, please let us know.

--
Fernando Dias
[Team Elevate]

Hi,

Thank you for the suggestions.

I have got already a working procedure but will try this definitely.