5 Replies - 343 Views - Last Post: 05 September 2017 - 10:00 AM Rate Topic: -----

#1 ikhlas06  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 178
  • Joined: 20-May 16

how to tell which 'or' statment it is hitting?

Posted 05 September 2017 - 08:19 AM

Using oracle;

how to tell which 'or' statment it is hitting?

I have a simple select statment with 4 cols. In 'where' caluse i have 3 'OR' statments (filters).
Is there a way to test which filter it is getting?

below are 3 filters i want in 'where' caluse of query
filter#1: col1 > 10 
filter#2: col3 > 20
filter#3: col2 > 20


I want this output, as you can see 'col4' will tell which or statment it is hitting:
|------|------|------|----------| 
| col1 | col2 | col3 | col4     |
|------|------|------|----------|  
|20    | 05   | 10   | filter#1 | 
|05    | 30   | 05   | filter#2 | 
|05    | 05   | 30   | filter#3 | 
|------|------|------|----------| 



this is the query I have. Any idea how to go about this problem?
select col1, col2, col3
from myTable
Where (col1 > 10) OR (col3 > 20) OR (col2 > 20)


Is This A Good Question/Topic? 0
  • +

Replies To: how to tell which 'or' statment it is hitting?

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 13483
  • View blog
  • Posts: 53,820
  • Joined: 12-June 08

Re: how to tell which 'or' statment it is hitting?

Posted 05 September 2017 - 08:25 AM

If you want to have the words 'filter1' or 'filter2' then think about using three case-when statements.
Was This Post Helpful? 1
  • +
  • -

#3 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 4136
  • View blog
  • Posts: 13,051
  • Joined: 08-June 10

Re: how to tell which 'or' statment it is hitting?

Posted 05 September 2017 - 08:41 AM

Quote

I want this output, as you can see 'col4' will tell which or statment it is hitting:

what do you display if multiple filters are hitting?
Was This Post Helpful? 1
  • +
  • -

#4 ikhlas06  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 178
  • Joined: 20-May 16

Re: how to tell which 'or' statment it is hitting?

Posted 05 September 2017 - 09:21 AM

View PostDormilich, on 05 September 2017 - 08:41 AM, said:

Quote

I want this output, as you can see 'col4' will tell which or statment it is hitting:

what do you display if multiple filters are hitting?




some thing like 'filter#1-Filter#2'. Wording does not have to be 'filter#1' is can be just a number '1'.

I did think of "case-when-then-end' but than problem will be if it is hitting multi-filters.
In this query if it hits first filter than it will end.

My best bet is to do some thing like '+=' (in java or C) but I am not sure how to do this in oracle queries


select col1, col2, col3, 
(
case
when (col1 > 10) THEN ('1')
WHEN (col3 > 20) THEN ('3')
WHEN (col2 > 20) THEN ('2') 
end 
)
from myTable
Where (col1 > 10) OR (col3 > 20) OR (col2 > 20)


but issue with this query is that
Was This Post Helpful? 0
  • +
  • -

#5 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon


Reputation: 6979
  • View blog
  • Posts: 14,599
  • Joined: 16-October 07

Re: how to tell which 'or' statment it is hitting?

Posted 05 September 2017 - 09:44 AM

Perhaps
select a.* from (
  select col1, col2, col3, 
      (case when (col1 > 10) THEN 1 WHEN (col3 > 20) THEN 3 WHEN (col2 > 20) THEN 2 else 0 end ) as filter
    from myTable
    ) a
  where a.filter<>0


Was This Post Helpful? 0
  • +
  • -

#6 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon


Reputation: 6979
  • View blog
  • Posts: 14,599
  • Joined: 16-October 07

Re: how to tell which 'or' statment it is hitting?

Posted 05 September 2017 - 10:00 AM

Wait, oracle?

Ok, just for fun, bits?
select col1, col2, col3,
    decode(bitand(filter,1),1,'X','-') as filter1,
    decode(bitand(filter,2),2,'X','-') as filter2,
    decode(bitand(filter,4),4,'X','-') as filter3
  from (
    select col1, col2, col3,
        0 
        + (case when (col1 > 10) THEN 1 else 0 end)
        + (case when (col2 > 20) THEN 2 else 0 end)
        + (case when (col3 > 20) THEN 4 else 0 end)
        as filter
      from (
        select 100 as col1, 0 as col2, 30 as col3 from dual
        union select 0, 45, 2 from dual
        union select 20, 45, 2 from dual
        union select 2, 4, 2 from dual
        union select 2, 4, 22 from dual
        )
    )
  where filter<>0




Sorry, had to throw in test data to make sure it would work.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1