Home » SQL & PL/SQL » SQL & PL/SQL » need column headers
need column headers [message #246153] Wed, 20 June 2007 02:11 Go to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
i have a sql query as follows



select '"'||a.ename ||'"'|| ','||'"'||b.dname||'"'
 from emp a,dep b
where a.depid=b.depid



output is




SQL> select '"'||a.ename ||'"'|| ','||'"'||b.dname||'"'
  2   from emp a,dep b
  3  where a.depid=b.depid;

'"'||A.ENAME||'"'||','||'"'||B.DNAME||'"'
---------------------------------------------
"emp1","dep1"
"emp2","dep1"
"emp3","dep2"





now, in my project, i am exporting this to a csv file,
but am not getting the column headers...
i want to have the column headers for this output...
i mean in the excel file, i cant see any headers, for this
purpose, i have an idea that we need to put another select
statement above this, like "select a.ename,b.dname as..."

but they are not working

can anyone please tell me how to include column headers for this output?

Re: need column headers [message #246162 is a reply to message #246153] Wed, 20 June 2007 02:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select '"'||a.ename ||'"'|| ','||'"'||b.dname||'"' as "ename,dname"
 from emp a,dep b
where a.depid=b.depid
/

Regards
Michel
Re: need column headers [message #246171 is a reply to message #246162] Wed, 20 June 2007 03:24 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Considering the "I want do do an Excel report from a query" - questions onslaught the last couple of days I thought I mention another option, which also takes care about the column width, headers and some other stuff.

Sql*plus can output results in HTML format, which can then be imported into Excel, which can be combined with the -s "silent" option to supress the comand feedback itself :

sqlplus -s scott/tiger@db


Sql*plus script :

set echo off
set markup HTML on
set pages 0
set feedback off
spool user_tables.html
select * from user_tables;
spool off
set markup HTML off
not solved yet [message #246195 is a reply to message #246153] Wed, 20 June 2007 04:35 Go to previous messageGo to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member


the result i am getting in my excel, when i open is
somethinglike this


"abc","mycustomer","CUSER1"
"abc","T13","CUSER1"
"abc","TSB1","CUSERSB1"
"abc","TCORP1","CUSERSB1"
"abc","CORP1","DUPUSER1"
"abc","SB1","DUPUSER1"
"abc","CORP2","DUPUSER2"
"abc","SB2","DUPUSER2"




where as, i want a column heading..not an alias as mentioned
in a solution earlier...that earlier solution did not work
for me.........

i used the following example


[CODE]

SET HEADING ON;
COLUMN ENAME HEADING "EMPNAME";
COLUMN DNAME HEADING "DEPNAME";
SPOOL MYLOG.TXT;
select '"'||a.ename ||'"'|| ','||'"'||b.dname||'"' 
 from emp a,dep b
where a.depid=b.depid;
SPOOL OFF;


[/CODE]

if i use "as", the sqlplus output is fine, but i am not
able to see the results in excel file
the above example also doesnt seem to work



Re: need column headers [message #246197 is a reply to message #246153] Wed, 20 June 2007 04:39 Go to previous messageGo to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
I GET identifier too long error, is it related to set linesize?
Re: not solved yet [message #246198 is a reply to message #246195] Wed, 20 June 2007 04:40 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Alias is reported as column header.
Why it is not working for you?
SQL> set underline off
SQL> select '"'||a.ename ||'"'|| ','||'"'||b.dname||'"' as "ENAME,DNAME"
  2   from emp a,dept b
  3  where a.deptno = b.deptno
  4  /
ENAME,DNAME
"CLARK","ACCOUNTING"
"KING","ACCOUNTING"
"MILLER","ACCOUNTING"
"JONES","RESEARCH"
"FORD","RESEARCH"
"ADAMS","RESEARCH"
"SMITH","RESEARCH"
"SCOTT","RESEARCH"
"WARD","SALES"
"TURNER","SALES"
"ALLEN","SALES"
"JAMES","SALES"
"BLAKE","SALES"
"MARTIN","SALES"

14 rows selected.

Regards
Michel
Previous Topic: Where clause in SQL MERGE UPDATE statement
Next Topic: Replace No Rows Returned with Zeros
Goto Forum:
  


Current Time: Sun Dec 04 02:30:01 CST 2016

Total time taken to generate the page: 0.11515 seconds