Pipe Delimitted file in CSV format [message #439877] |
Tue, 19 January 2010 16:25  |
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   |
ThomasG
Messages: 3212 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 #439883 is a reply to message #439881] |
Tue, 19 January 2010 20:44   |
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 #440065 is a reply to message #439909] |
Wed, 20 January 2010 21:58  |
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!
|
|
|