Re: SQL Question - Creating a View
Date: Tue, 16 Jun 1998 12:39:22 GMT
Message-ID: <358866f1.2479996_at_192.86.155.100>
A copy of this was sent to NeedaHoliday
(if that email address didn't require changing)
On Tue, 16 Jun 1998 12:19:16 GMT, you wrote:
>I am creating a view that requires the capability to do the following:
>
>- I have a table with three address lines. They can contain
>information or be null. The requirement is, scan each address line
>(1,2,3). If address line 1 contains data, populate the view. If
>address line 1 is null, check 2 and so on. Out of all address lines
>(1,2,3) populate the view with the first address contain information.
>I tried the DECODE to no avail. Is there another function I can use?
>
>Help
>
>Jim Stewart
>james.stewart!!pwgsc.gc.ca
>
>Substitute !! with _at_ for my email address (SPAM Guard)
How did you try to do it?
select decode( addr_line1,
NULL, decode( addr_line2, NULL, addr_line3, addr_line2 ), , addr_line1 ) ADDR
from T
will do it. The decode there says:
if ( addr_line1 is NULL )
return ( if addr_line2 is NULL return addr_line3 else return addr_line2 ) else
return addr_line1
You could also use NVL
select nvl( addr_line1, nvl( addr_line2, addr_line3 ) ) from T
as well....
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Jun 16 1998 - 14:39:22 CEST