"if, then, else" in SQLite

Question

Without using custom functions, is it possible in SQLite to do the following. I have two tables, which are linked via common id numbers. In the second table, there are two variables. What I would like to do is be able to return a list of results, consisting of: the row id, and NULL if all instances of those two variables (and there may be more than two) are NULL, 1 if they are all 0 and 2 if one or more is 1.

What I have right now is as follows:


SELECT 
    a.aid, 
    (SELECT count(*) from W3S19 b WHERE a.aid=b.aid) as num, 
    (SELECT count(*) FROM W3S19 c WHERE a.aid=c.aid AND H110 IS NULL AND H112 IS NULL) as num_null, 
    (SELECT count(*) FROM W3S19 d WHERE a.aid=d.aid AND (H110=1 or H112=1)) AS num_yes 
FROM W3 a

So what this requires is to step through each result as follows (rough Python pseudocode):


if row['num_yes'] > 0:
    out[aid] = 2
elif row['num_null'] == row['num']:
    out[aid] = 'NULL'
else:
    out[aid] = 1

Is there an easier way? Thanks!

jmz · Accepted Answer · 2010-10-07 12:22:44Z

23

Use CASE...WHEN, e.g.

CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END

Read more from SQLite syntax manual (go to section "The CASE expression").

answered Oct 7, 2010 at 12:22

jmz

5,51730 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Berry Tsakala · Accepted Answer · 2013-12-29 16:28:49Z

1

There's another way, for numeric values, which might be easier for certain specific cases. It's based on the fact that boolean values is 1 or 0, "if condition" gives a boolean result:

(this will work only for "or" condition, depends on the usage)

SELECT (w1=TRUE)*r1 + (w2=TRUE)*r2 + ...

of course @evan's answer is the general-purpose, correct answer

answered Dec 29, 2013 at 16:28

Berry Tsakala

16.9k14 gold badges64 silver badges92 bronze badges

Collectives™ on Stack Overflow

"if, then, else" in SQLite

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related