Akshobhya Akshobhya - 5 months ago 13
SQL Question

Recursively query in oracle

I have two tables in oracle for getting the software versions

RequiredVersion Table
major minor maintenance requiredversion
20 0 0 20.0.1
20 0 1 20.0.3
20 0 3 null
20 0 4 null
20 0 2 20.0.5
20 0 5 null
20 0 6 null

OptimumVersion Table
major minor maintenance optimumver
20 0 0 20.0.2
20 0 2 20.0.6
20 0 1 20.0.4


User will send the input 20.0.0 for this version I am splitting and comparing with major minor and maintenance in both the tables. how can I get all the dependencies i.e required versions and optimum version

I/P 20.0.0
O/p 20.0.1
20.0.2
20.0.3
20.0.4
20.0.5
20.0.6


Each version I get may or may not have required and optimum version . I tried a lot using query but not getting how we can call in loop.Please help me to solve this issue.

Structure : 20.0.0
/ \
(reqver) 20.0.1 20.0.2 (optimumvers)
/ \ / \
20.0.3 20.0.4 20.0.5 20.0.6
(reqver) (optver) (req) (opt)


Thanks in advance

Answer

Sample data

-- Data preparation
CREATE TABLE REQUIRED_VERSION
  (
    MAJOR           NUMBER(3),
    MINOR           NUMBER(3),
    MAINTENANCE     NUMBER(3),
    REQUIREDVERSION VARCHAR2(11)
  );

CREATE TABLE OPTIMUM_VERSION
  (
    MAJOR          NUMBER(3),
    MINOR          NUMBER(3),
    MAINTENANCE    NUMBER(3),
    OPTIMUMVERSION VARCHAR2(11)
  );

-- Data
Insert into OPTIMUM_VERSION (MAJOR,MINOR,MAINTENANCE,OPTIMUMVERSION) values ('20','0','0','20.0.2');
Insert into OPTIMUM_VERSION (MAJOR,MINOR,MAINTENANCE,OPTIMUMVERSION) values ('20','0','2','20.0.6');
Insert into OPTIMUM_VERSION (MAJOR,MINOR,MAINTENANCE,OPTIMUMVERSION) values ('20','0','1','20.0.4');

Insert into REQUIRED_VERSION (MAJOR,MINOR,MAINTENANCE,REQUIREDVERSION) values ('20','0','0','20.0.1');
Insert into REQUIRED_VERSION (MAJOR,MINOR,MAINTENANCE,REQUIREDVERSION) values ('20','0','1','20.0.3');
Insert into REQUIRED_VERSION (MAJOR,MINOR,MAINTENANCE,REQUIREDVERSION) values ('20','0','3',null);
Insert into REQUIRED_VERSION (MAJOR,MINOR,MAINTENANCE,REQUIREDVERSION) values ('20','0','4',null);
Insert into REQUIRED_VERSION (MAJOR,MINOR,MAINTENANCE,REQUIREDVERSION) values ('20','0','2','20.0.5');
Insert into REQUIRED_VERSION (MAJOR,MINOR,MAINTENANCE,REQUIREDVERSION) values ('20','0','5',null);
Insert into REQUIRED_VERSION (MAJOR,MINOR,MAINTENANCE,REQUIREDVERSION) values ('20','0','6',null);

Query

SELECT DISTINCT DEPENDENCY
FROM (
  SELECT VERSION,DEPENDENCY
  FROM (
    SELECT MAJOR||'.'||MINOR||'.'||MAINTENANCE AS VERSION, REQUIREDVERSION AS DEPENDENCY FROM REQUIRED_VERSION
    UNION
    SELECT MAJOR||'.'||MINOR||'.'||MAINTENANCE AS VERSION, OPTIMUMVERSION AS DEPENDENCY FROM OPTIMUM_VERSION
  )
  START WITH VERSION = '20.0.0' -- Put your version number here
  CONNECT BY PRIOR DEPENDENCY = VERSION AND DEPENDENCY IS NOT NULL
)
ORDER BY DEPENDENCY ASC;

Solution consist of 3 nested queries, explained from deepest one.

  1. Concatenates major, minor, maintenance version with dots to make life easier in treating this concatenation same way as required version (which is varchar2). Unions the two tables into one (it's obviously table of dependencies split into two pieces by you)
  2. Hierarchical query - does what it sounds like. For every row which has dependency makes a "root" and loads it's dependency (if has any)
  3. Filters result to display each dependency only once (in case, they were two versions dependent on single other). Orders results in ascending order.