Convert an entire string to Sqlite

Asked

Viewed 401 times

-1

Dear I am trying to create an sql for a mobile application, sql is okay but the application gives the following error when running: "field waiting for a string and current is integer", the question is how do I convert the "icone" field from sql to whole, follow sql: 

select
    l.*,
    c.descricao as categoria,
    case when l.tipo_lancamento = ‘c’ then 1 else 0 end as icone
from
    tab_lancamento l 
join
    tab_categoria c on (c.id_categoria = l.id_categoria)

The field type_lancamento is a varchar but, but I’ve already changed to an integer but still giving of incompatibility

From now on I thank you!!!

sqlite3

  • What kind of camp tipo_lancamento on the table tab_lancamento ? you could post the structure of the tables ?

  • The field type_lancamento is a varchar but, but I’ve already changed to an integer but still giving of incompatibility

    1. If the field tipo_lancamento is a whole, why compare it to a c ? 2) You seem to be using a kind of acute accent to delimit the c, try using single quotes.

1 answer

0

Assuming your Structure is similar to the following:

CREATE TABLE tab_lancamento
(
    id_lancamento integer,
    id_categoria integer,
    tipo_lancamento character varying(1)
);

CREATE TABLE tab_categoria
(
    id_categoria integer,
    descricao text
);

Dice:

INSERT INTO tab_categoria ( id_categoria, descricao ) VALUES ( 1, 'Primeira Categoria' );
INSERT INTO tab_categoria ( id_categoria, descricao ) VALUES ( 2, 'Segunda Categoria' );
INSERT INTO tab_categoria ( id_categoria, descricao ) VALUES ( 3, 'Terceira Categoria' );

INSERT INTO tab_lancamento ( id_lancamento, id_categoria, tipo_lancamento ) VALUES ( 1, 1, 'a' );
INSERT INTO tab_lancamento ( id_lancamento, id_categoria, tipo_lancamento ) VALUES ( 2, 2, 'c' );
INSERT INTO tab_lancamento ( id_lancamento, id_categoria, tipo_lancamento ) VALUES ( 3, 3, 'c' );
INSERT INTO tab_lancamento ( id_lancamento, id_categoria, tipo_lancamento ) VALUES ( 4, 1, 'a' );
INSERT INTO tab_lancamento ( id_lancamento, id_categoria, tipo_lancamento ) VALUES ( 5, 2, 'b' );
INSERT INTO tab_lancamento ( id_lancamento, id_categoria, tipo_lancamento ) VALUES ( 6, 3, 'c' );

The following consultation should work:

SELECT
    l.*,
    c.descricao AS categoria,
    (CASE WHEN l.tipo_lancamento = 'c' THEN 1 ELSE 0 END) AS icone
FROM
    tab_lancamento l 
JOIN
    tab_categoria c ON ( c.id_categoria = l.id_categoria );

Exit:

id_lancamento  id_categoria  tipo_lancamento  categoria           icone     
-------------  ------------  ---------------  ------------------  ----------
1              1             a                Primeira Categoria  0         
2              2             c                Segunda Categoria   1         
3              3             c                Terceira Categoria  1         
4              1             a                Primeira Categoria  0         
5              2             b                Segunda Categoria   0         
6              3             c                Terceira Categoria  1

Browser other questions tagged sqlite3

You are not signed in. Login or sign up in order to post.