 Raplace Group by clause [message #443922] |
Thu, 18 February 2010 00:41  |
visuorac
Messages: 10
Registered: February 2010
Location: chennai
|
Junior Member |
|
|
Hi, 
I used Count(*) function in my query .So i need to put groupby clause for every selected fields.I want to replace group by clause because performance issue.Is there any other way to replace group by clause and put count(*) function.
For ex:
SELECT
/*+PARALLEL(a,4)*/
a.uabscon_prem_code,
a.uabscon_cust_code,
a.uabscon_cancellation_date,
COUNT(*) NumApps ------------------------Aggrecate function
FROM
uimsmgr.uabscon a,
uimsmgr.uarserq b,
(
SELECT
/*+PARALLEL(b,4)*/
b.utrjapp_srvc_code,
b.utrjapp_styp_code,
b.utrjapp_visit_months,
b.utrjapp_li_ind,
c.utvsrvc_guarantee
FROM
uimsmgr.utrjapp b,
uimsmgr.utvsrvc c
WHERE
c.utvsrvc_bus_sector_id = 1
AND b.utrjapp_srvc_code = c.utvsrvc_code
AND b.utrjapp_visit_months <> 0
AND rownum<100
) japp1
WHERE
b.uarserq_cust_code = a.uabscon_cust_code
AND b.uarserq_prem_code = a.uabscon_prem_code
AND a.uabscon_status_ind = 'C'
AND a.uabscon_cancellation_date BETWEEN
TO_DATE('01-FEB-2009')
AND TO_DATE('31-JAN-2010'||' 23:59:59','DD-MON-YYYY HH24:MI:SS')
AND b.uarserq_srvc_code = japp1.utrjapp_srvc_code
AND b.uarserq_styp_code = japp1.utrjapp_styp_code
AND b.uarserq_serv_num > 0
-- No other active CONTRACT at the PREMISES
AND NOT EXISTS (
SELECT
'X'
FROM
uimsmgr.uabscon f
WHERE
f.uabscon_prem_code = a.uabscon_prem_code
AND f.uabscon_status_ind != 'P'
AND f.uabscon_status_ind != 'C'
AND rownum<100
)
-- Most recent quote
AND b.uarserq_quote_number = (
SELECT MAX(t.uabletq_quote_number)
FROM
uimsmgr.uabletq t
WHERE
t.uabletq_cust_code = a.uabscon_cust_code
AND t.uabletq_prem_code = a.uabscon_prem_code
AND rownum<100
)
AND rownum<100
GROUP BY
a.uabscon_prem_code,---------------------group by clause.
a.uabscon_cust_code,
a.uabscon_cancellation_date
|
|
|
|
|
|
|
|
|
|
| Re: Raplace Group by clause [message #443985 is a reply to message #443983] |
Thu, 18 February 2010 04:03  |
 |
Michel Cadot
Messages: 68650
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Just to make him understand that a smiley is not just a picture it has a meaning and putting it in inappropriate place is like my answer: (put here the adjective you want).
It seems there is a new fashion here to put smileys everywhere above all when they are irrelevant (I precise this is not the case of yours in your signature).
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
