If() Statement Alternative in SQLite

IF() statement alternative in SQLite

For generic SQL you can use CASE:

CASE is used to provide if-then-else type of logic to SQL. Its syntax is:

SELECT CASE ("column_name")
  WHEN "condition1" THEN "result1"
  WHEN "condition2" THEN "result2"
  ...
  [ELSE "resultN"]
  END
FROM "table_name"

From http://www.sqlite.org/lang_expr.html section "The CASE expression"

E.g.

UPDATE pages
SET rkey = rkey + 2,
    lkey = CASE  WHEN lkey >= $key THEN lkey + 2 ELSE lkey END
WHERE rkey >= $key

Another link about SQLite & CASE (with example of update with subselect) http://sqlite.awardspace.info/syntax/sqlitepg09.htm

CASE can be used in UPDATE in generic SQL, but I have no info about SQLite support of UPDATEs with CASE

http://www.craigsmullins.com/ssu_0899.htm section "Using CASE Expressions When Modifying Data"

How to execute a query with IF and ELSE in Android sqlite?

These three questions might help you figure out the syntax problem.

IF-Statement in SQLite: update or insert?

IF() statement alternative in SQLite

Does sqlite support any kind of IF(condition) statement in a select

Hope this helps

SQLite: Something like Oracle Decode or MS If

I think case is just as easy to use. It's familiarity with syntax that may be buggin you but this works just as well.

CASE Gender WHEN 'F' then 'FEMALE'
            WHEN 'M' then 'MALE'
            ELSE 'UNDEFINED'
            END as GenderName

or

CASE WHEN GENDER = 'F' THEN 'FEMALE'
     WHEN GENDER = 'M' THEN 'MALE'
     ELSE 'UNDEFINED' 
     END as GenderName

It should be noted that CASE is pretty much db agnostic: Decode, iif, if, and other variations are not. So if your query needs to work on several different databases; stick to case as it's more likely to work across multiple database platforms.

With regards to performance & Why have both:
SQL Server IIF vs CASE

• I've read articles stating the generated execution plan is the same for both.

• I've read articles stating the difference between the two is nano-seconds.

• I've also read articles stating case is slightly faster because the engine basically switches the iif to a case; so iif has overhead.

• I've read articles that state iif is faster because it's a binary check and is only ever going to return true or false (or null) so it has less overhead.

• I've seen where you can only nest up to 10 for each type. I've also seen where someone indicated you can have 12 nested values...

• I've seen some state if they need true/false result and it's a just a single comparison to only use IIF, if multiple, case.

• To know which is better: testing is required for specific RDBMS/Version.

• From a personal standpoint... I prefer CASE due to the multiple checks it supports and I personally find it easier to read. But if you just work in MSFT environment: you may find the inverse to be true.

SQLite if statement in WHERE clause

CASE can compute any value, even a boolean value as returned by a comparison.
In SQLite, 1 is the same as "true":

...
AND %d >= (wines.year + wines.maturity)
AND CASE WHEN wines.apogee IS NOT NULL
         THEN %d < (wines.year + wines.apogee)
         ELSE 1
    END
...

The same can be done with the ifnull() function:

...
AND %d >= (wines.year + wines.maturity)
AND ifnull(%d < (wines.year + wines.apogee), 1)
...

SQLite: Alternative Query for Nested SELECT Statements

If what you want in the results is the DataSourceIDs then there is no need for a join to DataSources.

Also, the query:

SELECT Date FROM TimeSteps WHERE TimeStepID = ?

returns only 1 row with 1 column, so there is no need for the operator IN.

Try this:

SELECT DISTINCT DataSourceID 
FROM TimeSteps
WHERE Date = (SELECT Date FROM TimeSteps WHERE TimeStepID = ?);

If the combination of DataSourceID and Date is unique, then DISTINCT is not needed.

Whats an alternative to having to use sqlite prepared statements to escape characters

Use FMDB, and then you won't have to. It has built-in parameter binding support, and that will take care of any escaping you need for you.

---

Related Topics