OraFAQ Forum: SQL & PL/SQL » Need help with UNION while selecting from dual

Home » SQL & PL/SQL » SQL & PL/SQL » Need help with UNION while selecting from dual (Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Need help with UNION while selecting from dual [message #596253]

Thu, 19 September 2013 09:35

rappaj
Messages: 147
Registered: June 2010
Location: NYC

Senior Member

Hi,

It's been a few years since I've posted here, and I am glad to see that you all are still around.

I would like to SELECT these 3 hardcoded titles from DUAL, and have a blank line under each, on the output in this order from the SQL. But the result does not end up that way
Can someone give me a hand with this please? Thank you, Joe

SQL> set heading off;
1 select '#ENCODING WINDOWS-1252' from dual
2 union
3 select ' ' from dual
4 union
5 select 'Language Section EN-US' from dual
6 union
7 select ' ' from dual
8 union
9* select 'Catalog Section Title Date Source' from dual
SQL> /

#ENCODING WINDOWS-1252
Catalog Section Title Date Source
Language Section EN-US

- - - - - - - - - - - - - - - - -

Desired Output:
#ENCODING WINDOWS-1252

Language Section EN-US

Catalog Section Title Date Source

Report message to a moderator

Re: Need help with UNION while selecting from dual [message #596254 is a reply to message #596253]

Thu, 19 September 2013 09:42

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

You want UNION ALL, UNION will return distinct records.

Report message to a moderator

Re: Need help with UNION while selecting from dual [message #596257 is a reply to message #596254]

Thu, 19 September 2013 09:49

rappaj
Messages: 147
Registered: June 2010
Location: NYC

Senior Member

Roachcoach, Thanks. That works !

Joe

Report message to a moderator

Re: Need help with UNION while selecting from dual [message #596259 is a reply to message #596257]

Thu, 19 September 2013 10:31

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You also need an ORDER BY clause if you want an order otherwise you cannot guarantee the rows will be in the order you want:

SQL> col ord noprint
SQL> select 1 ord, '#ENCODING WINDOWS-1252' val from dual
  2  union all
  3  select 2, ' ' from dual
  4  union all
  5  select 3, 'Language Section EN-US' from dual
  6  union all
  7  select 4, ' ' from dual
  8  union all
  9  select 5, 'Catalog Section Title Date Source' from dual
 10  order by 1
 11  /
VAL
---------------------------------
#ENCODING WINDOWS-1252

Language Section EN-US

Catalog Section Title Date Source

5 rows selected.

Report message to a moderator

Re: Need help with UNION while selecting from dual [message #596261 is a reply to message #596259]

Thu, 19 September 2013 10:34

rappaj
Messages: 147
Registered: June 2010
Location: NYC

Senior Member

Thanks, Michel. Order is important here.

Joe

Report message to a moderator

Re: Need help with UNION while selecting from dual [message #596669 is a reply to message #596261]

Wed, 25 September 2013 10:34

rappaj
Messages: 147
Registered: June 2010
Location: NYC

Senior Member

Hi again,

While turning off UNDERLINE, HEADING and FEEDBACK, I seem to return a blank row on the first line, but the rest is ok. Is there a way that I can avoid that 1 blank underline as the first row? Thank you, Joe.

set heading off
set underline off
set linesize 1000
set feedback off

spool c:\oracle\bpa_output_ng.txt

col ord noprint

select 1 ord, '#ENCODING WINDOWS-1252' from dual
union all
select 2, ' ' from dual
union all
select 3, 'Language Section EN-US' from dual
union all
select 4, ' ' from dual
union all
select 5, 'Catalog Section Title Date Source' from dual
order by 1;

spool off

[Updated on: Wed, 25 September 2013 10:44]

Report message to a moderator

Re: Need help with UNION while selecting from dual [message #596672 is a reply to message #596669]

Wed, 25 September 2013 10:46

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

set pagesize 0

Report message to a moderator

Re: Need help with UNION while selecting from dual [message #596674 is a reply to message #596672]

Wed, 25 September 2013 10:54

rappaj
Messages: 147
Registered: June 2010
Location: NYC

Senior Member

Yep, that worked !!! Thanks again, Michel.

Report message to a moderator

Re: Need help with UNION while selecting from dual [message #597180 is a reply to message #596674]

Tue, 01 October 2013 08:14

rappaj
Messages: 147
Registered: June 2010
Location: NYC

Senior Member

Hi all,

Sorry, I am back with this project again and I can't figure it out. Here is the problem. Although all the select from duals work, there are invisible characters after the line and I can't just chop it off right at the last letter of my constant.

This is my first line of code:

set heading off
set feedback off
set underline off
col ord noprint
set pagesize 0
set echo off
spool c:\FAQ_Help.txt
select 1 ord, '#ENCODING WINDOWS-1252' from dual;

#ENCODING WINDOWS-1252

As you can see the result LOOKS, just the way I want it. But if you open it up in NOTEPAD++, you'll see the Carriage Return and Line Feed about 50 positions to the right after my WINDOWS-1252.

Is there a way that I can just STOP the output at '#ENCODING WINDOWS-1252' then go to the next line? I've tried many things, without luck.

Screenshot attached

Thank you again,
Joe