RE: Query tuning help needed

From: Chelur, Jayadas {PBSG} <jayadas.chelur_at_pepsi.com>
Date: Tue, 29 Apr 2003 09:31:39 -0800
Message-ID: <F001.0058C081.20030429093139@fatcity.com>

One more Change ...  

SELECT ORIGIN
,DESTINATION
,SERVICE_CODE
,AVG(SEG_TIME) + INSTR('XYZQ',SERVICE_CODE) AS ADD_ON_VALUE
,SYSDATE

     ,'SYSTEM'
  FROM -----Original Message-----
Sent: Tuesday, April 29, 2003 12:57 PM
To: Multiple recipients of list ORACLE-L

Brad, Ranga

I am sorry but I have to disagree, stored procedure is not an answer to this problem. I prefer to call this I-saw-a-new-feature-and-i-want-to-use-it syndrome.

How about following ...

SELECT ORIGIN
,DESTINATION

          ,SERVICE_CODE 
          ,CASE WHEN SERVICE_CODE = 'X' THEN AVG(SEG_TIME) + 1 
            WHEN SERVICE_CODE = 'Y' THEN AVG(SEG_TIME) + 2 
            WHEN SERVICE_CODE = 'Z' THEN AVG(SEG_TIME) + 3 
            WHEN SERVICE_CODE = 'Q' THEN AVG(SEG_TIME) + 4 END AS
ADD_ON_VALUE 


      ,SYSDATE 

          ,'SYSTEM' 
  FROM 
          (SELECT META_ROUTING_KEY     AS ROUTE_KEY 
             ,ST1.STATION_CODE     AS ORIGIN 
             ,ST2.STATION_CODE     AS DESTINATION 
             ,SERVICE_CODE 
             ,SUM(CASE WHEN t.TYPE IN ('OD','OT','TD','TT') THEN
NVL(TT_TIME_IN_MIN,0) seg_time 
        FROM SEG_MIN_TIMES M, STATION ST3, STATION ST4,PRY_TWO_MR_TRANSIT T,
SERVICE 
       WHERE M.ORIGIN = ST3.STATION_CODE 
         AND M.DESTINATION = ST4.STATION_CODE 
         AND ST3.STN_KEY = T.SEG_ORG_STN_KEY 
         AND ST4.STN_KEY = T.SEG_DEST_STN_KEY 
         AND M.SERVICE = SERVICE_CODE 
         AND SERVICE_CODE IN ('X','Y','Z','Q') 
       GROUP BY META_ROUTING_KEY, ST1.STATION_CODE,
ST2.STATION_CODE,SERVICE_CODE 
          ) 

   GROUP BY ORIGIN, DESTINATION, SERVICE_CODE /

Test and Test and Test ... this should give you an idea though .. Raj

---

---

Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Chelur, Jayadas {PBSG}
  INET: jayadas.chelur_at_pepsi.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Tue Apr 29 2003 - 12:31:39 CDT