The following situation has me mystified. I created a view, say, like so --
> CREATE qry_contacts AS > SELECT c.contact_id, c.firstname, c.lastname, > (CASE > WHEN > (c.firstname & c.lastname) ISNULL > THEN > 'unnamed' > ELSE > (c.firstname & ' ' & c.lastname) > END) AS fullname, > ct.contacttype > FROM contacts c LEFT JOIN contacttypes ct ON > c.contact_id = ct.contact_id;
Now, using DBI, when I try to run a query like so --
SELECT contact_id, fullname, contacttype FROM qry_contacts WHERE contact_id = 4
I get an error saying there is no such column as contact_id.
After much hand-wringing I concluded that the column names in my view were not (for the want of a better word) auto-aliasing to the non-dot-prefixed column names. That is, c.contact_id was remaining as c.contact_id, not aliasing as contact_id.
Modifying the view to --
> CREATE qry_contacts AS
> SELECT c.contact_id AS contact_id, c.firstname AS firstname, c.lastname AS lastname,
> (CASE
> WHEN
> (c.firstname & c.lastname) ISNULL
> THEN
> 'unnamed'
> ELSE
> (c.firstname & ' ' & c.lastname)
> END) AS fullname,
> ct.contacttype AS contacttype
> FROM contacts c LEFT JOIN contacttypes ct ON
> c.contact_id = ct.contact_id;
made it work.
Is that normal and expected behavior of views (doesn't happen like in Access or SQL Server or Oracle), or normal and expected behavior of SQLite? Is that a DBD-SQLite issue?
The reason I ask the latter is because from within SQLite, the first view works fine... Also, of course, SELECT * works fine as long as I don't provide a WHERE clause. Printing everything out with Data-Dumper shows the column names without any dot-prefix.
Any guidance will be much appreciated.
======
Is there an archive of these mailing lists where I can view previous posts?
Many thanks,
Puneet.
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
