Skip to content
+1-888-319-3663

COMMUNITY FORUM

IF THEN in Query

K Moran asked 4 years ago
I’ve been trying to write a query with a ‘IF THEN’ in the Map Setup (will paste example below) but keep bumping into syntax errors, any input/advice would be greatly appreciated.
 
    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,
Answers
Lorren Zemke answered 4 years ago
K,
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.
https://www.eonesolutions.com/tech-tuesday-sum-document-line-amounts-using-global-variables/
Lorren
K Moran replied 4 years ago

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.

Lorren Zemke Staff replied 4 years ago

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.

Jo deRuiter replied 3 years ago

This response is less than helpful… what IS the correct code fro .NET?

Lorren Zemke replied 3 years ago

Jo deRuiter,

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
else
return 0
end if

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.