RE: SQL help

From: Larry Elkins <elkinsl_at_flash.net>
Date: Thu, 12 Sep 2002 22:08:18 -0800
Message-ID: <F001.004CF42A.20020912220818@fatcity.com>

If you don't want to apply criteria to the first part to see if data from the second part is returned, you can avoid it by using an analytic function. Ok, the examples below might look a little complex, but basically you have your UNION ALL in the inner most in-line view. In the in-line view enclosing that you use the "count(*) over" to know the total count of rows. You then evaluate that count in the outer most in-line view. If the bottom half returned rows, and you already know the top half will return 1, then a count greater than 1 means you found rows in the bottom query.

In this example, note the bottom part of the UNION ALL says 1=2, no row will be returned by the bottom query, and since the total count of rows will be one, no rows are returned (the header is suppressed):

  1 select y.x
  2 From (select x.x, count (*) over () cnt

  3        from (select 'x' x
  4              from   dual
  5              UNION ALL
  6              Select 'y'
  7              from   dual
  8              where 1=2) x ) y

  9* where y.cnt > 1
SQL> / no rows selected

In this case, the bottom half returns a row (1=1), so you will get rows since the cnt is > 1:

SQL> ed
Wrote file afiedt.buf

  1 select y.x
  2 From (select x.x, count (*) over () cnt

  3        from (select 'x' x
  4              from   dual
  5              UNION ALL
  6              Select 'y'
  7              from   dual
  8              where 1=1) x ) y

  9* where y.cnt > 1
SQL> / X
-
x
y

Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of
> Rick_Cale_at_teamhealth.com
> Sent: Thursday, September 12, 2002 4:48 PM
> To: Multiple recipients of list ORACLE-L
> Subject: SQL help
>
>
> Hi DBAs,
>
> I have a query something like
>
> SELECT sysdate,'txt1','txt2'
> FROM dual
> UNION
> SELECT date1,txtfield1,txtfield2
> FROM t1,t2,...,tn
> WHERE
> ....... ;
>
> The output would be one header record from the first select then the data
> from the second select.
> If there are no records selected in second select the header record is
> still selected. If there are no
> records in second select I do not want header record selected. How can I
> suppress it? I do not want to apply the where
> clause in 2nd select to the first because of the complexity.
>
> Thanks
> Rick
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: Rick_Cale_at_teamhealth.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Larry Elkins
  INET: elkinsl_at_flash.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri Sep 13 2002 - 01:08:18 CDT