Generate fixed tuples for each tuple of a result

Question

Generate fixed tuples for each tuple of a result

Navigation

#1 by (0 votes)

0

I have 3 tables: areas , roles and personas . I generate a hierarchy based on them where personas hang from their role, each role hangs from its area , and all areas of a fixed tuple. The basic query that I leave as an example would be:

SELECT 'U' AS ID, NULL AS ID_PADRE, 'PADRE DE TODOS' AS DESCRIPCION FROM DUAL

UNION ALL 

SELECT ('A_' || ID_AREA) AS ID, 'U' AS ID_PADRE, DESCRIPCION_AREA AS DESCRIPCION FROM AREAS

UNION ALL

SELECT ('R_' || ID_ROL) AS ID, ('A_' || ID_AREA_ROL) AS ID_PADRE, DESCRIPCION_ROL AS DESCRIPCION FROM ROLES R

UNION ALL

SELECT ('P_' || ID_PERSONA) AS ID, ('R_' || ID_ROL_PERSONA) AS ID_PADRE, NOMBRE AS DESCRIPCION FROM PERSONA

And it generates me a hierarchy of this type (people are not seen, but would hang from each of the roles that are underneath everything, such as " Driver ", " Nurse

sql oracle

asked by Genarito 17.08.2017 в 22:09

source

1 answer

Error executing two procedures in Codeigniter How to select an element of the DOM for its id in ionic2

score 0 · Accepted Answer

I solved it by changing:

SELECT ('P_' || ID_PERSONA) AS ID, ('R_' || ID_ROL_PERSONA) AS ID_PADRE, NOMBRE AS DESCRIPCION FROM PERSONA

By:

SELECT ('P_' || ID_PERSONA) AS ID, ('R_' || ID_ROL_PERSONA) AS ID_PADRE, NOMBRE AS DESCRIPCION FROM PERSONA
WHERE ID_ROL_PERSONA IS NOT NULL

And I added the following two questions:

-- Los "Sin roles para cada area"
SELECT ('RN_' || ID_AREA_PERSONA) AS ID, ('A_' || ID_AREA_PERSONA) AS ID_PADRE, "Sin rol" AS DESCRIPCION FROM PERSONA
WHERE ID_ROL_PERSONA IS NULL
GROUP BY ID_AREA_PERSONA

-- Los trabajadores sin roles
SELECT ('P_' || ID_PERSONA) AS ID, ('RN_' || ID_AREA_PERSONA) AS ID_PADRE, NOMBRE AS DESCRIPCION FROM PERSONA
WHERE ID_ROL_PERSONA IS NULL