CODEWITHSUNDEEP
sqlsqlite
SaldaVonSchwartz
SaldaVonSchwartz

Reputation: 3809

sqlite: is there a way to make a conditional OR / COALESCE in the SELECT clause?

I have the following view:

CREATE VIEW FilmTableView AS

    SELECT  (TitleSP || " / " || TitleEN) as Title,  
            CompanyName, 
            CoverURI,
            CompanyFilmRelation.CompanyId,
            CompanyFilmRelation.FilmId 

    FROM    Film
    JOIN    CompanyFilmRelation on CompanyFilmRelation.FilmId = Film.FilmId 
    JOIN    Company on CompanyFilmRelation.CompanyId = Company.CompanyId 

    ORDER BY Title;

But I might end up with records where either TitleSP or TitleEN are empty. In such case, I'd like to only include whichever column is not null and not include the "/".

Is there a way to do this? That is, something following the logic of:

if(TitleSP && TitleEN)
   select (TitleSP || " / " || TitleEN) as Title
else
   select (TitleSP ? TitleSP : TitleEn) as Title

Upvotes: 3

Views: 6221

Answers (4)

onedaywhen
onedaywhen

Reputation: 57023

SELECT COALESCE(
                NULLIF(TitleSP, '') || ' / ' || NULLIF(TitleEN, ''), 
                NULLIF(TitleSP, ''), 
                NULLIF(TitleEN, ''), 
                ''
               ), 
...

Upvotes: 1

tidwall
tidwall

Reputation: 6949

Use the IFNULL function.

CREATE VIEW FilmTableView AS

    SELECT  ifnull((TitleSP || " / " || TitleEN), ifnull(TitleSP, TitleEN)) as Title,  
            CompanyName, 
            CoverURI,
            CompanyFilmRelation.CompanyId,
            CompanyFilmRelation.FilmId 

    FROM    Film
    JOIN    CompanyFilmRelation on CompanyFilmRelation.FilmId = Film.FilmId 
    JOIN    Company on CompanyFilmRelation.CompanyId = Company.CompanyId 

    ORDER BY Title;

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

SELECT CASE WHEN (TitleSP = '' OR TitleSP IS NULL)
                THEN COALESCE(TitleEN, '')
            WHEN (TitleEN = '' OR TitleEN IS NULL)
                THEN TitleSP
            ELSE (TitleSP || ' / ' || TitleEN)
       END AS Title, 
...

Upvotes: 2

Hogan
Hogan

Reputation: 70523

In sql if one operator is null then the expression becomes null, you can use this with the coalesce function to solve this problem easily. Something like this should work (nb not tested):

   COALESCE(TitleSP || " / " || TitleEN,TitleSP,TitleEN,"No Title") as Title

If you can't do it this way then case is the way to go, something like this (will also handle empty strings):

  TitleSP || 
  CASE WHEN ((ISNULL(TRIM(TitleSP),"") = "") OR (ISNULL(TRIM(TitleEN),"") = "")) THEN "" ELSE " / " END || 
  TitleEN as Title

Upvotes: 4

Related Questions