Help: HAVING clause and a hint

From: Gerard M. Averill <e-mail.address_at_my.sig>
Date: Mon, 01 Feb 99 23:57:11 GMT
Message-ID: <795er8$jd0$1@news.doit.wisc.edu>

I'm having problems specifying a HAVING clause when using a hint in a select statement. The SELECT is:

select /*+

        index(REPORT_REQUEST IN$REPORT_REQUESTER)
        */
        SERVICE_ID


,       REQUESTER_TYPE


,       REQUESTER_LOGIN


,       count(*) - decode(REQUESTER_TYPE, 0, 200, 1000)

from REPORT_REQUEST
where SERVICE_ID = 1396
and REQUEST_STATUS in (2, 3, 4)
group by

        SERVICE_ID


,       REQUESTER_TYPE


,       REQUESTER_LOGIN


having
        count(*) - decode(REQUESTER_TYPE, 0, 200, 1000) > 0

(Basically I'm looking for report requesters who have exceeded a maximum number of report requests (based upon their type)). When I don't use a hint the statement works fine. When I add the hint, the statement returns rows, but the REQUESTER_TYPE (numeric) and REQUESTER_LOGIN (varchar2) columns have junk characters in them. (The IN$REPORT_REQUESTER index is composed of the columns SERVICE_ID, REQUESTER_TYPE, REQUESTER_LOGIN, and REQUEST_ID)

I've drop and recreated the index to no avail.

Any ideas?
TIA,
Gerard

---

Gerard M. Averill, Researcher
CHSRA, University of Wisconsin - Madison GAverill<at>chsra<dot>wisc<dot>edu Received on Mon Feb 01 1999 - 17:57:11 CST