Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 7 of 7 total |
Why does referencing a function in a Select statement cause it to be InSensitive? |
Thu, Mar 28 2013 4:21 PM | Permanent Link |
Barry | I was reading the SQL manual today and discovered on page 253 the following statement:
<Warning Using a function created using this statement will prevent a sensitive cursor from being created if the function is used in any of the SELECT columns for the query expression that is used to create the cursor. For example, if you create a function called LookupTaxRate that accepts a state and county parameter, then using it in a query like this: SELECT State, County, LookupTaxRate(State,County) AS SalesTaxRate FROM Municipalities will prevent the cursor created when the query is executed from being a sensitive cursor.> I was wondering why does referencing a function make the Select statement InSensitive? Why is it any more restrictive than a SubSelect which still produces a Sensitive result? Inquiring minds want to know. Barry |
Thu, Mar 28 2013 6:34 PM | Permanent Link |
Raul Team Elevate | On 3/28/2013 4:21 PM, Barry wrote:
> I was wondering why does referencing a function make the Select statement InSensitive? Why is it any more restrictive than a SubSelect which still produces a Sensitive result? I would guess that result set is insensitive (read-only) as there is data present that EDB does not know how to map back to the source database/table - the function result column is is essentially a one way black box for EDB. Raul |
Thu, Mar 28 2013 11:56 PM | Permanent Link |
Barry | Raul wrote:
On 3/28/2013 4:21 PM, Barry wrote: >> I was wondering why does referencing a function make the Select statement InSensitive? Why is it any more restrictive than a SubSelect which still produces a Sensitive result?<< >I would guess that result set is insensitive (read-only) as there is data present that EDB does not know how to map back to the source database/table - the function result column is is essentially a one way black box for EDB.< I don't think the column that references a function has to be mapped back to the source database/table. Only the column that references a function should be read-only, as if it was displaying a constant. The only reason I can think of is if any of the columns in the result set are updated, the function may not display the correct value if it references the modified column without refreshing the entire result set. For example. The function could theoretically reference any prior or past row in the result set, so changing a column value may affect the function value 2 rows further on. To get that row to display the correct value, the query has to be refreshed after any row is modified. So it may have been made read-only for speed. Barry |
Fri, Mar 29 2013 1:44 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Barry,
<< I was wondering why does referencing a function make the Select statement InSensitive? >> What section/topic is that in ? I don't think it's valid anymore - EDB internally uses generated columns for such expressions, but still allows for a sensitive result set. Thanks, Tim Young Elevate Software www.elevatesoft.com |
Fri, Mar 29 2013 3:57 PM | Permanent Link |
Raul Team Elevate | On 3/29/2013 1:44 PM, Tim Young [Elevate Software] wrote:
> What section/topic is that in ? I don't think it's valid anymore - EDB > internally uses generated columns for such expressions, but still allows > for a sensitive result set. When i was looking it up it was under 5.69 (Create function) section (page 268/259 from current sql manual pdf) Raul |
Sat, Mar 30 2013 12:43 AM | Permanent Link |
Barry | "Tim Young [Elevate Software]" wrote:
Barry, << I was wondering why does referencing a function make the Select statement InSensitive? >> <What section/topic is that in ? I don't think it's valid anymore - EDB internally uses generated columns for such expressions, but still allows for a sensitive result set.> Tim, You're right. Using a function in an SQL statement does NOT make the query insensitive. I just tried it. The manual is incorrect for "Create Function". It seems I burnt all those brain cells for nothing, trying to rationalize why a function would make the query results insensitive. Barry |
Mon, Apr 1 2013 5:04 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Raul,
<< When i was looking it up it was under 5.69 (Create function) section (page 268/259 from current sql manual pdf) >> Thanks, it's now fixed. Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Saturday, April 27, 2024 at 08:52 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |