1 Reply Latest reply: Jun 19, 2014 8:52 AM by Gysbert Wassenaar RSS

    sum() script with several where conditions

      Hi Guys,

      I still don't get that problem solved, to get several where conditions in one expression sum()


      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()?