Skip to Content
avatar image
Former Member

Integrating Crystal Parameter with SQL code WHERE clause

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Mar 02, 2017 at 02:44 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 02, 2017 at 11:46 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 02, 2017 at 12:15 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 02, 2017 at 01:21 PM

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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 02, 2017 at 07:51 PM

    Outstanding. Many Many Thanks!

    Add comment
    10|10000 characters needed characters exceeded