Home » Developer & Programmer » Forms » cursor is not working ? (Forms [32 Bit] Version 6.0.8.25.2 )
cursor is not working ? [message #391749] Fri, 13 March 2009 05:40 Go to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

HI
Please help me to solve this issue when i am compiling this cursor im getting the form error

Declare
	     Cursor Cur1 is    SELECT   slcode, 
                                               vendname,
                                               vendtdsperc,
                                               glcode, 
                                               Sum(debitamount) - [B]Nvl((SELECT Sum(creditamount) 
                                                                       FROM   ledger  
                                                                       WHERE  a.unitid = 1 
                                                                              AND a.periodid = 12 
                                                                              AND a.slcode = b.slcode 
                                                                              AND a.glcode = b.glcode 
                                                                              AND Substr(a.documentno,2,3) = '207'),0) derval [/B]
                                      FROM     ledger b, 
                                               vendmast c 
                                      WHERE    b.unitid = 1 
                                               AND b.periodid = 12 
                                               AND Substr(b.documentno,2,3) = '212' 
                                               AND b.glcode NOT IN ('61118','61129','61210') 
                                               AND b.debitamount <> 0 
                                               AND b.slcode = c.vendcode 
                                      GROUP BY slcode, 
                                               glcode,
                                               vendtdsperc, 
                                               vendname;
Begin
	Null;
End;



Error follows

Error 103 at line 30 column 72
   Encountered the symbol "SELECT" when expecting one of the following
(- + mod not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
cast < a string literal with character set specification>
<a number><a single-quoted SQL string>

Error 103 at line 36, column 116

Encountered the symbol "," When expecting one of the following:

)intersect minus union


But without the following query , it is compiling successfully

Nvl((SELECT Sum(creditamount) 
                                                                       FROM   ledger a 
                                                                       WHERE  a.unitid = 1 
                                                                              AND a.periodid = 12 
                                                                              AND a.slcode = b.slcode 
                                                                              AND a.glcode = b.glcode 
                                                                              AND Substr(a.documentno,2,3) = '207'), 
                                                                      0) derval



please help me to solve this issue.

kanish
Re: cursor is not working ? [message #391764 is a reply to message #391749] Fri, 13 March 2009 07:02 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'd say that Forms 6i's PL/SQL engine isn't capable of compiling such a syntax.

Remove that "NVL" part from a cursor and select it independently within a loop (I guess you are going to use it in a loop, aren't you?). Then calculate "cur1.derval" (from a cursor) - "creditamount" (from a SELECT statement).
Re: cursor is not working ? [message #392065 is a reply to message #391749] Mon, 16 March 2009 04:26 Go to previous message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
One way round most of the query restrictions in forms 6i is to create a view based on your query and then query the view in forms.
Previous Topic: how to fix the vlaue for current item?
Next Topic: how to count the no of rows in a block before inserting
Goto Forum:
  


Current Time: Fri Dec 09 13:20:50 CST 2016

Total time taken to generate the page: 0.19622 seconds