We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

sql query output should save in excel format

Medium Priority
2,019 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

Comment
Watch Question

Richard OlutolaConsultant

Commented:
Yes you can mail the output using shell script or using Oracle UTL_SMTP.

Are you running unix/Linux or Windows?

R.
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
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.
Consultant
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
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 ?????????????
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

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

Author

Commented:
sorry ...You never tried solve my problem
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
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.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

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