Our system is customisable, in that the users can add extra fields and tables, or create their own views, or customise the existing views to suit themselves.
Hardcoding SELECT's within the software would seriously degrade the flexibility of the product. By having the product do a SELECT * on a view allows the user maximum flexibility when customising the product. Regards, Jason. -----Original Message----- From: George Gensure [mailto:[EMAIL PROTECTED] Sent: 04 December 2003 11:12 To: Fitzharris, Jason Cc: [EMAIL PROTECTED] Subject: Re: [sqlite] Can't order a view with joins 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]
