Dynamic Select Query 1

[xsw1971]

Crystal 8.5/CE 8 with an Oracle database

I have a parameter that asks the user to select one of the following:
Qualified Projects
Non-qualified Projects

When the user selects Qualified Projects, I want to include all qualified projects and all non-qualified that reference qualified projects (if the NQ projects don’t reference any project or they reference other NQ projects I do not want to include them)

Here are my queries, which work fine if I edit the actual SQL statement in crystal:

Non-qualified:

SELECT id
FROM projects
WHERE qualified = ‘NO’

Qualified:

SELECT id
FROM projects
WHERE qualified = ‘YES’
UNION
SELECT id
FROM projects
WHERE qualified = ‘NO’
AND reference_id IN 
  (SELECT id
   FROM projects
   WHERE qualified = ‘YES’)

Is there a way to change the SQL based on a parameter? I couldn’t figure out how to do this with the select expert since it involves a UNION and an IN statement.

Thanks!

 

[synapsevampire]

Not in CR 8.5.

You can do this using a Stored Procedure though, they're very easy to create.

Another ugly alternative would be to have a subreport, and if they select non qualified, then suppress the subreport, otherwise suppress everything in the main report and unsuppress the subreport and have the subreport use the qualified query.

In CR8.5 you cxan manually edit the SQL to add in a UNION query.

The latter is my least favorite solution though.

-k

 

[xsw1971]

Thanks for the feedback! I first tried subreports it took >8 minutes to run 2 months of data. I wrote a stored procedure and it works awesome!

Question on the SP - when I run the report, it prompts the user for all the parameters I need, but it also prompts the user for a value for the cursor. Is there any way to hide that parameter from the user and just force it send the null value through? Otherwise I have just put a statement in the prompting text area that says to leave that field blank. Please let me know.

Thanks again!