IF THEN in Query
IF SUM([DR]) > SUM([CR]) THEN RETURN SUM([DR]) ELSE SUM([CR]) END IF AS debit,
IF SUM([CR]) > SUM([DR]) THEN RETURN SUM([CR]) ELSE SUM([DR]) END IF AS credit,
You should be able to follow the instructions from this Tech Tuesday article to sum line amounts and create a calculated field to set your amounts.
Thanks Lorren, unfortunately the map we are working with already has DEBITAMOUNT and CREDITAMOUNT as calculated fields that we were hoping not to mess with. That is why we are trying to figure something out in the query itself. We’ve tried a number of variants, the latest being:
case when IsNull(SUM(DR),0) > IsNull(SUM(CR),0) then 0 else
IsNull(SUM(CR),0)- IsNull(SUM(DR),0) end AS debit,
case when IsNull(SUM(CR),0) > IsNull(SUM(DR),0) then 0 else
IsNull(SUM(DR),0)- IsNull(SUM(CR),0) end AS credit,
Unfortunately that gives us a syntax/missing operator error. Any input is appreciated. Cheers.
You can do the same thing but name your calculated column something different.
The reason you are getting a syntax error is because you are typing SQL code and you need to use .NET code.
This response is less than helpful… what IS the correct code fro .NET?
There are a lot of .NET examples available. https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/if-then-else-statement
Considering one can’t do what is being requested in the above statement as a calculated field such as summing (i provided a tech tuesday article on how to do that), when doing if-then-else statements it’s similar to this.
this is .net code for a task or calculation
if _DR > _CR then
return _CR – _DR
else if _CR > _DR then
return _DR – _CR
if writing SQL code for a data source as the original post requested, what was provided would work for an ODBC datasource.
If you would like to submit an answer or comment, please sign in to the eOne portal.