8 Replies Latest reply: Jul 3, 2013 12:20 AM by Bobby468 RSS

    Problem Regarding a COlumn In Oracle Reports 10g

    Bobby468

      Hi everybody,

                          I am having a simple problem with a field in Oracle Reports 10g . Actually the requirement is we need to have 5 columns the values for the 1st three columns were getting from the query and the 4th column is the average of the 3columns of that record and teh 5th column is the sum of the averages of the previous 3 records of the 4th column . For example

       

       

      a          b          c          d((a+b+c)/3)          e

      ---       ----        ----         ----                      ----   

      1          1          1          1                         0   

      2          2          2          2                         0

      1          2          3          2                         1   

       

      Where D and E are place holder columns

      The probelm is that if all a=0,b=0,c=0  then d should be also 0 it is displaying it as 0 but it is storing internally some value which s equal to teh value of d in the previous Record . which i came to with teh help of SRW.Message .

       

       

      Thanks,

      Best Regards

        • 1. Re: Problem Regarding a COlumn In Oracle Reports 10g
          Archana.D

          Hi ,

          you can retrieve  column d in sql instead of placeholder.. But what is the functionality of column e? .. If it has some logic behind it , use placeholder for e ..

          WITH D AS(select 1 A, 1 B, 1 C
                from dual
                union
                select 2 ,2,2
                from dual
                union
                select 1,2,3
                from dual
                union
                select 0,0,0
                from dual
              )
          SELECT  A,B,C ,(A+B+C)/3 D
          FROM   D
          ;
          A,B,C,D
          0,0,0,0
          1,1,1,1
          1,2,3,2
          2,2,2,2
          
          • 2. Re: Problem Regarding a COlumn In Oracle Reports 10g
            Bobby468

            Yes E is a placeholder column The value of E is equal to the sum of the averages of the previous 3 values of D which is nothing but the Last 3 Moving Averages of D column

            • 3. Re: Problem Regarding a COlumn In Oracle Reports 10g
              Archana.D

              ok.. Is your placeholder of E working fine? Or you find any issues with that.. If so please post your code...

               

              Regards,

              Archana

              • 4. Re: Problem Regarding a COlumn In Oracle Reports 10g
                Bobby468

                Nope it is not working properly Externally it is not displaying nay value but internally it is storing some value . Th eproblem is coming if all the three columns such as  a,b,c were not having any values . A,b,c, were being calcuated from the query d,e were calculated based upon the values of a,b,c. D is calculated as the average of the Current values of a,b,c and E is calcualted as the Current and last 2 values of D 

                • 5. Re: Problem Regarding a COlumn In Oracle Reports 10g
                  Archana.D

                  Cannot identify the error from your explanation.... Check out the way you used to return value if a,b,c is null and the usage of your placeholder..

                  what is the code behind d.. ?

                   

                  Regards,

                  Archana

                  • 6. Re: Problem Regarding a COlumn In Oracle Reports 10g
                    O.Developer

                    hi

                     

                    You can get the fourth column from below query

                     

                    select a,b,c, (a+b+c)/3  average1,  from table

                     

                    But regarding 5th column value - please clarify how do we identify  the previous rows.  Provide your  sample  full structure ,  so that a fix can be identified.

                     

                    Check and provide your  information

                    • 7. Re: Problem Regarding a COlumn In Oracle Reports 10g
                      Bobby468

                      This is the code for teh caluculation of D&E COLUMNS . I am getting teh values of A,B,C from the query D is nothing but the average of the values of a,b,c IF :D IS NOT NULL THEN :T := NVL(:T,0)+1; ST := NVL(ST,0)+:D; :SDST := NVL(:SDST,0)+(:D*:D); :CUMUL_AVG := ST/:T; :E := (NVL(:D,0)+NVL(:A,0)+NVL(:B,0))/3;   :B := :A;   :A := :D;   IF :T > 2 AN:D :D IS NOT NULL THEN :STAN:D_:DEV := SQRT( ( (:T*:SDST)-(ST*ST) )                         / (:T*(:T-1))                   );   END IF;  END IF;

                      • 8. Re: Problem Regarding a COlumn In Oracle Reports 10g
                        Bobby468

                        This is the code for teh caluculation of D&E COLUMNS . I am getting teh values of A,B,C from the query D is nothing but the average of the values of a,b,c IF :D IS NOT NULL THEN :T := NVL(:T,0)+1; ST := NVL(ST,0)+:D; :SDST := NVL(:SDST,0)+(:D*:D); :CUMUL_AVG := ST/:T; :E := (NVL(:D,0)+NVL(:A,0)+NVL(:B,0))/3;  :B := :A;  :A := :D;  IF :T > 2 AN:D :D IS NOT NULL THEN :STAN:D_:DEV := SQRT( ( (:T*:SDST)-(ST*ST) ) / (:T*(:T-1))); END IF;  END IF;