SQL - Sav role Features and Web Services

 SELECT sv.rolename    AS "Role Name",

       'Feature'      AS "Type of Access",
       fr.featurename AS "Feature",
       fr.description AS "Description"
FROM   savrole_feature svf
       INNER JOIN savroles sv
               ON sv.rolekey = svf.rolekey
       INNER JOIN feature fr
               ON fr.featureid = svf.featureid
UNION
SELECT Trim(Substring_index(Substring_index(rm.configattribute, ',', n.n), ',',
            -1)) AS
       "Role Name",-- Extract each role name
       'WebService'
       AS "Type of Access",
       rm.url
       AS "Feature",
       rm.description
       AS "Description"
FROM   requestmap rm
       INNER JOIN (SELECT a.n + b.n * 10 + 1 n
                   FROM   (SELECT 0 AS N
                           UNION
                           SELECT 1
                           UNION
                           SELECT 2
                           UNION
                           SELECT 3
                           UNION
                           SELECT 4
                           UNION
                           SELECT 5
                           UNION
                           SELECT 6
                           UNION
                           SELECT 7
                           UNION
                           SELECT 8
                           UNION
                           SELECT 9) a
                          CROSS JOIN (SELECT 0 AS N
                                      UNION
                                      SELECT 1
                                      UNION
                                      SELECT 2
                                      UNION
                                      SELECT 3
                                      UNION
                                      SELECT 4
                                      UNION
                                      SELECT 5
                                      UNION
                                      SELECT 6
                                      UNION
                                      SELECT 7
                                      UNION
                                      SELECT 8
                                      UNION
                                      SELECT 9) b
                   ORDER  BY n) n
               ON Char_length(rm.configattribute) - Char_length(
                  Replace(rm.configattribute, ',',
                             '')) >= n.n
                  - 1
WHERE  rm.description <> 'ALLACCESS'
       AND rm.configattribute IS NOT NULL; 

Comments

Popular posts from this blog

Enhanced Query - To Update Account Status and Name

Connection Config - STATUS_THRESHOLD_CONFIG

SQL - Connection table