asked on

Create View

Dear Experts, please help !
This sentence is quoted from of the 'Interbase 5 Data Definition Guide'
( the last line of page 126 )
* Important : When creating views, the Select statement cannot include an Order by clause

The question is : How can we make an Orderly View ?

pc_melsa

ASKER

Thanks from apin

ASKER CERTIFIED SOLUTION

inthe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JoeBooth

The SQL definition of a view does not have an ORDER BY section. You would need to add the ORDER BY clause on the statement that uses the view.

Try to think of a view as a virtual table, rather than a special entity.

simonet

I am kinda guessing here, but if you add a GROUP BY clause to the VIEW, you have the same effect than the ORDER BY. Obviously, since GROUP BY is not meant for this (the effect is what counts here), there will be sligthly more overhead than if you could have used ORDER BY.

The best approach, however, is to go with JoeBooth's suggestion.

Alex

inthe

didnt my comment say the same thing ?
can you not use order by in a query that uses the view?

simonet

Whatever. My approach was using GROUP BY, rather than ORDER BY.

Besides, I just skimmed through the other comments, not actually reading the thouroughly.

pc_melsa

ASKER

Dear Experts,
The grid doesn't display orderly as I'd like to, please help !

Thanks !

unit uDisplayStock;
interface
uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
ExtCtrls, DBCtrls, Grids, DBGrids, Db, IBDatabase, IBCustomDataSet,
IBTable, StdCtrls, ComCtrls;
type
TMyForm = class(TForm)
IBDatabase1: TIBDatabase;
IBTransaction1: TIBTransaction;
DataSource1: TDataSource;
DBGrid1: TDBGrid;
DBNavigator1: TDBNavigator;
ChangeBtn: TButton;
Label1 : TLabel;
IBStockTable: TIBTable;
IBStockTablePARTCODE:
TIBStringField;
IBStockTablePARTNAME:
TIBStringField;
IBStockTablePRICE: TIntegerField;
IBStockTableQUANTITY: TIntegerField;
procedure ChangeDisplayBtnClick(Sender: TObject);
private { Private declarations }
public { Public declarations }
end;

var
MyForm: TMyForm;

implementation
{$R *.DFM}

procedure TMyForm.ChangeBtnClick(Sender: TObject);
begin
IBStockTable.DisableControls ;
With IBStockTable Do
Begin
Active := False ;
IF TableName = 'FULL_STOCK' Then
Begin
TableName := 'PARTIAL_STOCK';
Label1.Caption := 'Display
only for quantity > 0 '> ';
ChangeBtn.Hint := 'Show all parts' ;
ChangeBtn.Caption := '&Entire Stock';
End
Else
Begin
TableName := 'FULL_STOCK';
Label1.Caption := 'Show all
parts' ;
ChangeBtn.Hint := 'Display
only for quantity > 0' ;
ChangeBtn.Caption := '&Partial Stock' ;
End ;
IndexName := '' ;
Active := True ;
End ; // With
IBStockTable.EnableControls ;
End ;
End.

Note : here is the ISql Script
CONNECT 'c:\Bram\stock.gdb'
USER 'SYSDBA' PASSWORD 'masterkey' ;

CREATE TABLE MyStock(PartCode CHAR(7) Not Null PRIMARY KEY,
PartName CHAR(30),
Price Integer, Quantity Integer ) ;

/* View: FULL_STOCK, Owner: SYSDBA */
CREATE VIEW FULL_STOCK AS Select * From MyStock ;

/* View: PARTIAL_STOCK, Owner: SYSDBA */
CREATE VIEW PARTIAL_STOCK AS Select * From MyStock Where Quantity > 0 ;

CREATE INDEX MyStockIndex ON MyStock(PartCode) ;