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)
Need help with UNION while selecting from dual [message #596253] Thu, 19 September 2013 09:35 Go to next message
rappaj
Messages: 114
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
Re: Need help with UNION while selecting from dual [message #596254 is a reply to message #596253] Thu, 19 September 2013 09:42 Go to previous messageGo to next message
Roachcoach
Messages: 1202
Registered: May 2010
Location: UK
Senior Member
You want UNION ALL, UNION will return distinct records.
Re: Need help with UNION while selecting from dual [message #596257 is a reply to message #596254] Thu, 19 September 2013 09:49 Go to previous messageGo to next message
rappaj
Messages: 114
Registered: June 2010
Location: NYC
Senior Member
Roachcoach, Thanks. That works !

Joe
icon4.gif  Re: Need help with UNION while selecting from dual [message #596259 is a reply to message #596257] Thu, 19 September 2013 10:31 Go to previous messageGo to next message
Michel Cadot
Messages: 59118
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.


Re: Need help with UNION while selecting from dual [message #596261 is a reply to message #596259] Thu, 19 September 2013 10:34 Go to previous messageGo to next message
rappaj
Messages: 114
Registered: June 2010
Location: NYC
Senior Member
Thanks, Michel. Order is important here.

Joe
Re: Need help with UNION while selecting from dual [message #596669 is a reply to message #596261] Wed, 25 September 2013 10:34 Go to previous messageGo to next message
rappaj
Messages: 114
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

icon3.gif  Re: Need help with UNION while selecting from dual [message #596672 is a reply to message #596669] Wed, 25 September 2013 10:46 Go to previous messageGo to next message
Michel Cadot
Messages: 59118
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

set pagesize 0

Re: Need help with UNION while selecting from dual [message #596674 is a reply to message #596672] Wed, 25 September 2013 10:54 Go to previous messageGo to next message
rappaj
Messages: 114
Registered: June 2010
Location: NYC
Senior Member
Yep, that worked !!! Thanks again, Michel.
Re: Need help with UNION while selecting from dual [message #597180 is a reply to message #596674] Tue, 01 October 2013 08:14 Go to previous messageGo to next message
rappaj
Messages: 114
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



Re: Need help with UNION while selecting from dual [message #597181 is a reply to message #597180] Tue, 01 October 2013 08:17 Go to previous messageGo to next message
Michel Cadot
Messages: 59118
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

set trimout on trimspool on

Re: Need help with UNION while selecting from dual [message #597182 is a reply to message #597180] Tue, 01 October 2013 08:18 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
set trimspool on
Re: Need help with UNION while selecting from dual [message #597189 is a reply to message #597182] Tue, 01 October 2013 08:28 Go to previous messageGo to next message
rappaj
Messages: 114
Registered: June 2010
Location: NYC
Senior Member
I'm just sitting here shaking my head because I spent a day trying to get around this.

Thanks again, Michel. IT WORKS PERFECTLY !!!
Re: Need help with UNION while selecting from dual [message #597190 is a reply to message #597189] Tue, 01 October 2013 08:31 Go to previous message
rappaj
Messages: 114
Registered: June 2010
Location: NYC
Senior Member
P.S. Cookiemonster. Thanks to you too for your reply as well.
Previous Topic: confused about outer join
Next Topic: Regexp_SUBSTR on an array returning no value
Goto Forum:
  


Current Time: Wed Sep 17 19:03:14 CDT 2014

Total time taken to generate the page: 0.08802 seconds