We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Oracle Database export

Medium Priority
777 Views
Last Modified: 2012-05-06
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

Comment
Watch Question

CERTIFIED EXPERT

Commented:
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.

Author

Commented:
That part seems to be working ok... it is sorting by a ticket # which is a integer...
David VanZandtSr Software Engineer
CERTIFIED EXPERT

Commented:
Confirm that s_dt is datetype DATE, and specifically which Oracle errors?

Author

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)

Author

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...

Author

Commented:
yes that should have been s_dt is Date datatype..
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
"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

Author

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......
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
What is the exact "where" clause you are using?

Author

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

Author

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

Database Administrator
CERTIFIED EXPERT
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Thank you so much, Perfect!!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.