7 Replies Latest reply: Jun 19, 2014 7:27 AM by Tobias Meyer RSS

    sum() im Script with where condition ?

      Hi guys

      I've following Script:

      ODBC CONNECT TO DB1;

      Database:
      Load "Posting Date",
          
      Month("Posting
      Date"
      ) AS Month,
          
      Year("Posting
      Date"
      ) AS Year,
          
      Amount,
          
      sum(Amount) as ALL.AMOUNT where "G_L
      Account No_"
      >=400000 and "G_L Account No_"<=479999 or "Global Dimension 1 Code"= 'T83400' or "Global
      Dimension 1 Code"
      = 'T83500',
          Description,
         
      "Document No_",
         
      "Entry No_",
         
      "G_L Account No_",
         
      "G_L Account No_" as "DB1-Konten",
         
      "Global Dimension 1 Code",
         
      "Global Dimension 1 Code" as KST,
         
      "Global Dimension 2 Code",
         
      "Global Dimension 2 Code" as KTR

         
      //-------- Start Multiple Select
      Statements ------
      SQL SELECT *

      FROM "DB1";
      //-------- End Multiple Select Statements
      -----

       

       

      but it doenst work ? whats my mistake?

      neither with :

       

      sum((Amount) WHERE ( "G_L Account No_">=400000 and "G_L Account No_"<=479999 or "Global Dimension 1 Code"= 'T83400' or "Global Dimension 1 Code" = 'T83500')) as ALL.AMOUNT,

       

      and

       

      sum(Amount) WHERE ( "G_L Account No_">=400000 and "G_L Account No_"<=479999 or "Global Dimension 1 Code"= 'T83400' or "Global Dimension 1 Code" = 'T83500') as ALL.AMOUNT,

       

      ____

       

      My Intention is to sum() the Amount table within where conditions..

        • Re: sum() im Script with where condition ?
          Manish Kachhia

          If you have used SUM(Amount) you need to use Group By also...

          • Re: sum() im Script with where condition ?
            Tresesco B

            I guess you have to try like: Sum(If(......

            See: New value as quotient of other two value

            • Re: sum() im Script with where condition ?
              Clever Anjos

              It depends.

              If you want all records retrieved, and rows that doesn´t match your criteria be equal 0 then you can use

              sum(if("G_L Account No_">=400000 and "G_L Account No_"<=479999 or "Global Dimension 1 Code"= 'T83400' or "Global Dimension 1 Code" = 'T83500',Amount)) as ALL.AMOUNT


              if you want only records that match your criteria then you have only one "where" statement, preferably at SQL level

              • Re: sum() im Script with where condition ?
                Jacek Mossakowski

                I think you should try Sum(if((G_L Account No_>=400000 and "G_L Account No_" <=479999) or "Global Dimension 1 Code"='T83400' or "Global Dimension 1 Code" = 'T83500'),Amount) as ALL.AMOUNT

                • Re: sum() im Script with where condition ?

                  Hi Guys,

                  I still don't get that problem.


                  ODBC CONNECT TO NAV;
                  Load Amount,
                     
                  "Global Dimension 1 Code" as X,
                     
                  "Global Dimension 2 Code" as Y,
                     
                  "G_L Account No_" as Z,
                     
                  "Posting Date",
                     
                  Month("Posting Date") as Month,
                     
                  Year("Posting Date") as Year,
                     
                  sum(if("Global Dimension 1 Code" = 'T83400' or "Global
                  Dimension 1 Code"
                  ='T83500' or "G_L Account No_">=400000 and "G_L
                  Account No_"
                  <=479999 , Amount)) as ALL.AMOUNT Group By Amount, "Global Dimension 1 Code", "Global
                  Dimension 2 Code"
                  ,"G_L Account No_", "Posting
                  Date"

                      ;
                     
                  SQL SELECT Amount,
                      "Global Dimension 1 Code",
                      "Global Dimension 2 Code",
                      "Posting Date",
                      "G_L Account No_"
                  FROM "DB ".dbo."DB7";

                  _____________

                   

                   

                  I tried with

                  - sum(if(...)Amount) as ALL.AMOUNT;

                  - sum(if((...),Amount) as ALL.AMOUNT;

                  - sum(Amount) as ALL.AMOUNT WHERE (...) Group By ...;

                  ___

                  Load "Global Dimension 1 Code",
                     
                  "Global Dimension 2 Code",
                     
                  "G_L Account No_", "Posting Date",

                  sum(Amount) as Amount.ALL WHERE("G_L Account No_">=400000 and "G_L Account No_"<=479999 or "Global Dimension 1 Code"= 'T83400' or "Global Dimension 1 Code" = 'T83500') Group By  Amount"Global Dimension 1 Code","Global Dimension 2 Code",
                     
                  "G_L Account No_", "Posting Date" ;

                   

                  ______

                   

                  My Intention is to filter a sum(Amount) with more then 1 condition like:

                   

                  Sum(Amount) where ("X" = 'T83400', "X"='T83500' , "Z"<479999, Z>400000) ...

                   

                  When I have to use OR / AND ? How can I give this conditions to one sum()?