Home » Developer & Programmer » Forms » Re: Matrix Report by period - Can this be done ? - Builder 3.0
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

----------------------------------------------------------------------
Previous Topic: Re: Prompt Background Color in Forms 6
Next Topic: Re: First time Use of Repository, Probem with import
Goto Forum:
  


Current Time: Fri Mar 29 00:16:00 CDT 2024