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]