Re: Oracle hints - still !!!

[Chad Redman <chad.redman@YALE.EDU>]

Mark,

Try this variation (move the DISTINCT to below the hint):

begin-SELECT ON-ERROR=SQL-Error
/*+ ordered */

distinct L.STATISTICS_CODE

SUM(L.POSTED_TOTAL_AMT) &Sum_Posted_Total_Amt

from [$TableNames]
WHERE L.STATISTICS_CODE [$_Stats_Clause]
[etc.]

What I get for the cursor is:

select /*+ ordered */distinct L.STATISTICS_CODE
SUM(L.POSTED_TOTAL_AMT)  from [$TableNames] WHERE L.STATISTICS_CODE
[$_Stats_Clause] [$_gsTreeDtlFldNameWhere] AND L.LEDGER = :1 AND
L.Scenario = :2 AND J.product = L.product AND J.EFFDT = (SELECT
MAX(J2.EFFDT) FROM PS_product_TBL J2 WHERE J2.EFFDT <= :3 AND
J.product = J2.product) [$_Group_Where] [$_Type_Where] [$Timespan]
AND [$_Sort_Type_Fieldname]     =  :4 AND [$_Sort_Group_Fieldname]
  = :5 [$Chartfield_And] [$_Prcs_Business_Unit_Where]
[$_Rqst_Cp_Job_Where] [$_Rqst_Cp_Project_Id_Where]
[$_Rqst_Cp_Function_Cd_Where] [$_Rqst_Cp_Job_Exp_Cd_Where] GROUP BY
L.STATISTICS_CODE


I tried this for a simpler statement (select sysdate from dual) and it
complained about "SQL expression not ended, perhaps missing &name". I added
&sysdate after sysdate, and it worked fine.


Chad
------------------------------------------------------------------
Chad Redman                                  chad.redman@yale.edu
ITS/Admin. Sys., Yale University                   (203) 432-8853

On Tue, 4 Apr 2000 20:32:23 +1000, Pietersz Mark x8938
<mpieters@CITIPOWER.COM.AU> wrote:

[...]
>begin-SELECT DISTINCT ON-ERROR=SQL-Error
>/*+ ordered */
>
>L.STATISTICS_CODE
>
>SUM(L.POSTED_TOTAL_AMT) &Sum_Posted_Total_Amt
>
>from [$TableNames]
>WHERE L.STATISTICS_CODE [$_Stats_Clause]
>[$_gsTreeDtlFldNameWhere] AND
>L.LEDGER = $Ledger AND
>L.Scenario = $Scenario
>AND J.product = L.product
>AND J.EFFDT = (SELECT MAX(J2.EFFDT) FROM PS_product_TBL J2
>WHERE J2.EFFDT <= $_Rqst_AsOfDate AND J.product = J2.product)
>[$_Group_Where]
>[$_Type_Where]
>[$Timespan]
>AND [$_Sort_Type_Fieldname]     =  $_Sort_Type_Field
>AND [$_Sort_Group_Fieldname]    = $_Sort_Group_Field
>[$Chartfield_And]
>[$_Prcs_Business_Unit_Where]
>[$_Rqst_Cp_Job_Where]
>[$_Rqst_Cp_Project_Id_Where]
>[$_Rqst_Cp_Function_Cd_Where]
>[$_Rqst_Cp_Job_Exp_Cd_Where]
>GROUP BY L.STATISTICS_CODE
>END-SELECT
>
>And this is the statement from the SQR.LOG file
>
> Cursor #22:
>   SQL = select distinct /*+ ordered */L.STATISTICS_CODE,
>         SUM(L.POSTED_TOTAL_AMT)  from [$lsSQLTemp] WHERE L.STATISTICS_CODE
>         [$_Stats_Clause] [$_gsTreeDtlFldNameWhere] AND L.LEDGER = :1 AND
>         L.Scenario = :2 AND J.product = L.product AND J.EFFDT = (SELECT
>         MAX(J2.EFFDT) FROM PS_product_TBL J2 WHERE J2.EFFDT <= :3 AND
>         J.product = J2.product) [$_Group_Where] [$_Type_Where] [$Timespan]
>         AND [$_Sort_Type_Fieldname]     =  :4 AND [$_Sort_Group_Fieldname]
>           = :5 [$Chartfield_And] [$_Prcs_Business_Unit_Where]
>         [$_Rqst_Cp_Job_Where] [$_Rqst_Cp_Project_Id_Where]
>         [$_Rqst_Cp_Function_Cd_Where] [$_Rqst_Cp_Job_Exp_Cd_Where] GROUP BY
>         L.STATISTICS_CODE
> Compiles = 3
> Executes = 3
> Rows     = 3
>
>Why does my hint appear to no work.  Help !!
>
>Regards
>
>Mark Pietersz