Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: QUERY HELP
Nirmal,
You said "I need this in reports". If you mean Oracle Reports, there are a few ways to do it. If not Oracle Reports, skip down to the <<<SQL>>> part.
Following is example PL/SQL for the formula column where "CP_1" is the placeholder column and "STEP" is the value we are comparing for gaps:
function CF_1Formula return Char is
v_out varchar2(2);
begin
If :CP_1 is not null Then
If :CP_1 <> :step-1 Then v_out := '**'; else v_out := null; End if;
There might be a more efficient way to do this in Oracle Reports, but, this is the first thing that popped to mind.
<<<SQL>>>
A method that avoids a self join.
I try to minimize the number of formula's, frames, etc in Oracle Reports. So, an alternative method using pure (Oracle's) SQL, if on 8.1.6 or higher, would be to use LAG analytical function. This will allow you to avoid a self join. Following is an example with multiple columns so that you can see how the LAG/LEAD functions work. This SQL could be plugged directly into Oracle Reports, or, used "as is". Note the nvl stuff I did to handle the first row since the lag value for the first row would be null (it could have been handled many other ways):
SQL> l
1 SELECT
2 Decode(step-1,nvl_lag_step,null,'**') Flag, 3 x.step, 4 x.ename, 5 x.lag_step, 6 x.lead_step, 7 x.nvl_lag_step, 8 x.nvl_lead_step
11 step, 12 ename, 13 lag(step,1) over (order by step) lag_step, 14 lead(step,1) over (order by step) lead_step, 15 nvl(lag(step,1) over (order by step),step-1) nvl_lag_step, 16 nvl(lead(step,1) over (order by step),step+1) nvl_lead_step17 from nirmal ) x
1 SMITH 2 0 2 2 ALLEN 1 3 1 3 3 WARD 2 5 2 5
** 5 JONES 3 7 3 7
** 7 MARTIN 5 8 5 8
8 BLAKE 7 9 7 9 9 CLARK 8 10 8 10 10 SCOTT 9 15 9 15
** 15 KING 10 16 10 16
16 TURNER 15 21 15 21
** 21 ADAMS 16 23 16 23
** 23 JAMES 21 25 21 25
** 25 FORD 23 28 23 28
** 28 MILLER 25 25 29
Last but not least, if you are *not* talking about Oracle Reports, and, you are on a version *earlier* than 8.1.6, get back to me. There are other ways to approach this -- a self join, a function keeping track of a package variable, etc.
Regards,
Larry G. Elkins
elkinsl_at_flash.net
-----Original Message-----
Muthu Kumaran
Sent: Tuesday, June 26, 2001 8:01 AM
To: Multiple recipients of list ORACLE-L
Dear Guru's,
How can i refer the previous record detail(s), when oracle fetchs the
current row details?.
sql> SELECT rownum rnum, empno eno, ename FROM EMP;
RNUM ENO ENAME
1 7369 SMITH 2 7499 ALLEN 3 7521 WARD 4 7566 JONES 7 7782 CLARK 8 7788 SCOTT 10 7844 TURNER
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Larry Elkins INET: elkinsl_at_flash.net Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Tue Jun 26 2001 - 08:38:45 CDT