RE: [sqlite] CONCAT in SQLite (was Re: [sqlite] correct syntax for CASE...)
> -Original Message-
> From: Andrew Piskorski [mailto:[EMAIL PROTECTED]
> Sent: Sunday, May 02, 2004 12:09 PM
> To: Puneet Kishor
> Cc: D. Richard Hipp; SQLite
> Subject: Re: [sqlite] CONCAT in SQLite (was Re: [sqlite]
> correct syntax
> for CASE...)
>
>
> On Sun, May 02, 2004 at 01:59:34PM -0500, Puneet Kishor wrote:
>
> > >Probably you mean the concatenate operator, which in
> > >SQL is ||, not &. & is not an SQL operator as far as
>
> > drats... concat is '+' in Javascript, '.' in Perl, '&' in
> Access and
> > SQL Server, '||' in SQlite, and, well, 'CONCAT' in Oracle.
> Why can't
> > the entire world just speak Hindi ;-).
>
> It is '||' in Oracle, and I believe PostgreSQL as well. 'CONCAT' may
> also work there, but if so I've never seen it used.
>
CONCAT is used in a few DBMS's as it allows one to use a function call instead of an
(infix?) operator.
E.g.
select concat('Hello', ' world') from mytable
Obviously many nested uses of concat() can look rather hairy...
As a side note SQL Server also uses "+" (I think "&" was just added to keep Access
users happy :-p).
Chris
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] CONCAT in SQLite (was Re: [sqlite] correct syntax for CASE...)
On May 2, 2004, at 6:52 PM, Kurt Welgehausen wrote: ... handicapped by not being able to use parens in a FROM clause. It's not that you can't use parens anywhere in a FROM clause; you just can't have the entire table-list enclosed in parens. You can still do something like select * from (t1 left join t2 on ...) join t3 on ... Thanks Kurt, for explaining this patiently. I am looking at my SQL statement... .. FROM (contacts AS c LEFT JOIN contacts_contacttypes AS cct ON c.contact_id = cct.contact_id) LEFT JOIN cities AS ci ON c.city_id = ci.city_id) LEFT JOIN contacttypes AS ct ON cct.contacttype_id = ct.contacttype_id) LEFT JOIN orgs AS o ON c.org_id = o.org_id) LEFT JOIN orgtypes AS ot ON o.orgtype_id = ot.orgtype_id) LEFT JOIN orgsubtypes AS ost ON o.orgsubtype_id = ost.orgsubtype_id As far as I can see, I don't have the entire table list in parens... it is not different from what you are suggesting, and yet, it does not work. SQLite croaks saying error at (. Am I seeing things differently? - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] CONCAT in SQLite (was Re: [sqlite] correct syntax for CASE...)
> ... handicapped by not being able to use parens in a FROM clause. It's not that you can't use parens anywhere in a FROM clause; you just can't have the entire table-list enclosed in parens. You can still do something like select * from (t1 left join t2 on ...) join t3 on ... Regards - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] CONCAT in SQLite (was Re: [sqlite] correct syntax for CASE...)
On May 2, 2004, at 4:07 PM, Kurt Welgehausen wrote: FROM (contacts AS c LEFT JOIN ... This has come up several times on this list. This behavior is entirely consistent with the grammar for select statements given in lang.html. The (partial) syntax is "FROM table-list"; "FROM (table-list)" is not allowed. I believe this also agrees with the SQL92 std. Interesting. I find it difficult to imagine any good reason to not allow parens as they improve readability. Additionally, parens are the only way _I_ find it easy to mentally parse SQL92 type FROM JOIN type statements, each join becoming like a table and then joined to the next table. Unless I decide to start using Oracle type syntax by putting my join logic in my constraint and using the (+) method, I find myself fairly handicapped by not being able to use parens in a FROM clause. Oh well... - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] CONCAT in SQLite (was Re: [sqlite] correct syntax for CASE...)
>> FROM (contacts AS c LEFT JOIN ... This has come up several times on this list. This behavior is entirely consistent with the grammar for select statements given in lang.html. The (partial) syntax is "FROM table-list"; "FROM (table-list)" is not allowed. I believe this also agrees with the SQL92 std. Regards - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] CONCAT in SQLite (was Re: [sqlite] correct syntax for CASE...)
>> It is '||' in Oracle, and I believe PostgreSQL as well... "||" is the concatenation operator in std SQL, and has been at least since 1992. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
