Crystal Syntax: Case statement

[cisscott]

Hi all.. Im trying to use a case statement in a selection formula. The example given by crystal online is pretty bare, and doesn't give enough info. I have 6 different fields. The record should be included if any of these fields is = 1 . The IF or IF or IF gets pretty sloppy, and I want to try to use CASE. However I cannot get the syntax right. Can someone post a correct example of a long case statement?

 

[cisscott]

Sorry, forgot...

MSSQL Server 2000
CR 9.0 Pro

 

[kskid]

The following is from 8.5 Crystal Help under Control Structures. Not sure where it is in CR 9

//Select example 1
Select {Customer.Fax}[1 To 3]
Case "604", "250" :
"BC"
Case "206", "509", "360" :
"WA"
Default :
"";

This one is selecting the 1st 3 characters ([1 to 3]) from {Customer.Fax}

The other example they have is

//Select example 2
Select {movie.NOM}
Case 1,2,3, Is < 1 :
(
//Can have expression lists by using
//parentheses
10 + 20;
"low"
)
Case 4 To 6, 7, 8, 9 :
"medium"
Case 10 :
"high"
Default :
"extreme"

 

[lbass]

Select case" is more suited to if/then conditions using one field, and it sounds more like you are trying to include records based on multiple fields. Have you tried using something like the following?

{table.field1} = 1 or
{table.field2} = 1 or
{table.field3} = 1 //etc.

-LB

 

[kskid]

Ooops! I misread that. Thanks, LB.

 

[cisscott]

OK, below is the code i have been trying to use for this function. Keep in mind I have tried many different combinations of If..then...else and using OR & AND in different combinations as well. The code works fine for every selection except the {?All flags_prm} option. If the user selects All Flags the report should pull all records where any of the FLAG fields = 1. For instance one record may have the box office flagged, and the next one may have the giftshop flagged and the next would have concessions flagged. The report should pull both of these records if All FLags is selected in the prompts. Currently it will only pull the very first one in the line (box_office_prm). Any advice on how to do this correctly would be appreciated.

{comment04v2.dateofshow} in ({?Begin Date} to {?End Date}) AND
((if ({?gift_flag_prm} = true) then
{comment04v2.giftshop_flag} = 1) OR
(if ({?merch_flag_prm} = true) then
{comment04v2.merch_flag} = 1) OR
(if ({?Box Office Flags_prm} = true) then
{comment04v2.box_flag} = 1) OR
(if ({?Concessions Flags_prm} = true) then
{comment04v2.conc_flag} = 1) OR
(if ({?Music Singing Flags_prm} = true) then
{comment04v2.musicquality} = 1) OR
(if ({?Photo Flags_prm} = true) then
{comment04v2.photo_flag} = 1) OR
(if ({?Price Value Flags_prm} = true) then
{comment04v2.price_flag} = 1) OR
(if ({?Production Flags_prm} = true) then
{comment04v2.production_flag} = 1) OR
(if ({?Unique Flags_prm} = true) then
{comment04v2.unique_flag} = 1) OR
(if ({?Usher Flags_prm} = true) then
{comment04v2.usher_flag} = 1) OR

\\HERE IS THE PART THAT WON'T WORK ! IF {?All Flags_prm} is
\\ true, then it needs to pull all records where any of the
\\following flags = 1.It only pulls the 1st one (box_flag)

if ({?All Flags_prm} = true) then
(({comment04v2.box_flag} = 1) OR
({comment04v2.conc_flag}=1) OR
({comment04v2.giftshop_flag}= 1) OR
({comment04v2.merch_flag} = 1) OR
({comment04v2.photo_flag} = 1) OR
({comment04v2.price_flag} = 1) OR
({comment04v2.production_flag} = 1) OR
({comment04v2.unique_flag}= 1) OR
({comment04v2.usher_flag} = 1)))

 

[lbass]

I think the problem is in the order of the formula. You want the formula to evaluate for "All" first. Try:

{comment04v2.dateofshow} in ({?Begin Date} to {?End Date}) AND
(
if {?All Flags_prm} = true then
({comment04v2.box_flag} = 1 OR
{comment04v2.conc_flag}=1 OR
{comment04v2.giftshop_flag}= 1 OR
{comment04v2.merch_flag} = 1 OR
{comment04v2.photo_flag} = 1 OR
{comment04v2.price_flag} = 1 OR
{comment04v2.production_flag} = 1 OR
{comment04v2.unique_flag}= 1 OR
{comment04v2.usher_flag} = 1) else
if {?All Flags_prm} = false then
((if {?gift_flag_prm} = true then
{comment04v2.giftshop_flag} = 1) OR
(if {?merch_flag_prm} = true then
{comment04v2.merch_flag} = 1) OR
(if {?Box Office Flags_prm} = true then
{comment04v2.box_flag} = 1) OR
(if {?Concessions Flags_prm} = true then
{comment04v2.conc_flag} = 1) OR
(if {?Music Singing Flags_prm} = true then
{comment04v2.musicquality} = 1) OR
(if {?Photo Flags_prm} = true then
{comment04v2.photo_flag} = 1) OR
(if {?Price Value Flags_prm} = true then
{comment04v2.price_flag} = 1) OR
(if {?Production Flags_prm} = true then
{comment04v2.production_flag} = 1) OR
(if {?Unique Flags_prm} = true then
{comment04v2.unique_flag} = 1) OR
(if {?Usher Flags_prm} = true then
{comment04v2.usher_flag} = 1))
)

-LB

 

[cisscott]

Same result... GRRR... I still only get results for the first parameter in the series (which happens to be the box office_flag parameter). This is making me nutz ! Thanks for trying though.

 

[lyanch]

Are any of your flags ever null?

Lisa

 

[lbass]

Don't know why this doesn't work, but if the second part is working you could change the formula to:

{comment04v2.dateofshow} in ({?Begin Date} to {?End Date}) AND
(
if {?All Flags_prm} = false then
(if {?gift_flag_prm} = true then
{comment04v2.giftshop_flag} = 1) OR
(if {?merch_flag_prm} = true then
{comment04v2.merch_flag} = 1) OR
(if {?Box Office Flags_prm} = true then
{comment04v2.box_flag} = 1) OR
(if {?Concessions Flags_prm} = true then
{comment04v2.conc_flag} = 1) OR
(if {?Music Singing Flags_prm} = true then
{comment04v2.musicquality} = 1) OR
(if {?Photo Flags_prm} = true then
{comment04v2.photo_flag} = 1) OR
(if {?Price Value Flags_prm} = true then
{comment04v2.price_flag} = 1) OR
(if {?Production Flags_prm} = true then
{comment04v2.production_flag} = 1) OR
(if {?Unique Flags_prm} = true then
{comment04v2.unique_flag} = 1) OR
(if {?Usher Flags_prm} = true then
{comment04v2.usher_flag} = 1) else true
)

This would return all records if {?All Flags_prm} is true. Then you could go to the section expert->details->suppress->x+2 and enter:

{comment04v2.box_flag} <> 1 and
{comment04v2.conc_flag} <> 1 and
{comment04v2.giftshop_flag} <> 1 and
{comment04v2.merch_flag} <> 1 and
{comment04v2.photo_flag} <> 1 and
{comment04v2.price_flag} <> 1 and
{comment04v2.production_flag} <> 1 and
{comment04v2.unique_flag}<> 1 and
{comment04v2.usher_flag} <> 1

This wouldn't be as efficient, but might work. Another approach might be:

{comment04v2.dateofshow} in ({?Begin Date} to {?End Date}) AND
(
if {?All Flags_prm} = true then
(1 in [{comment04v2.box_flag},comment04v2.conc_flag},{comment04v2.giftshop_flag},{comment04v2.merch_flag},{comment04v2.photo_flag},{comment04v2.price_flag},{comment04v2.production_flag},{comment04v2.unique_flag},{comment04v2.usher_flag}]) else
if {?All Flags_prm} = false then
((if {?gift_flag_prm} = true then
{comment04v2.giftshop_flag} = 1) OR
(if {?merch_flag_prm} = true then
{comment04v2.merch_flag} = 1) OR
(if {?Box Office Flags_prm} = true then
{comment04v2.box_flag} = 1) OR
(if {?Concessions Flags_prm} = true then
{comment04v2.conc_flag} = 1) OR
(if {?Music Singing Flags_prm} = true then
{comment04v2.musicquality} = 1) OR
(if {?Photo Flags_prm} = true then
{comment04v2.photo_flag} = 1) OR
(if {?Price Value Flags_prm} = true then
{comment04v2.price_flag} = 1) OR
(if {?Production Flags_prm} = true then
{comment04v2.production_flag} = 1) OR
(if {?Unique Flags_prm} = true then
{comment04v2.unique_flag} = 1) OR
(if {?Usher Flags_prm} = true then
{comment04v2.usher_flag} = 1))
)

Not sure this would make a difference though. Out of curiosity, is the other field value for each flag a "0" or is it null?

-LB

 

[cisscott]

The other value is <NULL>