Home » Other » Client Tools » Columns in a delimited file (Oracel PL/SQL 9i Microsoft 2K3)
Columns in a delimited file [message #433531] Wed, 02 December 2009 15:58 Go to next message
dnfrantum
Messages: 11
Registered: November 2009
Location: California
Junior Member
How can I add column headers to a query that uses concatenation to add delimiters?

Here is a sample of the query:
SELECT DISTINCT T3.R_Object_ID||','||T3.Object_Name||','||T3.Title||','||T3.R_Object_Type||','||
DECODE (T3.R_Status,2,'Superseded' ,3,'Retired',4,'Draft',5,'Review Copy',7,'Authorised',8,'Withdrawn')
||','||T3.R_version||','||T0.Object_Name||','||T0.R_Object_Type||','||DECODE(T0.R_Status,0,'Draft',1,'Workflow Approval',2,'Workflow Approved',
3,'Workflow Disapproved',4,'Cancelled',5,'Editing',6,'Release Approval',7,'Authorised Pending',8,'Release Disapproved',
9,'Awaiting Pre-Approval',10,'Change Review',11,'Pending Release',12,'Released',13,'Superceded',14,'Edit Complete',
15,'Review',16,'Review Complete',17,'Change Review Complete',18,'Undefined',19,'Workflow Approved',20,'Release Approved',
21,'Authorised')||','||T0.R_TargetDate||','||T4.User_Name||','||T8.User_Name||','||T9.R_Name||','||T10.R_Order||','||DECODE(T7.R_Status,
0,'Not Applicable', 1,'Disapproved', 2,'Approved', 3,'Not Required', 4,'Pending', 5,'Withdrawn')||','||CASE WHEN T7.R_STATUS <> 4 THEN ROUND(T7.R_DecisionDate - T7.R_StartDate,0) 
WHEN T7.R_STATUS = 4 THEN ROUND(sysdate - T7.R_StartDate,0) ELSE null END


Thanks in advance,
Donald

Re: Columns in a delimited file [message #433535 is a reply to message #433531] Wed, 02 December 2009 19:29 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SQL> set colsep ","
SQL> select * from employer;

	ID,NAME      ,	  SALARY,     MONTH
----------,----------,----------,----------
	 1,Eric      ,	    1500,	  6
	 1,Eric      ,	    2000,	  7
	 1,Eric      ,	    1800,	  8
	 2,Steve     ,	    2200,	  7
	 2,Steve     ,	    1200,	  8
Re: Columns in a delimited file [message #433545 is a reply to message #433531] Wed, 02 December 2009 21:19 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
There are so many ways....

1) Black swan already suggested(Only for sql).
2) search for Asktom`s dump csv function.
3) Use a simple logic header list concated with ',' union all your required column list.
Ex:
select 'SRIRAM'||','||'ENAME'||','||'JOB' from dual
union all
select SRIRAM||','||ENAME||','||JOB from emp;


sriram Smile
Re: Columns in a delimited file [message #433567 is a reply to message #433545] Thu, 03 December 2009 00:51 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
ramoradba wrote on Thu, 03 December 2009 04:19
1) Black swan already suggested(Only for sql).

The tool is called sqlplus. SQL is a language. It is not a Microsoft product, nor a tool.
Re: Columns in a delimited file [message #433570 is a reply to message #433567] Thu, 03 December 2009 01:03 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Yes,i know that. That is a typo mistake Wink
Re: Columns in a delimited file [message #433581 is a reply to message #433570] Thu, 03 December 2009 02:24 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
"SQL*Puls" is a typo.

"SQL" instead of "SQL*Plus" is a bad habit.
Previous Topic: Date Difference
Next Topic: Specific Client side Port numbers For Client tools
Goto Forum:
  


Current Time: Fri Mar 29 02:28:44 CDT 2024