SQL Expression Field - Combine Declared Variable With Case Statement

[Former Member]

Hello All, I have been using Crystal & Business Objects for a few months now and have figured out quite a bit on my own. This is the first real time I have struggled with something and while I could do this as a Formula Field I would like to know how to do this as a SQL Expression. Basically I want to create a SQL Expression that uses a CASE statement but I wanted to make the code a little more efficient and employ a variable to hold a string and then use the variable in the CASE statement. The expression editor accepts the CASE statement OK but I don't know how to declare the variable. Please assist with the syntax?

This is what I have:

CASE

WHEN u201CDatabaseu201D.u201DFieldu201D = u2018Hu2019 THEN u2018Hedgeu2019

WHEN u201CDatabaseu201D.u201DFieldu201D = u2018Pu2019 THEN u2018PVIu2019

ELSE u2018Noneu2019

END

This is what I want:

DECLARE strVar AS VARCHAR(25)

strVar = u201CDatabaseu201D.u201DFieldu201D

CASE

WHEN strVar = u2018Hu2019 THEN u2018Hedgeu2019

WHEN strVar = u2018Pu2019 THEN u2018PVIu2019

ELSE u2018Noneu2019

END

[former_member11696]

SQL Expressions are limited to what the ODBC or OLE DB driver is capable of.

[Former Member]

Hi Todd,

Please use the following for loop; your problem will be solved.

Local StringVar str := "";
Local NumberVar strLen := Length ({Database.Field});
Local NumberVar i;

For i := 1 To strLen Do
(

       if {Database.Field} <i> = "H" then str := "Hedge"
        else if {Database.Field} <i> = "P" then str := "PVI"
        else str := "None"; exit for
        
);
str

Let me know once done!

Thank you,

Ashok

[Former Member]

Ashok, see posting above, thx.

[Former Member]

Hi Todd,

SQL Expressions are used to get scalar values like sum(), count() which has only one value. So I think better to create a formula like

select {Database Field}

case 'H' : "Hedge"

case 'P' : "PVI"

default: "None"

Regards,

Raghavendra

[Former Member]

Hi Raghavendra, I actually wanted to manipulate a string, hold it in a variable and then use the variable in the case statrment. Also I want to create this as a SQL Expression NOT a Crystal Formula (I already know how to do it this way).

Ashok, thank for the possible answer but as above I wish to create a SQL Expression not a Crystal Function which is what you have provided. Any ideas?