date format and nesting
Date: Sat, 13 Apr 2002 06:58:53 GMT
Message-ID: <NGQt8.2441$o66.5928_at_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 - 08:58:53 CEST