Solved

sql query output should save in excel format

Posted on 2009-07-04
10
1,934 Views
Last Modified: 2013-11-17
hI...the query below down should daily run automatically and save the output in excel format. Can we mail the output to particular id daily...can we make automate..??????///
thanks lot.
SELECT distinct cci.callid	"TicketNo",

                    to_char(cci.ondate,'dd/mm/yyyy hh:mi AM') "Logged Date",

										mvc.CATEGORYNAME "Problem Category",

										mvc.MODULEname "Problem Type",

										mvc.EVENTname "Problem Item",

										cci.STATUSNAME "Status",

										mvc.SEVERITYNAME "Severity",

										mvc.Problemsummaryname "Problem Summary",

										mvc.LOGGEDFROMCCNAME "Location",

										cci.USERNAME "Ticket Initiator",

										cci.SPUSERNAME "Solution Provider",

										substr(c.errormessage,1,25) "Problem Description",

										dense_rank() over(order by cci.CALLID desc) as id,

										 to_char(cci.SOLVEDDATE,'dd/mm/yyyy hh:mi AM') "SolvedDate",

										to_char(ST.ClosedDate,'dd/mm/yyyy hh:mi AM') "ClosedDate", 

										((cci.TOTALEFFORTHOURS + cci.EFFORTHOURS) + trunc((cci.TOTALEFFORTMINUTES + cci.EFFORTMINUTES)/60))

		                || ' Hr.s ' || mod((cci.TOTALEFFORTMINUTES + cci.EFFORTMINUTES),60) || ' min.s' "Effort"   

										FROM

										tbl_call_currentInfo cci,

										mv_call mvc,call c, tbl_solutiontrace ST 

										where cci.callid=mvc.ticketno and cci.callid=c.callid

										AND ST.CallId = cci.CallId  

										AND ST.TraceId = c.TraceId  

										AND mvc.categoryid = 5

										AND cci.statusid in (2,4)

										AND ((cci.SOLVEDDATE >= trunc(to_date('26/06/2009', 'dd/mm/yyyy hh:mi:ss AM'))

										and cci.SOLVEDDATE <= to_date('01/07/2009','dd/mm/yyyy hh:mi:ss AM'))

                                                                                or (cci.CLOSEDDATE >= trunc(to_date('26/06/2009', 'dd/mm/yyyy hh:mi:ss AM'))

										and cci.CLOSEDDATE <= to_date('01/07/2009','dd/mm/yyyy hh:mi:ss AM' )))

										ORDER BY cci.CALLID DESC;

Open in new window

0
Comment
Question by:shenid
  • 3
  • 2
  • 2
10 Comments
 
LVL 16

Expert Comment

by:Richard Olutola
ID: 24822779
Yes you can mail the output using shell script or using Oracle UTL_SMTP.

Are you running unix/Linux or Windows?

R.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 24824264
First, I see some problems in your query.  I would be *VERY* surprised if it executes without error.  The main problems I see are in these four "where" clauses that include "to_date":
  AND ((cci.SOLVEDDATE >= trunc(to_date('26/06/2009', 'dd/mm/yyyy hh:mi:ss AM'))
  and cci.SOLVEDDATE <= to_date('01/07/2009','dd/mm/yyyy hh:mi:ss AM'))
  or (cci.CLOSEDDATE >= trunc(to_date('26/06/2009', 'dd/mm/yyyy hh:mi:ss AM'))
  and cci.CLOSEDDATE <= to_date('01/07/2009','dd/mm/yyyy hh:mi:ss AM' ))

You never need to combine "trunc" plus "to_date" when you supply a literal value like: "26/06/2009" that does *NOT* include a time component!   That value is already truncated!  Then, your format mask: 'dd/mm/yyyy hh:mi:ss AM' does not match the literal value '26/06/2009'.  For that value, you need a shorter format mask of just: 'dd/mm/yyyy'.

Next, I do not like to see the "distinct" keyword in an Oracle query.  That can cause lots of overhead to do a sort, if the sort is not needed.  And, if you ever include date columns that include a non-midnight time-of-day component, Oracle's interpretation of "distinct" may be different from what most users expect.

Finally, SQL queries by default return fixed-length ASCII values.  If you run SQL queries in SQL*Plus or TOAD is it easy to "spool" this output to plain text files that tools like Excel can then open.

Which tool or program do you plan to use to run the SQL statement?  Maybe that tool offers the ability to convert the output to Excel format, but SQL by itself does not.  Also, SQL does not do e-mail.  But whatever tool you plan to use to run the SQL statement may be able to do send an e-mail and include the output as an attached file.
0
 
LVL 16

Accepted Solution

by:
Richard Olutola earned 125 total points
ID: 24824353
Yes Excel will read a simple CSV format, so no need to save in 'excel format' which only MS knows about anyway.

You can include your SQL in a shell script and include an email procedure which sends your output as an attachment.

The alternative is to write a procedure which will send your output using the Oracle package UTL_MAIL

R.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:shenid
ID: 24825064
thanks....that query runs properly in sqlscrtch pad....I dont have any tool otherthan sqlplus and sqlsratchpad to run query...can we write a script in pl/sql to run query and save it in folder ?????????????
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 24825789
PL\SQL basically just gives us procedural extensions (like: loop..., if..., while..., etc.) to the non-procedural SQL language.  But it is not a full-featured programming language.  Oracle does offer a supplied PL\SQL package named UTL_SMTP (that was already mentioned) that can be used to send e-mail from PL\SQL.  But, this is not particularly easy to use.

Usually a reporting tool like: Oracle Reports, Crystal Reports, Oracle BI Publisher, etc. is used to convert the output from SQL queries to the desired file format.  These reporting tools can usually also send e-mail.
0
 

Author Comment

by:shenid
ID: 24923481
sorry ...You never tried solve my problem
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 24924549
SQL is not designed or intended to save query output in Excel format.  So, if that is your "problem" you need to change your expectations to match reality.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Objective of This Article In 1990’s, when I was a budding software professional, I had a lot of confusion about which stream or technology, I had to choose to build my career. In those days, I had lot of confusion like whether to choose System so…
I showed you how to use console view (HERE (http://www.experts-exchange.com/articles/18379/Getting-Started-and-Using-the-Salesforce-com-Console.html)) -– but how do you set it up on the admin side of Salesforce? Note that you have to have Admin leve…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
The purpose of this video is to demonstrate how to Test the speed of a WordPress Website. Site Speed is an important metric of a site’s health. Slow site speed can result in viewers leaving your site quickly and not seeing your content. This…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now