Re: Analytic Functions in Stored Procedure 8.1.7 - do they work?

Charlie Brown wrote:
> I've got a SQL script with MIN and MAX analytic functions that works
> just fine all by itself but as soon as I try to compile a procedure
> using that same script it fails after the OVER saying it's looking for
> either ',' or 'from'. I'm by no means a guru but have compiled other
> procedures without analytic functions without any problem. Will 8.1.7
> accept an analytic function within a procedure?
>
> Any advice or information....???? Thanks
>
> The procedure follows:
>
> CREATE OR REPLACE PROCEDURE pr_build_pcli IS
>
> BEGIN
>
> pr_log('INFO','Start pr_build_pcli');
>
> FOR cursor_repos IN
> (SELECT POLI_STAT.BUYER_EXCHANGE_ID
> ,POLI_STAT.PURCHASE_ORDER_NUM
> ,POLI_STAT.RELEASE_NUM
> ,POLI_STAT.GHX_ORDER_NUM
> ,POLI_STAT.PURCHASE_ORDER_LINE_NUM
> ,POLI_STAT.SUPPLIER_PART_NUM
> ,POLI_STAT.SUBMISSION_INSERT_DATE
> ,0 COMP_INDICATOR
> ,1 CONSIS_INDICATOR
>
> ,DECODE(POLI_SUB.EXPECTED_PRICE,0,POLI_STAT.UNIT_PRICE,POLI_SUB.EXPECTED_PRICE)
> CONS_EXPECTED_PRICE
> ,0 MIN_EXPECTED_PRICE
> ,0 MAX_EXPECTED_PRICE
> ,MIN(POLI_STAT.UNIT_PRICE) OVER (PARTITION BY
> POLI_STAT.BUYER_EXCHANGE_ID,
> POLI_STAT.SUPPLIER_EXCHANGE_ID,
> POLI_STAT.SUPPLIER_PART_NUM,
> POLI_STAT.UNIT_OF_MEASURE) MIN_UNIT_PRICE
> ,MAX(POLI_STAT.UNIT_PRICE) OVER (PARTITION BY
> POLI_STAT.BUYER_EXCHANGE_ID,
> POLI_STAT.SUPPLIER_EXCHANGE_ID,
> POLI_STAT.SUPPLIER_PART_NUM,
> POLI_STAT.UNIT_OF_MEASURE) MAX_UNIT_PRICE
> FROM
> POLI_SUB,
> POLI_STAT,
> DIM_LINE_ITEM_STATUS
> WHERE
> POLI_SUB.GHX_ORDER_NUM=POLI_STAT.GHX_ORDER_NUM
> AND POLI_SUB.BUYER_EXCHANGE_ID=POLI_STAT.BUYER_EXCHANGE_ID AND
> POLI_SUB.PURCHASE_ORDER_NUM=POLI_STAT.PURCHASE_ORDER_NUM AND
> POLI_SUB.RELEASE_NUM = POLI_STAT.RELEASE_NUM AND
> POLI_SUB.PURCHASE_ORDER_LINE_NUM=POLI_STAT.PURCHASE_ORDER_LINE_NUM
> AND
> POLI_SUB.UNIT_OF_MEASURE=POLI_STAT.UNIT_OF_MEASURE
> AND
> POLI_STAT.LINE_ITEM_STATUS=DIM_LINE_ITEM_STATUS.LINE_ITEM_STATUS AND
> DIM_LINE_ITEM_STATUS.LINE_ITEM_PARENT IN
> ('ACCEPTED','SHIPPED','SUBSTITUTE','SUCCESSFUL')
> AND POLI_STAT.UNIT_PRICE IS NOT NULL AND
> POLI_STAT.UNIT_PRICE != 0
> AND POLI_SUB.SUBMISSION_INSERT_DATE BETWEEN
> DECODE(TO_CHAR(TRUNC(SYSDATE),'DD'),'01',TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE)+1,-2)),TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE)+1,-1)))
> AND
> DECODE(TO_CHAR(TRUNC(SYSDATE),'DD'),'01',TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE),-1))+.99999,TRUNC(LAST_DAY(SYSDATE))+.99999)
> AND POLI_STAT.SUBMISSION_INSERT_DATE BETWEEN
> DECODE(TO_CHAR(TRUNC(SYSDATE),'DD'),'01',TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE)+1,-2)),TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE)+1,-1)))
> AND
> DECODE(TO_CHAR(TRUNC(SYSDATE),'DD'),'01',TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE),-1))+.99999,TRUNC(LAST_DAY(SYSDATE))+.99999)
> )
>
> LOOP
>
> INSERT INTO PRICE_COMPLIANCE_LINE_ITEM
> (BUYER_EXCHANGE_ID
> ,PURCHASE_ORDER_NUM
> ,RELEASE_NUM
> ,GHX_ORDER_NUM
> ,PURCHASE_ORDER_LINE_NUM
> ,SUBMISSION_INSERT_DATE
> ,COMP_INDICATOR
> ,CONSIS_INDICATOR
> ,COMPL_EXPECTED_PRICE
> ,MIN_EXPECTED_PRICE
> ,MAX_EXPECTED_PRICE
> ,MIN_UNIT_PRICE
> ,MAX_UNIT_PRICE)
> VALUES (cursor_repos.BUYER_EXCHANGE_ID
> ,cursor_repos.PURCHASE_ORDER_NUM
> ,cursor_repos.RELEASE_NUM
> ,cursor_repos.GHX_ORDER_NUM
> ,cursor_repos.PURCHASE_ORDER_LINE_NUM
> ,cursor_repos.SUBMISSION_INSERT_DATE
> ,cursor_repos.COMP_INDICATOR
> ,cursor_repos.CONSIS_INDICATOR
> ,cursor_repos.CONS_EXPECTED_PRICE
> ,cursor_repos.MIN_EXPECTED_PRICE
> ,cursor_repos.MAX_EXPECTED_PRICE
> ,cursor_repos.MIN_UNIT_PRICE
> ,cursor_repos.MAX_UNIT_PRICE);
>
> COMMIT;
>
> END LOOP
>
> COMMIT;
>
> pr_log('INFO','pr_build_pcli END');
>
>
> EXCEPTION
> WHEN NO_DATA_FOUND THEN NULL;
> pr_log('ERROR','NO DATA FOUND ERROR');
> WHEN OTHERS THEN NULL;
> pr_log('ERROR','Problem loading pcli: SQLCODE:'||SQLCODE||'
> SQLERRM:'||SQLERRM);
>
> END pr_build_pcli;
> /

Using dynamic sql is another solution.

Gerard Received on Mon Jan 19 2004 - 06:42:26 CST