Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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..
If you have used SUM(Amount) you need to use Group By also...
I guess you have to try like: Sum(If(......
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
sum(Amount) as AmountALL 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 "Global Dimension 1 Code",
with that I still get the message "wrong Syntax"
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
Consider that you have below
Load
Customer,
Country,
Month,
SUM(Sales)
From TableName
Group By Customer, Country, Month ;
i.e. You have to use group by all Fields used in ...
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()?