Trying to pull amounts using SQL expression with mult. when & then

[gbuchman1]

Hi all,
Im trying to pull account balances using a SQL expression, but am getting nothing. I tried multiple WHEN and THEN lines such as:

CASE
WHEN (tblGLBLBalance.sCodeIDf_2 = '0060'
AND tblGLBLBalance.sCodeIDf_1 IN ('0160','0161'))
THEN tblGLBLBalance.curDebit - tblGLBLBalance.curCredit
WHEN (tblGLBLBalance.sCodeIDf_2 = '0070'
AND tblGLBLBalance.sCodeIDf_1 = '0226')
THEN tblGLBLBalance.curDebit - tblGLBLBalance.curCredit
WHEN (tblGLBLBalance.sCodeIDf_2 = '0073'
AND tblGLBLBalance.sCodeIDf_1 = '0237')
THEN tblGLBLBalance.curDebit - tblGLBLBalance.curCredit
ELSE 0

I also tried this with just one when then and got errors:
CASE
WHEN (tblGLBLBalance.sCodeIDf_0 BETWEEN ('111001' and 499999')
AND tblGLBLBalance.sCodeIDf_2 = '0016'
AND tblGLBLBalance.sCodeIDf_1 IN ('0061','0062'))
THEN tblGLBLBalance.curDebit - tblGLBLBalance.curCredit
ELSE 0
END
END

but this yielded nothing. Is there a way to do these as a SQL expression that will work?

Thanks for your help!

 

[lbass]

They both look like they should work. Try removing the "else 0" and just putting 'end' and also you can remove the outer set of parens around the when clauses--but I don't think that should matter.

What error messages are you getting? Are you placing the expression in the detail section? Is anything returned? What type of database are you using?

-LB

 

[gbuchman1]

The SQL expression is not in the Details section; it is part of a sum formula in group header 3 and group footer 2. There are two other columns that use sums of SQL expressions in the same sections and they work fine; they dont have their SQL expression in their detail section either.

I am on Crystal 11 and using an MS SQL db.
I removed the ELSE 0 and just put END, but there was no change.

Here is how it looks now.

CASE
WHEN tblGLBLBalance.sCodeIDf_0 = '476001'
THEN tblGLBLBalance.curDebit - tblGLBLBalance.curCredit
WHEN (tblGLBLBalance.sCodeIDf_2 = '0016'
AND tblGLBLBalance.sCodeIDf_1 IN ('0061','0062'))
THEN tblGLBLBalance.curDebit - tblGLBLBalance.curCredit
WHEN (tblGLBLBalance.sCodeIDf_2 = '0017'
AND tblGLBLBalance.sCodeIDf_1 IN ('0076','0077'))
THEN tblGLBLBalance.curDebit - tblGLBLBalance.curCredit
WHEN (tblGLBLBalance.sCodeIDf_2 = '0060'
AND tblGLBLBalance.sCodeIDf_1 IN ('0160','0161'))
THEN tblGLBLBalance.curDebit - tblGLBLBalance.curCredit
WHEN (tblGLBLBalance.sCodeIDf_2 = '0070'
AND tblGLBLBalance.sCodeIDf_1 = '0226')
THEN tblGLBLBalance.curDebit - tblGLBLBalance.curCredit
WHEN (tblGLBLBalance.sCodeIDf_2 = '0073'
AND tblGLBLBalance.sCodeIDf_1 = '0237')
THEN tblGLBLBalance.curDebit - tblGLBLBalance.curCredit
END

All I am getting is the figures for the first loop (those records with tblGLBLBalance.sCodeIDf_0 = '476001'

 

[lbass]

How are you getting the sum in the group header? Are you using a formula? Or are you placing the SQL expression in the detail section and then inserting a sum on it at the group level and then removing it from the detail section? The expression itself is a detail level expression, not a summary.

-LB

 

[gbuchman1]

I am using a formula to put the sum of the SQL expression in the group header. Here is the formula that is in the group header:

SUM({%BadDebt-Serv&PyOtcontractual},{@GLacct}) + SUM({%Other-Adj},{@GLacct})

@GLacct is the group name.

 

[gbuchman1]

I have looked closely at this and I think my problem could be that I am not limiting my tblGLBLBalance.sCodeIDf_0 value correctly. I have to limit this to the 300000 and 400000 accounts. Therefore I have altered my formula for this. Here is part of it:

WHEN tblGLBLBalance.sCodeIDf_0 BETWEEN ('311001' AND '499999')
AND tblGLBLBalance.sCodeIDf_2 = '0016'
AND tblGLBLBalance.sCodeIDf_1 IN ('0061','0062','0293')
THEN tblGLBLBalance.curDebit - tblGLBLBalance.curCredit

However, I get an error on the range and it says:
"Database Connector Error:'42000:[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'AND',[Database Vendor Code: 156]'. The word 'TO' in between the values gives the same error. How can I correctly enter the range?

 

[gbuchman1]

I tried removing the parentheses in the range and that worked. Now it is:
BETWEEN '311001' AND '499999'

That worked. Thanks for your help.