Home » SQL & PL/SQL » SQL & PL/SQL » Pipe Delimitted file in CSV format (Oracle 9i, Sun Solaris)
Pipe Delimitted file in CSV format [message #439877] Tue, 19 January 2010 16:25 Go to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Hi,
I have following sql and it works fine but use asking pipe delimitted CSV file.
Wjhen i am trying to use pipe delimitted in this sql, it throws an error as from clause not find.(like below two line)
My guess it becuase i am already using some pipe notation to join two column select sql but not sure.
I tried to take it out alias for columns but still throwing same error.
(select d.FNAME from user d where d.id = c.Report_id)||' '||(select d.lname from user d where d.id = c.report_id) "Mgr Name",
(select d.email from user d where d.id = c.Report_id) "Mgr EMAIL"


Original sql (this works fine)
============
select distinct
d.login "Login",
d.FNAME "First Name",
d.LNAME "Last Name",
d.email "Email",
c.DIV||c.DEPT "Department",
b.PHONE1,
f.PHONE2,
e.desc "Description", 
(select d.FNAME from user d where d.id = c.Report_id)||' '||(select d.lname from user d where d.id = c.report_id) "Mgr Name",
(select d.email from user d where d.id = c.Report_id) "Mgr EMAIL",
f.wave
from
cont_info b, E_info c, user d,code e, web_info f 
where d.id = b.id and
d.id = c.u_id and d.id = f.u_id 
order by d.login


If i tried to use Pipe then it throws an error:
select distinct
d.login "Login"||'|'||
d.FNAME "First Name"||'|'||
d.LNAME "Last Name"||'|'||
d.email "Email"||'|'||
c.DIV||c.DEPT "Department"||'|'||
b.PHONE1||'|'||
f.PHONE2||'|'||
e.desc "Description"||'|'|| 
(select d.FNAME from user d where d.id = c.Report_id)||' '||(select d.lname from user d where d.id = c.report_id) "Mgr Name"||'|'||
(select d.email from user d where d.id = c.Report_id) "Mgr EMAIL"||'|'||
f.wave
from
cont_info b, E_info c, user d,code e, web_info f 
where d.id = b.id and
d.id = c.u_id and d.id = f.u_id 
order by d.login


I need to pull data into pipe delimitted in csv format, i have done other reports fine with hte same but this one throwing an error.

appreciate your help!
Re: Pipe Delimitted file in CSV format [message #439878 is a reply to message #439877] Tue, 19 January 2010 16:59 Go to previous messageGo to next message
ThomasG
Messages: 3185
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
When you concatenate the columns, everything is returned in one column in the result set.

Hence your column aliases make no more sense, and are not syntactically correct any more.
Re: Pipe Delimitted file in CSV format [message #439880 is a reply to message #439878] Tue, 19 January 2010 17:23 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks Thomas.
I removed it earlier but was getting same problem.

Thanks,
Re: Pipe Delimitted file in CSV format [message #439881 is a reply to message #439880] Tue, 19 January 2010 18:36 Go to previous messageGo to next message
BlackSwan
Messages: 24907
Registered: January 2009
Senior Member
>If i tried to use Pipe then it throws an error:
ERROR? What Error? I don't see any error.
Re: Pipe Delimitted file in CSV format [message #439883 is a reply to message #439881] Tue, 19 January 2010 20:44 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
This is my guess that i want result in pipe delimitted and i am also concating two fields as || ||.
Ex.
(select d.FNAME from user d where d.id = c.Report_id)||' '||(select d.lname from user d where d.id = c.report_id)

Thanks,
Re: Pipe Delimitted file in CSV format [message #439885 is a reply to message #439877] Tue, 19 January 2010 21:41 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
As BlackSwan said Please POST YOUR ERROR. and then we can try to solve your problem.
Re: Pipe Delimitted file in CSV format [message #439909 is a reply to message #439877] Wed, 20 January 2010 01:23 Go to previous messageGo to next message
Littlefoot
Messages: 20827
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Instead of concatenating the pipe, set it as SQL*Plus delimiter and use the original query:
SQL> set colsep "|"
SQL> select * from dept;

    DEPTNO|DNAME         |LOC
----------|--------------|-------------
        10|ACCOUNTING    |NEW YORK
        20|RESEARCH      |DALLAS
        30|SALES         |CHICAGO
        40|OPERATIONS    |BOSTON

SQL>
Re: Pipe Delimitted file in CSV format [message #440065 is a reply to message #439909] Wed, 20 January 2010 21:58 Go to previous message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thank you!
Thanks for suggetion to use colsep but they like old fashioned csv way but I got it the sql with pipe delimitted.
I just need to take it out aliases it was meaningless and causing problem too and removed order by clause which was stopping to execute sql.

Thanks for your help!
Previous Topic: How to control the loop
Next Topic: Ping an IP from oracle-check for response.
Goto Forum:
  


Current Time: Tue Sep 27 09:17:21 CDT 2016

Total time taken to generate the page: 0.33751 seconds