Re: How to use outer join with Oracle functions ?
Date: 1996/03/07
Message-ID: <4hnd67$l4v_at_news.Belgium.EU.net>#1/1
In article <4h7qpp$ba0_at_news.citynet.net>, coolcat_at_citynet.net says...
!>
!>Hi
!>
!>I want to do the following:
!>
!>SELECT a.*, b.*
!>FROM tablea a, tableb b
!>WHERE to_char(a.datecolumn,'hh:mi')(+) = b.timecolumn;
!>
!>where
!>- a.datecolumn is a column of type DATE.
!>- b.timecolumn is a column of type CHAR(5) containing data in the
!>format hh:mi (colons et all !)
!>
!>However, because of the presence of the to_char(), using the outer
!>join returns the error message : Invalid Relational Operator.
!>
!>I was also unable to convert both expressions using to_date().
!>
!>Any ideas/workarounds ?
!>
!>TIA.
!>
I wonder if this would work
WHERE to_char(a.datecolumn(+),'HH:MI') = b.timecolumn;
But this should fail for the outer join (when no a-record is available, the a-fields are replaced by nulls, to_char(NULL) is NULL and NULL <> b.timecolumn).
So, one more try:
WHERE nvl(to_char(a.datecolumn(+),'HH:MI'),b.timecolumn) = b.timecolumn;
I don't have PO on my 386 at home, so you'll have to try it out yourself.
Veel geluk! Received on Thu Mar 07 1996 - 00:00:00 CET