• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 764
  • Last Modified:

Oracle Database export

I have the following sql statement set up to export data from my Oracle database Iwould like to change the file output type to CSV and the date range to 7days from todays date back...
I have been experementing with but keep getting errors in the date feild...
set pages 0
set head off
set feed off
set echo off
set term off
set lines 10000
column seq noprint
spool transactions.csv
select p.cmvt_cd||CHR(9)||p.s_dt||CHR(9)||sort_by.cust_name(p.cust_id)||CHR(9)||pd.disp_name||CHR(9)||pd.x_ext_price||CHR(9)||pd.net||CHR(9)||addr,city||CHR(9)||
state||CHR(9)||zip||CHR(9)||licno||CHR(9)||wt||CHR(9)||dob||CHR(9)||gender from cust_scanned_images csi, p,pd
where p.cmvt_id=pd.cmvt_id and csi.cust_id=p.cust_id and trunc(s_dt) = trunc(sysdate)  and p.cmst_cd = 'PD' order by p.cmvt_cd 
/
  -- = trunc(sysdate)
spool off
set pages 100
exit

Open in new window

0
Vincent Costanza
Asked:
Vincent Costanza
1 Solution
 
ravindran_eeeCommented:
What is that "sort_by"? I think if you remove that everything would run properly. If you want to sort by that field, you should add it to ORDER BY clause.
0
 
Vincent CostanzaOwnerAuthor Commented:
That part seems to be working ok... it is sorting by a ticket # which is a integer...
0
 
DavidSenior Oracle Database AdministratorCommented:
Confirm that s_dt is datetype DATE, and specifically which Oracle errors?
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
Vincent CostanzaOwnerAuthor Commented:
the main thing is to change the file type to csv... no matter what I do it seems to give me tabbed output.
I also dont seem to be able to get the date range statment correct.. Loooking for something like   between (sysdate - 7 thru sysdate)
0
 
Vincent CostanzaOwnerAuthor Commented:
yes s_dt is datatype... not sure of the exact error, it had to do with the date format I will run it again and get the error...
0
 
Vincent CostanzaOwnerAuthor Commented:
yes that should have been s_dt is Date datatype..
0
 
Mark GeerlingsDatabase AdministratorCommented:
"sort_by" looks to me like a table name, and if that is true in your system, then that is no problem.

To create a CSV file, you need to change "CHR9" to ',' (a single quote, followed by a comma, and another single quote) like this:

select p.cmvt_cd||','||p.s_dt||','||sort_by.cust_name(p.cust_id)||','||pd.disp_name||','||pd.x_ext_price||','||pd.net||','||addr,city||','||
state||','||zip||','||licno||','||wt||','||dob||','||gender from cust_scanned_images csi, p,pd
where p.cmvt_id=pd.cmvt_id and csi.cust_id=p.cust_id and trunc(s_dt) = trunc(sysdate)  and p.cmst_cd = 'PD' order by p.cmvt_cd

But, if any of your text fields happen to have a comma character in the field, that will cause a problem unless you also include double-quotes around the field(s) that may have this.

You may want to change the direct select of the date field (dob) to use "to_char(dob,'MM/DD/YYYY')" (or whatever date format you want instead of: 'MM/DD/YYYY').

This filter:
trunc(s_dt) = trunc(sysdate)
should work without an error *IF* the column: "s_dt" is a "date" column.  Using "trunc" (or any other operator, including: min, max, upper, to_char, etc.) though on a database column in the "where" clause can add a significant performance penalty.  Depending on the number of records in the table, and the speed of your server, this may or may not be a problem for you.

To get records from the most recent 7 days only, try something like this in the "where" clause:

and s_dt > trunc(sysdate) -7
0
 
Vincent CostanzaOwnerAuthor Commented:
Mark,
thanks the csv part is working great!
Still having trouble with the date filter... I am only getting records dated today instead of the last seven days......
0
 
Mark GeerlingsDatabase AdministratorCommented:
What is the exact "where" clause you are using?
0
 
Vincent CostanzaOwnerAuthor Commented:

where p.cmvt_id=pd.cmvt_id and csi.cust_id=p.cust_id and trunc(s_dt) = trunc(sysdate)  and p.cmst_cd = 'PD'
order by p.cmvt_cd
/
  -- = trunc(sysdate)-7
0
 
Vincent CostanzaOwnerAuthor Commented:
ok, here is the full routine...
now I am getting just one day seven days ago...
 
Not sure about the syntax but I think I need something like
"between sysdate and sysdate -7" somewhere in there... or the reverse...


set pages 0
set head off
set feed off
set echo off
set term off
set lines 150
column seq noprint
spool transactions.csv
select 
 
p.cmvt_cd||','||p.s_dt||','||sort_by.cust_name(p.cust_id)||','||pd.disp_name||','||pd.x_ext_price||','||pd.
 
net||','||addr||','||city||','||
state||','||zip||','||licno||','||wt||','||dob||','||gender from cust_scanned_images csi, p,pd
where p.cmvt_id=pd.cmvt_id and csi.cust_id=p.cust_id and trunc(s_dt) = trunc(sysdate) -7  and p.cmst_cd = 
 
'PD' order by p.cmvt_cd 
/
  -- = trunc(sysdate)-7
spool off
set pages 100
exit

Open in new window

0
 
Mark GeerlingsDatabase AdministratorCommented:
Correct.  You have:
trunc(s_dt) = trunc(sysdate) -7

That will give you records from 7 days ago only.

You need this:
s_dt >= trunc(sysdate) -7

0
 
Vincent CostanzaOwnerAuthor Commented:
Thank you so much, Perfect!!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now