Saturday, August 28, 2010

Sub Query

How to write a query when different logic is used for a single row? Use sub query. Example problem by using employees and deparment table in Oracle:

Objective
Sum the salary for each department for old and new staff

Field names and logics in the extraction
Department Name, if (hire_date after 01-Jan-1995 as new_staff) then sum of employee's salary else null, if (hire_date before 01-Jan-1995 as old_staff) then sum of employee's salary else null.

The first thing we notice is that we can't put the date 01-Jan-1995 condition in the where clause because the condition covers all hire date. So, the sub query comes in.

click on image for larger view

In other point of view, if department name can have duplicate OR new_staff and old_staff salary should not be in the same row, below is another sample sub query.

click on image for larger view

In conclusion, sub query is handy but sometimes it might get complex. In some slight different scenario (although they looked similar as above example), we may end up using almost complete different query.

Tuesday, August 17, 2010

Oracle Decode VS Case (Date result)

What's the difference between these 2 queries?
--#1---------------------------------------------------
SELECT TO_CHAR (DECODE (TRUNC (SYSDATE),
TO_DATE ('01012010', 'DDMMYYYY'), NULL,
TRUNC (SYSDATE)
),
'DD/MM/YYYYY'
)
FROM DUAL;

--#2---------------------------------------------------
SELECT TO_CHAR (CASE TRUNC (SYSDATE)
WHEN TO_DATE ('01012010', 'DDMMYYYY')
THEN NULL
ELSE TRUNC (SYSDATE)
END,
'DD/MM/YYYY'
)
FROM DUAL;

Both are SQL "grammar" are correct but one worked, one doesn't. The #1 query has failed with error "ORA-01722: invalid number" but second worked fine. I was scratching my head as both DECODE and CASE return the same value of TRUNC(SYSDATE), however DECODE has failed when convert to char.

Why?

Reformat the query to only DECODE and CASE gave me the result below:
--#1---------------------------------------------------
SELECT DECODE (TRUNC (SYSDATE),
TO_DATE ('01012010', 'DDMMYYYY'), NULL,
TRUNC (SYSDATE)
)
FROM DUAL;

Result: 17-AUG-10 (using TOAD statement execution)

--#2---------------------------------------------------
SELECT CASE TRUNC (SYSDATE)
WHEN TO_DATE ('01012010', 'DDMMYYYY')
THEN NULL
ELSE TRUNC (SYSDATE)
END
FROM DUAL;

Result: 8-17-10 (using TOAD statement execution)

From here, the to_char has failed due to unable to format the non-date result returned from decode (probably, according to my opinion). The question is:
  1. Is this a bug? Seriously, I think it is.
  2. Which is the correct result? The correct result should be #2 because the same result return if I run this query: select trunc(sysdate) from dual;
  3. How to fix the DECODE beside using CASE? Use TO_DATE function on top of DECODE.
SELECT TO_CHAR (TO_DATE (DECODE (TRUNC (SYSDATE), TO_DATE ('01012010', 'DDMMYYYY'), NULL, TRUNC (SYSDATE) ) ), 'DD/MM/YYYYY' ) FROM DUAL;

In conclusion, use case for better date result and readability.

Monday, August 9, 2010

SQL Best Practice Video

Although I'm a developer, I recommend the SQL videos below for those who interested in SQL best practice and performance tuning. It fun to watch the author present the real world problem in a interesting way. :D