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.
1) Create a placeholder column outside query (or use a package variable,
whatever floats your boat).
2) Create a formula column within the group. In it's PL/SQL:
a) Compare the value against the value in the placeholder and setup the
return value.
b) Set the placeholder column equal to the current value.
c) Return the value derived in step a.
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;
End if;
:CP_1 := :step;
return(v_out);
end;
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
9 FROM (
10 select
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_step
17 from nirmal ) x
18* order by step
SQL> /
FL STEP ENAME LAG_STEP LEAD_STEP NVL_LAG_STEP NVL_LEAD_STEP
-- ---------- ---------- ---------- ---------- ------------ -------------
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
[EMAIL PROTECTED]
-----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
In the above, can i able to put * mark in record 7 and 10, since before
these two records, some records are missing.
Is this possible to do this by query. I need this in reports.
Basically my question is,
How can i refer the previous row detail(s), when oracle fetchs the current
row details?.
Thanks in adv.
REgards,
Nirmal.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Larry Elkins
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
