| mutually exclusive query [message #611672] |
Sun, 06 April 2014 23:41  |
ravikanth_b
Messages: 42
Registered: November 2007
Location: Bay Area, CA
|
Member |
|
|
Hello All,
Could you please help me with this problem.
CREATE TABLE TEST_VERSION(VERSION_ID NUMBER,SUBVERSION VARCHAR2(100));
insert into TEST_VERSION VALUES(1,null);
insert into TEST_VERSION VALUES(1,'Alpha');
insert into TEST_VERSION VALUES(1,'Internal Beta');
insert into TEST_VERSION VALUES(1,'Technical Preview');
insert into TEST_VERSION VALUES(1,'RTM');
insert into TEST_VERSION VALUES(2,null);
insert into TEST_VERSION VALUES(2,'Alpha');
insert into TEST_VERSION VALUES(3,'Internal Beta');
insert into TEST_VERSION VALUES(4,'Technical Preview');
SELECT * FROM TEST_VERSION;
select rules:
for given version_id:
1) if there is a SUBVERSION='RTM' row(s) in the set and also a row with SUBVERSION= NULL ignore the
row with SUBVERSION= NULL and select all the other rows including RTM
2) if there is a row SUBVERSION= NULL and there are no rows with SUBVERSION='RTM' then pick all the
rows including SUBVERSION=NULL
In essence, row with SUBVERSION= NULL cannot not co-exist with SUBVERSION='RTM' for the same
version_id. They are mutually exclusive. RTM has higher priority.
|
|
|
|
|
|
| Re: mutually exclusive query [message #611675 is a reply to message #611674] |
Mon, 07 April 2014 00:21  |
 |
Littlefoot
Messages: 21809
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Here's one option (not too smart, but - until someone provides something better, have a look):
SQL> set verify off
SQL> col subversion format a20
SQL>
SQL> with rtms as
2 (select version_id, sum(decode(subversion, 'RTM', 1, 0)) cnt
3 from test_version
4 group by version_id
5 )
6 select t.*
7 from test_version t,
8 rtms r
9 where t.version_id = &&par_version_id
10 and t.version_id = r.version_id (+)
11 and r.cnt > 0
12 and t.subversion is not null
13 union
14 select t.*
15 from test_version t,
16 rtms r
17 where t.version_id = &&par_version_id
18 and t.version_id = r.version_id (+)
19 and r.cnt = 0;
Enter value for par_version_id: 1
VERSION_ID SUBVERSION
---------- --------------------
1 Alpha
1 Internal Beta
1 RTM
1 Technical Preview
SQL> undefine par_version_id
SQL> /
Enter value for par_version_id: 2
VERSION_ID SUBVERSION
---------- --------------------
2 Alpha
2
SQL> |
|
|
|
| Re: mutually exclusive query [message #611680 is a reply to message #611675] |
Mon, 07 April 2014 01:41 |
ravikanth_b
Messages: 42
Registered: November 2007
Location: Bay Area, CA
|
Member |
|
|
Following would work:
select VERSION_ID,SUBVERSION from (
select t.*,
sum(case when SUBVERSION is null then 1 when SUBVERSION = 'RTM' then 1 end) over (partition by VERSION_ID) flag
from TEST_VERSION t
)
where nvl(SUBVERSION,'-1') != (case when flag >= 2 then '-1' else '1' end);
[Updated on: Mon, 07 April 2014 01:41] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
