Thursday, September 27, 2007

ORCL - Blank Line

My colleage asked me if there is a way to have empty blink line inside SQL select to seperate the totals. Actullay you can use CASE to check some column (this column maybe generated in the subquery).

I tried the following statement, and it's pretty cool, just an idea to be fancy:))

Connected to Oracle9i Release 9.2.0.6.0
Connected as advance
SQL>
select CASE
WHEN receipt_number IS NULL THEN
''
ELSE
donor_id
END id_no,
receipt_number,
CASE
WHEN receipt_number IS NULL THEN
''
ELSE
to_char(amount)
END
from (select g.donor_id,
g.receipt_number,
sum(g.some_amount) amount
from table_name g
group by rollup(g.donor_id, g.receipt_number))

No comments:

Post a Comment