Re: [sqlite] How should I use parenthesis?

[James K. Lowden]

On Mon, 27 Jan 2014 18:57:26 +0100
Jean-Christophe Deschamps  wrote:

> (
> select * from A where x in (subselectA)
> left outer join
> select * from B where y in (subselectB)
> )
> 
> union all
> 
> (  -- <-- error
> select * from B where y in (subselectC)
> left outer join
> select * from A where x in (subselectD)
> )

Perhaps you want:

select * from (
   select * from A where x in (subselectA)
   left outer join (
select * from B where y in (subselectB)
   ) as a
   on -- something
) as A1

  UNION ALL

select * from (
   select * from B where y in (subselectC)
   left outer join (
select * from A where x in (subselectD)
   ) as b
   on -- something
) as B1

JOIN takes tables (or table-like objects) as operands.  SELECT does not
yield a table unless it's wrapped in parenthesis.  

(I generally put UNION in uppercase to make it stand out, lest on a
quick scan it seem like two separate statements.)  

HTH.

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

Re: [sqlite] How should I use parenthesis?

[Igor Tandetnik]

On 1/27/2014 12:57 PM, Jean-Christophe Deschamps wrote:

I'm trying to find the correct syntaxt for this, but I hit a syntax
error each time: either SQLite shokes on outer parenthesis or on union all.

(
select * from A where x in (subselectA)
left outer join
select * from B where y in (subselectB)
)


This part already doesn't make sense. "left outer join" can only appear 
in the FROM clause - but that ends at the WHERE keyword. I can't even 
begin to guess what you are trying to do here. What exactly are you 
joining to what else, and on what condition?

--
Igor Tandetnik

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

[sqlite] How should I use parenthesis?

[Jean-Christophe Deschamps]

Dear list,

I'm trying to find the correct syntaxt for this, but I hit a syntax 
error each time: either SQLite shokes on outer parenthesis or on union all.


(
select * from A where x in (subselectA)
left outer join
select * from B where y in (subselectB)
)

union all

(  -- <-- error
select * from B where y in (subselectC)
left outer join
select * from A where x in (subselectD)
)

Union [all], except, intersect don't seem to accept parenthesis around 
the left or right parts.


Of course a workaround could be to create two views then union all 
them, but there must be a way to express this construct in a single 
statement.


I don't want the statement to be interpreted this way:

select * from A where x in (subselectA)
left outer join
(
select * from B where y in (subselectB)
union all
select * from B where y in (subselectC)
)
left outer join
select * from A where x in (subselectD)

--
jcd

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

Re: [sqlite] pragmas in subselects?

[Stephan Beal]

On Sun, Jan 26, 2014 at 9:45 PM, Jay Kreibich  wrote:

>
> Chapter 10 of Using SQLite covers virtual tables.  One of the examples
> given shows how to wrap a PRAGMA statement, so it can be used as system
> catalog and used in normal SELECT statements.  It would be pretty easy to
> expand the given example to cover almost any SQL statement (including any
> PRAGMA).
>
> Even if you don't have a copy of the book, you can download the example
> code off the product page:
>

i've got the book, so many thanks for that tip - i'll take a look.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tcl variable substitution issue

[Donald Allen]

I apologize for not replying directly to your messages. My account on
this mailing list is not set up to send me individual messages.

Richard Hipp wrote:

You should do what you want, of course.

But this statement is surprising since SQLite is really just a TCL
extension that has "escaped" into the wild.

My response:

That is certainly true, but the genesis doesn't change the mismatch.
Tcl is an almost-typeless language and this situation requires the
specification of types to be handled correctly, but there is no way in
Tcl to express a type explicitly. So it is necessary to resort to
tricks such as the one you suggested.

This problem could have been taken care of in the Tcl API, by
providing something analogous to the typed bind functions present in
the C API, e.g.,

int sqlite3_bind_double(sqlite3_stmt*, int, double);
int sqlite3_bind_int(sqlite3_stmt*, int, int);
int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*));

But the designers of the API chose not to do this, leaving the
Three-Card Monte, at-no-time-did-my-fingers-leave-my-hand kind of
trickery you proposed a necessity now, after wasting time figuring out
why 'set x 1' doesn't get treated as an integer.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] pragmas in subselects?

[Clemens Ladisch]

big stone wrote:
> There is a non-logicality of having "pragma table_info(my_table)" :
> - answering like a select,
> - but being not usable as a "select", nor queriable by a "select".

SQLite's PRAGMA implementations do not plug into the internal query
mechanism used by SELECT, but hardcode a VDBE program that directly
generates the output as seen by sqlite3_step/column_xxx.  There is no
built-in mechanism that would allow to use the output of a VDBE program
directly as input for other queries.  (But it is possible to write
a virtual table that does this.)


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users