Home » SQL & PL/SQL » SQL & PL/SQL » Extract in csv format in Oracle SQL (Oracle 8i)
Extract in csv format in Oracle SQL [message #322915] Mon, 26 May 2008 19:06 Go to next message
apps_user
Messages: 35
Registered: May 2008
Member
HI
The requirement is to create an extract in csv(comma separated) format as follows.
The extract includes column header also.

Quote:

dept empno empname hiredate active
ACC 2490 Dori 10-OCT-07 Y
IT 2500 Harry 10-MAY-08 Y
FIN 2501 Rick 10-APR-08 Y



I tried like this
select 'dept'||','||'empno'||','||'empname'||','||'hiredate'||','||'active'
from dual
union
select concat(d.dept,','),concat(e.empno,','),concat(e.empname,','),concat(e.hiredate,','),e.active
from emp e,dept d
where e.dept_no=d.dept_no;

But the output is as follows(the column header is showing on the second line instead of first line:
Quote:

ACC 2490 Dori 10-OCT-07 Y
dept empno empname hiredate active
IT 2500 Harry 10-MAY-08 Y
FIN 2501 Rick 10-APR-08 Y


I tried union all instead of union, it didnot help.
Order by also didnot help.
Please help.
Thanks in advance.


Re: Extract in csv format in Oracle SQL [message #322916 is a reply to message #322915] Mon, 26 May 2008 19:16 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above


The order of rows returned can ONLY be assured by using ORDER BY clause.

[Updated on: Mon, 26 May 2008 19:19] by Moderator

Report message to a moderator

Re: Extract in csv format in Oracle SQL [message #322917 is a reply to message #322916] Mon, 26 May 2008 19:29 Go to previous messageGo to next message
apps_user
Messages: 35
Registered: May 2008
Member
Ana
Sorry to say so,order by doesnot help.
Thanks
Re: Extract in csv format in Oracle SQL [message #322918 is a reply to message #322915] Mon, 26 May 2008 19:37 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>Sorry to say so,order by doesnot help.
Your use of ORDER BY may not have produced the results you expected or desired;
but that reality does not make my statement to be false.



Re: Extract in csv format in Oracle SQL [message #322921 is a reply to message #322918] Mon, 26 May 2008 20:27 Go to previous messageGo to next message
apps_user
Messages: 35
Registered: May 2008
Member
Let us stress on approaching the solution for this.
On my first post on this thread,I have mentioned already that "Order by" clause doesnot work.
Let us not waste our time and energy on same thing again and again.
Also I donot want to spend time on whose statement is right or wrong.Everyone is a learner and everyone learns new things everyday.No one in this world can say that he or she knows 100%.

Re: Extract in csv format in Oracle SQL [message #322922 is a reply to message #322915] Mon, 26 May 2008 20:29 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: Extract in csv format in Oracle SQL [message #322923 is a reply to message #322915] Mon, 26 May 2008 20:34 Go to previous messageGo to next message
apps_user
Messages: 35
Registered: May 2008
Member
I am following the guidelines.
Please advise if I am wrong somewhere.
Re: Extract in csv format in Oracle SQL [message #322924 is a reply to message #322915] Mon, 26 May 2008 20:38 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
* Provide DDL (CREATE TABLE, etc.)
* Provide INSERT statements for sample data
Re: Extract in csv format in Oracle SQL [message #322926 is a reply to message #322924] Mon, 26 May 2008 21:05 Go to previous messageGo to next message
apps_user
Messages: 35
Registered: May 2008
Member
I am attaching herewith the following three files:
emp_ddl.sql (creating tables)
emp_dml.sql (inserting sample data)
emp.sql (sql for running and creating extract)
  • Attachment: emp_ddl.sql
    (Size: 0.17KB, Downloaded 157 times)
Re: Extract in csv format in Oracle SQL [message #322927 is a reply to message #322915] Mon, 26 May 2008 21:06 Go to previous messageGo to next message
apps_user
Messages: 35
Registered: May 2008
Member
No Message Body
  • Attachment: emp_dml.sql
    (Size: 0.50KB, Downloaded 153 times)
Re: Extract in csv format in Oracle SQL [message #322928 is a reply to message #322915] Mon, 26 May 2008 21:07 Go to previous messageGo to next message
apps_user
Messages: 35
Registered: May 2008
Member
No Message Body
  • Attachment: emp.sql
    (Size: 0.36KB, Downloaded 163 times)
Re: Extract in csv format in Oracle SQL [message #322929 is a reply to message #322915] Mon, 26 May 2008 21:11 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>The requirement is to create an extract in csv(comma separated) format as follows.

How/Why are there NO commas in any of your "sample output" as posted above & none generated by your SQL?

Re: Extract in csv format in Oracle SQL [message #322930 is a reply to message #322915] Mon, 26 May 2008 21:12 Go to previous messageGo to next message
apps_user
Messages: 35
Registered: May 2008
Member
Ana
If you try the emp.sql which I attached the output will be as follows.
Quote:

ACC,2490,Dori,10-OCT-07,Y
FIN,2501,Rick,10-APR-08,Y
IT,2500,Harry,10-MAY-08,Y
dept,empno,empname,hiredate,active

Re: Extract in csv format in Oracle SQL [message #322932 is a reply to message #322915] Mon, 26 May 2008 21:21 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
The challenge is to overcome natural collation sequence.

  1  select 'AAdept'||','||'empno'||','||'empname'||','||'hiredate'||','||'active'
  2  from dual
  3  union
  4  select d.dept_name||','||e.empno||','||e.empname||','||e.hiredate||','||e.active
  5  from emp e,dept d
  6* where e.dept_no=d.dept_no
SQL> /
AAdept,empno,empname,hiredate,active
ACC,2490,Dori,10-OCT-07,Y
FIN,2501,Rick,10-APR-08,Y
IT,2500,Harry,10-MAY-08,Y

It is difficult to make a pig fly.

Good Luck!
Re: Extract in csv format in Oracle SQL [message #322934 is a reply to message #322932] Mon, 26 May 2008 21:25 Go to previous messageGo to next message
apps_user
Messages: 35
Registered: May 2008
Member
Ana
Thanks for your help.
Unfortunately,I cannot add "AA" before the first column name as in this case it is dept.I cannot play around with the requirement.
Any other suggestions?
Re: Extract in csv format in Oracle SQL [message #322936 is a reply to message #322934] Mon, 26 May 2008 21:30 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
SQL> select * from (
  2  select 'dname'||','||'empno'||','||'ename'||','||'hiredate' d
  3  from dual
  4  union all
  5  select lower(d.dname)||','||e.empno||','||e.ename||','||e.hiredate d
  6  from emp e, dept d
  7  where e.deptno = d.deptno)
  8  order by decode(d, 'dname,empno,ename,hiredate', 1, 2);
dname,empno,ename,hiredate
sales,7499,ALLEN,20-FEB-81
sales,7521,WARD,22-FEB-81
research,7566,JONES,02-APR-81
sales,7654,MARTIN,28-SEP-81
sales,7698,BLAKE,01-MAY-81
accounting,7782,CLARK,09-JUN-81
research,7788,SCOTT,19-APR-87
accounting,7839,KING,17-NOV-81
sales,7844,TURNER,08-SEP-81
research,7876,ADAMS,23-MAY-87
sales,7900,JAMES,03-DEC-81
research,7902,FORD,03-DEC-81
accounting,7934,MILLER,23-JAN-82
research,7369,SMITH,17-DEC-80
Re: Extract in csv format in Oracle SQL [message #322937 is a reply to message #322915] Mon, 26 May 2008 21:37 Go to previous messageGo to next message
apps_user
Messages: 35
Registered: May 2008
Member
HI Ebrian
I tried the sql you posted on this thread.
Here is what I got.

  1  select * from (
  2    select 'dname'||','||'empno'||','||'ename'||','||'hiredate' d
  3    from dual
  4    union all
  5    select lower(d.dept_name)||','||e.empno||','||e.empname||','||e.hiredate d
  6    from emp e, dept d
  7    where e.dept_no = d.dept_no
  8    order by 1)
  9*   order by decode(d, 'dept_name,empno,empname,hiredate', 1, 2)
SQL> /

D
--------------------------------------------------------------------------------
acc,2490,Dori,10-OCT-07
dname,empno,ename,hiredate
fin,2501,Rick,10-APR-08
it,2500,Harry,10-MAY-08


Looks like we are back to square one.
Did I miss something by any chance?
Re: Extract in csv format in Oracle SQL [message #322939 is a reply to message #322937] Mon, 26 May 2008 21:43 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
The first inner select should be changed to correspond to your column names.

select * from (
   select 'dept_name'||','||'empno'||','||'empname'||','||'hiredate' d
    from dual
    union all
    select lower(d.dept_name)||','||e.empno||','||e.empname||','||e.hiredate d
    from emp e, dept d
    where e.dept_no = d.dept_no
    order by 1)
order by decode(d, 'dept_name,empno,empname,hiredate', 1, 2);

Plus, you can do away with the lower function if you don't need it.

[Updated on: Mon, 26 May 2008 21:47]

Report message to a moderator

Re: Extract in csv format in Oracle SQL [message #322942 is a reply to message #322939] Mon, 26 May 2008 22:20 Go to previous messageGo to next message
apps_user
Messages: 35
Registered: May 2008
Member
Yahoo, it works.
You are great,Ebrian.
Thanks Ebrian for your help on this.
Re: Extract in csv format in Oracle SQL [message #323000 is a reply to message #322915] Tue, 27 May 2008 00:54 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The better way (I think) when you have union of queries you want to be displayed in a specific order is to use a hidden column.
Col myorder noprint
select 1 myorder,
       'dept'||','||'empno'||','||'empname'||','||'hiredate'||','||'active'
from dual
union all
select 2 myorder, concat(d.dept,','),concat(e.empno,','),concat(e.empname,','),concat(e.hiredate,','),e.active
from emp e,dept d
where e.dept_no=d.dept_no
order by 1;

Regards
Michel

[Updated on: Tue, 27 May 2008 00:54]

Report message to a moderator

Previous Topic: Hash partitions
Next Topic: Search through Wild Card character
Goto Forum:
  


Current Time: Fri Dec 02 20:57:16 CST 2016

Total time taken to generate the page: 0.12969 seconds