Integrating Crystal Parameter with SQL code WHERE clause

[former_member997610]

Greetings,

I am creating a SQL command embedded in my Crystal report. My goal is to use my parameters in the WHERE clause of my SQL Command statement.

Here is the situation:

End Users will select one or more patient classes as a parameter in Crystal {?Class} (i.e. Inpatient, Outpatient, Rehab...)

I want my end users to be able to choose whether to Include or Exclude these classes. I am using a parameter named {?Flag} for this

The embedded WHERE clause in my code looks like this:

WHERE

CASE WHEN {?Flag} = 1 THEN CLASS_TABLE.CLASS IN ({?CLASS})

WHEN {?Flag} = 2 THEN CLASS_TABLE.CLASS NOT IN ({?CLASS})

END

I keep getting Syntax errors. (The Paramaters exist in Crystal and in my Command definitions)

Any suggestions?

Many Thanks

[Former Member]

I guessed Class Parameter was a string but it is a number. Just remove quote marks around parameter

AND ({?Flag} = 1 and PAT_ENC_2.ADT_PAT_CLASS_C IN ({?Class})) OR ({?Flag} = 2 and PAT_ENC_2.ADT_PAT_CLASS_C NOT IN ({?Class}))

Ian

[former_member997610]

Outstanding. Many Many Thanks!

[former_member997610]

OK... Many Thanks to both of you.

I have no syntax errors anymore but my query returns null when I select Inpatient (majority are inpatients so not possible).

Here are the puzzle pieces.

1. Here is the actual code that is now error free thanks to you:

AND ({?Flag} = 1 and PAT_ENC_2.ADT_PAT_CLASS_C IN ('{?Class}')) OR ({?Flag} = 2 and PAT_ENC_2.ADT_PAT_CLASS_C NOT IN ('{?Class}'))

2. I am using both Crystal and Command parameters together. The screen shots below will hopefully tell the rest of the story.

Bottom line: When I use the code above in my Where statement, I get no errors, but it it returns a null set.

Thanks again for your assistance!!!!!

[Former Member]

I don't think you have valid SQL

Need to change to something like

WHERE (

({?Flag} = 1 and CLASS_TABLE.CLASS IN ('{?CLASS}'))

OR

({?Flag} = 2 and CLASS_TABLE.CLASS NOT IN ('{?CLASS}'))

)

I have not used IN before with a String List, but generally you have to wrap Crystal Command STRING parameters in single quotes as above.

As Abhilash has said you MUST use parameters created in the Command dialog, you can not use Crystal Report Parameters

Ian

[abhilash_kumar]

Hi Dave,

What is the error you receieve?

Also, I hope you created the Parameters in the 'Command' Window and not inside the report from the 'Field Explorer'.

-Abhilash