Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm trying to add grossmargin percent calculation in edit script from ODBC database (Sql server). The problem is that sometimes sales maybe 0 for row and then the script ends up to an error. I would need to check if sales is zero then gm%=0 else the formula. I have tried the following (below) but it just says "Incorrect syntax near the keyword 'If'. Please help me on this one how to do this.
ODBC
CONNECT32 TO TUOTANTO2 (XUserId is xx, XPassword is xx);
SQL SELECT CustName,
CustNum,
DirVarLabCost,
MaterialCost,
Sales,
if (Sales=0, 0, ((Sales-DirVarLabCost-MaterialCost)/Sales*100)) AS GMPros
FROM Tuotanto.dbo."ACS_MonthlyData";
Use a preceding LOAD:
ODBC
CONNECT32 TO TUOTANTO2 (XUserId is xx, XPassword is xx);
LOAD *,
if (Sales=0, 0, ((Sales-DirVarLabCost-MaterialCost)/Sales*100)) AS GMPros;
SQL SELECT CustName,
CustNum,
DirVarLabCost,
MaterialCost,
Sales
FROM Tuotanto.dbo."ACS_MonthlyData";
Use a preceding LOAD:
ODBC
CONNECT32 TO TUOTANTO2 (XUserId is xx, XPassword is xx);
LOAD *,
if (Sales=0, 0, ((Sales-DirVarLabCost-MaterialCost)/Sales*100)) AS GMPros;
SQL SELECT CustName,
CustNum,
DirVarLabCost,
MaterialCost,
Sales
FROM Tuotanto.dbo."ACS_MonthlyData";
Thanks for the quick reply and the solution!
It works!