Simon,
Id field in one table is defined as TEXT, because there are stored
identifiers which can be numeric or text mostly like in the example ("4",
"4,5", "10-1") (to be precise this map is created on the fly by
concatenating some ids and names from another tables). In second table
there are stored identifiers which are integer only. This ids means
something entirely different, but there is one case, when table with date
keeps ids from both tables. Unfortunately I cannot change input data - it
is taken from some APIs using csv files. Real schema of this database is
mostly determined by input data and hole database is treated like some kind
of cache.
As you wrote, there are many problems with data, that we're storing. Schema
of database is not stable, new features often need new data from other API
and it's hard to predict new types of data. As I wrote in my previous
message, I was quite sure, that comparing text columns and integer columns
should work. Hopefully in other cases fields which are compared are same
types.
Adrian
2016-12-23 13:00 GMT+01:00 Simon Slavin <slav...@bigfraud.org>:
>
> On 22 Dec 2016, at 5:55pm, Adrian Stachlewski <adrianstachlew...@gmail.com>
> wrote:
>
> > In this case I think that the best way to do this is cast integer column
> to
> > text.
> > CREATE VIEW id_map(id, name) as
> > SELECT CAST(id AS TEXT), name
> > FROM map_integer
> > UNION ALL
> > SELECT id, name
> > FROM map_text;
>
> In an earlier post
>
> > CREATE TABLE map_integer (id INTEGER PRIMARY KEY, name TEXT);
> > CREATE TABLE data (id TEXT PRIMARY KEY, flag BOOLEAN);
>
> Your problem is not the form of your enquiry, or the precise way that
> UNION works, it’s the data you’re storing.
>
> Sorry if you’ve already explained this, but can you tell me why you have
> an id field which holds numbers defined as TEXT in one table, but an id
> field which holds numbers defined as INTEGER in another table ? I know
> that the above schema is an example you made up for discussing the problem,
> and not your real schema, but it still points to an underlying problem with
> the data you’re storing.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
