![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 11 total |
![]() |
Wed, Oct 24 2018 7:16 AM | Permanent Link |
Iztok Lajovic (1) | 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 |
Wed, Oct 24 2018 7:52 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | 1
You missed something. All of columns from the source table will be editable, but those from other tables not. Roy Lambert |
Wed, Oct 24 2018 11:23 AM | Permanent Link |
Iztok Lajovic (1) | 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 |
Wed, Oct 24 2018 12:20 PM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 |
Wed, Oct 24 2018 12:42 PM | Permanent Link |
Iztok Lajovic (1) | 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 |
Wed, Oct 24 2018 1:29 PM | Permanent Link |
Raul ![]() | 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 |
Thu, Oct 25 2018 2:10 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 |
Thu, Oct 25 2018 5:27 AM | Permanent Link |
Iztok Lajovic (1) | 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 |
Thu, Oct 25 2018 6:42 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 |
Thu, Oct 25 2018 8:31 AM | Permanent Link |
Iztok Lajovic (1) | 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 |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Wednesday, August 14, 2024 at 07:31 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |