HAVING clause

[HezMac]

Hi

I'm having some issues running a procedure in PL/SQL from VB6.0. I've added in a HAVING clause to help with performace, and in PL/SQL it works fine, but when I call it from VB, I get an ORA-06512 error "Not a GROUP BY expression".

Here's the procedure:

SELECT COUNT (*)
  INTO out_TestCount
  FROM (SELECT MAX (FolderRSN) AS FolderRSN, TRUNC(MAX(PaymentDate)) AS LastPmtDate
          FROM (SELECT F.FolderRSN, AP.PaymentDate
                  FROM Folder F, AccountPayment AP, ValidStatus VS, ValidSub VSub
                 WHERE F.FolderType = in_FolderType
                   AND F.SubCode = VSub.SubCode
                   AND F.SubCode = DECODE(in_SubCode, -1, VSub.SubCode, in_SubCode)
                   AND F.StatusCode = VS.StatusCode
                   AND F.StatusCode = DECODE(in_Status, -1, VS.StatusCode, in_Status)
                   AND F.IssueUser = in_UserID
                   AND F.Indate > in_InDateAft
                   AND F.InDate < in_InDateBef
                   AND F.FolderRSN = AP.FolderRSN(+)
                   AND AP.VoidFlag (+) != 'Y'
                   AND AP.NSFFlag (+) != 'Y'
                   AND NOT EXISTS 
                       (SELECT 'x' FROM Batch_Reassign BR
                         WHERE F.FolderRSN = BR.FolderRSN
                           AND BR.ReassignFlag = 'N'
                           AND BR.ProcessedFlag = 'N'))
         WHERE NVL(TRUNC(PaymentDate), '01-JAN-3000') >= in_LastPay
         GROUP BY FolderRSN)
        HAVING F_CALC_OUTSTANDING_BALANCE(FolderRSN) > in_BalGreat 
           AND F_CALC_OUTSTANDING_BALANCE(FolderRSN) > in_BalLess;

Any suggestions would be much appreciated, I haven't used HAVING that often.

 

[PHV]

Replace this:[tt]
GROUP BY FolderRSN)
HAVING F_CALC_OUTSTANDING_BALANCE(FolderRSN) > in_BalGreat
AND F_CALC_OUTSTANDING_BALANCE(FolderRSN) > in_BalLess[/tt]
By this:[tt]
WHERE F_CALC_OUTSTANDING_BALANCE(FolderRSN) > in_BalGreat
AND F_CALC_OUTSTANDING_BALANCE(FolderRSN) > in_BalLess
GROUP BY FolderRSN)[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886