Re: [sqlite] Can't order a view with joins

[George Gensure]

Glad the solution works, but I'm not sure why it defeats the point of 
having views in the first place.  You can certainly have more 
interesting selects in a view than * with the order by.  How do you feel 
this negates the purpose of views?

-George

Fitzharris, Jason wrote:

Hi George,
 I didn't know that you could ORDER BY within a view, SQL Server and DB2
don't allow this. However in my case this isn't a solution as we have grids
where the user can decide which field to order by.
 I do notice that this works.
   SELECT *
   FROM tabTest_1, tabTest_2
   WHERE Field_1_1 = Field_2_1
   ORDER BY Field_1_1
 However, this defeats the point of having views in the first place.

Regards,
Jason.
-----Original Message-----
From: George Gensure [mailto:[EMAIL PROTECTED]
Sent: 02 December 2003 16:40
To: Fitzharris, Jason
Cc: [EMAIL PROTECTED]
Subject: Re: [sqlite] Can't order a view with joins
One way I get around this sort of problem is to have the view order
itself by specifying an ORDER BY in the CREATE VIEW.  This is limited,
obviously, since the only times I want data out of this view, I want it
in order, but if this is the case with your work, it should do nicely.
-George
[EMAIL PROTECTED]
On Tue, 2 Dec 2003, Fitzharris, Jason wrote:

 

Hi,
 I am getting an error when trying to do an order by on a view which has
   

a
 

join. To replicate, create the following two tables and the view.

CREATE TABLE tabTest_1 (
 Field_1_1 CHAR(10),
 Field_1_2 CHAR(10),
 Field_1_3 CHAR(10),
 Field_1_4 CHAR(10)
)
CREATE TABLE tabTest_2 (
 Field_2_1 CHAR(10),
 Field_2_2 CHAR(10),
 Field_2_3 CHAR(10),
 Field_2_4 CHAR(10)
)
CREATE VIEW vTest
AS
SELECT *
FROM tabTest_1, tabTest_2
WHERE Field_1_1 = Field_2_1
There is no need to insert any data. Now do a simple select from the view
like so.
SELECT * FROM vTest

This works fine as it returns all eight columns. Now try and run this
   

query.
 

SELECT * FROM vTest ORDER BY Field_1_1

This fails with the message "Select Error: no such column: Field_1_1"
However if I drop the view and recreate it by specifying all of the fields
like so.
CREATE VIEW vTest
AS
SELECT Field_1_1, Field_1_2, Field_1_3, Field_1_4, Field_2_1, Field_2_2,
Field_2_3, Field_2_4
FROM tabTest_1, tabTest_2
WHERE Field_1_1 = Field_2_1
Now if I run the second query with the order by it works fine.

Is this a bug with SQLite? And if so, is there any indication if/when it
will be fixed?
I have many views which join several tables which have many columns, and I
don't want to spend ages writing out all of the columns if a fix is
   

pending.
 





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



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