Re: how to do this with sql?

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

"Michel Cadot" <micadot{at}altern{dot}org> a écrit dans le message de news: 44622e15$0$288$626a54ce_at_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
|

Sorry, forgot dt is not a real date:

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,'00000000',dt) desc
  9                 rows between unbounded preceding and unbounded following) col1,
 10         first_value(col2)
 11           over (order by decode(col2,null,'00000000',dt) desc
 12                 rows between unbounded preceding and unbounded following) col2,
 13         first_value(col3)
 14           over (order by decode(col3,null,'00000000',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:23:21 CDT