Re: [sqlite] Unable to create table, default value of column [ID] is not constant

I made some changes. You do not need "unique" and "primary key" on the same variable. You still need a rowid. I removed the unique constraint otherwise updating to duplicates does not work, and then you cannot see that it works.. CREATE TABLE Programmes ( ID TEXT, Title

Re: [sqlite] Unable to create table, default value of column [ID] is not constant

But you could use an INSERT  trigger instead. Also "does contains no sub-queries" ought to "doesn't contain any sub-queries", shouldn't it? Peter On Monday, June 20, 2016 5:55 PM, Peter Aronson wrote: According to https://www.sqlite.org/lang_createtable.html:

Re: [sqlite] Order of applying subquery in SELECT statement

> On Mon, Jun 20, 2016 at 5:18 PM, Keith Medcalf > wrote: > > > > > You have all ill-phrased correlated subquery. Only by putting c.id in > > the select list and adding c.id to the group go do you have a properly > > phrased correlated subquery. > > > > However, > > > >

Re: [sqlite] Unable to create table, default value of column [ID] is not constant

According to https://www.sqlite.org/lang_createtable.html: "An explicit DEFAULT clause may specify that the default value is NULL, a string constant, a blob constant, a signed-number, or any constant expression enclosed in parentheses. A default value may also be one of the special

[sqlite] Unable to create table, default value of column [ID] is not constant

Alright, trying to set the scenario: * I open/create a database * I register a custom function called HASH (set to be deterministic), tested and working * I create a bunch of tables (if not exists) So far, everything works fine. Then, the troublesome create: CREATE TABLE IF NOT EXISTS

Re: [sqlite] Order of applying subquery in SELECT statement

On 21 Jun 2016, at 1:31am, J Decker wrote: > as long as the dataset > is exactly the same or is modified in ways that don't break the index > lookup ordering you'll be 'stable' Or you upgrade to a new version of SQLite which optimizes the query differently. Simon.

Re: [sqlite] Order of applying subquery in SELECT statement

On Mon, Jun 20, 2016 at 5:18 PM, Keith Medcalf wrote: > > You have all ill-phrased correlated subquery. Only by putting c.id in > the select list and adding c.id to the group go do you have a properly > phrased correlated subquery. > > However, > > The projection (join)

Re: [sqlite] Order of applying subquery in SELECT statement

You have all ill-phrased correlated subquery. Only by putting c.id in the select list and adding c.id to the group go do you have a properly phrased correlated subquery. However, The projection (join) will be performed first using whatever order and indexes the optimizer decides to use to

Re: [sqlite] Order of applying subquery in SELECT statement

On 6/20/2016 3:32 PM, R Smith wrote: SELECT a.id, MAX(b.orderno) as maxorderno FROM a INNER JOIN c on c.a_id = a.id INNER JOIN b ON b.c_id = c.id GROUP BY a.id; -- | maxorder- --- This is what I think you might actually want, -- id | no --- it

Re: [sqlite] Referencing a column alias (subquery) as function parameter

Just a fix - the last example had a mistake in and should read: Option 2 (CTE): WITH Q (c1, c2, cd, ac) AS ( SELECT c1, c2, CurrentDate, (CASE ActiveCol WHEN 0 THEN c3 ELSE c4 END) FROM ... ) SELECT c1, c2, cd, ac, MyFn(c1,ac,cd) AS xc1, MyFn(c2,ac,cd) AS xc2

Re: [sqlite] Order of applying subquery in SELECT statement

On 2016/06/20 5:37 PM, Harmen de Jong - CoachR Group B.V. wrote: I have a query with a subquery (using an aggregate function) in the SELECT statement. Will this subquery be executed before or after the GROUP BY? For example: SELECT a.id, (SELECT max(orderno) FROM b WHERE b.c_id=c.id) as

Re: [sqlite] Order of applying subquery in SELECT statement

On 6/20/2016 11:37 AM, Harmen de Jong - CoachR Group B.V. wrote: I have a query with a subquery (using an aggregate function) in the SELECT statement. Will this subquery be executed before or after the GROUP BY? For example: SELECT a.id, (SELECT max(orderno) FROM b WHERE b.c_id=c.id) as

Re: [sqlite] Referencing a column alias (subquery) as function parameter

On 2016/06/20 6:21 PM, Bernard Ertl wrote: Is it possible to do something like this: SELECT c1, c2, CurrentDate, (CASE ActiveCol WHEN 0 THEN c3 ELSE c4 END) AS ac, MyFn(c1,ac,CurrentDate) AS xc1, MyFn(c2,ac,CurrentDate) AS xc2 FROM ... where c1, c2, CurrentDate, ActiveCol, c3, c4 are

Re: [sqlite] Order of applying subquery in SELECT statement

On Mon, Jun 20, 2016 at 8:37 AM, Harmen de Jong - CoachR Group B.V. < har...@coachr.com> wrote: > I have a query with a subquery (using an aggregate function) in the SELECT > statement. Will this subquery be executed before or after the GROUP BY? > For example: > SELECT a.id, (SELECT max(orderno)

Re: [sqlite] Referencing a column alias (subquery) as function parameter

On Mon, Jun 20, 2016 at 10:21 AM, Bernard Ertl wrote: > Is it possible to do something like this: > > SELECT c1, c2, CurrentDate, (CASE ActiveCol WHEN 0 THEN c3 ELSE c4 END) AS > ac, > MyFn(c1,ac,CurrentDate) AS xc1, MyFn(c2,ac,CurrentDate) AS xc2 FROM > ... >

[sqlite] Referencing a column alias (subquery) as function parameter

Is it possible to do something like this: SELECT c1, c2, CurrentDate, (CASE ActiveCol WHEN 0 THEN c3 ELSE c4 END) AS ac, MyFn(c1,ac,CurrentDate) AS xc1, MyFn(c2,ac,CurrentDate) AS xc2 FROM ... where c1, c2, CurrentDate, ActiveCol, c3, c4 are all columns in the table(s) referenced in the

[sqlite] Order of applying subquery in SELECT statement

I have a query with a subquery (using an aggregate function) in the SELECT statement. Will this subquery be executed before or after the GROUP BY? For example: SELECT a.id, (SELECT max(orderno) FROM b WHERE b.c_id=c.id) as maxorderno FROM a INNER JOIN c on c.a_id=a.id GROUP BY a.id Best regards,

Re: [sqlite] ":memory:" path does not seem to work on Windows anymore

On 30.05.2016 17:09, Richard Hipp wrote: On 5/30/16, Kirill Müller wrote: I'd appreciate any pointers on bisecting SQLite. Thanks. (1) Make sure you have tclsh 8.5 or later installed on your system, as there are various TCL scripts that SQLite makefile needs to run in

Re: [sqlite] Lemon bug in shiftreduce action for error symbol

On Fri, Jun 17, 2016 at 11:50:48AM -0400, Richard Hipp wrote: > On 6/6/16, Vincent Zweije wrote: > > > > > When the shiftreduce action is used in an error context, > > such as: > > > >X -> alpha error. > > > > the adjustment is not made. This causes error handling to