"be consistent" | |
PerlMonks |
Re: SQL in perl/tkby chacham (Prior) |
on Jan 13, 2012 at 11:59 UTC ( [id://947733]=note: print w/replies, xml ) | Need Help?? |
The query should be executed by Oracle, not by the driver, hence the complexity should not matter. As marto said, turn on error reporting.
BTW, you can rewrite "AND (gamm.PROCESS_NAME = 'SAM_Exact_Product_Search' OR gamm.APPLICATION_NAME = 'SAM_Exact_Product_Search')" as "AND 'SAM_Exact_Product_Search' IN (gamm.PROCESS_NAME, gamm.APPLICATION_NAME)" . Get's rid of the OR and only requires the literal once.trunc(gmz.testtime) will obviate the use of any INDEX on gmz.testtime, better to use AND gmz.testtime >= to_date('09/12/2011','MM/DD/YYYY') AND gmz.testtime < to_date('09/16/2011','MM/DD/YYYY') IIUC, i don't think you need to specify TO_CHAR in the GROUP BY clause. For formatting the query, so people can read it, try: SELECT gamm.PROCESS_NAME, GAMM.APPLICATION_NAME, TO_CHAR(TRUNC(gmz.testtime - 7 / 24, 'DD'),'MM/DD/YYYY') Fiscal_Day, ROUND ( AVG ( DECODE(total_response_time, 0, NULL, total_response_time) / DECODE (total_page_count,0, 1,total_page_count) ) / 1000, 2 ) Average_Response_time FROM portal_gomez_bb_load gmz, GOMEZ_APP_MAPPING_MASTER gamm WHERE gmz.monitor_id = 3805260 AND 'SAM_Exact_Product_Search' IN (gamm.PROCESS_NAME, gamm.APPLICATION_NAME) AND gmz.testtime >= to_date('09/12/2011','MM/DD/YYYY') AND gmz.testtime < to_date('09/16/2011','MM/DD/YYYY') AND success_flag = 0 GROUP BY gamm.PROCESS_NAME, GAMM.APPLICATION_NAME, TRUNC (gmz.testtime - 7 / 24, 'DD');
In Section
Seekers of Perl Wisdom
|
|