SQLite and If() condition

Other General Resources like icon sets, sound files etc.,

Moderator: Rathinagiri

Post Reply
User avatar
Rathinagiri
Posts: 5161
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Has thanked: 127 times
Been thanked: 129 times
Contact:

SQLite and If() condition

Post by Rathinagiri » Mon Jul 12, 2010 5:30 am

I started my SQL journey with MySQL after Harbour -> MySQL api had been created. Then came SQLite api too.

It looked easy for me and some of my clients to have embedded SQL database like SQLite.

The only difficulty I felt with SQLite is the non-existence of if() condition inside a SQL statement. I was using some work-around queries to get the effect of if(). I thought that if() is an important, basic and frequently used function which eases the programmer a lot and how come SQLite people have ignored it. Today, while searching the net for other alternatives available for if(), I came across the case expression for SQLite. God! this is what I am searching for and it is already there in SQLite!

So, if you have a SQL statement in MySQL "select a.name,if(b.code > 0,(select .....),' ') from ....." the equivalent SQLite query would be, "select a.name, case when b.code > 0 then (select .....) else ' ' end from ...."

Hope this would be useful to somebody who wishes to use both MySQL and SQLite Database as their backend.

The following is the documentation from SQLite website.

The CASE expression

A CASE expression serves a role similar to IF-THEN-ELSE in other programming languages. WHEN expressions are evaluated from left to right until one is found that is true, at which point the corresponding THEN term becomes the result. If no WHEN expression is true then the ELSE clause determines the result or the result is NULL if there is no ELSE clase.

The optional expression that occurs in between the CASE keyword and the first WHEN keyword is the "base" expression. There are two basic forms of a CASE expression: those with and without a base expression. In a CASE without a base expression, each WHEN expression is evaluted as a boolean and the overall result is determined by first WHEN expression that is true. In a CASE with a base expression, the base expression is evaluted just once and the result is compared against each WHEN expression until a match is found. When comparing a base expression against a WHEN expression, the same collating sequence, affinity, and NULL-handling rules apply as if the base expression and WHEN expression are respectively the left- and right-hand operands of an = operator.

Assuming the subexpressions have no side-effects, the following two expressions are equivalent:

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

The only difference between the two CASE expressions shown above is that the x expression is evaluated exactly once in the first example but might be evaluated multiple times in the second.

A NULL result is considered false when evaluating WHEN terms. If the base expression is NULL then the result of the CASE is the result of the ELSE expression if it exists, or NULL if the ELSE clause is omitted.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.

User avatar
sudip
Posts: 1444
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India
Has thanked: 4 times

Post by sudip » Mon Jul 12, 2010 8:42 am

Excellent Rathi :D
How can you understand what we are thinking (especially for my case)? May be it's because, "we are on the same boat brother" :D :lol:
With best regards,
Sudip

Post Reply