How to return different statements in where clause ? (oracle)

oracle

I mean:

CREATE OR REPLACE FUNCTION FUNCTION1(ID IN NUMBER) RETURN NUMBER
BEGIN
SELECT COLUMN1, COLUMN2, COLUMN3 FROM TABLE1
WHERE COLUMN1<= Y AND (CASE WHEN ID < X THEN COLUMN1<COLUMN2 ELSE COLUMN2>COLUMN3 END),
AND COLUMN 3 = XYZ;

RETURN SOMETHING.

END;

I know case statement does not return an expression but a value. How can i use some kind of "conditional statements" or something to return expressions ? I use to do it easily in mysql with "IF conditional" but i don't know how to do it in Oracle 11g.
EDIT: Does it work the same, not only for where statemen but for select ?

Best Answer

Just use grouped WHERE conditions:

WHERE COLUMN1<= Y 
   AND ((ID < X AND COLUMN1<COLUMN2)
        OR(ID >= X AND COLUMN2>COLUMN3))
AND COLUMN 3 = XYZ;

Remember you really just want to get some boolean evaluations back here - as long as you can reduce your requirements to distinct sets of conditions you can put it into WHERE which will be plenty fast.

The time to use CASE is if you have overlapping criteria that you need to short circuit.

Related Solutions

Oracle – Select the Max from Two Max Values

Here's one possibility:

with departures(departure_id, cnt) as (
    select f.departure_id, count(t.id_passenger)
    from flight f
    join takes t
        on f.id = t.id_flight
    where year(departure_date) = ?
    group by f.departure_id
),
arrivals as (arrival_id, cnt) as (
    select f.arrival_id, count(t.id_passenger)
    from flight f
    join takes t
        on f.id = t.id_flight
    where year(arrival_date) = ?
    group by f.arrival_id
)
select position, max(cnt)
from (
    select arrival_id as position, cnt
    from arrivals
    union
    select departure_id as position, cnt
    from departures
) as t;

ORA-00937 – Calculating Sub-Value in Aggregate SELECT Statement

Your additional calculated value:

(select count(t1.num) from T t1)

Is a scalar subquery, which is a dynamic rather than static expression. As such it's treated the same as a column as far as the aggregate is concerned and needs to be included in the group by clause to avoid the ORA-00937: not a single-group group function error

However, oracle does not allow subqueries as part of the group by clause and trying to include the scalar subquery and/or the whole case statement:

group by (case (select count(*) cnt from t t1) when 0 then 1 else 0 end)

just results in an ORA-22818: subquery expressions not allowed here error.

The only ways around this are to either convert your scalar subquery to an aggregate value like so:

max(case (select count(*) cnt from t t1) when 0 then 1 else 0 end)

(case max((select count(*) cnt from t t1)) when 0 then 1 else 0 end)

or rewrite your query to move the unaggregated scalar subquery out of the aggregated query:

select (case (select count(*) cnt from t t1) when 0 then 1 else 0 end) * sum
  from (select sum(t3.num) sum from t t3) t2;

or precompute your scalar subquery so it can be used in the group by clause:

select case t1.cnt when 0 then 1 else 0 end * sum(t2.num)
  from t t2
     , (select count(*) cnt from t) t1
 group by case t1.cnt when 0 then 1 else 0 end

Best Answer

Related Solutions

Oracle – Select the Max from Two Max Values

ORA-00937 – Calculating Sub-Value in Aggregate SELECT Statement

Related Question