1

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

Improve this question
2
  • It is difficult to understand how table OptimumVersion takes influence on desired output. In your output it is simply print all rows in RequiredVersion in some order. Can you give us more complex example? Commented Jul 3, 2016 at 6:45
  • see let us say i/p is 20.0.0 we should check required version table major=20 , minor=0 and maintenance=0 . if there is any required version get that. like wise we should check in optimum version table major=20 , minor=0 and maintenance=0 and get the optimum version. for each req or opt vers we should follow the above steps Commented Jul 3, 2016 at 6:50

3 Answers 3

Reset to default
1

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.
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

distinct is NOT a function
0

Test data

WITH RequiredVersion(major, minor, maintenance, requiredversion) AS(
SELECT 20,0,0,'20.0.1' FROM dual
  UNION all
SELECT 20,0,1,'20.0.3' FROM dual
  UNION all
SELECT 20,0,3,null FROM dual
  UNION all
SELECT 20,0,4,null FROM dual
  UNION all
SELECT 20,0,2,'20.0.5' FROM dual
  UNION all
SELECT 20,0,5,null FROM dual
  UNION all
SELECT 20,0,6,null FROM dual),
OptimumVersion(major, minor, maintenance, optimumver) AS(
SELECT 20,0,0,'20.0.2' FROM dual
  UNION all
SELECT 20,0,2,'20.0.6' FROM dual
  UNION all
SELECT 20,0,1,'20.0.4' FROM dual)

Query

SELECT DISTINCT major, minor, maintenance FROM 
 (SELECT * FROM RequiredVersion UNION ALL SELECT * FROM OptimumVersion)
    CONNECT BY PRIOR requiredversion = major || '.' || minor || '.' || maintenance
    START WITH major = 20 and minor= 0 and maintenance = 0
  ORDER BY major, minor, maintenance

For me it is still difficult to understand logic. In this query I try union data from RequiredVersion and OptimumVersion and build hierarchy removing duplicate rows.

Improve this answer

1 Comment

Okay thank you. Let me work on this to get required data
0

Here is a solution using a recursive factored subquery. It uses the bind variable :inputversion as a mechanism to input the starting point (for example 20.0.0)

with 
     dep_version (version, dependentversion) as (
       select major || '.' || minor || '.' || maintenance, requiredversion
       from   required_version
       union all
       select major || '.' || minor || '.' || maintenance, optimumversion
       from optimum_version
     ),
     rec (dependentversion) as (
       select :inputversion from dual
       union all
       select d.dependentversion
       from   rec r inner join dep_version d 
                          on   r.dependentversion = d.version
       where  d.dependentversion is not null
     ) 
select dependentversion
from   rec
where  dependentversion != :inputversion
;
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.