I used the example from help:

SELECT CustNo,
             (SELECT Company FROM Customer WHERE Customer.CustNo=Orders.CustNo) AS Company
FROM Orders

It gives sensitive result set but only for field CustNo from Orders, but field Company cannot be edited. I understand sensitive result set as to return all fields to be editable. Do I miss something?

Iztok Lajovic

1


You missed something. All of columns from the source table will be editable, but those from other tables not.

Roy Lambert

Roy,

I see in Roadmap section the statement:
'Ability to generate sensitive results sets for SELECT statements with sub-queries in the SELECT column list'.

Nowhere is written that sensitivity is limited to the source file and that it does not apply to columns from sub-query. This functionality, as I tested and I see it, is half way solution, a bit better than joined query (which returns non-sensitive result, though). For editing purposes one has to write additional SQL statements with greater possibilities of errors.

I propose to include this problem on a Roadmap, maybe for start resolve the statement I copied from help.

Iztok Lajovic

1


Its the equivalent of OnCalcFields in Delphi. You posted a fairly simple and easy to interpret example. What if you had a complex one in which the result of the subquery relied on a subquery within itself, or (a fairly common one) RANGE 1 to 1 is used to make sure the result is a scalar. Where do you apply changes?

Where you need to edit the non- source table fields its best to use either a temporary table or an in-memory table created from the result set you want. Unfortunately you do have to write the code to update the tables yourself.

The other possibility is client datasets which I've never used so can't sat anything about.

Roy Lambert

Roy,

I am absolutely aware of consequences using multiple nested queries.

I had in mind only one level of nested queries with 1:1 relation with master query. Such situation is really simple to manage edits of columns of subqueries. It may enormously simplify complete edit process without additional coding.

Iztok Lajovic

On 10/24/2018 11:23 AM, Iztok Lajovic (1) wrote:
> Nowhere is written that sensitivity is limited to the source file and that it does not apply to columns from sub-query. This functionality, as I tested and I see it, is half way solution, a bit better than joined query

See here
https://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Result_Set_Cursor_Sensitivity

"...and all inserts, updates, and deletes performed via the cursor are
performed directly on the source table"


Raul

1

>I am absolutely aware of consequences using multiple nested queries.
>
>I had in mind only one level of nested queries with 1:1 relation with master query. Such situation is really simple to manage edits of columns of subqueries. It may enormously simplify complete edit process without additional coding.

It may seem simple but I've seen a number of posts around the "disappearance" of data when editing in a master:detail relationship or filter or range caused by the change of the controlling data. Its always easy to plead a special case (I know I've done it enough) but once that's in there I guarantee there will be someone pushing for the next level, and then the next.

Roy

Roy,

I agree with you that in time the users are pushing more and more wishes.

I have looked in documentation of several databases and only Oracle permits editable view for one level of subquery only with several understandable restrictions. On the other side it permits to select more than one field from subquery table. This is for information only.

Yes, I pleaded for such one level subquery with 1 : 1 relation. There is no possibility for side effects regarding use of filters or range because if either is in use then the record should not be shown. Only one level of subquery should be recognized by parser very easily and pointers to both tables are unambiguous.

I am not pushing it, it is only a thought that implementation of such simple one level subquery could make great simplification of editing process.

Iztok

1

>I agree with you that in time the users are pushing more and more wishes.

Nothing wrong with that - I have made a fair few suggestions myself. The problem is that the more bits are added the slower things get. I'm old enough to remember the non-gui OS fitting onto a single floppy disk.

>I have looked in documentation of several databases and only Oracle permits editable view for one level of subquery only with several understandable restrictions. On the other side it permits to select more than one field from subquery table. This is for information only.
>
>Yes, I pleaded for such one level subquery with 1 : 1 relation. There is no possibility for side effects regarding use of filters or range because if either is in use then the record should not be shown. Only one level of subquery should be recognized by parser very easily and pointers to both tables are unambiguous.

The would be side effects. Your filter is Customer.CustNo=Orders.CustNo so lets say someone changes Company - is it only used in that table, or are there many places where it needs changing, what does the user think / say when that row from a grid vanishes?

>I am not pushing it, it is only a thought that implementation of such simple one level subquery could make great simplification of editing process.

The problem is when you start thinking it through what seems a simple case to you might not be in other environments. Take one of my applications (recruitment) - the company ID column is used in 11 other tables. A simple one level subquery could be used to present that with career detail information. Change that and it needs to be reflected in 10 tables that do not form part of the query.

OK I could change the company name which would only require a data change in the subquery table but there would be impacts on other areas eg a stored query that selects companies based on name.

I suppose the rules could be changed from

You can't edit columns from subqueries

to

You can edit columns from subqueries unless:
a) its not a simple one level query
b) the column isn't part of a primary key
c) the column isn't used in triggers
d) the column isn't used in constraints
e) a column of that name is used in more than one table
etc
etc

Roy

Roy,

OK, I was starting from my way of programming and therefore things seemed to me simple. By example, I use idents strictly as hidden information that user never can change and is used only for locate records, and, I never use text information for identification of records.

There are many programming techniques you mentioned and in some cases there could be dangerous situations in implementation of my proposal. I accept your explanation and thank you for it.

Regards
Iztok