Home » SQL & PL/SQL » SQL & PL/SQL » Group function MAX( ) inside a query (Oracle 9i)
Group function MAX( ) inside a query [message #315871] Wed, 23 April 2008 01:34 Go to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
I am stuck in a query as I need the MAX(bpt.visit_Date) to be used inside the query and its saying that "GROUP FUNCTION IS NOT ALLOWED HERE".

SELECT COUNT(doctor_id) 
   FROM(
     SELECT DISTINCT(bpt.doctor_id) doctor_id,
            bpt.anhfsc_id anh_fsc_id,
            MAX(bpt.visit_Date) visitDate,
            ROUND(SYSDATE - TO_DATE(MAX(bpt.visit_Date))) daysNotVisited,
            usr.first_name fsc_first_name, usr.last_name fsc_last_name,
            doc.doctor_name, doc.doctor_mobile doctor_mobile_no,
            doc.doctor_cl_phone doctor_contact_no
      FROM  ts_bpt_leads bpt,
            ts_doctor_master doc,
            adm_users usr
      WHERE bpt.anhfsc_id IN (SELECT b.user_id
                                FROM adm_profile_master a, adm_users b
                               WHERE a.user_Id=b.user_id
                                 AND a.IS_ACTIVE=1
                                 AND B.IS_ACTIVE=1
                                 AND a.role_id = '181'
                               START WITH a.user_Id= 'AMEY_GODE' 
                             CONNECT BY PRIOR a.user_id = a.reporting_to)
                                 AND doc.doctor_id=bpt.doctor_id
                                 AND doc.doctor_flag=1
                                 AND bpt.ANHFSC_ID=usr.USER_ID
                                 AND SYSDATE >= (MAX(bpt.visit_Date) + 30)  --->>>>
                                 AND SYSDATE < DECODE(60, NULL, (sysdate + 1),
                                                     ( MAX(bpt.visit_Date) + 60 ))
                               GROUP BY bpt.doctor_id,bpt.anhfsc_id,
                                     usr.first_name,usr.last_name,doc.doctor_name,
                                     doc.doctor_mobile,doc.DOCTOR_CL_PHONE,bpt.doctor_id
                               ORDER BY bpt.anhfsc_id);


Please suggest me a way to use the MAX() Function inside the query.

Thank in advance,
Mahi
Re: Group function MAX( ) inside a query [message #315874 is a reply to message #315871] Wed, 23 April 2008 01:53 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this link.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10002.htm#i2130020

But I am sure your query needs few more changes as well. But I will let you to find it.

Regards

Raj

[Updated on: Wed, 23 April 2008 01:53]

Report message to a moderator

Re: Group function MAX( ) inside a query [message #315912 is a reply to message #315871] Wed, 23 April 2008 03:41 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
I added the line
having bpt.visit_Date = MAX(bpt.visit_Date)

in the below code.

SELECT COUNT(doctor_id) 
   FROM(
     SELECT DISTINCT(bpt.doctor_id) doctor_id,
            bpt.anhfsc_id anh_fsc_id,
            MAX(bpt.visit_Date) visitDate,
            ROUND(SYSDATE - TO_DATE(MAX(bpt.visit_Date))) daysNotVisited,
            usr.first_name fsc_first_name, usr.last_name fsc_last_name,
            doc.doctor_name, doc.doctor_mobile doctor_mobile_no,
            doc.doctor_cl_phone doctor_contact_no
      FROM  ts_bpt_leads bpt,
            ts_doctor_master doc,
            adm_users usr
      WHERE bpt.anhfsc_id IN (SELECT b.user_id
                                FROM adm_profile_master a, adm_users b
                               WHERE a.user_Id=b.user_id
                                 AND a.IS_ACTIVE=1
                                 AND B.IS_ACTIVE=1
                                 AND a.role_id = '181'
                               START WITH a.user_Id= 'AMEY_GODE' 
                             CONNECT BY PRIOR a.user_id = a.reporting_to)
                                 AND doc.doctor_id=bpt.doctor_id
                                 AND doc.doctor_flag=1
                                 AND bpt.ANHFSC_ID=usr.USER_ID
                                 AND SYSDATE >= (bpt.visit_Date + 30)  --->>>>
                                 AND SYSDATE < DECODE(60, NULL, (sysdate + 1),
                                                     ( bpt.visit_Date + 60 ))
                               GROUP BY bpt.doctor_id,bpt.anhfsc_id,
                                     usr.first_name,usr.last_name,doc.doctor_name,
                                     doc.doctor_mobile,doc.DOCTOR_CL_PHONE,bpt.doctor_id
HAVING bpt.visit_Date = MAX(bpt.visit_Date) -->>>>
                               ORDER BY bpt.anhfsc_id);



Can you please suggest me as what changes I need to make in the query as you said in the above message.

Thanks,
Mahi
Re: Group function MAX( ) inside a query [message #315924 is a reply to message #315912] Wed, 23 April 2008 04:38 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
To be honest I am not able to understand what this line means
Quote:
AND SYSDATE <
DECODE(60, NULL, (sysdate + 1),( bpt.visit_Date + 60 ))

Distinct is an operator and not a function. Distinct will be applied on a resultset and not on a single attribute.

These are a few things which I could see in the first glance.

Regards

Raj

Re: Group function MAX( ) inside a query [message #315928 is a reply to message #315871] Wed, 23 April 2008 04:48 Go to previous message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
In the DECODE I have hardcoded some values for testing only.
DISTINCT is not a function, but it doesn't gives error that way too.
However I have removed the brackets.
 DISTINCT(bpt.doctor_id)  


Thanks,
Mahi

Previous Topic: Reg the Query Optimization in Oracle
Next Topic: % concatenation problem in string
Goto Forum:
  


Current Time: Sat Dec 10 00:59:01 CST 2016

Total time taken to generate the page: 0.11331 seconds