On May 2, 2004, at 1:28 PM, D. Richard Hipp wrote:


Puneet Kishor wrote:
I am getting seriously hassled trying to do the following simple thing...
SELECT
(CASE
WHEN
c.firstname ISNULL AND c.lastname ISNULL
THEN
'unnamed'
ELSE
c.firstname & ' ' & c.lastname
^ ^

Probably you mean the concatenate operator, which in
SQL is ||, not &.  & is not an SQL operator as far as
I am aware, though SQLite uses it to mean bit-wise AND,
just like C/C++.

        END) AS fullname,
FROM contacts c



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 ;-).


Thanks though, for the tip, and that sets that one right, but raises a few other...

CREATE VIEW qry_contacts AS
SELECT c.contact_id, c.firstname, c.lastname,
CASE
WHEN
((c.firstname ISNULL) AND (c.lastname ISNULL))
THEN
'unnamed'
ELSE
c.firstname || ' ' || c.lastname
END AS fullname,
c.title, c.org_id, o.org, o.orgtype_id, o.orgsubtype_id, ost.orgsubtype,
ot.orgtype, c.city_id, ci.city, c.ph_direct, c.email, ct.contacttype_id,
ct.contactype
FROM contacts c LEFT JOIN contacts_contacttypes cct ON
c.contact_id = cct.contact_id LEFT JOIN cities ci ON c.city_id =
ci.city_id LEFT JOIN contacttypes ct ON cct.contacttype_id =
ct.contacttype_id LEFT JOIN orgs o ON c.org_id = o.org_id LEFT JOIN
orgtypes ot ON o.orgtype_id = ot.orgtype_id LEFT JOIN orgsubtypes
ost ON o.orgsubtype_id = ost.orgsubtype_id


creates the view. However, when I was doing the following I kept on getting errors (note the multitude of parens)...

..
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


Seems like SQLite doesn't like all those parentheses. I take out the parens and it works. What gives?


--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]



Reply via email to