Nice little SQL tricks #1

Here are some nice little SQL tricks:

1) Filter or not

Sometimes you want to get an input from the user and use that value for filtering your query.

Let's say:

SELECT count(1) 
FROM emp
WHERE dept_id = :input;

And that's fine as long as the user obligated to choose a specific department.
We want to give him the option of selecting 'All departments', in the same query.

The best way to do so is:
(without using "or" or "union all")

SELECT count(1) 
FROM emp
WHERE nvl(:input, dept_id) = dept_id;

While the application sends NULL whenever 'all departments' has been chosen.

2) Dynamic table

If you want to dynamically create a table that contains numbers between 1 to 50 (or any other natural number), try this one:

SELECT level as num
FROM dual
CONNECT BY level < 50; 

This trick comes in hand a lot in data migration, when you have one row need to be splitted into several rows. (Just make a Cartesian product between the original and the dynamic).

For example, the following query produces 3 rows for each employee in the table:

SELECT emp_id, num

FROM emp, (select level as num from dual connect by level < 4);

3) Paging mechanism

If you have a need for taking only rows 20-30 (like if you're task involves paging)

you can use the row_number analytic function which in this case, order the result in your column(s) choice and give each row a unique number in that order.

In the following example, we show 10 employees, ordered by their hire date, starting with the 20th employee:

SELECT * FROM

(

      SELECT e.*, row_number() over (order by date_hire desc) as rwnm

      FROM emp

)

WHERE rwnm BETWEEN 20 AND 30;

More tricks soon!