Re: how to do this with sql?

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Wed, 10 May 2006 20:16:53 +0200
Message-ID: <44622e15$0$288$626a54ce@news.free.fr>

"hopehope_123" <hopehope_123_at_yahoo.com> a écrit dans le message de news: 1147281011.141642.118900_at_v46g2000cwv.googlegroups.com...
| Hi Michel ,
|
| Thanks for your mail.But it returns the error:
|
| select distinct first_value(col1 ignore nulls)
| *
| ERROR at line 6:
| ORA-00907: missing right parenthesis
|
| This is 9.0.2.6.
|
| Kind Regards,
| tolga
|

Yes, "ignore nulls" is new in 10g.
In 9i, you can workaround this with a patch in "order by" clause:

SQL> with data as (
  2 select '20060510' dt, null col1, 'yy' col2, 1 col3 from dual   3 union all
  4 select '20060509' dt, 'xx' col1, null col2, 1 col3 from dual   5 )
  6 select distinct

  7         first_value(col1)
  8           over (order by decode(col1,null,to_date('01/01/0001','DD/MM/YYYY'),dt) desc
  9                 rows between unbounded preceding and unbounded following) col1,
 10         first_value(col2)
 11           over (order by decode(col2,null,to_date('01/01/0001','DD/MM/YYYY'),dt) desc
 12                 rows between unbounded preceding and unbounded following) col2,
 13         first_value(col3)
 14            over (order by decode(col3,null,to_date('01/01/0001','DD/MM/YYYY'),dt) desc
 15                  rows between unbounded preceding and unbounded following) col3

 16 from data
 17 /
CO CO COL3
-- -- ----------
xx yy 1

1 row selected.

Regards
Michel Cadot Received on Wed May 10 2006 - 13:16:53 CDT