Fastest way to build vendor-independent queries

Prepare to be amazed. Really.

What do you know about grouping sets? Not much?…

Let’s see how hard it is to build this query first. From the Databases tab, on the left, I right-click on the emp table and select Add to New Query. I keep checked only the DEPTNO, JOB and SAL shape items. I show only the Field, Lookup, Group, Sort and Format builder bars. I click on Page Size to turn paging off.

Simple Grouping Query

…I replace DEPTNO with the related department name (which is more friendly as information), by simply selecting the DNAME choice from the Lookup combo box. I select Sum for SAL. With what we have, we can say I want all total employee salaries per department and job. Let’s sort this ascending by department’s name, and descending (with NULLs first, if any) by job title. We’ll also show the sum of salaries with a currency format.

…It didn’t take long, did it? This is a simple grouping query, and you may also instantly drill-down to aggregated data for each row. All Aggregates shows you here there have been two CLERK employees in RESEARCH, whose sum of salaries was $1,900 ($800 + $1,100).

Adding Grouping Sets

But let’s go further to grouping sets! You see the small cube button in your bottom toolbar? Click on it and check Rollup from your choices (you should have (none), Grouping Sets, Rollup and Cube). You’ll be asked if you want Grouping Flag and Grouping Fields (GROUPING-based extensions we created in Query Xtractor) automatically added to your query. Add them, they instantly give you all information you need. Run the query again and … tadaa, you may be the first person on Earth to get so easily a ROLLUP query on SQLite. Because SQLite … does not support grouping sets in the first place, we just emulated them!…

Briefly stated, if you look at all your rows with flag 0, they are just like before. The fields tell you we grouped by the query just like before, by the DEPTNO and JOB underlined fields (we underline any GROUP BY field). For the rest, we dynamically remove one of the GROUP BY fields, and the total will actually be: total employee salaries per department, for flag 1, and total employee salaries overall (or grand total, with no GROUP BY field) for last row. That’s all folks, no such big deal!…

Visual Effects

I challenge anyone to look at the emulated SQL and write faster such a query. Or point me to a generic tool that helps you generate faster SQL. Still not convinced? Add also MGR as a GROUP BY field, with DNAME as lookup. And let’s simply add other nice effects: click on Hide Nulls and Hide Duplicate Values (make sure your Merge Cells on Duplicates option is on). Look at how clean your result could get:

By adding one more grouping column, you get more combinations, and your SQL is even more complex. Because grouping sets are not the only feature not natively supported by SQLite. There are no lookups in SQLite, no sorting with NULLs first or last, no data formats. Translating all these goodies into SQL could be a challenge even for experienced programmers.

Emulation and Simulation

And yes, let’s assume you are truly an expert in SQLite. But get the same database in Oracle, PostgreSQL, SQL Server, MySQL and a dozen of other supported platform, and recreate the exact same query, following the exact same simple instructions. The result should be the same, even if the generated SQL query underneath may look completely different.

Should I also say you do not actually need to install Oracle, PostgreSQL etc to see how your SQL query would look on those platforms? Switch your current choice on the Simulate toolbar button, and we instantly generate your current SQL query like you’re connected on something else. Try your query for Oracle (and make sure the Always Simulate Grouping Sets option is off), and you should see a completely different syntax, with GROUPING function calls and GROUP BY ROLLUP(…) by the end.

Query Xtractor proves to be indeed a no-kidding productivity tool. It helps you build simple to very complex vendor-independent queries within seconds. It helps you understand complex features – like grouping sets here – by simply playing with your builder options, inspecting your results and looking at the generated SQL. It helps you creating complex and flexible SQL without writing one line of SQL. Without knowing in fact SQL.