Oracle Analytics Function

Question

I have an input dataset that looks like this:

header1,null,null,null
null,1,X,Y,Z
null,2,X,Y,Z
null,3,X,Y,Z
header2,null,null,null
null,4,X,Y,Z
null,5,X,Y,Z
null,6,X,Y,Z

I'd like to write a query against that input to return a result set that looks like this:

header1,1,X,Y,Z
header1,2,X,Y,Z
header1,3,X,Y,Z
header2,4,X,Y,Z
header2,5,X,Y,Z
header2,6,X,Y,Z

I'm on Oracle 11g R2. I've been looking at the Oracle analytics intro found here: http://www.oracle-base.com/articles/misc/analytic-functions.php

I'm thinking there is a solution but I've not found it. The problem I think is that I have nothing to really group by. My only 'indicator' is to repeat the header value until I hit a set of nulls, then grab the next header and repeat it until I hit some nulls. So order is important, but you can see we have no column to order by.

Below are a couple quick scripts to build the test table and insert the rows.

Thanks you.

create table test (
col01 varchar2(10),
col02 varchar2(10),
col03 varchar2(10),
col04 varchar2(10))

insert into test values ('header1',null,null,null);
insert into test values (1,'X','Y','Z');
insert into test values (2,'X','Y','Z');
insert into test values (3,'X','Y','Z');
insert into test values ('header2',null,null,null);
insert into test values (4,'X','Y','Z');
insert into test values (5,'X','Y','Z');
insert into test values (6,'X','Y','Z');

If you have no column to order by how can you expect Oracle to do the ordering? — René Nyffenegger
– René Nyffenegger, Commented Sep 26, 2012 at 16:35

rs. · Accepted Answer · 2012-09-26 16:32:49Z

2

Try this

WITH CTE AS (
SELECT COL01, COL02, COL03, COL04, 
CASE WHEN COL01 LIKE 'header%' THEN col01 else null END NEW_COL01
FROM TESTTable
)

SELECT * FROM (
SELECT COL01, COL02, COL03, COL04, 
CASE WHEN NEW_COL01 IS NULL 
THEN LAG(NEW_COL01 IGNORE NULLS) OVER (ORDER BY ROWID) END HEAD_COL
FROM CTE
) WHERE col01 not like 'header%';

answered Sep 26, 2012 at 16:32

rs.

27.6k13 gold badges71 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Over a year ago

Cool. Thats the ticket. Thank you.

hol · Accepted Answer · 2012-09-26 20:44:09Z

2

LAG is the answer but you need to rely that the rows always come in the same sequence and then you can use rowid.

Look at the SQL Fiddle

select * 
  from
    (select case when header is null
                 then lag(header ignore nulls) over (order by rowid) 
             end header,col01,col02,col03,col04
       from
       (select case when col01 like 'header%' 
                    then col01 
                end header,col01,col02,col03,col04
        from test) 
    )
where header is not null

I hope this is to process the table one time and bring it into a proper data model because the table design is not of quite good design but I guess you know that already.

answered Sep 26, 2012 at 20:44

hol

8,4615 gold badges37 silver badges63 bronze badges

Collectives™ on Stack Overflow

Oracle Analytics Function

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related