Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello and please help.
I have a code in my SQL
LOAD
IF(Money > 0, 'Debitor','ok') AS Debitor_sign,
Customer_number;
SQL SELECT
Customer_Payment."Customer No_" AS Customer_number,
Customer_Payment."Document No_" AS Document_number,
Customer_Payment."Document Date" AS Document_date,
Customer_Payment."Due Date" AS Document_due_date,
SUM(Customer_Payment_Rows."Amount (LCY)") AS Money
FROM *$Cust_ Ledger Entry" AS Customer_Payment
LEFT OUTER JOIN *$Detailed Cust_ Ledg_ Entry" AS Customer_Payment_Rows
ON Customer_Payment_Rows."Cust_ Ledger Entry No_" = Customer_Payment."Entry No_";
GROUP BY
Customer_Payment."Customer No_",
Customer_Payment."Document No_",
Customer_Payment."Document Date",
Customer_Payment."Due Date";
As a result I get:
Customer Money
123 +5
123 +3
123 +1
456 - 5
...
How can I get another SUM in order to get:
Customer Money
123 + 9
456 -5
Thank you in advance!
Try like Below:
LOAD
IF(Money > 0, 'Debitor','ok') AS Debitor_sign,
Customer_number,
Money
;
LOAD
Customer_number,
SUM(Money) AS Money
Group By
Customer_number
;
SQL
SELECT
Customer_Payment."Customer No_" AS Customer_number,
Customer_Payment."Document No_" AS Document_number,
Customer_Payment."Document Date" AS Document_date,
Customer_Payment."Due Date" AS Document_due_date,
SUM(Customer_Payment_Rows."Amount (LCY)") AS Money
FROM
*$Cust_ Ledger Entry" AS Customer_Payment
LEFT OUTER JOIN
*$Detailed Cust_ Ledg_ Entry" AS Customer_Payment_Rows
ON
Customer_Payment_Rows."Cust_ Ledger Entry No_" = Customer_Payment."Entry No_";
GROUP BY
Customer_Payment."Customer No_",
Customer_Payment."Document No_",
Customer_Payment."Document Date",
Customer_Payment."Due Date";
Or
directly sum all the values in the SQL query itself like below:
LOAD
IF(Money > 0, 'Debitor','ok') AS Debitor_sign,
Customer_number,
Money
;
SQL
SELECT
Customer_Payment."Customer No_" AS Customer_number,
SUM(Customer_Payment_Rows."Amount (LCY)") AS Money
FROM
*$Cust_ Ledger Entry" AS Customer_Payment
LEFT OUTER JOIN
*$Detailed Cust_ Ledg_ Entry" AS Customer_Payment_Rows
ON
Customer_Payment_Rows."Cust_ Ledger Entry No_" = Customer_Payment."Entry No_";
GROUP BY
Customer_Payment."Customer No_"
;
exclude document_number and document_data, and due date
SQL SELECT
Customer_Payment."Customer No_" AS Customer_number,
SUM(Customer_Payment_Rows."Amount (LCY)") AS Money
FROM *$Cust_ Ledger Entry" AS Customer_Payment
LEFT OUTER JOIN *$Detailed Cust_ Ledg_ Entry" AS Customer_Payment_Rows
ON Customer_Payment_Rows."Cust_ Ledger Entry No_" = Customer_Payment."Entry No_";
GROUP BY
Customer_Payment."Customer No_";
Try like Below:
LOAD
IF(Money > 0, 'Debitor','ok') AS Debitor_sign,
Customer_number,
Money
;
LOAD
Customer_number,
SUM(Money) AS Money
Group By
Customer_number
;
SQL
SELECT
Customer_Payment."Customer No_" AS Customer_number,
Customer_Payment."Document No_" AS Document_number,
Customer_Payment."Document Date" AS Document_date,
Customer_Payment."Due Date" AS Document_due_date,
SUM(Customer_Payment_Rows."Amount (LCY)") AS Money
FROM
*$Cust_ Ledger Entry" AS Customer_Payment
LEFT OUTER JOIN
*$Detailed Cust_ Ledg_ Entry" AS Customer_Payment_Rows
ON
Customer_Payment_Rows."Cust_ Ledger Entry No_" = Customer_Payment."Entry No_";
GROUP BY
Customer_Payment."Customer No_",
Customer_Payment."Document No_",
Customer_Payment."Document Date",
Customer_Payment."Due Date";
Or
directly sum all the values in the SQL query itself like below:
LOAD
IF(Money > 0, 'Debitor','ok') AS Debitor_sign,
Customer_number,
Money
;
SQL
SELECT
Customer_Payment."Customer No_" AS Customer_number,
SUM(Customer_Payment_Rows."Amount (LCY)") AS Money
FROM
*$Cust_ Ledger Entry" AS Customer_Payment
LEFT OUTER JOIN
*$Detailed Cust_ Ledg_ Entry" AS Customer_Payment_Rows
ON
Customer_Payment_Rows."Cust_ Ledger Entry No_" = Customer_Payment."Entry No_";
GROUP BY
Customer_Payment."Customer No_"
;
Hi,
Create a straight table and use the customer field as dimension and might be use sum(Money) as your expression or some set analysis functions like sum({<Customer=>}Money) could also help.
Hope it helps!
uhh... I understand .. I made a syntax error. I tried:
SUM(money) .... without AS Money
Is it correct, that I can use a lot of LOAD (one after another) for one SQL?
You can crate "N" of LOAD statements one after other but it may degrade the load performance. It all depends the on the requirements.