Crystal Nested if syntax problem 1

[turnde]

This is really driving me insane. I am running the code below in the Crystal Select Expert. The second part of my two 'OR' statements will not work. I am not getting any errors, it just brings up a blank report when the second part of the or statement should be valid. This same syntax has worked in previous Crystal reports. Any Ideas?:-}
Deirdre

{COURSE.CDP} = {?InCDP} and
(if {?inGroup} = "GRP" then
 (({LOCATION_GRP.SQDN} = {?inSelection}) or ({LOCATION_GRP.GRP} = {?inSelection})) 
else
if {?inGroup} = "MAJOR" then
    ({LOCATION_GRP.MAJOR} = {?inSelection}) 
ELSE
if {?inGroup} = "TY" then
    ({LOCATION_GRP.TYCOM} = {?inSelection})
ELSE
if {?inGroup} = "WING" then
    ({LOCATION_GRP.WING} = {?inSelection})
ELSE
if {?inGroup} = "ADDGRP" then  
  (({LOCATION_GRP.PARENT_GRP} = {?inSelection}) or ({LOCATION_GRP.PARENT_SUBGRP} = {?inSelection})) 
ELSE
if {?inGroup} = "UIC" then
    ({MEMBER.UIC} = {?inSelection}))

 

[bdreed35]

Do you have data for those conditions? What version of Crystal are you using and what is the database?
This is a little bit easier to read so lets try this to start:

{COURSE.CDP} = {?InCDP} and
switch
(
    {?inGroup} = "GRP",    (({LOCATION_GRP.SQDN} = {?inSelection}) or 
                            ({LOCATION_GRP.GRP} = {?inSelection})),
    {?inGroup} = "MAJOR",   ({LOCATION_GRP.MAJOR} = {?inSelection}),
    {?inGroup} = "TY",      ({LOCATION_GRP.TYCOM} = {?inSelection}),
    {?inGroup} = "WING",    ({LOCATION_GRP.WING} = {?inSelection}),
    {?inGroup} = "ADDGRP", (({LOCATION_GRP.PARENT_GRP} = {?inSelection}) or 
                            ({LOCATION_GRP.PARENT_SUBGRP} = {?inSelection})),
    {?inGroup} = "UIC",     ({MEMBER.UIC} = {?inSelection}))
)

~Brian

 

[synapsevampire]

Make sure that you check the Datbase->Show SQL to ensure that Brian's solution is being passed to the database, otherwise you'll suffer poor performance. Switch does a reasonable job of passing, but sometimes it hiccups too.

Let's hear if it isn't passed and I'll rewrite your formula.

-k

 

[Ngolem]

This is one "ugly" if-then block for a select formula

{COURSE.CDP} = {?InCDP} and
(if {?inGroup} = "GRP" then
({LOCATION_GRP.SQDN} = {?inSelection} or
{LOCATION_GRP.GRP} = {?inSelection})
ELSE if {?inGroup} = "MAJOR" then
{LOCATION_GRP.MAJOR} = {?inSelection}
ELSE if {?inGroup} = "TY" then
{LOCATION_GRP.TYCOM} = {?inSelection}
ELSE if {?inGroup} = "WING" then
{LOCATION_GRP.WING} = {?inSelection}
ELSE if {?inGroup} = "ADDGRP" then
({LOCATION_GRP.PARENT_GRP} = {?inSelection} or
{LOCATION_GRP.PARENT_SUBGRP} = {?inSelection})
ELSE if {?inGroup} = "UIC" then
{MEMBER.UIC} = {?inSelection}
);
)

Well I stripped out some unnecessary brackets and thought things were not ended properly but maybe they were.

no errors but getting no data suggests the following

1. {COURSE.CDP} <> {?InCDP} - if this is true there will be no data

2. There are NULLS in your database...you have made no provision for encountering a NULL and hence the formula will crash when one is read...with no error given

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[turnde]

Thanks for the reply's,
I'm using Crystal 8.5 with an Oracle db. I'm passing the parameters from asp.net and should definitely be getting rows back. Only one of these will be true
({LOCATION_GRP.SQDN} = {?inSelection} or
{LOCATION_GRP.GRP} = {?inSelection})
The report brings back data if the condition on the left side of the 'or' statement is true. Basically, it brings back a blank report only when the right side of the or statement is true regardless of which field is on the right side. I've tried Brian's code and I'm getting the same results. I've checked oracle to see what is being passed and Crystal is not parsing the if statement out to sql. :-}
Deirdre

 

[jerry5766]

Are any of your fields NULL? I have had many issues with compound conditions where one of the fields being tested is NULL.

 

[turnde]

Thanks for all your help. bdreed35's code did finally work for me when I moved around the brackets a bit. I'm still unsure what the issue was but its working beautifully. I'm soo happy:-}
Deirdre