Function used in view definition is called multiple times
814021Dec 15 2010 — edited Dec 15 2010I am trying to write a view that boils down to the following statement:
SELECT val, val FROM (
SELECT SYS_GUID() val
FROM dual
)
Against my expectation, this calls SYS_GUID() twice, resulting in two different values for val.
I've written it here as inline view, but it must be a regular view in reality. Is there any way to construct this view so that SYS_GUID is called only once, even if the resulting value is then selected multiple times? Naively, I'd like to assign the result of SYS_GUID to a variable and then select the value of this variable. However, this does not seem possible in a view.
New to Oracle PL/SQL. This works as expected in SQL Server T-SQL.
Thanks for any help or insight you can provide.
-- Brian