| Re: Matrix Report by period - Can this be done ? - Builder 3.0 [message #77803] |
Thu, 22 November 2001 01:36 |
Kavitha
Messages: 40
Registered: December 1999
|
Member |
|
|
Hi Waris,
As you are using UNION to solve the problem, you can enclose the whole lot of UNION into an inline view and use NVL function on the columns. For example:
your current SQL:
SELECT sequence, empno, sal
from emp, table_a
where table_a.sequence = 1
and table_a.col_value = emp.empno (+)
UNION
SELECT sequence, empno, sal
from emp_deleted, table_a
where table_a.sequence = 2
and table_a.col_value = emp_deleted.empno (+)
and where emp_deleted does not have any rows and can return NULL sal, do the following:
SELECT sequence, NVL(empno, 'NO EMP'), NVL(sal, 0)
FROM (
SELECT sequence, empno, sal
from emp, table_a
where table_a.sequence = 1
and table_a.col_value = emp.empno (+)
UNION
SELECT sequence, empno, sal
from emp_deleted, table_a
where table_a.sequence = 2
and table_a.col_value = emp_deleted.empno (+)
)
The inline view will fetch all the rows from the union and then apply NVL on top of that.
HTH
Kavitha
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
