Enforce blank record [message #655897] |
Thu, 15 September 2016 11:49 |
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 |
John Watson
Messages: 8951 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 |
Solomon Yakobson
Messages: 3286 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 |
|
Michel Cadot
Messages: 68693 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.
|
|
|