Export to a CSV file 1

[pnad]

Hello !

I have the following query that I need to run on an automatic basis to produce a CSV file.

select case b.entityno1
when '001' then
'PLIC'
when '003' then
'PLAC'
when '005' then
'OLIC'
when '090' then
'ILIC'
when '091' then
'NILIC'
when '057' then
'NILICS'
when '060' then
'ILICS'
else
'PLIC'
end as ORGUNIT,
case b.entityno1
when '001' then
'UYB'
when '003' then
'UYB'
when '005' then
'USB'
when '090' then
'USB'
when '091' then
'ABB'
when '057' then
'BGY'
when '060' then
'BGY'
else
'FTB'
end as BANK,
when '001' then
'PLIC'
when '003' then
'PLAC'
when '005' then
'OLIC'
when '090' then
'ILIC'
when '091' then
'NILIC'
when '057' then
'NILICS'
when '060' then
'ILICS'
else
'PLIC'
end as COMPANY,
concat(substr(b.entityno1, -2, 2), substr(b.invname, 1, 7)) as DEST,
to_char(to_date(a.duedate, 'yyyymmdd'), 'dd-MON-yyyy'),
to_char(to_date(a.duedate, 'yyyymmdd'), 'dd-MON-yyyy'),
lpad(trunc(a.cashflow, 2),18,'0'),
case b.entityno1
when '001' then
'68768478354'
when '003' then
'46746764765'
when '005' then
'34545435444'
when '090' then
'23423432423'
when '091' then
'07023353159'
when '057' then
'34534534534'
when '060' then
'89664346354'
else
'56757537457'
end as ACCOUNT
from cashflow a, investor b
where a.invid = b.invid;

The CSV file should have a fixed header similar to:
#Orgunit,Bank,Category, Company,Dest, Release, DateFld, Amount,Account, Source

Also, the value of Source and Category in the CSV file should be hard-coded to 'M IN' and 'MLA' respectively.

Any help will be greatly appreciated !

 

[SantaMufasa]

PNAD, here is my suggestion (running from the SQL*Plus prompt) for producing a CSV file in the format you posted, while slimming down the code significantly:

set feedback off
set pagesize 0
set linesize 2000
set trimspool on
spool filename.csv
select case rownum when 1 then '#Orgunit,Bank,Category, Company,Dest, Release, DateFld, Amount,Account, Source'
else orgunit||','||bank||',MLA,'||orgunit||','||
       dest||','||duedate||','||duedate||','||
       cashflow||','||account||',M IN' end
 from (select case b.entityno1
         when '001' then 'PLIC'
         when '003' then 'PLAC'
         when '005' then 'OLIC'
         when '090' then 'ILIC'
         when '091' then 'NILIC'
         when '057' then 'NILICS'
         when '060' then 'ILICS'
         else 'PLIC' end ORGUNIT,
       case b.entityno1
         when '001' then 'UYB'
         when '003' then 'UYB'
         when '005' then 'USB'
         when '090' then 'USB'
         when '091' then 'ABB'
         when '057' then 'BGY'
         when '060' then 'BGY'
         else 'FTB' end BANK,
       substr(b.entityno1, -2, 2)||substr(b.invname, 1, 7)DEST,
       lpad(trunc(a.cashflow, 2),18,'0') cashflow,
       case b.entityno1
         when '001' then '68768478354'
         when '003' then '46746764765'
         when '005' then '34545435444'
         when '090' then '23423432423'
         when '091' then '07023353159'
         when '057' then '34534534534'
         when '060' then '89664346354'
         else '56757537457' end ACCOUNT
  from cashflow a, investor b
 where a.invid = b.invid);
spool off
set linesize 200
set pagesize 35

The csv file name in the above example is "filename.csv". You can changes that to any filename you wish, including full-path qualification.

Let us know if this does what you wanted.

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

 

[pnad]

Works like a charm !

 

[pnad]

Just a quick question, though - the SQL query shows up at the top of the csv file. How do I turn that off?

 

[pnad]

Also, the padding with zeroes for the amount field is gone when I open the CSV file using MS Excel.

 

[LKBrwnDBA]

Are you sure the FIRST row is not missing?

SQL> select
  2   EMPLOYEE_ID
  3  ,FIRST_NAME
  4  ,LAST_NAME
  5  ,JOB_ID
  6  ,SALARY
  7  from employees
  8  where department_id = '30'
  9  /

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 JOB_ID         SALARY
----------- -------------------- ------------------------- ---------- ----------
        114 Den                  Raphaely                  PU_MAN          11000
        115 Alexander            Khoo                      PU_CLERK         3100
        116 Shelli               Baida                     PU_CLERK         2900
        117 Sigal                Tobias                    PU_CLERK         2800
        118 Guy                  Himuro                    PU_CLERK         2600
        119 Karen                Colmenares                PU_CLERK      2599.95

6 rows selected.

SQL> select case rownum when 1 then
  2  '#EMPLOYEE_ID ,FIRST_NAME ,LAST_NAME ,JOB_ID ,SALARY '
  3  else
  4   EMPLOYEE_ID||','||FIRST_NAME||','||LAST_NAME||','||JOB_ID||','||SALARY
  5  end
  6  from (
  7  select
  8   EMPLOYEE_ID
  9  ,FIRST_NAME
 10  ,LAST_NAME
 11  ,JOB_ID
 12  ,SALARY
 13  from employees
 14* where department_id = '30')
 15 /

CASEROWNUMWHEN1THEN'#EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,SALARY'ELSEEMPLOYEE
--------------------------------------------------------------------------------
#EMPLOYEE_ID ,FIRST_NAME ,LAST_NAME ,JOB_ID ,SALARY
115,Alexander,Khoo,PU_CLERK,3100
116,Shelli,Baida,PU_CLERK,2900
117,Sigal,Tobias,PU_CLERK,2800
118,Guy,Himuro,PU_CLERK,2600
119,Karen,Colmenares,PU_CLERK,2599.95

6 rows selected.

Notice only 5 rows were selected plus one row for the header.

You may need something more like:

set blah blah blah
col sq noprint
Select 0 sq, '#header ...blah'
Union
Select rownum SQ, col1||','||col2||...blah
  From ..blah..
 Order by 1;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

PNAD,

Sorry, I forgot to mention the "SET ECHO OFF" statement that supresses the display of script statements (when executed from a script; not from copy-and-paste).

How are you doing with all of this? Is it behaving as you wish now (with the addition of the "SET ECHO OFF")?

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

 

[Beilstwh]

an easier way to do it would be

set blah blah blah
Select '#header ...blah'
Union all
Select *
from
(select col1||','||col2||...blah
From ..blah..
Order by 1);

Bill
Oracle DBA/Developer
New York State, USA