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
Post a Comment