Skip to Content
0

Integrating Crystal Parameter with SQL code WHERE clause

Mar 02, 2017 at 11:30 AM

36

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

5 Answers

Ian Waterman Mar 02, 2017 at 02:44 PM
1

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

Share
10 |10000 characters needed characters left characters exceeded
Abhilash Kumar
Mar 02, 2017 at 11:46 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Ian Waterman Mar 02, 2017 at 12:15 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Dave Fandrey Mar 02, 2017 at 01:21 PM
0

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!!!!!


1.jpg (35.7 kB)
2.jpg (154.6 kB)
3.jpg (47.3 kB)
2.jpg (154.6 kB)
-4.jpg (142.0 kB)
-5.jpg (46.3 kB)
Share
10 |10000 characters needed characters left characters exceeded
Dave Fandrey Mar 02, 2017 at 07:51 PM
0

Outstanding. Many Many Thanks!

Share
10 |10000 characters needed characters left characters exceeded