Crystal Report Invalid Syntax near 'Ox0880'

Hello Experts,

Trying to preview a report inside of crystal and i am getting the following errorIncorrect Syntax.

I am pulling the majority of the fields onto the report using a SQL command. In that command i have embedded a parameter field that i think may be causing this issue. I have set up the parameter to accept multiple values. It seems as though the the report is functioning fine if only select one value for the parameter but not if i select multiple. I have attached the SQL command that i am using to pull in data for the reports. Including the {?PracticeID} parameter that i think may be causing the issue.

SELECT DISTINCT pm.provider_id, 
                pm.description, 
                ppm.practice_id, 
                p.practice_name as practice_name,
                provider_type_supervising_ind , 
                attending_ind,
                ISNULL(a.SupCount,0) as SupCount
FROM  provider_practice_mstr ppm INNER JOIN practice p
on ppm.practice_id = p.practice_id INNER JOIN provider_mstr pm
ON ppm.provider_id = pm.provider_id LEFT JOIN 
		(SELECT  ppm.practice_id, 
						COUNT(provider_type_supervising_ind) as SupCount 
		FROM  provider_practice_mstr ppm INNER JOIN practice p
		on ppm.practice_id = p.practice_id INNER JOIN provider_mstr pm
		ON ppm.provider_id = pm.provider_id
		WHERE ( ppm.provider_type_supervising_ind = 'Y')
		and ppm.delete_ind = 'N'
		AND pm.delete_ind = 'N'
		Group by  ppm.practice_id) as a
ON p.practice_id = a.practice_id
WHERE (ppm.attending_ind = 'Y' or ppm.provider_type_supervising_ind = 'Y') and (ppm.practice_id in ({?PracticeID}))
and ppm.delete_ind = 'N'
AND pm.delete_ind = 'N'

Open in new window


Any thoughts on what i may be doing wrong here and why it would be causing this error?

Thanks!
robthomas09Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
What version of Crystal?

How did you set the parameter to accept multiple values?

mlmcc
0
mlmccCommented:
Try taking the ( ) around the parameter out

(ppm.practice_id in ({?PracticeID}))

to

(ppm.practice_id in {?PracticeID})

mlmcc
0
robthomas09Author Commented:
Hello,

It's Crystal XI.

Removing the set of parentheses around the parameter also caused an invalid syntax error. (see below)

Invalid
The parameter in question is set up so that the "Accept multiple values" option is set to true. It is pulling in its dropdown options from another command in the report. I have attached a screenshot of how it is set up.

PracticeID
0
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

mlmccCommented:
You are using a command.  You have to edit the parameters in the command.

I don't have CR XI here but I believe command parameters were limited to single values.

mlmcc
0
robthomas09Author Commented:
Correct me if i'm wrong but i believe once you create a parameter inside of a command you can edit it using the edit dialog above. Once i created the parameter inside of the command it was available for modification inside of the field explorer. Like you said though it may be a version difference.
0
mlmccCommented:
It is available for modification but I believe the modification you made is causing the problem.

CR2008 added the ability in the command parameter to specify allowing multiple values.  I don't think this would have been added if the intent of allowing modifications included allowing multiple values.

mlmcc
0
James0628Commented:
I think mlmcc is correct.  I think I read that CR 2008 added the ability to use a parameter with multiple values with a Command, which implies that it was not supported in earlier versions.

 Unless upgrading to a later version of CR is an option for you, if you need multiple values, you may have to handle them another way.

 James
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
robthomas09Author Commented:
Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.