October 26, 2010 at 5:24 pm
Good afternoon. Hoping someone can assist.
I'm working on a database of databases in SQL Server 2008. Two of the tables in the database are DatabaseProduct and DatabaseVersion.
DatabaseProduct has a row for each product (DatabaseProductID,Name).
1,SQL Server
2,Oracle
3,MySQL
DatabaseVersion has a row for each version (DatabaseVersionID, DatabaseProductID, Version)
1,1,2000
2,1,2005
3,1,2008
4,1,2008R2
5,2,10G
6,2,11G
7,3,5.1.51
I would like is to have a query based on a variable like @Version. From the variable's value be able to select either a single database version or all versions of a specific product.
For instance if @Version = 1 then
SELECT DP.Name, DV.Version
FROM DatabaseProduct DP
JOIN DatabaseVersion DV
ON DP.DatabaseProductID = DV.DatabaseProductID
WHERE DV.DatabaseVersionID = '1'
but if @Version = ALLSQLSERVER then
SELECT DP.Name, DV.Version
FROM DatabaseProduct DP
JOIN DatabaseVersion DV
ON DP.DatabaseProductID = DV.DatabaseProductID
WHERE DV.DatabaseProductID = '1'
So I thought to write something like this but it isn't working.
SELECT DP.Name, DV.Version
FROM DatabaseProduct DP
JOIN DatabaseVersion DV
ON DP.DatabaseProductID = DV.DatabaseProductID
CASE
WHEN @Version = 'ALLSQLSERVER' THEN WHERE DP.DatabaseProductID = '1'
WHEN @Version = 'ALLORACLE' THEN WHERE DP.DatabaseProductID = '2'
WHEN @Version = 'ALLMYSQL' THEN WHERE DP.DatabaseProductID = '3'
ELSE
WHERE DV.DatabaseVersionID = @Version
END
The WHERE can't start with CASE but I can't think of any other way to structure this query to accomplish what I'm looking for.
Can the WHERE clause be dynamic based on the value of a variable?
Thanks for helping.
Howard
October 26, 2010 at 5:38 pm
Try:
SELECT DP.Name, DV.Version
FROM DatabaseProduct DP
JOIN DatabaseVersion DV
ON DP.DatabaseProductID = DV.DatabaseProductID
WHERE
CASE @Version WHEN 'ALLSQLSERVER' THEN '1'
WHEN 'ALLORACLE' THEN '2'
WHEN 'ALLMYSQL' THEN '3
ELSE @Version END =
CASE @Version WHEN 'ALLSQLSERVER' THEN '1'
WHEN 'ALLORACLE' THEN '2'
WHEN 'ALLMYSQL' THEN '3'
ELSE DV.DatabaseVersionID END
I'd ask you to double check is your data type on DatabaseProductID and DatabaseVersionID, are they really character fields or ints? You'll need to cast them to the proper datatype as @version. Also, I would suggest that instead of using the text strings of 'ALLSQLSERVER', etc, to indicate that you want to return all of the versions for that product - you should send in two parameters, one for the database version and one for the database product. If the database version is null, then you can just use the database product id to return all of those versions for that product.
HTH,
Marcy
October 26, 2010 at 5:43 pm
Thanks Marcy, I'll give this a try.
You are right, the datatypes are int so 'ALLSQLSERVER' will not work. I was doing the query in my head and didn't think of that.
Since I'll never have 10000 versions, I could use some arbitrary number like @Version = '10000' for all SQL Server and '20000' for all Oracle.
October 26, 2010 at 5:55 pm
Marcy, I think it is close to working.
Using your example, I tried to do it this way:
declare @Version int
set @Version = '10000'
SELECT DP.Name, DV.Version
FROM DatabaseProduct DP
JOIN DatabaseVersion DV
ON DP.DatabaseProductID = DV.DatabaseProductID
WHERE
CASE @Version WHEN '10000' THEN '1'
WHEN '20000' THEN '2'
WHEN '30000' THEN '3'
ELSE @Version END =
CASE @Version WHEN '10000' THEN '1'
WHEN '20000' THEN '2'
WHEN '30000' THEN '3'
ELSE DV.DatabaseVersionID END
If my @Version is an actual DatabaseVersionID then I get the correct single record returned. If I specify a nonexistent DatabaseVersionID like 999, I get no results. If I specify 10000, 20000, or 30000 then I get all results instead of only the results for the one product.
October 26, 2010 at 6:27 pm
PHXHoward (10/26/2010)
Marcy, I think it is close to working.Using your example, I tried to do it this way:
declare @Version int
set @Version = '10000'
SELECT DP.Name, DV.Version
FROM DatabaseProduct DP
JOIN DatabaseVersion DV
ON DP.DatabaseProductID = DV.DatabaseProductID
WHERE
CASE @Version WHEN '10000' THEN '1'
WHEN '20000' THEN '2'
WHEN '30000' THEN '3'
ELSE @Version END =
CASE @Version WHEN '10000' THEN '1'
WHEN '20000' THEN '2'
WHEN '30000' THEN '3'
ELSE DV.DatabaseVersionID END
If my @Version is an actual DatabaseVersionID then I get the correct single record returned. If I specify a nonexistent DatabaseVersionID like 999, I get no results. If I specify 10000, 20000, or 30000 then I get all results instead of only the results for the one product.
The where clause I highlighted is the issue. You're setting 1 = 1, 2=2... etc.
What Marcy Recommended was more like this:
declare @Version int,
@product INT
SELECT @Version = NULL,
@product = 1
SELECT DP.Name, DV.Version
FROM
DatabaseProduct DP
JOIN
DatabaseVersion DV
ONDP.DatabaseProductID = DV.DatabaseProductID
WHERE
DV.DatabaseVersionID = ISNULL( @Version, DV.DatabaseVersionID)
AND DP.DatabaseProductID = @product
No, not the best optimization, but your system is small enough to not care, and it's easy to maintain. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 26, 2010 at 7:11 pm
Thanks for responding. Your example is very good.
I should explain a little better. This query will go into SSRS so I think I am limited to a single variable which is set through a drop down list parameter.
Is there any way to accomplish this with a single variable?
October 27, 2010 at 12:05 pm
You can definitely use two parameters in SSRS. You would just end up with two drop downs, and they could even been cascading so the user selects the Database first and then is presented with a filtered list of Database versions to select from. You would set the database version parameter to allow nulls and then you could use the code the person above had posted.
October 27, 2010 at 2:13 pm
Thanks, I'm eager to try it. That scenario would be perfect.
October 27, 2010 at 3:19 pm
This worked. Thanks a lot. There are now two drop down lists to set the parameters in SSRS but it seems to be working. Now I have to find how to make the available choices in the second box dependent on what is selected in the first box.
October 28, 2010 at 11:55 am
Cascading parameters are really easy, just send in your first parameter of Database product to your query that supplies the list for the Database version parameter. Also make sure that your parameters are listed in order of Database product first and then Database version. Here is the link to Microsoft's tutorial on cascading parameters: http://msdn.microsoft.com/en-us/library/aa337460(v=SQL.100).aspx.
October 28, 2010 at 11:56 am
Thank you. I'll take a look.
Howard
October 28, 2010 at 12:21 pm
When I have a cascading parameter, I understand that when the first is selected, the query must run to generate the list for the second parameter. Does it have to blank the report when it runs that second query? Possibly it is a limitation of web display?
October 28, 2010 at 1:41 pm
Thanks for responding Celko.
I was trying to avoid typing out and possibly misspelling SQL Server, Oracle, MySQL over and over again by using the foreign key approach. In this case, is it best to put product and version in the same table?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply