Re: help with my sql

From: Jaap W. van Dijk <j.w.vandijk.removethis_at_hetnet.nl>
Date: Thu, 22 Aug 2002 19:43:48 +0200
Message-ID: <r18amu0sdm1va5pjjpjam0bp4ke3p5gc0o@4ax.com>

On 21 Aug 2002 18:08:16 -0700, Thomas Kyte <tkyte_at_oracle.com> wrote:

...
>update A
> set date_key = (select date_key
> from b
> where B.year = to_char(A.date,'yyyy')
> and B.month = to_char(A.date,'mm')
> and B.day = to_char(A.date,'dd')
> and B.hour = to_char(A.date,'hh24')
> and B.minute = to_char(A.date,'mi'))
>where exists (select date_key
> from b
> where B.year = to_char(A.date,'yyyy')
> and B.month = to_char(A.date,'mm')
> and B.day = to_char(A.date,'dd')
> and B.hour = to_char(A.date,'hh24')
> and B.minute = to_char(A.date,'mi'))
...
One could trade the ugly duplicate where-clause for an ugly select-item by using a dummy group function which will allways give back one row, even if a corresponding record in B does not exists:

update A
set date_key =
(select decode(max(date_key),null,A.date_key,max(date_key))

                    from b
                   where B.year = to_char(A.date,'yyyy')
                     and B.month = to_char(A.date,'mm')
                     and B.day = to_char(A.date,'dd')
                     and B.hour = to_char(A.date,'hh24')
                     and B.minute = to_char(A.date,'mi'))

If date_key in A must be null if corresponding records in B do not exists the select-item becomes simply

select max(date_key)

Jaap. Received on Thu Aug 22 2002 - 12:43:48 CDT