Cross Joining a Table Created on the Fly Using Union All

Today I was working on a report to reconcile data between two systems that will be used to validate parallel testing as we work to move the new system into production. I mention this to provide you with some context around the two techniques I employed in one of the SQL queries I wrote that is the basis of this post. Sometimes it’s a very fine line between being clever and being obtuse, especially when you are writing SQL. Since this report will become obsolete once the new system goes to production and I will most likely be the only person that has to maintain it for a short period of time, I look at these techiques as more clever than obtuse in these circumstances.

Creating and dropping temp tables in batches (not stored procs) can be a hassle in SQL Server T-SQL. In Oracle, you only have global temporary tables and you have to deal with the select limitations of PL/SQL. Sometimes, you need a table but you don’t want to explicitly create it or you can’t because you are querying another system over which you have no control. You can create the table “on the fly” using a temporary view and the UNION ALL clause. For example, I frequently need to transform a block of time into hourly blocks and I don’t have a permanent table available and I don’t want to deal with temp tables. I can create the table I need on the fly like this:

(SELECT Hour = 0 UNION ALL SELECT Hour = 1 UNION ALL ... SELECT HOUR = 23) H

In Oracle, you would have to specify the FROM clause (good ol’ DUAL). You also can’t use the = construct but you get the idea. In my experience the performance of this “table on the fly” is pretty good. Obviously, if you are working in a stored proc, you can create a temp table in T-SQL or a TABLE varable in PL/SQL to achieve the same result. This technique is nice when you can’t use a stored procedure. It’s also useful if you only need a few rows once (say four or less). With less than four rows, it’s easier to create the table using a temporary view of UNION ALLs than it is to create the temp table or table variable.

Generally, you want to avoid CROSS JOINs (aka the dreaded Cartesian Product). However, there are many legitimate uses of CROSS JOIN. First, let me state that CROSS JOIN provides one of the most compelling arguments for using the ANSI-92 join syntax. If you see “CROSS JOIN” in a query, you can be fairly certain that the author of the query wanted a Cartesian product. If you see a FROM clause with a table that has no conditions specified in the WHERE clause, how certain can you be that the author intended a Cartesian product?

I digress. In the query I was developing today I was comparing schedules in two systems. In one of the systems, the schedule only included one side of a pair of transactions that offset each other. In the other system, both rows were there. I didn’t want the missing row from the one system to show up as a variance. Luckily, the existing query was already using a UNION to pick up this special case. I used a CROSS JOIN of a “table on the fly” to create the other row for me:

...
UNION (
SELECT
CASE WHEN D.Direction = 1 THEN 'BUY' ELSE 'SELL' END TransactionType,
A.Volume * D.Direction Volume
FROM OneSided A
CROSS JOIN (SELECT Direction = 1 UNION ALL Direction = -1) D
)
...

As you can see, I created the second row that I needed to make this system’s schedule match the other system’s schedule with just one line of SQL and minor modifications to the SELECT clause.

Oracle (PL/SQL) Equivalents for MS SQL Server (T-SQL) Constructs

For the last several months, I have been working primarily with Oracle 8i and 10g databases.  Over the last 10 years, 95% of my work has been with MS SQL server.  The other 5% was with Oracle, but it was limited to simple SELECT statements or calling stored procedures that other people wrote.  You don’t realize how much basic knowledge you take for granted until you get outside of your comfort zone and have to stop and figure out how to do every little thing.  Here’s a list of the top 10 things I wish I had known about Oracle – PL/SQL coming from an MS SQL Server – T-SQL background.

  1. Bind variables versus PL/SQL variables instead of just variables.  Oversimplifying, use “:” in place of “@” when you want a parameterized query.  This is the most awkward and annoying aspect of Oracle compared to MS SQL Server, IMO.  The bottom line is that you can’t very easily copy code from a stored procedure and run it in Toad or SQL Developer.  Nor can you directly assign values to bind variables — you get prompted for them.
  2. You can’t do a SELECT without an INTO inside of a PL/SQL block.  This is really puzzling.  Especially since you can’t have anything but bind variables outside of a PL/SQL block and you can’t programmatically assign values to bind variables.  All of this adds up to being forced to be prompted for bind variable values every time you run a parameterized query in the SQL Worksheet or in Toad.
  3. There’s no built-in Query Analyzer/SSMS Query tool.  There’s an extremely crippled but free version of Toad.  This what I use if I have to work with a pre-10g instance.  There’s Oracle SQL Developer which is what I use if I am working with a 10g instance.  They both are lacking compared to Query Analyzer/SSMS Query window.  You will press ctrl-E and ctrl-R repeatedly with no effect so many times it will make you sick.
  4. DATEADD doesn’t exist.  You have to add fractional days to dates instead.  DATEADD(hour, 1, D) would be D + 1/24.
  5. INT can’t be used for columns.  You use NUMBER instead.  This can be a real pain in ADO.NET because you have to explicitly convert all those NUMBERs to ints unless you want to treat them as decimals.
  6. No IDENTITY columns.  Instead of typing that one word you have to create a completely separate object called a SEQUENCE and you also have to write a trigger.
  7. You can’t have multiple databases on an Oracle instance.  There are just multiple schemas which are really owners.  Public synonyms are used to make objects from different schemas accessible without explicit schema specification. 
  8. The default date format does not include the time part.  You have to “alter session set NLS_DATE_FORMAT=’MM/DD/YYYY HH24:MI’;” to see the time part.
  9. You concatenate strings with the oh-so-intuitive || instead of the obscure + operation.  Alternatively you can use CONCAT.
  10. You can’t PRINT.  There’s a print-like statement, but it only works in SQL*PLUS.  You don’t ever really want to use SQL*PLUS unless you are a massochistic, command-line-loving freak from the past that still uses VI or EMACS.  Actually, there’s a web-based version of SQL*PLUS that’s usable but I like to stay in the SQL Worksheet.

Those are the top 10 — the things I really struggled to understand coming from the MS SQL side of things.  Here are some more that I found useful:

  • SP_TABLES = SELECT * FROM ALL_TABLES
  • sp_help = DESCRIBE
  • You can’t just SELECT without a FROM clause.  You always have to include FROM DUAL.  I still don’t know what “DUAL” is.
  • GETDATE() = SYSDATE
  • SUSER_SNAME() = USER
  • Getting just the date part of a date value: CONVERT(datetime, CONVERT(varchar(10), GETDATE(), 101)) = trunc(SYSDATE)
  • Just use VARCHAR2 and don’t ask about VARCHAR.
  • You can’t return values from procedures using the RETURN statement.  Use an OUT param instead.
  • In version 8i and earlier, there’s a “rule-based” optimizer.  The rule-based optimizer can suck badly and you may need to force the join order on some of your queries.  You do that with “SELECT /*+ ordered */” (yes, it really is in the comment).
  • The Oracle compiler doesn’t do a good job of telling what’s wrong with your statement.  It can give obscure messages for missing commas and semi-colons.  Don’t forget that you must terminate each statement with a semi-colon.
  • There’s no built-in support for UUIDs.
  • Oracle does not distingish between an empty string and null (which really sucks)

Here are a couple of non-obvious things I learned about Oracle and .NET 2.0:

  • You can’t send multiple SQL statements in a batch using ADO.NET like you can with MSSQL Server
  • You can’t use the ODP.NET with the Enterprise Library
  • You can’t use non-tnsnames.ora connection strings with System.Data.Oracle but you can with ODP