parameters in nested selects??

[sammydafish]

I'm using CR 8.5. I have a query that I need to use a nested select in. I can't seem to get Crystal to format the query as I need in order to take advantage of keys, but I can go to "Show SQL Query" and edit teh query to get my desired results. The problem is, I don't know how to get the data in "Show SQL Query" to use a parameter. I need to let the user enter a date into the subquery. How can I do this?

what I'm trying to do is something like this:

Select [columns] from [tables] where invoice in (select invoice from

where invdate = {parameter})

But if I try to insert something like {?invdate} I get syntax errors. What can I do?

- Anthony

 

[Hinfer]

Can you not use subreports instead? I think this would be a lot easier to do. You won't have to fiddle around with SQL.

 

[synapsevampire]

You can use a SQL Expression to create SQL Expressions and subqueries, but I don't think that you can use a parameter in them.

I fear that you're going to need a subreport, or a different design.

If you can create a View, break each select into a separate View and then join them, or in the SQL create a derived table from the subquery and join that way.

This is database dependent of course, which you haven't shared.

-k

 

[Hinfer]

If your parameters DOES NOT involve dates, then the view is a good idea (since its not really a filter out table).

Create a subreport and make sure they are linked. The parameter in the subreport will be prompted when you run the report.

Do becareful of performance issues since subreports take a longer time to run (it has to do a select in the main report and then another select in the sub)

 

[sammydafish]

Maybe I'm getting this wrong, but if i were to use a subreport. The parent report would run, then the subreport would run for each parent record found (what I need to get my results) This would be incredibly inefficient, and take possible hours to run (there are thousands of results). In the SQL example, the nested select will run 1st. The server will cache the results, then use them as if they were typed out in the IN statement. What I get it a report that runs in little under 30 secconds.

I can't use a view because I need the user to be able to enter the date every time the report is run.

Any other ideas?

 

[Hinfer]

I'd build a very simple report with a subreport just to test it first. Depending on your selection it may not seem that bad.

 

[sammydafish]

I tried the subreport thing a few ways. It simply doesn't work. I can't even get the results I need because crystal can't sort results of the subreport with other subreport results.

I'ver tried various other joins to try to get this to work. I can get the results by simply joining the tables and filtering based on the date. But the database must first jon all the records, then do the date filter. I tried it out and it just finnished running, two hours later.

Is there no way to get a parameter into this screen? Is there some other way to build you own query for crystal and be able to insert parameters into it?

 

[kenhamady]

Three options:

1) You can write a stored procedure with a parameter and report off of that.
2) You can use the CR SQL Designer and report off of the QRY file.
3) You can upgrade to v9 or v10 and create a SQL Command and report off of that.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com