How to skip case statement in sql?

Question

I am using a sql statement in Postgresql. Inside my statment is a case expression. How can I skip a row if case statement is not acceptet

Code:

SELECT *
FROM (SELECT a.link_id AS LinkID,

CASE
    WHEN a.From_Ref_Speed_Limit is null and a.To_Ref_Speed_Limit is not null THEN a.To_Ref_Speed_Limit
    WHEN a.From_Ref_Speed_Limit is not null and a.To_Ref_Speed_Limit is null THEN a.From_Ref_Speed_Limit
    WHEN a.From_Ref_Speed_Limit < a.To_Ref_Speed_Limit THEN a.To_Ref_Speed_Limit
    WHEN a.From_Ref_Speed_Limit > a.To_Ref_Speed_Limit THEN a.From_Ref_Speed_Limit
    WHEN a.From_Ref_Speed_Limit = a.To_Ref_Speed_Limit THEN a.To_Ref_Speed_Limit
    ELSE 0 --unknown
END as SpeedLimit,...

If no condition inside my case statement is guilty it should skip the row and select the next one. Like in case else it should not return NULL it should skip the row and should be go on with the next one. How can I realize this?

Just add a WHERE SpeedLimit <> 0 in the outer query to filter out unwanted rows. — Giorgos Betsos
@GiorgosBetsos solution is good. But it feels like this could be a common problem i.e. getting rid of rows under some calculated condition. Is there a way to short circuit evaluation in this case: calc1, if cond1(calc1) skip row else calc2, if cond2(calc2) skip row, else calc3 and return calc1,calc2,calc3. Doing this without multiple nested queries should be interesting. Anyone? On a different DB is this possible? — Dan Getz
@DanGetz No RDBMS support such sort of functionality, as SQL is set based rather than procedural. So you have to calculate all conidtions first, then use them for filtering, even for prioritizing which records are going to be selected. — Giorgos Betsos

Clodoaldo Neto Clodoaldo Neto · Accepted Answer · 2017-03-29T11:35:16

Your case can be replaced by greatest and lateral avoids a nested query:

select *
from
    a
    inner join lateral (
        select greatest(a.From_Ref_Speed_Limit, a.To_Ref_Speed_Limit) as SpeedLimit
    ) s on s.SpeedLimit is not null
;
 from_ref_speed_limit | to_ref_speed_limit | speedlimit 
----------------------+--------------------+------------
                    1 |                  0 |          1
                    0 |                  1 |          1
                      |                  1 |          1
                    0 |                    |          0

For versions prior to 9.4 (no lateral) a nested query is necessary:

with a (From_Ref_Speed_Limit, To_Ref_Speed_Limit) as ( values
    (1,0),(0,1),(null,1),(0,null),(null,null)
)
select *
from (
    select a.*, greatest(a.From_Ref_Speed_Limit, a.To_Ref_Speed_Limit) as SpeedLimit
    from a
) s
where s.SpeedLimit is not null
;

How to skip case statement in sql?

1 Answers