What is the syntax for a CASE statement in Crystal XI

[Former Member]

I'm having difficulty locating documentation for the use of CASE statements in Crystal syntax. My database administrator does not allow me to write SQL expressions so I need to covert the SQL below to Crystal Syntax. Can anyone help me? Thank you very much!!

Select

CASE

WHEN projects.ProjType like 'S%' then 'Shopping Center'

WHEN projects.ProjType like 'I%' then 'Industrial'

WHEN projects.ProjType like 'O%' then 'Office Building'

ELSE 'Other'

END

from projects

[Former Member · Accepted solution]

Here is the syntax:

select {projects.ProjType}

case is like 'S%' : "'Shopping Center"

case is like 'I%' : "Industrial"

case is like 'O%' : "Office Building"

default : "Other"

[Former Member · Accepted solution]

Hi Brian,

Thank you very much for the quick reponse! That syntax worked perfectly in my report.

What source do you use to answer Crystal syntax questions. I don't find the online help files to be very informative. I would appreciate any suggestions.

Thanks again,

David

[Former Member · Accepted solution]

I use the online help as a reference sometimes. I also Googled things alot or just searched on forums when I was learning. I also work with Crystal a lot so the syntax gets stuck in my head.

[Former Member]

Let's assume column XYZ has both numbers (1), and letters (any alphabet).

I have a case statement on SQL to turn any value that's not 1 into 0, then I am getting a sum of that column.

I am also grouping by Row A, B etc to get aggregated sum of column XYZ for those group.

Now on Crystal Reports function, I need to sum up values under column XYZ for all the groups.

If I try using sum function like below, I get an error stating:

"A number field or currency amount field is required here"

(sum({Command.XYZ}))

So I thought if I can use a case statement to change the non-numbers to 0 prior to sum that will probably resolve it. But I could not get the below case statement to work either (Error: A string is required). 

SELECT {Command.XYZ}

   Case 1:

      1 

   Default:

      0;

[DellSC]

I think you need to change it to this:

SELECT {Command.XYZ}

   Case = 1:

      1 

   Default:

      0;

Or you could use a "Switch" statement, like this:

Switch(

  {Command.XYZ} = 1, 1,

  true, 0

}

In Crystal, Switch is similar to Case, but I feel like I have more control over the comparisons.

-Dell

[Former Member]

Whether if I use CASE or SWITCH, I get a similar error

With SWITCH, "A string is required here" highlighting the one underlined below

Switch(

  {Command.XYZ} = 1, 1,

  true, 0

)

If I use CASE, I get an error "A number, currency amount, boolean, date, time, date-time, or string is expected here" and this one is highlighted on the equal sign.

SELECT {Command.XYZ}

   Case = 1:

     1 

   Default:

      0;

[abhilash_kumar]

Hi Ashish,

Please post this as a new discussion.

-Abhilash

[Former Member]

Done.

http://scn.sap.com/thread/3606647

[Former Member]

Hi David,

I have to look that one up all the time. It's pretty easy to find in the formulas help, but they call them 'SELECT' statements, instead of CASE statements.

As I recall the syntax is:

SELECT {field}
   Case <value>:
      action
   Case <another value>:
      another action
   Default:
      action; 

[Former Member]

Thanks Ryan!