sqlite select where...and vs. case...when

Question

I'm new to sqlite and have a populated database where I need to determine if any of the digital organisms in the database have a threshold level of fitness (fitness>=2) and are alive (live=1). I also need to exclude the indivID (the Primary Key) of the organism for whom I am trying to find a fit mate (indivID INTEGER PRIMARY KEY).

Below I tried the first line of code but it threw a SQL error:

[SQLITE_ERROR] SQL error or missing database (near "CASE": syntax error).

However I know the error is in the SQL statment because other functions are successfully accessing the database.

SELECT indivID FROM population CASE fitness >=2 WHEN live = 1 AND indivID!=[specific individual] ELSE NULL END

I have tried this next line of code, which works but does not properly exclude the indivID of the specific individual:

SELECT [some column names] FROM [a database] WHERE fitness>=2 AND live=1 AND indivID!=[specific individual]

I have three questions:

1) Where are my errors in the above statements

2) What is the difference between using "case...when" and "where...and" statements

3) It is possible and even probable on these queries that there may not be a "live=1" individual with high enough fitness (above 2) to qualify for the select statement, so will the outcome of both of these queries with no viable individual (if correctly written) then be null?

Thanks in advance for your help!

Your second query seems fine. Could you please expand on the *‘does not properly exclude the indivID of the specific individual’* part? — Andriy M, May 31 '13 at 18:13
Thanks guys! @AndriyM In cases where I reset the database so that everyone has a fitness of zero and I boost one individual's fitness to 2 so that the system queries for fit mate (another individual with fitness>=2), it reports back that there is one, when I know all other individuals are at zero. — gromiczek, May 31 '13 at 18:21
If there is only one row matching the condition and you are specifying its ID to exclude and yet the query still returns it, then yes, I can see how one would be confused by that. But are you certain that you didn't make a mistake? Perhaps the excluded ID was wrong (and so the server didn't exclude anything and returned the only matching row)? [Here's a simple example](http://sqlfiddle.com/#!5/59e16/1) matching, I think, the situation you described, and I can see no issue there (the query returns no rows as expected). Could you similarly set up a test case illustrating the problem? — Andriy M, May 31 '13 at 18:33

Aleks G · Accepted Answer · 2014-02-11T20:40:35.970

According to your first query, you seem to misunderstand the usage of case when. It's like an if for a single value. It's usually used to get the required value based on some column values, e.g.

SELECT CASE WHEN col>0 THEN 1 ELSE 0 END FROM table

However it can also be used for some nifty tricks on the condition, e.g.

SELECT col FROM table WHERE CASE WHEN some_param = 1 THEN col2 = 1 ELSE true END

This statement retrieves column col for rows where col2 = 1 if some input parameter is 1 or for all rows if input parameter is something else than 1.

Using WHERE ... AND checks the condition, e.g.

SELECT col FROM table WHERE col>0 AND col2=0

In your case, you're trying to select a null if no matching values are found. I don't think it's the right way of dealing with it. You should attempt to select what you're looking for and then check whether you got any rows back. You would essentially get to something like this:

ResultSet rs = stmt.executeQuery("SELECT [some column names] FROM [a database] WHERE fitness>=2 AND live=1 AND indivID!=[specific individual]");
if(rs.first()) {
    //You have some data - loop over the resultset and retrieve it
}
else {
    //There are no matches to your query
}

If you only want one match, then you can simplify this to

ResultSet rs = stmt.executeQuery("SELECT [some column names] FROM [a database] WHERE fitness>=2 AND live=1 AND indivID!=[specific individual] LIMIT 1");
String name = rs.first() ? rs.getString(1) : null;

Note that I used index 1 for getString - but it may be whatever you need.

EDIT: If you're dealing with SQLite, then you have a single-direction-moveable resultset, therefore you need to change the above code slightly:

ResultSet rs = stmt.executeQuery("SELECT [some column names] FROM [a database] WHERE fitness>=2 AND live=1 AND indivID!=[specific individual]");
if(!rs.isBeforeFirst()) {
    //You have some data - loop over the resultset and retrieve it
}
else {
    //There are no matches to your query
}

And, correspondingly,

ResultSet rs = stmt.executeQuery("SELECT [some column names] FROM [a database] WHERE fitness>=2 AND live=1 AND indivID!=[specific individual] LIMIT 1");
String name = !rs.isBeforeFirst() ? null : rs.getString(1) : null;;

Note the change of rs.first() to !rs.isBeforeFirst() and the reversal of the condition.

Thanks! I've tried both solutions and for each got the SQL Exception: ResultSet is TYPE_FORWARD_ONLY on the lines that use rs.first(). From looking online I see that exception means I can only iterate forward through the result set, so why would asking for first() yield that result? — gromiczek, May 31 '13 at 19:27
I discovered [this](http://stackoverflow.com/questions/867194/java-resultset-how-to-check-if-there-are-any-results) answer to my problem in the comment above with TYPE_FORWARD. It uses `if (!rs.isBeforeFirst() )` (with the conditional outcomes reversed) instead of `if (rs.first())`. However, now I have an error that says the database is locked. — gromiczek, May 31 '13 at 19:56
Fixed the locked problem---I'd forgotten to comment out a previous bit of test code. — gromiczek, May 31 '13 at 20:28
@Alex G Thanks so much for the clarification and help---it's up and running! — gromiczek, May 31 '13 at 21:02

sqlite select where...and vs. case...when

1 Answers1