[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 147
  • Last Modified:

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 ?
0
pc_melsa
Asked:
pc_melsa
  • 2
  • 2
  • 2
  • +1
1 Solution
 
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
 
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now