Does SQLite Support Any Kind of If(Condition) Statement in a Select

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

See the case expression.

A CASE expression serves a role similar to IF-THEN-ELSE in other programming languages.

For your example

select case when length(a) > 4 then a else '' end as b
from foo

SQLite3 IF statement, condition based on other columns

You don't have any ELSE case in your query. Without ELSE there will be no results if shares is less than zero. Also, you don't need to assign price * -1 to price. Just use it like this:

CASE WHEN shares < 0
     THEN price * -1
     ELSE price
END

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"

Is there an sqlite function that can check if a field matches a certain value and return 0 or 1?

In SQLite, boolean values are just integer values 0 and 1, so you can use the comparison directly:

SELECT col1, col2 = 200 AS SomeFunction FROM MyTable

How to use conditions in SQLite (like if-statements, etc.)

I think you need to use CASE operator - see http://sqlite.awardspace.info/syntax/sqlitepg09.htm

EDIT - try:

SELECT MAX(Series), CASE WHEN STRFTIME ( '%w', Date ) = 0 THEN DATE(Date, 'weekday 0') ELSE DATE(DATE(Date, 'weekday 0'), '-7 days') END AS dateStartOfWeek FROM SeriesScores
  WHERE Season = '2010-2011' 
  AND dateStartOfWeek = '2010-08-29'
  GROUP BY DateStartOfWeek

see http://www.sqlite.org/lang_datefunc.html

SQLite conditionals in creation of a trigger

In SQLite there is no IF conditional statement.

In this case you can use simple WHERE clauses:

CREATE TRIGGER document_handler AFTER UPDATE ON document
FOR EACH ROW
BEGIN
  INSERT INTO DocumentUpdates (id_document, attribute_changed, lastvalue, newvalue, modification_date)
  SELECT NEW.id, 'type', OLD.type, NEW.type, CURRENT_TIMESTAMP
  WHERE NEW.type IS NOT OLD.type;

  INSERT INTO DocumentUpdates (id_document, attribute_changed, lastvalue, newvalue, modification_date)
  SELECT NEW.id, 'title', OLD.title, NEW.title, CURRENT_TIMESTAMP
  WHERE NEW.title IS NOT OLD.title;
    
  INSERT INTO DocumentUpdates (id_document, attribute_changed, lastvalue, newvalue, modification_date)
  SELECT NEW.id, 'path', OLD.path, NEW.path, CURRENT_TIMESTAMP
  WHERE NEW.path IS NOT OLD.path;
END;

I changed all <> with IS NOT which can compare also NULL values.

Also, the equivalent of NOW() in SQLite is CURRENT_TIMESTAMP.

See a simplified demo.

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

---

Related Topics