Path: g2news1.google.com!postnews.google.com!u30g2000hsc.googlegroups.com!not-for-mail
From:  hgha <hou...@globe-tekcorp.com>
Newsgroups: comp.databases.oracle.server
Subject: Slow Query
Date: Fri, 01 Jun 2007 14:26:19 -0700
Organization: http://groups.google.com
Lines: 24
Message-ID: <1180733179.125540.201770@u30g2000hsc.googlegroups.com>
NNTP-Posting-Host: 209.250.135.154
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1180733179 32460 127.0.0.1 (1 Jun 2007 21:26:19 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 1 Jun 2007 21:26:19 +0000 (UTC)
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.2; .NET CLR 1.1.4322; InfoPath.1; .NET CLR 2.0.50727),gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: u30g2000hsc.googlegroups.com; posting-host=209.250.135.154;
   posting-account=E6hU8g0AAABZ5MypQXCrTdqFndDcy9vy

Hello,

The following query is performing poorly in the app. I have tried the
scalar subquery approach (using formatted to_char)  with no luck. any
help would be appreciated.

SELECT  MP_NO, MP_DESC, MP_ACTIVE AS "FLAG_YES_NO",
(
  ( SELECT COUNT(*)	FROM ST, STINV
		WHERE ST.ST_ID = STINV.ST_ID
		AND ST.ST_POSTED IS NOT NULL
		AND STINV.MP_NO = MPT.MP_NO )
   +
  ( SELECT COUNT(*) FROM PT, PTINV
	 	  WHERE PT.PT_ID = PTINV.PT_ID
		  AND PT.PT_POSTED IS NOT NULL
		  AND PTINV.MP_NO = MPT.MP_NO  )
)
FROM MPT
WHERE COMP_ID = 1
GROUP BY MP_NO, MP_DESC, MP_ACTIVE

Thanks,