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]
