Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Dynamic SQL Based on Parameter selection

Status
Not open for further replies.

CrystalProgDev

Programmer
Oct 1, 2010
69
US
I need to generate Dynamic SQL Based on Parameter selection. Here is the scenario.

Parameters: Department ---- Values: A-Department, B-Department, C-Department

If user selects A-Department the SQL Should use Dept_A, Product_A and Sales_A Tables
If user selects B-Department the SQL Should use Dept_B, Product_B and Sales_B Tables
If user selects C-Department the SQL Should use Dept_C, Product_C and Sales_C Tables

We can do it by creating a stored procedure and call that stored procedure from Crystal. But Team is not agree with my advice. Is there any other way we can implement this in Crystal report? Please suggest. It is urgent.

Thank you in advance
 
Why the team is against the stored procedure? Can you use a command.

Seems like your database is not normalized. Best practice in this case is to normalize it first and then filter the normalized data.

SELECT col1,col2...
FROM (
select 'Dept_A' as Department, col1,col2 ... FROM Dept_A, Product_A, Sales_A ...
UNION
select 'Dept_B' as Department, col1,col2 ... FROM Dept_B, Product_B, Sales_B ...
UNION
select 'Dept_C' as Department, col1,col2 ... FROM Dept_C, Product_C, Sales_C ...
) t
WHERE t.Department = {?Department} and .... the other parameters here

This might be slower compared to an "if" clause , which is checking the Department value and running a separate query for each case

Viewer and Scheduler for Crystal reports and SSRS.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top