crystal report(case)

Question

im using asp.net crystal report ........ sql query:

CONVERT(NUMERIC(17,3), CASE 
                WHEN 
            CASE WHEN GLDD_DOC_AMOUNT > 0 THEN GLDD_DOC_AMOUNT ELSE 0 END = 0 THEN NULL 
                ELSE 
            CASE WHEN GLDD_DOC_AMOUNT > 0 THEN GLDD_DOC_AMOUNT ELSE 0 END 
            END) DR,
             CONVERT(NUMERIC(17,3), CASE 
                WHEN (- 1 * 
            CASE WHEN GLDD_DOC_AMOUNT < 0 THEN GLDD_DOC_AMOUNT ELSE 0 END) = 0 THEN NULL 
                ELSE - 1 * 
            CASE WHEN GLDD_DOC_AMOUNT < 0 THEN GLDD_DOC_AMOUNT ELSE 0 END 
            END) CR,

the above query is which is from sql server 2005..... i have to use this query in crystal report Formula editor..... how?

Answer 1

You can either paste the two SQL parts into SQL Expression formulas (one for the CR part and another for the DR part), or rewrite them as Crystal formulas. Both formulas have some redundancy, and are easier in SQL as

CONVERT(NUMERIC(17,3), Case When GLDD_DOC_AMOUNT > 0 Then GLDD_DOC_AMOUNT Else null End) AS DR,
CONVERT(NUMERIC(17,3), Case When GLDD_DOC_AMOUNT < 0 Then -1*GLDD_DOC_AMOUNT Else null End) AS CR

In Crystal Syntax, you can use If...Else