January 13, 2011 at 12:31 pm
Hoping someone can help with this.
I have a function that get's the user name. I want to filter the data for one user, but not for others. I have other similar conditions I'd like to do this way in the same query.
The variable @pg is being evaluated correctly. If I run the query as user 'jtaylor', then the query correctly yields results. However if it does not, even though @pg does show as evaluating to "[product group id]", the query results in no data. However if I change the where clause to be "WHERE [product group id] = [product group id]" then I do get data.
I'm assuming it just doesn't work the way I want it to? Is there an easy to deal with this? Thanks for any direction!
DECLARE @pg as varchar(20)
SET @PG = CASE WHEN dbo.getusername() = 'jtaylor' then '4600' ELSE '[product group id]' END
SELECT top 10 Customer, sum(Profit) Profit
FROM sales_history
WHERE [product group id] = @pg
GROUP BY customer
Order by sum(profit) desc
January 13, 2011 at 1:20 pm
Disregard. I suppose I'm still curious about this. But as I started thinking through some of my other conditions I realized that this would be more complex, so I solved with unioned data.
January 13, 2011 at 2:37 pm
The problem is that your SET expression is returning text, not a column name. If you want to return a column you should rewrite your query as follows:
SELECT top 10 Customer, sum(Profit) Profit
FROM sales_history
WHERE [product group id] = CASE WHEN dbo.getusername() = 'jtaylor' then '4600' ELSE [product group id] END
GROUP BY customer
Order by sum(profit) desc
Note that you can't use the SET expression, because it would only return the last value. Dropping the single quotes returns the column instead of static text.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply