sql compare problem

[zubz]

Hi,

I have the following situation. In our database we have a reports table that stores reports and role ids.
Basically this table shows what roles can view what reports.


ReportID roles_allowed
12 MIS,MANAGER,ACCOUNTS,DP
14 ACCOUNTS,MIS,DP
15 CSRUP,MANAGER

Now when a user logs in his roles are set in a comma delimited list. such as MIS,ACCOUNTS etc. this is set as a session variable.

Now what i want is to do a compare of his user roles set in the seesion to the rows (the roles_allowed column) in the table
and bring back any records that contains any of the roles matched in the roles session variables

So if person A logs and his session role variable is MIS,DP it will bring back report IDS 12 and 14 since MIS and DP are contained in both these reports.

I'm doing something like the following but it doesn't seem to work

SELECT
report_id,category,name,description,max_instance,
last_run_date,roles_allowed,access_level,service_center,
fuseaction ,created_by,created_date,amended_by,
amendment_date,user_comment
FROM Report
WHERE 1=1
AND UPPER(trim(roles_allowed)) like '%MIS,DP%'

can anyone help me please?

thanks

 

[SQLDenis]

>>Now when a user logs in his roles are set in a comma delimited list. such as MIS,ACCOUNTS etc. this is set as a session variable.


BAD, BAD, BAD....what a horrible design, you do understand that what you have will never ever be able to use an index. You have 2 choices fix it now or fix it later when your system is sooooo slow that everyone will be conmplaining about the performance

another performance breaker

AND UPPER

why do you need upper, is your DB case sensitive? don't you know that using an UPPER function in the WHERE clause is bad for performance (table/index scan instead of seek)



please normalize your design and create a lookup for the roles

here is a kludge but I would seriously think about fixing the design

AND roles_allowed like '%MIS%'
OR roles_allowed like '%DP%'

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[r937]

denis, i think it should be AND-AND, not AND-OR

also, while it might not be relevant in this instance, any time you are dealing with finding a value in a comma-delimited list, you need to delimit the values in the search

AND ','+UPPER(trim(roles_allowed))+',' like '%,MIS,%'
AND ','+UPPER(trim(roles_allowed))+',' like '%,DP,%'

appending commas fore and aft will ensure that you don't find false positives such as "SQLServer" when searching for "SQL"

r937.com | rudy.ca

 

[SQLDenis]

>>denis, i think it should be AND-AND, not AND-OR


based on this data yes, however if there is a role containing this

19 DP

then is should be OR

It is up to the OP to decide

You are right about the comma thing, however that will perform badly since it can not use an index (nothing to do with the comma, it is the % operator that the value starts with)

This thing needs to be redesigned!

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[zubz]

Hi all,

thanks for the replies. yes i do agree the db should be redesigned. However I am just the developer here :-(. Will pass on the comments to our DBA here!