Passing a field reference into the where clause

  • 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

  • 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.

  • 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