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

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

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

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

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

"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

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