Joined: 12 Aug 2009
Location: United States
Online Status: Offline
Posts: 2
Topic: Command w/ parameter does not return correct data Posted: 12 Aug 2009 at 10:43am
I just ran into a rather weird problem with CR 2008. If I add a Command with sql syntax using date parameters the command does not return the same data as when I add the command using the same values but entered statically.
Essentially I have written a simple SQL query using the Server Management Studio and verified that it is returning the correct data. If I transfer this to CR by selecting "Add a Command" in database expert it returns the correct data. But then, if I replace the two static date values in the query with parameters and give these parameters the exact same value as they had in the static query the report returns incorrect data. I can even then go to "Show SQL" copy the text back to Studio, replace the parameter names with the values I entered in CR and it returns correct data. I don't get what CR is changing when it actually runs the SQL string. I am not linking the Command with any table or any other command and am not doing any additional filtering or sorting, I am just printing the output (a single column) to the report. Has anyone experienced anything like this before?
Joined: 12 Aug 2009
Location: United States
Online Status: Offline
Posts: 2
Posted: 12 Aug 2009 at 10:45am
By the way, the SQL string I am using is:
SELECT DISTINCT PatientFullName FROM PatientOrders WHERE ServiceMasterName IN ('...','...','etc') AND StartDate_Local<'7/31/2009 11:59:11' AND (StopDate_Local>'7/01/2009 11:59:11' OR StopDate_Local Is Null) ORDER BY PatientFullName
And with parameters (returns incorrect data):
SELECT DISTINCT PatientFullName
FROM PatientOrders
WHERE ServiceMasterName IN ('...','...','etc')
AND StartDate_Local<{?Date Maximum}
AND (StopDate_Local>{?Date Minimum} OR StopDate_Local Is Null) ORDER BY PatientFullName
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Posted: 12 Aug 2009 at 11:29am
Don't use paremeters in Command statememts.
If you want to use the params at the DB level, uses a stored procedure, define the params there and they will be created in crystal automaticlly (although you will have to use a DATETIME field ratehr than a Date field).
Otherwise make your COMMAND exclude the date filter and add that in as part of your select stamtement in crystal while using crystal built parameters in that select statement.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum
IP Logged
