date:20200126

Re: [sqlite] SQL CASE WHEN THEN ELSE END

2020-01-26 Thread Keith Medcalf


Do you perhaps mean:

  SELECT a.a,
 a.c,
 a.e,
 b.g,
 b.h,
 b.i,
 coalesce((
   SELECT 'YES'
 FROM t2
WHERE wYear == a.c
  AND pid == a.a
  ),  'NO') AS digital
FROM t0 as a, t1 as b
   WHERE a.a == b.f
 AND a.idate == (SELECT MAX(idate) from t0 where a = a.a)
 AND b.idate == (SELECT MAX(idate) from t1 where f = a.a)
 AND a.a IN (SELECT pid FROM t2)
 AND a.c == 2020
;

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users  On
>Behalf Of Jose Isaias Cabrera
>Sent: Sunday, 26 January, 2020 19:44
>To: SQLite mailing list 
>Subject: [sqlite] SQL CASE WHEN THEN ELSE END
>
>
>Greetings!
>
>I am getting the wrong output, and I don't know how to get it to work.
>Please take a look at the following (Pardon the lengthy data):
>create table t0 (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
>insert into t0 (a, b, c, d, e, idate) values ('p001', 1, 2019, 'n', 4,
>'2019-02-11');
>insert into t0 (a, b, c, d, e, idate) values ('p002', 2, 2019, 'n', 4,
>'2019-02-11');
>insert into t0 (a, b, c, d, e, idate) values ('p003', 3, 2019, 'n', 4,
>'2019-02-11');
>insert into t0 (a, b, c, d, e, idate) values ('p004', 4, 2019, 'y', 4,
>'2019-02-11');
>insert into t0 (a, b, c, d, e, idate) values ('p005', 5, 2019, 'y', 4,
>'2019-02-11');
>insert into t0 (a, b, c, d, e, idate) values ('p001', 10, 2020, 'n', 4,
>'2019-02-12');
>insert into t0 (a, b, c, d, e, idate) values ('p002', 2, 2019, 'n', 4,
>'2019-02-12');
>insert into t0 (a, b, c, d, e, idate) values ('p003', 3, 2019, 'y', 4,
>'2019-02-12');
>insert into t0 (a, b, c, d, e, idate) values ('p004', 4, 2019, 'y', 4,
>'2019-02-12');
>insert into t0 (a, b, c, d, e, idate) values ('p005', 5, 2019, 'y', 4,
>'2019-02-12');
>insert into t0 (a, b, c, d, e, idate) values ('p001', 10, 2020, 'n', 4,
>'2019-02-13');
>insert into t0 (a, b, c, d, e, idate) values ('p002', 2, 2019, 'n', 4,
>'2019-02-13');
>insert into t0 (a, b, c, d, e, idate) values ('p003', 3, 2020, 'y', 4,
>'2019-02-13');
>insert into t0 (a, b, c, d, e, idate) values ('p004', 5, 2019, 'n', 4,
>'2019-02-13');
>insert into t0 (a, b, c, d, e, idate) values ('p005', 5, 2020, 'y', 8,
>'2019-02-13');
>
>create table t1 (n INTEGER PRIMARY KEY, f, g, h, i, j, idate);
>insert into t1 (f, g, h, i, j, idate) values ('p001', 1, 4, 'n', 4,
>'2019-02-11');
>insert into t1 (f, g, h, i, j, idate) values ('p002', 2, 3, 'n', 4,
>'2019-02-11');
>insert into t1 (f, g, h, i, j, idate) values ('p003', 3, 2, 'n', 4,
>'2019-02-11');
>insert into t1 (f, g, h, i, j,  idate) values ('p004', 4, 5, 'y', 4,
>'2019-02-11');
>insert into t1 (f, g, h, i, j, idate) values ('p005', 5, 3, 'y', 4,
>'2019-02-11');
>insert into t1 (f, g, h, i, j, idate) values ('p001', 10, 2, 'n', 4,
>'2019-02-12');
>insert into t1 (f, g, h, i, j, idate) values ('p002', 2, 6, 'n', 4,
>'2019-02-12');
>insert into t1 (f, g, h, i, j, idate) values ('p003', 3, 7, 'y', 4,
>'2019-02-12');
>insert into t1 (f, g, h, i, j, idate) values ('p004', 4, 1, 'y', 4,
>'2019-02-12');
>insert into t1 (f, g, h, i, j, idate) values ('p005', 5, 2, 'y', 4,
>'2019-02-12');
>insert into t1 (f, g, h, i, j, idate) values ('p001', 10, 1, 'n', 4,
>'2019-02-13');
>insert into t1 (f, g, h, i, j, idate) values ('p002', 2, 8, 'n', 4,
>'2019-02-13');
>insert into t1 (f, g, h, i, j, idate) values ('p003', 3, 9, 'y', 4,
>'2019-02-13');
>insert into t1 (f, g, h, i, j, idate) values ('p004', 5, 3, 'n', 4,
>'2019-02-13');
>insert into t1 (f, g, h, i, j, idate) values ('p005', 5, 3, 'y', 8,
>'2019-02-13');
>
>create table t2 (pid, WYear);
>insert into t2 values ('p001', 2019);
>insert into t2 values ('p003', 2019);
>insert into t2 values ('p004', 2019);
>insert into t2 values ('p002', 2020);
>insert into t2 values ('p003', 2020);
>insert into t2 values ('p005', 2020);
>
>When I run this SELECT,
>
>SELECT a.a, a.c, a.e, b.g, b.h, b.i,
>(
>CASE
>(
>  SELECT WYear FROM t2 WHERE pid = a.a
>)
>WHEN c.WYear = 2020 THEN “YES”
>ELSE “NO” END
>) AS DIGITAL
>FROM t0 as a, t1 as b, t2 as c
>WHERE a.a = b.f and a.a = c.pid
>AND a.c = 2020
>AND a.idate = (SELECT MAX(idate) from t0 where a = a.a)
>AND b.idate = (SELECT MAX(idate) from t1 where f = a.a)
>GROUP BY a.a
>;
>
>I get,
>
>p001|2020|4|10|1|n|NO
>p003|2020|4|3|9|y|NO
>p005|2020|8|5|3|y|NO
>
>I expected this,
>
>p001|2020|4|10|1|n|NO
>p003|2020|4|3|9|y|YES
>p005|2020|8|5|3|y|YES
>
>I've tried a few changes, but nothing seems to work.  Thoughts please?
>thanks.
>
>josé
>___
>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

Re: [sqlite] SQL CASE WHEN THEN ELSE END

2020-01-26 Thread Keith Medcalf


I get nothing at all except a complaint that the syntax is invalid.  In 
particular 

(
CASE
(
  SELECT WYear FROM t2 WHERE pid = a.a
)
WHEN c.WYear = 2020 THEN “YES”
ELSE “NO” END
) AS DIGITAL

Is not a valid scalar expression.  Parsing fails at "WHEN".  What exactly do 
you intend this scalar expression to do?

(and if the syntax was not invalid, the result would always be NO since it is 
impossible for the result of the subselect (which will always be a 4 digit 
number because that is all that is in t2, or null, if not found (and a random 
year at that since there can be multiple rows with the same pid and you did not 
specify which one you want)) to be equal to the 0 or 1 (true or false) boolean 
expression after the WHEN.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users  On
>Behalf Of Jose Isaias Cabrera
>Sent: Sunday, 26 January, 2020 19:44
>To: SQLite mailing list 
>Subject: [sqlite] SQL CASE WHEN THEN ELSE END
>
>
>Greetings!
>
>I am getting the wrong output, and I don't know how to get it to work.
>Please take a look at the following (Pardon the lengthy data):
>create table t0 (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
>insert into t0 (a, b, c, d, e, idate) values ('p001', 1, 2019, 'n', 4,
>'2019-02-11');
>insert into t0 (a, b, c, d, e, idate) values ('p002', 2, 2019, 'n', 4,
>'2019-02-11');
>insert into t0 (a, b, c, d, e, idate) values ('p003', 3, 2019, 'n', 4,
>'2019-02-11');
>insert into t0 (a, b, c, d, e, idate) values ('p004', 4, 2019, 'y', 4,
>'2019-02-11');
>insert into t0 (a, b, c, d, e, idate) values ('p005', 5, 2019, 'y', 4,
>'2019-02-11');
>insert into t0 (a, b, c, d, e, idate) values ('p001', 10, 2020, 'n', 4,
>'2019-02-12');
>insert into t0 (a, b, c, d, e, idate) values ('p002', 2, 2019, 'n', 4,
>'2019-02-12');
>insert into t0 (a, b, c, d, e, idate) values ('p003', 3, 2019, 'y', 4,
>'2019-02-12');
>insert into t0 (a, b, c, d, e, idate) values ('p004', 4, 2019, 'y', 4,
>'2019-02-12');
>insert into t0 (a, b, c, d, e, idate) values ('p005', 5, 2019, 'y', 4,
>'2019-02-12');
>insert into t0 (a, b, c, d, e, idate) values ('p001', 10, 2020, 'n', 4,
>'2019-02-13');
>insert into t0 (a, b, c, d, e, idate) values ('p002', 2, 2019, 'n', 4,
>'2019-02-13');
>insert into t0 (a, b, c, d, e, idate) values ('p003', 3, 2020, 'y', 4,
>'2019-02-13');
>insert into t0 (a, b, c, d, e, idate) values ('p004', 5, 2019, 'n', 4,
>'2019-02-13');
>insert into t0 (a, b, c, d, e, idate) values ('p005', 5, 2020, 'y', 8,
>'2019-02-13');
>
>create table t1 (n INTEGER PRIMARY KEY, f, g, h, i, j, idate);
>insert into t1 (f, g, h, i, j, idate) values ('p001', 1, 4, 'n', 4,
>'2019-02-11');
>insert into t1 (f, g, h, i, j, idate) values ('p002', 2, 3, 'n', 4,
>'2019-02-11');
>insert into t1 (f, g, h, i, j, idate) values ('p003', 3, 2, 'n', 4,
>'2019-02-11');
>insert into t1 (f, g, h, i, j,  idate) values ('p004', 4, 5, 'y', 4,
>'2019-02-11');
>insert into t1 (f, g, h, i, j, idate) values ('p005', 5, 3, 'y', 4,
>'2019-02-11');
>insert into t1 (f, g, h, i, j, idate) values ('p001', 10, 2, 'n', 4,
>'2019-02-12');
>insert into t1 (f, g, h, i, j, idate) values ('p002', 2, 6, 'n', 4,
>'2019-02-12');
>insert into t1 (f, g, h, i, j, idate) values ('p003', 3, 7, 'y', 4,
>'2019-02-12');
>insert into t1 (f, g, h, i, j, idate) values ('p004', 4, 1, 'y', 4,
>'2019-02-12');
>insert into t1 (f, g, h, i, j, idate) values ('p005', 5, 2, 'y', 4,
>'2019-02-12');
>insert into t1 (f, g, h, i, j, idate) values ('p001', 10, 1, 'n', 4,
>'2019-02-13');
>insert into t1 (f, g, h, i, j, idate) values ('p002', 2, 8, 'n', 4,
>'2019-02-13');
>insert into t1 (f, g, h, i, j, idate) values ('p003', 3, 9, 'y', 4,
>'2019-02-13');
>insert into t1 (f, g, h, i, j, idate) values ('p004', 5, 3, 'n', 4,
>'2019-02-13');
>insert into t1 (f, g, h, i, j, idate) values ('p005', 5, 3, 'y', 8,
>'2019-02-13');
>
>create table t2 (pid, WYear);
>insert into t2 values ('p001', 2019);
>insert into t2 values ('p003', 2019);
>insert into t2 values ('p004', 2019);
>insert into t2 values ('p002', 2020);
>insert into t2 values ('p003', 2020);
>insert into t2 values ('p005', 2020);
>
>When I run this SELECT,
>
>SELECT a.a, a.c, a.e, b.g, b.h, b.i,
>(
>CASE
>(
>  SELECT WYear FROM t2 WHERE pid = a.a
>)
>WHEN c.WYear = 2020 THEN “YES”
>ELSE “NO” END
>) AS DIGITAL
>FROM t0 as a, t1 as b, t2 as c
>WHERE a.a = b.f and a.a = c.pid
>AND a.c = 2020
>AND a.idate = (SELECT MAX(idate) from t0 where a = a.a)
>AND b.idate = (SELECT MAX(idate) from t1 where f = a.a)
>GROUP BY a.a
>;
>
>I get,
>
>p001|2020|4|10|1|n|NO
>p003|2020|4|3|9|y|NO
>p005|2020|8|5|3|y|NO
>
>I expected this,
>
>p001|2020|4|10|1|n|NO
>p003|2020|4|3|9|y|YES
>p005|2020|8|5|3|y|YES
>
>I've tried a few changes, but nothing seems to work.  Thoughts please?
>thanks.
>
>josé
>___
>sqlite-users mailing list

Re: [sqlite] SQL CASE WHEN THEN ELSE END

2020-01-26 Thread Jose Isaias Cabrera


Igor Tandetnik, on Sunday, January 26, 2020 09:57 PM, wrote...
>
> On 1/26/2020 9:44 PM, Jose Isaias Cabrera wrote:
> >  CASE
> >  (
> >SELECT WYear FROM t2 WHERE pid = a.a
> >  )
> >  WHEN c.WYear = 2020 THEN “YES”
> >  ELSE “NO” END
> > ) AS DIGITAL
>
> This should probably be simply
>
> case c.WYear when 2020 then 'YES' else 'NO' end
>
> or equivalently
>
> case when c.WYear=2020 then 'YES' else 'NO' end

Thanks, Igor.

josé
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL CASE WHEN THEN ELSE END

2020-01-26 Thread Simon Slavin

On 27 Jan 2020, at 2:44am, Jose Isaias Cabrera  wrote:

> CASE
>(
>  SELECT WYear FROM t2 WHERE pid = a.a
>)
>WHEN c.WYear = 2020 THEN “YES”
>ELSE “NO” END

That's not the structure of a CASE statement.

After CASE comes an expression.
After WHEN comes another expression.
If they equal one another, then the the bit after the THEN is returned.

You want something more like

SELECT 
(CASE WYear WHEN 2020 THEN 'YES' ELSE 'NO' END) AS DIGITAL
FROM t2 WHERE pid = a.a

but you'll have to fit this in with how your overall SELECT works.

Sse "The CASE expression" on this page for more details:

Also note that to quote strings you use a single apostrophe at both ends, not 
directed speech marks, just as you had in your INSERT commands.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL CASE WHEN THEN ELSE END

2020-01-26 Thread Igor Tandetnik


On 1/26/2020 9:44 PM, Jose Isaias Cabrera wrote:

 CASE
 (
   SELECT WYear FROM t2 WHERE pid = a.a
 )
 WHEN c.WYear = 2020 THEN “YES”
 ELSE “NO” END
) AS DIGITAL


This should probably be simply

case c.WYear when 2020 then 'YES' else 'NO' end

or equivalently

case when c.WYear=2020 then 'YES' else 'NO' end

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQL CASE WHEN THEN ELSE END

2020-01-26 Thread Jose Isaias Cabrera


Greetings!

I am getting the wrong output, and I don't know how to get it to work.  Please 
take a look at the following (Pardon the lengthy data):
create table t0 (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
insert into t0 (a, b, c, d, e, idate) values ('p001', 1, 2019, 'n', 4, 
'2019-02-11');
insert into t0 (a, b, c, d, e, idate) values ('p002', 2, 2019, 'n', 4, 
'2019-02-11');
insert into t0 (a, b, c, d, e, idate) values ('p003', 3, 2019, 'n', 4, 
'2019-02-11');
insert into t0 (a, b, c, d, e, idate) values ('p004', 4, 2019, 'y', 4, 
'2019-02-11');
insert into t0 (a, b, c, d, e, idate) values ('p005', 5, 2019, 'y', 4, 
'2019-02-11');
insert into t0 (a, b, c, d, e, idate) values ('p001', 10, 2020, 'n', 4, 
'2019-02-12');
insert into t0 (a, b, c, d, e, idate) values ('p002', 2, 2019, 'n', 4, 
'2019-02-12');
insert into t0 (a, b, c, d, e, idate) values ('p003', 3, 2019, 'y', 4, 
'2019-02-12');
insert into t0 (a, b, c, d, e, idate) values ('p004', 4, 2019, 'y', 4, 
'2019-02-12');
insert into t0 (a, b, c, d, e, idate) values ('p005', 5, 2019, 'y', 4, 
'2019-02-12');
insert into t0 (a, b, c, d, e, idate) values ('p001', 10, 2020, 'n', 4, 
'2019-02-13');
insert into t0 (a, b, c, d, e, idate) values ('p002', 2, 2019, 'n', 4, 
'2019-02-13');
insert into t0 (a, b, c, d, e, idate) values ('p003', 3, 2020, 'y', 4, 
'2019-02-13');
insert into t0 (a, b, c, d, e, idate) values ('p004', 5, 2019, 'n', 4, 
'2019-02-13');
insert into t0 (a, b, c, d, e, idate) values ('p005', 5, 2020, 'y', 8, 
'2019-02-13');

create table t1 (n INTEGER PRIMARY KEY, f, g, h, i, j, idate);
insert into t1 (f, g, h, i, j, idate) values ('p001', 1, 4, 'n', 4, 
'2019-02-11');
insert into t1 (f, g, h, i, j, idate) values ('p002', 2, 3, 'n', 4, 
'2019-02-11');
insert into t1 (f, g, h, i, j, idate) values ('p003', 3, 2, 'n', 4, 
'2019-02-11');
insert into t1 (f, g, h, i, j,  idate) values ('p004', 4, 5, 'y', 4, 
'2019-02-11');
insert into t1 (f, g, h, i, j, idate) values ('p005', 5, 3, 'y', 4, 
'2019-02-11');
insert into t1 (f, g, h, i, j, idate) values ('p001', 10, 2, 'n', 4, 
'2019-02-12');
insert into t1 (f, g, h, i, j, idate) values ('p002', 2, 6, 'n', 4, 
'2019-02-12');
insert into t1 (f, g, h, i, j, idate) values ('p003', 3, 7, 'y', 4, 
'2019-02-12');
insert into t1 (f, g, h, i, j, idate) values ('p004', 4, 1, 'y', 4, 
'2019-02-12');
insert into t1 (f, g, h, i, j, idate) values ('p005', 5, 2, 'y', 4, 
'2019-02-12');
insert into t1 (f, g, h, i, j, idate) values ('p001', 10, 1, 'n', 4, 
'2019-02-13');
insert into t1 (f, g, h, i, j, idate) values ('p002', 2, 8, 'n', 4, 
'2019-02-13');
insert into t1 (f, g, h, i, j, idate) values ('p003', 3, 9, 'y', 4, 
'2019-02-13');
insert into t1 (f, g, h, i, j, idate) values ('p004', 5, 3, 'n', 4, 
'2019-02-13');
insert into t1 (f, g, h, i, j, idate) values ('p005', 5, 3, 'y', 8, 
'2019-02-13');

create table t2 (pid, WYear);
insert into t2 values ('p001', 2019);
insert into t2 values ('p003', 2019);
insert into t2 values ('p004', 2019);
insert into t2 values ('p002', 2020);
insert into t2 values ('p003', 2020);
insert into t2 values ('p005', 2020);

When I run this SELECT,

SELECT a.a, a.c, a.e, b.g, b.h, b.i,
(
CASE
(
  SELECT WYear FROM t2 WHERE pid = a.a
)
WHEN c.WYear = 2020 THEN “YES”
ELSE “NO” END
) AS DIGITAL
FROM t0 as a, t1 as b, t2 as c
WHERE a.a = b.f and a.a = c.pid
AND a.c = 2020
AND a.idate = (SELECT MAX(idate) from t0 where a = a.a)
AND b.idate = (SELECT MAX(idate) from t1 where f = a.a)
GROUP BY a.a
;

I get,

p001|2020|4|10|1|n|NO
p003|2020|4|3|9|y|NO
p005|2020|8|5|3|y|NO

I expected this,

p001|2020|4|10|1|n|NO
p003|2020|4|3|9|y|YES
p005|2020|8|5|3|y|YES

I've tried a few changes, but nothing seems to work.  Thoughts please?  thanks.

josé
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] importing data to a table that has generated-columns

2020-01-26 Thread Brian Curley

Is there a missed parsing on the input file? It's likely to be a bare word
situation where an extra delimiter is encountered in the record. It's
probably only on one record in particular, although I don't know if the
error message reads that back.

The shell is sensitive to these, as it's expected the input file to be CSV
compliant.

Regards.

Brian P Curley



On Sun, Jan 26, 2020, 1:37 PM Scott Robison  wrote:

> On Sun, Jan 26, 2020 at 11:01 AM chiahui chen 
> wrote:
>
> > Hi,
> >
> > After creating a table (total 8 columns including 1 generated column) , I
> > tried to import data from a csv file (each record has values for 7
> columns
> > that match the non-generated column names and data types, no headers ).
> >
> > The system issued " error:  table has 7 columns but 8 values were
> supplied.
> > ' I wonder why.
> >
> > After experimenting different ways to import data to a table that has a
> > generated column, so far I only found that  .read command with a .sql
> file
> > that was output  as a result of  'mode insert'  is successful. Is there
> any
> > other ways to import data to an existing generated-column table?
> >
>
> I would be inclined to import the csv as a temp table, then write an INSERT
> INTO ... SELECT ... query to move the appropriate columns from the temp
> table into the new table.
> ___
> 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

Re: [sqlite] importing data to a table that has generated-columns

2020-01-26 Thread Keith Medcalf

On Sunday, 26 January, 2020 10:29, chiahui chen  wrote:

>After creating a table (total 8 columns including 1 generated column), I
>tried to import data from a csv file (each record has values for 7 
>columns that match the non-generated column names and data types, no 
>headers ).

>The system issued "error: table has 7 columns but 8 values were supplied."

>I wonder why.

This is because of the way the .import (currently) command works in the CLI.  

Looking at the code this is how it presently works:

Check if  exists, and if it does not, then create it, optionally using 
the column names from the CSV import file in the order they appear.
Now that the table exists, use "SELECT * FROM " to determine the number 
of columns in the table (which will include computed always columns, if any).
Generate a statement of the form "INSERT INTO  VALUES (? ...)" where 
there is one ? parameter for each column in the table.
Loop through the CSV file and bind the parameters to the above statement, 
executing a sqlite3_step at the end of each row.

You will note that:
1.  Column Names in the CSV are *only* used if  is created.
2.  Column Names in the CSV are *not* used for the INSERT statement.
3.  The number of column names from which you can "SELECT *" is not the same as 
the number of columns you can "INSERT INTO" if the table pre-exists and 
contains generated always columns (which cannot be INSERT INTO'd).
4.  Specifying column names (a header line) does not "match up" those column 
names to the INSERT INTO column names.

>After experimenting different ways to import data to a table that has a
>generated column, so far I only found that  .read command with a .sql
>file that was output  as a result of  'mode insert'  is successful. Is 
>there any other ways to import data to an existing generated-column table?

1. .import into a temporary  then use INSERT INTO  () SELECT * FROM  and then drop the temporary table.
2. include/load the csv extension and use an insert with an explicit column 
list to insert into  from the csv virtual table.

Otherwise, Richard *may* make some changes to the .import logic which *might* 
appear in the next release version.  Otherwise you will have to use one of the 
three noted word-arounds.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] importing data to a table that has generated-columns

2020-01-26 Thread Scott Robison

On Sun, Jan 26, 2020 at 11:01 AM chiahui chen 
wrote:

> Hi,
>
> After creating a table (total 8 columns including 1 generated column) , I
> tried to import data from a csv file (each record has values for 7 columns
> that match the non-generated column names and data types, no headers ).
>
> The system issued " error:  table has 7 columns but 8 values were supplied.
> ' I wonder why.
>
> After experimenting different ways to import data to a table that has a
> generated column, so far I only found that  .read command with a .sql file
> that was output  as a result of  'mode insert'  is successful. Is there any
> other ways to import data to an existing generated-column table?
>

I would be inclined to import the csv as a temp table, then write an INSERT
INTO ... SELECT ... query to move the appropriate columns from the temp
table into the new table.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] importing data to a table that has generated-columns

2020-01-26 Thread chiahui chen

Hi,

After creating a table (total 8 columns including 1 generated column) , I
tried to import data from a csv file (each record has values for 7 columns
that match the non-generated column names and data types, no headers ).

The system issued " error:  table has 7 columns but 8 values were supplied.
' I wonder why.

After experimenting different ways to import data to a table that has a
generated column, so far I only found that  .read command with a .sql file
that was output  as a result of  'mode insert'  is successful. Is there any
other ways to import data to an existing generated-column table?

Thank you advance.

Chiahui
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite: A couple minor doc bugs

2020-01-26 Thread Sigmund, Andrew

To whom it may concern,

  A couple minor SQLite documentation bugs:

https://sqlite.org/testing.html  Section 7.1, in paragraph beginning "Branch 
coverage is more strict": Paragraph ends saying "three test cases", but only 
two are listed (first appears garbled).

  https://sqlite.org/deterministic.html  Section 3, at the end of 
the first paragraph: "... where only deterministic functions are allows [sic]." 
 "allows" -> "allowed".

  (Reply directly to me, if necessary; I'm not subscribed to the 
list.)

  Thanks,
 Andy
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SSL: cannot connect to host www3.sqlite.org:443 (Connection refused)

2020-01-26 Thread Domingo Alvarez Duarte


Hello Richard !

I'm no getting this:



sqlite3$ fossil update
Autosync:  https://www3.sqlite.org/cgi/src
server says: 500 Server Malfunction
Pull done, sent: 1295  received: 217  ip: 64.225.41.2
Autosync failed.
continue in spite of sync failure (y/N)? n
update abandoned due to sync failure



Cheers !

On 25/1/20 21:53, Richard Hipp wrote:

On 1/25/20, Richard Hipp  wrote:

On 1/25/20, Domingo Alvarez Duarte  wrote:

Hello Richard !

Since yesterday I'm getting this message when trying to use fossil for
sqlite3.

Yeah.  That machine went completely bonkers and I had to rebuild it
from scratch, using a new IP address.  And because it used a new IP
address, I have to wait 24 hours for the new DNS information to
propagate before LetsEncrypt will give me a new cert.

Both https://www3.sqlite.org/ and https://www3.fossil-scm.org/ should
be back up now.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL CASE WHEN THEN ELSE END

Re: [sqlite] SQL CASE WHEN THEN ELSE END

Re: [sqlite] SQL CASE WHEN THEN ELSE END

Re: [sqlite] SQL CASE WHEN THEN ELSE END

Re: [sqlite] SQL CASE WHEN THEN ELSE END

[sqlite] SQL CASE WHEN THEN ELSE END

Re: [sqlite] importing data to a table that has generated-columns

Re: [sqlite] importing data to a table that has generated-columns

Re: [sqlite] importing data to a table that has generated-columns

[sqlite] importing data to a table that has generated-columns

[sqlite] SQLite: A couple minor doc bugs

Re: [sqlite] SSL: cannot connect to host www3.sqlite.org:443 (Connection refused)

12 matches

Site Navigation

Mail list logo

Footer information