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_melsaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pc_melsaAuthor Commented:
Thanks from apin
0
intheCommented:
i dunno much about this but from what i see  you need to specify the ORDER BY in the query that uses the view.
this is the example i found (a bit meaningless but shows what i mean):

Create View Home_Ops as
  Select C.ID, OP.OPSTATUS, OP.ORGID
  FROM CAREHOMES C LEFT OUTER JOIN OPERATORS OP ON (OP.ID = C.ID);
 
Then your query would be:
 
SELECT C.ID, C.NAME, O1.NAME AS MAJPROV, O2.NAME AS MAJMAN
FROM CAREHOMES C, ORGANISATIONS O1, ORGANISATIONS O2,
Home_OPS OP1, Home_Ops OP2
WHERE (C.ID = 1204)
  AND (OP1.ID = C.ID)
  AND (OP1.OPSTATUS = 'O')
  AND (O1.ID = OP1.ORGID)
  AND (OP2.ID = C.ID)
  AND (OP2.OPSTATUS = 'M')
  AND (O2.ID = OP2.ORGID)
  ORDER BY C.ID

hope it helps in some way
Regards Barry
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JoeBoothCommented:
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.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

simonetCommented:
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
0
intheCommented:
didnt my comment say the same thing ?
can you not use order by in a query that uses the view?
0
simonetCommented:
Whatever. My approach was using GROUP BY, rather than ORDER BY.

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

0
pc_melsaAuthor Commented:
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) ;

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.