No such Column Android Sqlite

Question

I need to accomplish the following select on Sqlite on Android

SELECT p.codigo, p.nome, p.preco_padrao,
   (coalesce((select sum(i.qtd) qtd
   from tbl_item_compra i
   inner join tbl_compra c on (c.codigo = i.chcompra)
   where i.chproduto = p.codigo and
         c.data_entrega >= CURRENT_DATE),0) -
   coalesce((select sum(i.qtd) qtd
   from tbl_item_venda i
   inner join tbl_venda v on (v.codigo = i.chvenda)
   where i.chproduto = p.codigo and
         v.pedido = false),0)) as qtd_estoque   
FROM tbl_produto p 

But when the select is executed, returns the following error:

    android.database.sqlite.SQLiteException: no such column: false (code 1): , while compiling: SELECT p.codigo, p.nome,  p.preco_padrao,   (coalesce((select sum(i.qtd)      from tbl_item_compra i      inner join tbl_compra c on (c.codigo = i.chcompra)      where i.chproduto = p.codigo and            c.data_entrega <= CURRENT_DATE),0) -      coalesce((select sum(i.qtd)      from tbl_item_venda i      inner join tbl_venda v on (v.codigo = i.chvenda)      where i.chproduto = p.codigo and            v.pedido = false),0)) as qtd_estoque   FROM tbl_produto p
#################################################################
Error Code : 1 (SQLITE_ERROR)
Caused By : SQL(query) error or missing database.
    (no such column: false (code 1): , while compiling: SELECT p.codigo, p.nome,  p.preco_padrao,   (coalesce((select sum(i.qtd)      from tbl_item_compra i      inner join tbl_compra c on (c.codigo = i.chcompra)      where i.chproduto = p.codigo and            c.data_entrega <= CURRENT_DATE),0) -      coalesce((select sum(i.qtd)      from tbl_item_venda i      inner join tbl_venda v on (v.codigo = i.chvenda)      where i.chproduto = p.codigo and            v.pedido = false),0)) as qtd_estoque   FROM tbl_produto p)
#################################################################

Answer 1

Paul, The mistake happens because you’re trying to make a select with a Boolean comparison. Sqlite does not have the Boolean data type, but maps them as integers (true = 1 and false = 0), see documentation: https://www.sqlite.org/datatype3.html

So change your query as follows:

SELECT p.codigo, p.nome, p.preco_padrao,
   (coalesce((select sum(i.qtd) qtd
   from tbl_item_compra i
   inner join tbl_compra c on (c.codigo = i.chcompra)
   where i.chproduto = p.codigo and
         c.data_entrega >= CURRENT_DATE),0) -
   coalesce((select sum(i.qtd) qtd
   from tbl_item_venda i
   inner join tbl_venda v on (v.codigo = i.chvenda)
   where i.chproduto = p.codigo and
         v.pedido = 0),0)) as qtd_estoque   
FROM tbl_produto p