Home » Developer & Programmer » Reports & Discoverer » NOT NULL THEN PASS VALUE (Report 6i, Oracle 9i)
NOT NULL THEN PASS VALUE [message #397670] Mon, 13 April 2009 05:24 Go to next message
cvs_1984
Messages: 136
Registered: August 2007
Location: Punjab, INDIA
Senior Member

Hi,

We have lot of division to our factory and their accounting are maintain separately for each division. We need to take the report for all Division or optional divisions accounts status.

In this report parameter user select the required divisions on
parameter Fields of P_A1, P_A2, P_A3, P_A4, there was select YES OR NO. Then the following Function run in After Parameter Form.


Ex:

Declare

U1   Number(3); /*Divison 1 */
U2   Number(3); /*Divison 2 */
U3   Number(3); /*Divison 3 */
U4   Number(3); /*Divison 4 */
C1   Char(1);
C2   Char(1);
C3   Char(1);

Begin
 
 If :P_A1 = 'YES' and ((:P_A2 = 'YES') or (:P_A3 = 'YES') or (:P_A4 = 'YES')) Then
     U1 := 1;
     C1 := ',';
 Elsif :P_A1 = 'YES' and :P_A2 = 'NO' and :P_A3 = 'NO' and :P_A4 = 'NO' Then
     U1 := '1';
 End if;
 
 If :P_A2 = 'YES' and ((:P_A3 = 'YES') or (:P_A4 = 'YES')) Then
     U2 := 2;
     C2 := ',';
 Elsif :P_A2 = 'YES' and :P_A1 = 'NO' and :P_A3 = 'NO' and :P_A4 = 'NO' Then
 	   U2 := 2;
 End if;

 If :P_A3 = 'YES' and :P_A4 = 'YES' Then
     U3 := 3;
     C3 := ',';
 Elsif :P_A3 = 'YES' and :P_A2 = 'NO' and :P_A1 = 'NO' and :P_A4 = 'NO' Then
 	   U3 := 3;
 End if;

 If :P_A4 = 'YES' Then
     U4 := 4;
 End if;


 Select Ledcode,Sum(Nvl(Clbalamt,0)) Opamt from BankAcctbal 
                      Where Mthno = 04
                        and yrsno  = 2009
                        and Rtrim(Ltrim(Unitid)) in (U1 || C1 || U2 || C2 || U3 || C3 || U4)
                        and Nvl(clbalamt,0) <> 0
                        Group by Ledcode;

End;




During the Run time the outputs are correctly selected, but the report is not work for multiple divisions and run only for single division.



Thanks,

C V S
Re: NOT NULL THEN PASS VALUE [message #397738 is a reply to message #397670] Mon, 13 April 2009 12:10 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Code you have provided does nothing that would help report to return desired result. In my opinion, you should use lexical parameters and set their (or its) values in the after parameter form trigger (the one you are using now). I guess that your lexical parameter might contain the whole WHERE condition.

For more information, read "About lexical references" chapter (as well as any other linked to it) in Reports Online Help system.

As of your code: you should not use CHAR datatype, but VARCHAR2. Although - in this very case - it is the same (CHAR(1) acts as VARCHAR2(1)), generally speaking try to avoid CHAR.

Also, the last SELECT statement you've provided is invalid; as it is part of a PL/SQL program, it should have an INTO clause (which yours does not).
Re: NOT NULL THEN PASS VALUE [message #398023 is a reply to message #397738] Tue, 14 April 2009 23:05 Go to previous messageGo to next message
cvs_1984
Messages: 136
Registered: August 2007
Location: Punjab, INDIA
Senior Member

Hi Mr. LittleFoot,

The Function i given is run on After_Parameter_Form, the lexical Parameter are unable to use in After_Parameter_Form. If any other have to solve it.



Thanks & Regards,

C V S
Re: NOT NULL THEN PASS VALUE [message #398055 is a reply to message #398023] Wed, 15 April 2009 00:57 Go to previous message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Lexical parameters should be set in the after parameter form trigger and used in report querie's WHERE clause.
Previous Topic: REP-0081: Error during file I/O operation
Next Topic: format trigger page resize
Goto Forum:
  


Current Time: Thu Dec 08 12:48:38 CST 2016

Total time taken to generate the page: 0.06410 seconds