PLSQL Tree structured query - expression build and display

select * from (

(select A.rule_id, A.expression_id, A.operator,a.parent_expression_id, B.section_id, B.field_id, B.attribute_name, C.expression_value
from apb_rules.apb_rule_details A, apb_rules.apb_expression B, apb_rules.apb_expression_data C
where A.rule_id = 'GR207'
and A.expression_id = B.expression_id
and B.expression_id = C.expression_id
UNION
select A.rule_id, A.expression_id, A.operator,a.parent_expression_id, '', '', '', '' from apb_rules.apb_rule_details A
where A.rule_id = 'GR207'
and (A.operator = 'OR' or A.operator = 'AND')) order by 1,4 NULLS FIRST, 2)

output: the above SQL Statement which produced this data:

RULE_ID|EXPRESSION_ID|OPERATOR|PARENT_EXPRESSION_ID|SECTION_ID|FIELD_ID|ATTRIBUTE_NAME|EXPRESSION_VALUE
GR207|2334|AND|||||
GR207|2335|<>|2334|||0030.STATE-CODE|CA
GR207|2336|<>|2334|||0030.STATE-CODE|FL
GR207|5274|ISEMPTY|2334|||BundleTracker|


Can you please provide a PL/SQL code for getting the following different output forms:
--1) For a given ruleId, find out number of parent expression id's
--2) For each expressionid belongs to that parent expressionid, get expressionid, operator, sectio id, fieldid, attribute_name, expression value
--3) ( 2335 AND 2336 AND 5274 )
--4) ( (0030.STATE-CODE <> CA ) AND ( 0030.STATE-CODE <> FL) AND (BundleTracker ISEMPTY) )


Question #1 is easy to answer and you don't need to use Oracle PL/SQL for that. Using plain old SQL is easier and quicker for this and as you avoid any context switching from SQL to pl/sql and back again, Oracle will run it much faster.

Judging from the supplied query, ruleId and parent expression id are both columns in the table called apb_rule_details, so obtaining a count of the number of parent expressions for a given rule just requires the use of a simple GROUP BY query.

SELECT rule_id
     ,COUNT( parent_expression_id)
FROM apb_rule_details
GROUP BY rule_id


The above query will list all rule_ids and the number of parent expressions for each. To make it specific for just one rule you just need to add a where clause.

Question #2 just requires use of the Oracle SQL CONNECT BY clause to connect each child row to its parent row and so convert the query to a hierarchical query.

select A.rule_id
,A.expression_id
,A.operator
,a.parent_expression_id
,B.section_id
,B.field_id
,B.attribute_name
,C.expression_value
from apb_rules.apb_rule_details A
, apb_rules.apb_expression B
, apb_rules.apb_expression_data C
where A.rule_id = 'GR207'
and A.expression_id = B.expression_id
and B.expression_id = C.expression_id
CONNECT BY PRIOR a.expression_id=a.parent_expression_id


Not having any data to test the query with we can't be certain that it will work but we can demonstrate the principle using the employees and departments tables in the hr schema that is in the sample Oracle database.

select first_name||' '||last_name employee
,departments.department_name department
from employees
, departments
where employees.department_id = departments.department_id
connect by prior employees.employee_id = employees.manager_id


The above query produces the following output:

EMPLOYEE DEPARTMENT
Neena Kochhar, Executive
Nancy Greenberg, Finance
Daniel Faviet, Finance
Ismael Sciarra, Finance
Jose Manuel Urman, Finance
Luis Popp, Finance
John Chen, Finance
Hermann Baer, Public Relations
Shelley Higgins, Accounting
William Gietz, Accounting
Susan Mavris, Human Resources
Jennifer Whalen, Administration
Lex De Haan, Executive
Alexander Hunold, IT
Bruce Ernst, IT
...
Curtis Davies, Shipping


Questions 3 and 4 unfortunately don't make sense so we can't help there.

You can learn more about hierarchical queries and using the CONNECT BY clause in the Click here for more help with SQL

For information about Oracle SQL and PL/SLQ training courses, both classroom-based and on-line, see the training page.

Click here to post comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.