| Find Out Odd Range [message #645483] |
Mon, 07 December 2015 08:30  |
Xandot
Messages: 235
Registered: January 2014
Location: India
|
Senior Member |
|
|
Hi All,
Please help me out ..
with t as
(select 1 ID, 1 Address, 2 Num from dual
union all
select 2 ID, 3 Address, 3 Num from dual
union all
select 3 ID, 7 Address, 6 Num from dual
union all
select 4 ID, 14 Address, 10 Num from dual
union all
select 5 ID, 17 Address, 3 Num from dual)
select * from t
I want to find out the odd range of address which belongs to other address.
Logic : if Address=14 and Num=10 then 14 to 24 (add+num) is allowed only in row num 4 in address column.
so I want to find out which address's are in not in range or in others range.
For Example: In row number 5 .. address=17 and num=3 but address 17 is occupied by row number 4 so
if I find out is there any other address is present in table which is in between the range of (17-18-19-20) then the output will be like
Output:
This row will be return because its in range of 17 to 20.
Thanks,
Xandot
|
|
|
|
|
|
|
|
| Re: Find Out Odd Range [message #645525 is a reply to message #645494] |
Tue, 08 December 2015 01:56  |
Xandot
Messages: 235
Registered: January 2014
Location: India
|
Senior Member |
|
|
Hi Michel,
You can take any row, I just took row=4 because its proper case.
Till now I tried this:
with t as
(select 1 ID, 1 Address, 2 Num from dual
union all
select 2 ID, 3 Address, 3 Num from dual
union all
select 3 ID, 7 Address, 6 Num from dual
union all
select 4 ID, 14 Address, 10 Num from dual
union all
select 5 ID, 17 Address, 3 Num from dual)
select * from t
where address <> 14
and ((address >= 14 and address <= (14 + 10 ))
or ((address + 10) ) >= 14 and
(address + 10 ) <= (14 + 10 ));
but its returning two rows (Id= 3,5) but it should be return only 5 row. Please help me out .
Thanks
Xandot
|
|
|
|
|
|
| Re: Find Out Odd Range [message #645535 is a reply to message #645528] |
Tue, 08 December 2015 03:45 |
Xandot
Messages: 235
Registered: January 2014
Location: India
|
Senior Member |
|
|
I want to find out the row which under the range of another rows.
Its not mandatory to have only one row, if the number in range of another row then it should be return those rows also.
for example: Address = 14 and Num = 10 then range = 14 to 24 (14+10)
Output should return those rows which fall under the range of 14 to 24.
Is there any other way to check range ?
Thanks
Xandot
|
|
|
|
|
|
| Re: Find Out Odd Range [message #645578 is a reply to message #645539] |
Wed, 09 December 2015 01:26 |
Xandot
Messages: 235
Registered: January 2014
Location: India
|
Senior Member |
|
|
Yes, I understood your point.
I have another example and its working only in one direction :
with t as
(select 1 ID, 405 Address, 3 Num from dual
union all
select 2 ID, 399 Address, 10 Num from dual)
select * from t
where address <> 399
and ((address >= 399 and address <= (399 + 10 ))
or ((address + 3 ) ) >= 399 and
(address + 3 ) <= (399 + 10 ));
but when I select Address=405 then the condition fails. Can anyone please help me ..how to create generic search ?
Not Working:
with t as
(select 1 ID, 405 Address, 3 Num from dual
union all
select 2 ID, 399 Address, 10 Num from dual)
select * from t
where address <> 405
and ((address >= 405 and address <= (405 + 3 ))
or ((address + 10 ) ) >= 405 and
(address + 10 ) <= (405 + 3 )); -- condition fails (409 <= 407)
Thanks
Xandot
[Updated on: Wed, 09 December 2015 01:27] Report message to a moderator |
|
|
|
| Re: Find Out Odd Range [message #645587 is a reply to message #645578] |
Wed, 09 December 2015 04:47 |
Xandot
Messages: 235
Registered: January 2014
Location: India
|
Senior Member |
|
|
Thanks Michel Cadot .. I got the solution. 
with t as
(select 1 ID, 405 Address, 3 Num from dual
union all
select 2 ID, 399 Address, 10 Num from dual
union all
select 4 ID, 396 Address, 5 Num from dual
union all
select 3 ID, 408 Address, 3 Num from dual)
select * from t
where address <> 405
and (
((address >= 405 and address <= (405 + 3 ))
or address >= 405 and (address + NUM ) <= (405 + 3)
or (address + NUM ) >= 405 and (address) <= (405 + 3)
or (address + NUM ) >= 405 and (address + NUM) <= (405 + 3))
) ;
[Updated on: Wed, 09 December 2015 04:48] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
