Oracle SQL: Understanding the behavior of SYS_GUID() when present in an inline view?
The documentation gives a reason as to why you may see a discrepancy (emphasis mine):
Caution:
Because SQL is a declarative language, rather than an imperative (or procedural) one, you cannot know how many times a function invoked by a SQL statement will run—even if the function is written in PL/SQL, an imperative language.
If your application requires that a function be executed a certain number of times, do not invoke that function from a SQL statement. Use a cursor instead.For example, if your application requires that a function be called for each selected row, then open a cursor, select rows from the cursor, and call the function for each row. This technique guarantees that the number of calls to the function is the number of rows fetched from the cursor.
Basically, Oracle doesn't specify how many times a function will be called inside a sql statement: it may be dependent upon the release, the environment, the access path among other factors.
However, there are ways to limit query rewrite as explained in the chapter Unnesting of Nested Subqueries:
Subquery unnesting unnests and merges the body of the subquery into the body of the statement that contains it, allowing the optimizer to consider them together when evaluating access paths and joins. The optimizer can unnest most subqueries, with some exceptions. Those exceptions include hierarchical subqueries and subqueries that contain a ROWNUM pseudocolumn, one of the set operators, a nested aggregate function, or a correlated reference to a query block that is not the immediate outer query block of the subquery.
As explained above, you can use ROWNUM
pseudo-column to prevent Oracle from unnesting a subquery:
SQL> WITH data AS (SELECT SYS_GUID() uuid FROM DUAL WHERE ROWNUM >= 1)
2 SELECT uuid, uuid FROM data;
UUID UUID
-------------------------------- --------------------------------
1ADF387E847F472494A869B033C2661A 1ADF387E847F472494A869B033C2661A
How to display one row when table has multiple rows related to Single record in other table?
The pivot
version would be:
select * from (
select apartment_id, feature_name, feature_value
from apartment_feature
)
pivot (
max(feature_value)
for feature_name in
( 'BedRooms' as bedrooms, 'BathRooms' as bathrooms, 'Flooring' as flooring )
)
order by apartment_id;
The max(feature_value)
is because pivot
syntax requires an aggregate expression here. Perhaps if feature_value
were a numeric column then sum(feature_value)
would make more sense.
Between operator
you are not using like
so '%' is treated as a regular character, you are retrieving strings greater than 'A%' up to 'D%'
'A' < 'A%'
make an insert of 'A%' and try again
maybe this will bring your desired results
SELECT first
FROM timepass
WHERE first between 'A' and 'D'
pl/sql function called how many times?
This is the kind of situation where some experimentation is useful (this was conducted on 10g). Using the following query, we can tell that normal functions, using the same parameters (in this case, none) will be executed each time they are called:
select dbms_random.value() from all_tables
This is because Oracle assumes that a function will not return the same value consistently unless you tell it otherwise. We can do that by creating a function using the deterministic
keyword:
CREATE FUNCTION rand_det
RETURN NUMBER
DETERMINISTIC AS
BEGIN
RETURN DBMS_RANDOM.VALUE ();
END;
Using this function instead of dbms_random
in the first query tells us that the query is being executed only once, despite the many calls. But this only clarifies the select
section. What if we use the same deterministic function in both a select
and a where
clause. We can test that using the following query:
SELECT rand_det
FROM all_tables
WHERE rand_det > .5;
You may have to run this several times to see our proof, but, eventually, you'll see a list of values less than 0.5. This provides us with evidence that even the deterministic function is being executed twice: once for each section it appears in. As an alternative, you can modify our deterministic function as follows, then run the subsequent query, which will reveal 2 lines written to DBMS_OUTPUT
.
CREATE OR REPLACE FUNCTION rand_det
RETURN NUMBER
DETERMINISTIC AS
BEGIN
DBMS_OUTPUT.put_line ('Called!');
RETURN DBMS_RANDOM.VALUE ();
END;
SELECT rand_det
FROM all_tables;
Related Topics
Join Tables Using a Value Inside a JSONb Column
What Data Can Be Stored in Varbinary Data Type of SQL Server
Select 10 Rows Per Day with Order
How to Get The First Day and The Last of Previous Month Using Sql
Iterate Through a List of Strings in SQL Server
Running a SQLite3 Script from Command Line
How to Get Current Database and User Name with 'select' in Postgresql
Does "Select for Update" Prevent Other Connections Inserting When the Row Is Not Present
Sql: Get Records Created in Time Range for Specific Dates
Blank Out Duplicate Column Values in SQL Reporting Services
What Are The Disadvantages of Having Many Indices
Sql Query to Convert Nvarchar to Int
How to Set The Starting Point for The Primary Key (Id) Column in Postgres via a Rails Migration
Why Can't I Create a View Inside of a Begin ... End Block
Aggregate Hstore Column in Postresql