We currently have some reports that query a specific table, but one of our users wants the ordering to be different. I don't want to go through and fix each of the reports, because that's rather a mammoth task, and only one user has a problem here. Is there a way to have SELECT queries on a base table automagically SELECT from a view table instead of the base table based on the user making the query? It would be a bonus if I can also base it on the program making the connection. We're using MSSQL 2008.
SELECT * FROM myTable would instead be
SELECT * FROM myView if the user executing the query was
[user] and the program making the connection was
[reporting software name].
Note: The outer reporting software is an old version of Crystal Reports running some
massive ugly queries built by the query builder.
Some sample output would be:
Regular Guy: Other Guy: ItemCode ItemCode AAAAAA745 AAAAAA745 AAABBB672 AAACCC657 AAACCC657 BBBAAA766 BBBAAA766 BBBCCC383 BBBBBB838 AAABBB672 BBBCCC383 BBBBBB838 CCCBBB883 CCCBBB883
As we want to order all items with
SUBSTRING(ItemCode, 4,3) = 'BBB' at the bottom.