Passing Parameters to SQL

[seller]

I am trying to pass a user supplied parameter at run time in crystal to a sybase anywhere runtime sql server. The statement used in the select expert formula wizard is:

{teacher.teacherid} = {?Teacher ID}

The sql query in "Show SQl" is:
This runs excruciatling slow coz it returns everything since I cannot reduce it down by passing a user supplied teacher id parameter.[/1]

SELECT DISTINCT
Teacher."Name",
Section."TeacherID", Section."CourseNum", Section."Section", Section."Period",
ScheduleTable."Deleted",
Course."Description",
Student."Deleted", Student."StudentID", Student."Name",
Attendance."Deleted", Attendance."AttendCode", Attendance."AttendDate"
FROM
{ oj (((("g2ds"."Teacher" Teacher INNER JOIN "g2ds"."Section" Section ON Teacher."TeacherID" = Section."TeacherID&quot INNER JOIN "g2ds"."Course" Course ON Section."CourseNum" = Course."CourseNum&quot INNER JOIN "g2ds"."ScheduleTable" ScheduleTable ON Section."CourseNum" = ScheduleTable."CourseNum" AND Section."Section" = ScheduleTable."Section&quot INNER JOIN "g2ds"."Attendance" Attendance ON ScheduleTable."StudentID" = Attendance."StudentID" AND ScheduleTable."CourseNum" = Attendance."CourseNum" AND ScheduleTable."Section" = Attendance."Section&quot LEFT OUTER JOIN "g2ds"."Student" Student ON Student."StudentID" = Attendance."StudentID"}
WHERE
Student."Deleted" = '0' AND ScheduleTable."Deleted" = '0' AND Attendance."Deleted" = '0'
ORDER BY
Teacher."Name" ASC, Section."Period" ASC, Student."Name" ASC

Need help, any ideas appreciated.

Thanks much,

Dave

 

[PKG]

Hi Dave,

Since you are using Sybase, you can write a stored procedure with the required parameter. Inside the stored procedure you can use this parameter in the select SQL.

Use the stored procedure as the source of your report.

--PKG *************************
Pradeep Kumar Gupta
Data Warehouse Consultant.
E-Mails: pradeep.k.gupta@indiainfo.com
pradeep.gupta@morganstanley.com

 

[kenhamady]

It also may be because CR is using default SQL syntax or 'oj' syntax. This is what CR defaults to when it doesn't have a registry entry for a particular flavor of SQL. You can get around this by using the SP as mentioned above, or you can see if CR can generate more optimal SQL. There is an article about this at:

http://support.crystaldecisions.com/kbase/c2003023.asp

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[seller]

Thanks for the feedback.

I cannot create a sp in sybase because this particular install is using a runtime version that came bundled with the app. Hopefully someday they will rehost to a full version of a database.

As for editing the registry...would this have to be done to each end user machine if the report were compiled and distributed?

What about using a SQL expression in CR (under Insert|Sql Expression) and then using that in the Select expert? Would that pass the parameter? Or would the SQL query still probably drop it due to syntax issues?

 

[seller]

This is crazy sounding but after spending an inordinate amount of time tweaking the sql query and researching the issue on the web...all I did to get it the parameter to pass was to delete all the parameters in the select wizard, close and save, open and add the parameters back (using the select wizard) and PRESTO the parameters started passing to the sql query.

Sometimes CR just kills me.

Must be a bug of some kind.

Dave

 

[Ngolem]

This is why I don't use the Select Wizard....rather I enter the criteria directly into the record select formula