Skip to Content
avatar image
Former Member

SQL Expression Field - Combine Declared Variable With Case Statement

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • avatar image
    Former Member
    Oct 02, 2008 at 08:18 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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?

  • avatar image
    Former Member
    Oct 02, 2008 at 08:41 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 06, 2009 at 01:14 AM

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

    Add comment
    10|10000 characters needed characters exceeded