Posts Tagged ‘NUMTODSINTERVAL’
Sometimes working with a product like Oracle brings a surprise, like a new feature you failed to catch when it was released. I’ve seen a lot of entries using inline views through the
WITH clause in the Oracle forums. It caught my eye because it is such a radical departure from portable SQL syntax of an inline view. I finally went searching to find the rationale for this approach.
The answer doesn’t lie with the Charlotte like National Treasure, but with simplifying the join syntax, as qualified in the Oracle Database 2 Day + Data Warehousing Guide 11g, Release 1. The following looks at this approach, and compares using the
WITH clause instead of the inline view to perform table fabrication.
Oracle tells us to use the
WITH clause when a query has multiple references to the same query block and there are joins and aggregations. Basically, the
WITH clause lets you name inline views and then reuse them inside other inline views. Like PL/SQL, they must be defined before they can be referenced. Unlike PL/SQL, they have no equivalent for forward referencing a query block. The basic syntax is:
The first code block is assigned the
inline name. You can then reuse the
inline code block inside any subsequent code block or the master query. The idea is that this syntax is simpler than the generic syntax.
The standard way of writing this is consistent across other SQL databases, and I don’t really see it as any more complex than the
WITH syntax Oracle provides.
WITH clause is also capable of letting you create tables from literal values, which is known as table fabrication. The following syntax uses the with clause to fabricate a table of two columns (
y) and two rows.
The next shows the traditional way of fabricating a table using an inline view:
You can also use this type of syntax in MySQL to fabricate a table. You can’t use the
WITH clause in MySQL because it’s not supported. You’ll notice in the example that the
FROM dual portion is omitted in MySQL. Wouldn’t it be nice if Oracle let that happen too?
A neat function that I picked up on the Oracle Technical Network is the
NUMTODSINTERVAL (number to date-stamp interval) function, which can create intervals for qualifying sales by hour or quarter hour. More or less it is a way to fabricate timing intervals. Here’s a quick example:
This has been the syntax, now I’ll have to check whether there are any performance differences. I suspect that since the execution plan is the same that there aren’t any performance differences but you never know until you test it.