date format and nesting

From: Louis Dumont <dumontls_at_dumontls.com>
Date: Sat, 13 Apr 2002 06:59:12 GMT
Message-ID: <4HQt8.2442$o66.5607@news-server.bigpond.net.au>

I have the following statements and am finding difficulty doing 2 things

formatting the date to 22-March-2002 and producing only the highest hours worked for each day, any ideas here please

SELECT TO_CHAR((N.PERS_NAME) || ' a ' || (P.POSN_NAME) || ' worked ' || A.ACTIV_HOURS || ' hours in ' || (U.UNIT_NAME) || ' on ' || (A.ACTIV_DATE)) FROM POSITION P,PERSONNEL N, ACTIVITY A, UNIT U WHERE P.POSN_ID=N.POSN_ID AND N.PERS_ID=A.PERS_ID
AND A.UNIT_ID=U.UNIT_ID
ORDER BY A.ACTIV_DATE Which gives me the following result

TO_CHAR((N.PERS_NAME)||'A'||(P.POSN_NAME)||'WORKED'||A.ACTIV_HOURS||'HOURSIN '||(

---

---

LEAR a LECTUER A worked 5 hours in I.S. 1 on 22-MAR-02 EVERETT a TUTOR worked 5 hours in I.S. 1 on 22-MAR-02 MENG a LECTUER B worked 4 hours in LAW 1 on 22-MAR-02 FOWLER a LECTUER A worked 5 hours in LAW 1 on 22-MAR-02 KENNETT a LECTUER A worked 4 hours in LAW 1 on 22-MAR-02 RICHARDSON a LECTUER B worked 4 hours in MANAGEMENT 1 on 22-MAR-02 HEUNG a LECTUER A worked 6 hours in MANAGEMENT 1 on 22-MAR-02 SMITH a LECTUER A worked 3 hours in ACCOUNTING 1 on 22-MAR-02 DAVILLIA a TUTOR worked 5 hours in ACCOUNTING 1 on 22-MAR-02

However I need to only output the person who wrks the most hrs for each day, and have tried putting Max(a.activ_hours) in the select statement, it doesn' t work.

Any help here

Also for the date I have to output in 22_march 2002 format and tried using Fm(a.activ_date, 'DD-Month-YYYY') alos tried fmFormat(...) and To_Date(...) it comes up with an error.

Ideas pls Received on Sat Apr 13 2002 - 01:59:12 CDT