UNION which must return nothing if the second part returns nothing

From: Stephane Faroult <sfaroult_at_oriole.com>
Date: Mon, 16 Sep 2002 11:23:25 -0800
Message-ID: <F001.004D0B4D.20020916112325@fatcity.com>

Don't remember who posted the problem (sorry), but I believe I have a solution which can work if the result set is not too big (it sorts it twice).
The problem is that the first part of the UNION should somehow anticipate on the result. The idea is to reverse first, check the rownum (the rownum for the header must be > 1), and reorder again. It's a bit complicated to explain but here is an example :

select datecol, txt1, txt2
from (select rownum rn, rowtype, x.datecol, x.txt1, x.txt2

from (select 'H' rowtype, sysdate datecol, 'ENAME' txt1, 'DNAME' txt2

            from dual
            union
            select 'D',
                   e.hiredate,
                   e.ename,
                   d.dname
            from emp e,
                 dept d
            where e.deptno = d.deptno
              and e.deptno = &deptno
            order by 1) x)

where (rn > 1 or rowtype = 'D')
order by rn desc;

After a set pagesize 0 :

SQL> @example
Enter value for deptno: 20

old  13:	       and e.deptno = &deptno
new  13:	       and e.deptno = 20
16-SEP-02 ENAME      DNAME
12-JAN-83 ADAMS      RESEARCH
09-DEC-82 SCOTT      RESEARCH
03-DEC-81 FORD	     RESEARCH
02-APR-81 JONES      RESEARCH
17-DEC-80 SMITH      RESEARCH

6 rows selected.

SQL> @example
Enter value for deptno: 70

old  13:	       and e.deptno = &deptno
new  13:	       and e.deptno = 70

no rows selected

--

Regards,

Stephane Faroult
Oriole Software
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Stephane Faroult
INET: sfaroult_at_oriole.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

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 Mon Sep 16 2002 - 14:23:25 CDT