On Tue, 11 May 2004, Mitchell Vincent wrote:
>This is a re-post as I didn't get any comments on what that error
>message really means..
>
>My view :
>
>CREATE view product_detail as SELECT * FROM products as p,categories as
>c WHERE c.category_id = p.category_id;
>
>When I do this query (which happens at a certain point in one of my
>applications) I get an error "ORDER BY terms must be non-integer
>constants"..
>
>Offending Query :
>
>SELECT * FROM product_detail WHERE lower("p.product_name") LIKE
>lower('%') ORDER BY "p.product_name" ASC
At a guess, I think it is that you're trying to access the internal
workings of the view.
Views are meant to be opaque, so p.product_name means nothing as p is not
defined in this context.
Select explicit columns into the view, so you know exactly what the
columns are called, and use the view column names, not the table column
names.
As an example, I have a database with packages and files, joined on the
package name:
create table files (
file text primary key,
package text,
md5 text
);
create table packages (
package text primary key,
version text
);
The following view:
create view files_by_package as select * from files as f, packages as p
where f.package = p.package;
gives me all the package details of each file. But the actual column names
for the view are:
sqlite> .headers on
sqlite> select * from files_by_package limit 1;
file|package|md5|package_1|version
bin/nco_run|common-scripts|d89dc9d4662f003eb1a988ad23f8258b|common-scripts|7.0
So package clashes and is resolved by sqlite by appending _1 to the second
instance of column package. No mention of f or p from the view definition.
A better view would be:
create view files_by_package as
select f.file as file, f.md5 as md5, p.package as package, p.version as version
from files as f, packages as p
where f.package = p.package;
This makes it explicit what mu columns are.
>
>It works fine and doesn't complain in 2.8.11 but in 2.8.13 it gives that
>error..
Probably an accident of implementation.
>
>Thanks!
>
>-- Mitchell Vincent
>
Christian
--
/"\
\ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
