| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: SQL help
Thanks but unfortuneately we only have standard edition so I do not have these features.
Rick
"Larry Elkins"
<elkinsl_at_flash To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
.net> cc:
Sent by: Subject: RE: SQL help
root_at_fatcity.c
om
09/13/2002
02:08 AM
Please respond
to ORACLE-L
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
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
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). -- 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).Received on Fri Sep 13 2002 - 08:04:06 CDT
 |
 |