From: Martin Haltmayer <Martin_Haltmayer@KirchGruppe.de>
Subject: Re: HOWTO: Use result of and EXISTS test in SELECT list?
Date: 1998/03/03
Message-ID: <34FC4130.47185ED4@KirchGruppe.de>#1/1
Content-Transfer-Encoding: 7bit
References: <6df5nt$99r@suriname.earthlink.net>
To: Randy Baker <rsbakerZ@msn.com>
Content-Type: text/plain; charset=us-ascii
Organization: KirchGruppe
Mime-Version: 1.0
Newsgroups: comp.databases.oracle.server



select column1, column2, aux.column3
from ...,
(SELECT DECODE (<any SQL statement that returns NULL if not existing>, NULL, 0,
1) ...) aux
...

Martin Haltmayer

Randy Baker wrote:
> 
> In Access and SQL Server, I can use an abomination of the following form:
> 
> select column1, column2, (EXISTS SELECT <any SQL statement>) as column3 from
> <table, join, query, etc>.
> 
> The column to which the EXISTS clause is bound is 0 if the EXISTS clause is
> false, non-zero otherwise. Is there an easy way to do this in Oracle?
> 
> Note that in any situtaion in which I use this, the SELECT clause within the
> the EXISTS table is dependent on the tables being queried in the main
> SELECT, so I can substitute a left/right outer join and test for NULL in the
> joined column.
> 
> --
> Randy Baker (remove Z from address in email replies)