RE: QUERY HELP

From: Larry Elkins <elkinsl_at_flash.net>
Date: Tue, 26 Jun 2001 06:38:45 -0700
Message-ID: <F001.0033821C.20010626064655@fatcity.com>

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.

Create a placeholder column outside query (or use a package variable, whatever floats your boat).
Create a formula column within the group. In it's PL/SQL:
Compare the value against the value in the placeholder and setup the return value.
Set the placeholder column equal to the current value.
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
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

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: 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