1

I have two tables. One table is a list of access points. The other is a list of who has access to what. It use to be a person had access to either one thing or all things. But, now that has changed. Now someone might have access to several points. The wrench in the system is that in the column that shows what they have access to may have a single value, "ALL" for all access or a comma separated list (which is new). I originally thought I could just do WHERE Access_To Here IN(), but I am unsure how to convert the value of Access_To to a formatted list. I need to be able to do this as a single query since I am using it for a LOV in APEX. So, I need some help.

The Access_Points_Table has only one column, Access_Points. Here are some example values that it might have:

CSX
CZR
XR3
NBO
QHG

The Users_List table has several columns, but the most important are User_Name, Access_To. Here are some example values that it might have:

Joe | ALL
Fred | CSX
Allen | CZR, NBO
Hank | QHG

Here is query I am currently using, but it only works if there is only a single value in the Access_To column. 

SELECT DISTINCT Access_Points VALUE
FROM Access_Points_Table apt
JOIN
Users_List ul
ON (ul.wwid = 'ZZ999'
AND (ul.Access_To = 'ALL' OR apt.symbol_name = ul.Access_To))
ORDER BY name ASC

What I am trying to accomplish is:

SELECT DISTINCT Access_Points VALUE
FROM Access_Points_Table apt
JOIN
Users_List ul
ON (ul.wwid = 'ZZ999'
AND (ul.Access_To = 'ALL' OR apt.symbol_name IN(Something Goes Here)))
ORDER BY name ASC

So, if run a query for the user Allen, it will return the rows:

CZR
NBO
David Faber
  • 12,277
  • 2
  • 29
  • 40
Soren
  • 797
  • 5
  • 15
  • 32
  • 2
    Storing comma separated values is a sign of bad design. Also, `in` won't won't work on them. – GolezTrol Jan 26 '15 at 21:38
  • possible duplicate of [Convert comma separated string to array in PL/SQL](http://stackoverflow.com/questions/3819375/convert-comma-separated-string-to-array-in-pl-sql) – GolezTrol Jan 26 '15 at 21:41
  • Damn. Pasted the wrong link. I meant: "Duplicate of [Oracle 10g Splitting string into multiple rows](http://stackoverflow.com/questions/14328621/oracle-10g-splitting-string-into-multiple-rows)". – GolezTrol Jan 26 '15 at 21:42
  • 1
    Maybe it is a bad design, but unfortunately, I'm not the DBA, just the developer. – Soren Jan 26 '15 at 23:06
  • Sorry to hear that. This is an error a developer could make, but a dedicated DBA who knows about normalization shouldn't. Anyway, I think you can find the answer in the links I provided. – GolezTrol Jan 26 '15 at 23:36

2 Answers2

0

Depending on the length of your comma-delimited data, you can use Oracle's regular expression engine (the difficulty is that Oracle limits regular expressions to 512 bytes):

SELECT DISTINCT Access_Points VALUE
  FROM Access_Points_Table apt
  JOIN Users_List ul
    ON ( ul.wwid = 'ZZ999'
   AND ( ul.Access_To = 'ALL'
      OR REGEXP_LIKE(apt.symbol_name, '^(' || REPLACE(ul.Access_To, ',', '|') || ')$') ) )
 ORDER BY name ASC

Alternately you can use LIKE:

SELECT DISTINCT Access_Points VALUE
  FROM Access_Points_Table apt
  JOIN Users_List ul
    ON ( ul.wwid = 'ZZ999'
   AND ( ul.Access_To = 'ALL'
      OR ',' || ul.Access_To || ',' LIKE '%,' || apt.symbol_name || ',%' ) )
 ORDER BY name ASC

Note that if Access_To has spaces after its commas as in your OP, it does add some complexity but that can be overcome, simply REPLACE(ul.Access_To, ' ').

By the way, I do wonder why this: ul.wwid = 'ZZ999' is in the ON clause instead of in a WHERE clause.

David Faber
  • 12,277
  • 2
  • 29
  • 40
0

Another way would be to use the instr test:

SELECT DISTINCT access_points VALUE FROM access_points_table a JOIN users_list u ON ( INSTR(u.access_to,a.access_points,1) > 0 ) ORDER BY 1

JohnFL
  • 52
  • 3