Quote Delimiters 1

[pnad]

I have a query (shown below) that I use to produce a CSV file. Now, the first 2 columns need to be enclosed in ".

The query:

select BUDGETCENTER ||','||ACCOUNTNUMBER ||','||TOTALAMOUNT
from
(select c.bu_type BUDGETCENTER,
a.account_number ACCOUNTNUMBER,
sum(a.converted_amount) TOTALAMOUNT
from table a,
table b,
table c,
table d
where a.tran_id = b.tran_id
and a.bu_set_id = c.bu_set_id(+)
and a.tran_detail_id = d.tran_detail_id(+)
group by c.bu_type, c.bu_code, a.account_number
order by 1)

The output currently looks like this:

76000 ,FICA-ER ,2675.24
76000 ,FUI-ER ,145.44
76000 ,OVERTIME ,137.81

How can I make it look like this:

"76000" ,"FICA-ER" ,2675.24
"76000" ,"FUI-ER" ,145.44
"76000" ,"OVERTIME" ,137.81

I tried the q' delimiter but that does not seem to work in this case.

Please help !

Thanks.

 

[SantaMufasa]

PNAD,

You can use this code:

select '"'||BUDGETCENTER ||'","'||ACCOUNTNUMBER ||'",'||TOTALAMOUNT...

I know that it is difficult to see, but inside the single quotes (') are double quotes (")...not sequential single quotes.

Also, I am puzzled as to why you are receiving all of the extra white space following BUDGETCENTER, ACCOUNTNUMBER, and possibly total amount. Do you know why that is happening while you are concatenating the column expressions?

Let us know how the double quotes work for you and why the extra white space.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[pnad]

Thanks Mufasa !

It worked like a charm. I used the trim function to remove the extra spaces - I really am not sure why I was getting them in the first place.