Home » SQL & PL/SQL » SQL & PL/SQL » Enforce blank record (Oracle 11G)
icon5.gif  Enforce blank record [message #655897] Thu, 15 September 2016 11:49 Go to next message
OlafCologne
Messages: 11
Registered: September 2008
Location: Cologne
Junior Member
select (select 'Result1' from dual) as Column1, Column2 from table1 where table1.xy = 'XXXX'
union
select (select 'Result2' from dual) as Column1, Column2 from table2 where table2.xy = 'XXXX'


If the select returns no result, no line is output.
How can I force that always prints a line?
The select is shown in simplified form. With subselect it is too complex.

Thanks!

Olaf


Re: Enforce blank record [message #655898 is a reply to message #655897] Thu, 15 September 2016 12:00 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You could UNION ALL a query with a NOT EXISTS predicate:
orclz> ed
Wrote file afiedt.buf

  1  select 'x',deptno from dept where deptno=50
  2  union all
  3* select null,null from dual where not exists (select 'x' from dept where deptno=50)
orclz> /

'     DEPTNO
- ----------


orclz> ed
Wrote file afiedt.buf

  1  select 'x',deptno from dept where deptno=10
  2  union all
  3* select null,null from dual where not exists (select 'x' from dept where deptno=10)
orclz> /

'     DEPTNO
- ----------
x         10

orclz>
Re: Enforce blank record [message #655899 is a reply to message #655897] Thu, 15 September 2016 12:40 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Just outer join it to dual:

with t as (
            select  (select 'Result1' from dual) as Column1,
                    Column2,
                    'X' dummy -- add this column
              from  table1
              where table1.xy = 'XXXX'
           union
            select  (select 'Result2' from dual) as Column1,
                    Column2,
                    'X' dummy -- add this column
              from  table2
              where table2.xy = 'XXXX'
          )
select  Column1,
        Column2
  from  t,
        dual d
  where d.dummy = t.dummy(+)
/

SY.
Re: Enforce blank record [message #655900 is a reply to message #655897] Thu, 15 September 2016 12:49 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
select (select 'Result1' from dual) as Column1, Column2 from table1 where table1.xy = 'XXXX'
NEVER use subquery in SELECT until you'll be an SQL expert.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Previous Topic: Global Temporary Tables
Next Topic: ORA-01847: day of month must be between 1 and last day of month 01847. 00000 - "day of month must b
Goto Forum:
  


Current Time: Thu Mar 28 13:01:09 CDT 2024