Home » SQL & PL/SQL » SQL & PL/SQL » RANGE type clause in analytic function
RANGE type clause in analytic function [message #245536] Mon, 18 June 2007 01:05 Go to next message
manoj_vilayil
Messages: 9
Registered: May 2007
Location: India
Junior Member
Sir
I have a query based on analytical function range type ,works correctly. but it fails in cursor.why?


the query is as follows




SELECT DT_BATCHDATE,NXT,VC_REACTNO,NXTR,NN_FGTNNO,NN_BTCHQTY,VC_PRODBATCHNO,NN_QTY,
SUM(NVL(NN_BTCHQTY,0)) OVER(PARTITION BY DT_BATCHDATE,VC_REACTNO ORDER BY DT_BATCHDATE,VC_REACTNO)-SUM(NN_QTY) OVER(PARTITION BY DT_BATCHDATE,VC_REACTNO ORDER BY DT_BATCHDATE,VC_REACTNO,NN_FGTNNO ROWS BETWEEN ROW_NUM PRECEDING AND CURRENT ROW) REAC_BALANCE
FROM
(
WITH BATCH_NO AS
(
SELECT DISTINCT NN_BATCHNO
FROM(
SELECT SF.NN_BATCHNO,DECODE(&Selmode2,'A','Y','N') CHOSE
FROM STR_FGTN SF
WHERE SF.NN_BRCODE=&brcode
AND SF.NN_ITCODE=&itcode
UNION
SELECT RBM.NN_BATCHNO,DECODE(&Selmode2,'A','N','Y') CHOSE
FROM RPT_BATCH_MAST RBM
WHERE RBM.NN_SEQNO= &seqno
) WHERE CHOSE='Y'
)
SELECT Q1.DT_BATCHDATE,LAG(Q1.DT_BATCHDATE,1,TO_DATE('01/01/1800','DD/MM/YYYY')) OVER(ORDER BY Q1.DT_BATCHDATE) NXT,Q1.VC_REACTNO,LAG(Q1.VC_REACTNO,1,'0') OVER(ORDER BY Q1.DT_BATCHDATE) NXTR,
DECODE(Q1.NN_FGTNNO,LAG(Q1.NN_FGTNNO,1,'0') OVER(PARTITION BY Q1.DT_BATCHDATE,Q1.VC_REACTNO ORDER BY Q1.DT_BATCHDATE,Q1.VC_REACTNO,Q1.NN_FGTNNO),'',Q1.NN_FGTNNO) NN_FGTNNO,
DECODE(Q1.NN_FGTNNO,LAG(Q1.NN_FGTNNO,1,'0') OVER(PARTITION BY Q1.DT_BATCHDATE,Q1.VC_REACTNO ORDER BY Q1.DT_BATCHDATE,Q1.VC_REACTNO,Q1.NN_FGTNNO),'',Q1.NN_BTCHQTY) NN_BTCHQTY,
Q2.VC_PRODBATCHNO,Q2.NN_QTY,ROW_NUMBER() OVER (PARTITION BY Q1.DT_BATCHDATE,Q1.VC_REACTNO ORDER BY Q1.DT_BATCHDATE,Q1.VC_REACTNO,Q1.NN_FGTNNO) ROW_NUM

FROM
(SELECT PBM.DT_BATCHDATE,PBM.VC_REACTNO,SF.NN_FGTNNO,NVL(SF.NN_BTCHQTY,0) NN_BTCHQTY
FROM PROD_BATCH_MAST PBM,STR_FGTN SF
WHERE PBM.C_ROWSTATUS <>'D'
AND PBM.NN_BRCODE=&brcode
AND PBM.DT_BATCHDATE BETWEEN &fromdt AND &todt
AND PBM.NN_PRODCODE=&itcode
AND PBM.NN_BATCHNO=SF.NN_BATCHNO
AND SF.NN_BATCHNO IN ( SELECT * FROM BATCH_NO)
AND SF.C_ROWSTATUS <>'D'
AND SF.NN_ITCODE=&itcode
AND SF.NN_BRCODE=&brcode) Q1,
(SELECT SPM.VC_PRODBATCHNO,NVL(SPD.NN_QTY,0) NN_QTY,SPD.NN_FGTNNO
FROM SAL_PRODBATCH_MAST SPM,SAL_PRODBATCH_DTL SPD
WHERE SPM.C_ROWSTATUS <> 'D'
AND SPM.NN_BRCODE=&brcode
AND SPM.NN_PRODBATCHNO=SPD.NN_PRODBATCHNO)Q2
WHERE Q1.NN_FGTNNO=Q2.NN_FGTNNO(+)
ORDER BY Q1.DT_BATCHDATE,Q1.VC_REACTNO,Q1.NN_FGTNNO
)
Re: RANGE type clause in analytic function [message #245538 is a reply to message #245536] Mon, 18 June 2007 01:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please always post your Oracle version (4 decimals).
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Break your lines to max 80-100 characters when you format.

Quote:
it fails in cursor

This is not an Oracle error message.

Regards
Michel
Re: RANGE type clause in analytic function [message #245563 is a reply to message #245538] Mon, 18 June 2007 02:29 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
It may be because you are using 8i. Analytic functions are only supported by the SQL engine in 8i, not the PL/SQL engine.

The work-around is to place the AF in a view, and select the view from the PL/SQL.

Ross Leishman
Previous Topic: plz solve this query....
Next Topic: Compare Database users
Goto Forum:
  


Current Time: Wed Dec 07 20:22:02 CST 2016

Total time taken to generate the page: 0.09068 seconds