Concept™ SQL Views
Reference / SQL ViewsTitle / Concept SQL View Documentation
Author / Russell Pooley
Date / 03 - 06 - 1999
Updated / 17-12-2004
Version / 3.0
© 2004 FSI (FM Solutions) Limited
SQL Views
Contents
Introduction 5
What are Views 5
What are the advantages of Views 6
Without View 6
With Views 6
Example Standard Views 7
VW_ASSET_DETAILS 7
VW_PPM_DETAILS 7
VW_PPM_TASKS 8
VW_CALL_TASKS 8
VW_TASK_DETAILS 8
VW_TASK_INVOICES 9
Contract Based Invoicing 9
Cost Centre Based Invoicing 9
VW_STOCK_DETAILS 10
VW_STOCK_SUPPLIERS 10
VW_STOCK_ISSUED 10
VW_STOCK_RETURNED 11
VW_RESOURCE_ATTACHED 11
VW_COST_MATRIX 12
VW_PURCHASE_DETAILS 12
VW_PURCHASE_ITEM_DETAILS 12
VW_ASSET_PROPERTIES 13
VW_TASK_ANSWERS 13
VW_CTR_GRP_TASKS 13
VW_RMB_BOOKINGS 14
VW_RMB_ATTENDEE 14
VW_RMB_CATERING 14
Example Complex Views 15
VW_DEPARTMENT_SUMMARY 15
VW_CATEGORY_SUMMARY 15
VW_ASSET_YEAR_COST_PLAN 15
VW_ASSET_EST_VERSUS_ACTUAL 16
VW_ASSET_SUMMARY 17
VW_ORDER_PAYMENTS_AND_INVOICES 18
VW_PURCHASE_ORDERS_ALIAS 19
Server Configuration 21
SQL Server 6.5/7 21
Oracle 21
SQL Views
Introduction
The following documentation is provided to explain the many usage's and the simplicity of using views to report upon, and how easy it is to create new views according to your own needs. A run through of two major database server types will be explained and how to best use the views for the purpose of reports.
What are Views
What a view does is to allow you to assign the results of a query to a new, personal table, that you can use in other queries, where this new table is given the view name in your FROM clause. When you access a view, the query that is defined in your view creation statement is performed (generally), and the results of that query look just like another table in the query that you wrote invoking the view. For example, to create a view:
CREATE VIEW TASKS AS SELECT * FROM F_TASKS;
Now, write a query using this view as a table, where the table is just a listing of all Task ID's from the Task table:
SELECT TA_TASK_ID
FROM TASKS
Another example of a view would be to automatically link for example the building to an asset, the view may look like this.
CREATE VIEW ASSET AS SELECT * FROM ASSETS, BUILDINGS WHERE
ASSET_BUILDING = BUILDING;
This view could then be used in the following manner. Note there is no need to manually link each table together.
SELECT ASSET, BUILDING FROM ASSET
Views can be used to restrict database access to only certain columns, as well as, in this case, simplify a complex query.
What are the advantages of Views
There are many advantages of using views to interrogate data within a database, it could be for example you wish to list all the Tasks within your system and which asset's they may be attached to, this can be easily done using a view.
Views enter a league of there own is when used with multiple tables, the following example will try to explain why and how it would make reporting easier using these tools.
You wish to review all the tasks within the system with Asset, Task, Building and Location details, this report is to be produced in Crystal Reports. I will first try to explain how this would be done without the use of a view, then with the use of a view.
Without View
What follows is a step-by-step process of how a report similar to the one above would be created using a view?
1. Create New Report, select database to use for report.
2. Choose Tables for Report, these will be FASSET,F_TASKS,FAREALO and FLOCATE
3. Using the Visual Linking Expert from within Crystal Reports you must now link up each table, this means creating 3 links between the tables.
4. Now using the Insert Fields window place onto the Report the required fields.
5. The report is now ready to run
This process wasn't particularly hard, but for many non-IT literate users this can be a bit overwhelming, especially when trying to explain why tables must be linked and the purpose for linking them. The only other alternative is to use a view, which will be explained next.
With Views
What follows is the same step-by-step as above except it will now use a view instead of selecting each separate table.
1. Create New Report, select database to use for report.
2. Choose Tables for Report. Instead of the previous 4 tables, select the TASK_VIEW view instead.
3. Now using the Insert Fields window place onto the Report the required fields.
4. The report is now ready to run
As you can probably see, the difference between the two reports is not very much, but the difficult part of setting up the relationships between database tables has gone. This part has been replaced by the view.
The view does need to be created but once it has been created, it can be used over and over again without the need to duplicate report data. The view has been made, and now anyone can use it, this is what makes views so powerful.
Example Standard Views
What is now provided is explanations of all the views we can currently supply, each once will be described on how they work and what data they will retrieve. This should hopefully give a few good examples on how to possibly write your own views, for your own specific needs
VW_ASSET_DETAILS
The following view when used will retrieve Asset Details from the database, linking to it Buildings, Locations, Suppliers and Cost Centres.
CREATE VIEW VW_ASSET_DETAILS
AS SELECT *
FROM FASSET, FLOCATE, FAREALO, FSUPPLY, F_COST_CENTRE
WHERE
BG_SEQ = AR_FKEY_BG_SEQ AND
LO_SEQ = AR_FKEY_LO_SEQ AND
SUP_SEQ = AR_FKEY_SUP_SEQ AND
FC_SEQ = AR_FKEY_FC_SEQ
VW_PPM_DETAILS
The following view will select all data from PPM Record table, linking to it Asset Details, Buildings, Location, Cost Centres, Cost Codes, Contract and Instruction Sets.
CREATE VIEW VW_PPM_DETAILS
AS SELECT *
FROM FASSET, FLOCATE, FAREALO, F_COST_CENTRE, F_COST_CODE, FPPM, FINFILE, F_CONTRACT
WHERE
PPM_FKEY_AR_SEQ = AR_SEQ AND
AR_FKEY_BG_SEQ = BG_SEQ AND
AR_FKEY_LO_SEQ = LO_SEQ AND
PPM_FKEY_FC_SEQ = FC_SEQ AND
PPM_FKEY_FCC_SEQ = FCC_SEQ AND
PPM_FKEY_IN_SEQ = IN_SEQ AND
PPM_FKEY_CTR_SEQ = CTR_SEQ AND
CTR_SEQ > 0
VW_PPM_TASKS
This view will select all the PPM Task Details within the database, this will automatically link to Cost Centre, Cost Code, Contract, Asset, Building, Location and PPM Details. (PPM's)
CREATE VIEW VW_PPM_TASKS
AS SELECT *
FROM F_TASKS, F_COST_CENTRE, F_COST_CODE, F_CONTRACT, FASSET, FLOCATE, FAREALO, F_PPM_DETAILS
WHERE
TA_FKEY_FC_SEQ = FC_SEQ AND
TA_FKEY_FCC_SEQ = FCC_SEQ AND
TA_FKEY_CTR_SEQ = CTR_SEQ AND
TA_FKEY_AR_SEQ = AR_SEQ AND
TA_FKEY_BG_SEQ = BG_SEQ AND
TA_FKEY_LO_SEQ = LO_SEQ AND
PDET_FKEY_TA_SEQ = TA_SEQ AND
CTR_SEQ > 0
VW_CALL_TASKS
This view is similar to VW_PPM_TASKS except it makes a link to Breakdown Details instead of PPM Details, thus only retrieves data related to Service Calls (Breakdowns). Cost Centre, Cost Code, Contract, Asset, Building, Location and Breakdown Details are used.
CREATE VIEW VW_CALL_DETAILS
AS SELECT *
FROM F_TASKS, F_COST_CENTRE, F_COST_CODE, F_CONTRACT, FASSET, FLOCATE, FAREALO, F_BD_DETAILS
WHERE
TA_FKEY_FC_SEQ = FC_SEQ AND
TA_FKEY_FCC_SEQ = FCC_SEQ AND
TA_FKEY_CTR_SEQ = CTR_SEQ AND
TA_FKEY_AR_SEQ = AR_SEQ AND
TA_FKEY_BG_SEQ = BG_SEQ AND
TA_FKEY_LO_SEQ = LO_SEQ AND
BDET_FKEY_TA_SEQ = TA_SEQ AND
CTR_SEQ > 0
VW_TASK_DETAILS
This view is a merge of the above two views, it allows the user to retrieve data about all the task details stored in the system regardless of whether they are PPM's or Service Calls (Breakdowns).
Cost Centre, Cost Code, Contract, Asset, Building and Location are used.
CREATE VIEW VW_TASK_DETAILS
AS SELECT *
FROM F_TASKS, F_COST_CENTRE, F_COST_CODE, F_CONTRACT, FASSET, FLOCATE, FAREALO
WHERE
TA_FKEY_FC_SEQ = FC_SEQ AND
TA_FKEY_FCC_SEQ = FCC_SEQ AND
TA_FKEY_CTR_SEQ = CTR_SEQ AND
TA_FKEY_AR_SEQ = AR_SEQ AND
TA_FKEY_BG_SEQ = BG_SEQ AND
TA_FKEY_LO_SEQ = LO_SEQ AND
CTR_SEQ > 0
VW_TASK_INVOICES
This view will list all the invoices and items for those invoices against each task, linking to the supplier and client. This view has a small modification according to how invoices are created. It can be either Contract based or Cost Centre based.
Contract Based Invoicing
CREATE VIEW VW_TASK_INVOICES
AS SELECT *
FROM F_TRANS_HEAD, F_TRANS_ITEM, F_TASKS, F_CONTRACT, FSUPPLY, F_CLIENT
WHERE
TRIT_FKEY_TRHD_SEQ = TRHD_SEQ AND
TA_FKEY_TRHD_SEQ = TRHD_SEQ AND
TRHD_FKEY_LNK_SEQ = CTR_SEQ AND
CTR_FKEY_SUP_SEQ = SUP_SEQ AND
CTR_FKEY_CLNT_SEQ = CLNT_SEQ AND
TRHD_FKEY_CLNT_SEQ = CLNT_SEQ AND
CTR_SEQ > 0
Cost Centre Based Invoicing
CREATE VIEW VW_TASK_INVOICES
AS SELECT *
FROM F_TRANS_HEAD, F_TRANS_ITEM, F_TASKS, F_COST_CENTRE, F_CONTRACT, FSUPPLY, F_CLIENT
WHERE
TRIT_FKEY_TRHD_SEQ = TRHD_SEQ AND
TA_FKEY_TRHD_SEQ = TRHD_SEQ AND
TRHD_FKEY_LNK_SEQ = FC_SEQ AND
CTR_FKEY_SUP_SEQ = SUP_SEQ AND
FC_FKEY_CLNT_SEQ = CLNT_SEQ AND
TRHD_FKEY_CLNT_SEQ = CLNT_SEQ AND
TA_FKEY_CTR_SEQ = CTR_SEQ AND
CTR_SEQ > 0
VW_STOCK_DETAILS
This view will display all the stock stored on the system linking with Building, Location.
CREATE VIEW VW_STOCK_DETAILS
AS SELECT *
FROM FSTOCK, FLOCATE, FAREALO, F_CONTRACT
WHERE
STK_FKEY_LO_SEQ = LO_SEQ AND
STK_FKEY_BG_SEQ = BG_SEQ AND
LO_FKEY_BG_SEQ = BG_SEQ AND
CTR_SEQ=STK_FKEY_CTR_SEQ AND
STK_SEQ > 0
VW_STOCK_SUPPLIERS
View all the Stock linking to Building, Location and Supplier.
CREATE VIEW VW_STOCK_SUPPLIERS
AS SELECT *
FROM FSTOCK, FLOCATE, FAREALO, FSUPPLY, F_LNK_STK_SUP
WHERE
STK_FKEY_LO_SEQ = LO_SEQ AND
STK_FKEY_BG_SEQ = BG_SEQ AND
LO_FKEY_BG_SEQ = BG_SEQ AND
FLSS_FKEY_SUP_SEQ = SUP_SEQ AND
FLSS_FKEY_STK_SEQ = STK_SEQ AND
STK_SEQ > 0
VW_STOCK_ISSUED
This view will display all the issued stock in the system, it will also link to Cost Centre, Cost Code, Tasks and Contracts.
CREATE VIEW VW_STOCK_ISSUED
AS SELECT *
FROM FSTOCK, F_STK_TRNS_HEAD, F_STK_TRANS, F_COST_CENTRE, F_COST_CODE, F_TASKS, F_CONTRACT
WHERE
STK_FKEY_CTR_SEQ = CTR_SEQ AND
STH_FKEY_FC_SEQ = FC_SEQ AND
STH_FKEY_FCC_SEQ = FCC_SEQ AND
STH_FKEY_CTR_SEQ = CTR_SEQ AND
STH_FKEY_TA_SEQ = TA_SEQ AND
STT_FKEY_STK_SEQ = STK_SEQ AND
STT_FKEY_STH_SEQ = STH_SEQ AND
STT_FKEY_TA_SEQ = TA_SEQ AND
STT_FKEY_CTR_SEQ = CTR_SEQ AND
TA_FKEY_CTR_SEQ = CTR_SEQ AND
TA_FKEY_FC_SEQ = FC_SEQ AND
TA_FKEY_FCC_SEQ = FCC_SEQ AND
STT_QUANTITY > = 0 AND
STK_SEQ > 0
VW_STOCK_RETURNED
This view will display all the returned stock in the system, it will also link to Cost Centre, Cost Code, Tasks and Contracts.
CREATE VIEW VW_STOCK_RETURNED
AS SELECT *
FROM FSTOCK, F_STK_TRNS_HEAD, F_STK_TRANS, F_COST_CENTRE, F_COST_CODE, F_TASKS, F_CONTRACT
WHERE
STK_FKEY_CTR_SEQ = CTR_SEQ AND
STH_FKEY_FC_SEQ = FC_SEQ AND
STH_FKEY_FCC_SEQ = FCC_SEQ AND
STH_FKEY_CTR_SEQ = CTR_SEQ AND
STH_FKEY_TA_SEQ = TA_SEQ AND
STT_FKEY_STK_SEQ = STK_SEQ AND
STT_FKEY_STH_SEQ = STH_SEQ AND
STT_FKEY_TA_SEQ = TA_SEQ AND
STT_FKEY_CTR_SEQ = CTR_SEQ AND
TA_FKEY_CTR_SEQ = CTR_SEQ AND
TA_FKEY_FC_SEQ = FC_SEQ AND
TA_FKEY_FCC_SEQ = FCC_SEQ AND
STT_QUANTITY < 0 AND
CTR_SEQ > 0
VW_RESOURCE_ATTACHED
These view displays all the resources stored within the system, displaying also the buildings it is attached to and supplier.
Links to Resource, Building and Supplier.
CREATE VIEW VW_RESOURCE_ATTACHED
AS SELECT *
FROM F_RESOURCE, FLOCATE, FSUPPLY, F_LNK_RES_BLDG
WHERE
RES_FKEY_SUP_SEQ = SUP_SEQ AND
LNK_RB_FKEY_RES_SEQ = RES_SEQ AND
LNK_RB_FKEY_BG_SEQ = BG_SEQ
VW_COST_MATRIX
This view is used to review all the details within the Cost Matrix.
This view links to Cost Centre, Cost Code and Cost Matrix.
CREATE VIEW VW_COST_MATRIX
AS SELECT *
FROM F_COST_CENTRE, F_COST_CODE, F_COST_MATRIX
WHERE
FCCM_FKEY_FC_SEQ = FC_SEQ AND
FCCM_FKEY_FCC_SEQ = FCC_SEQ
VW_PURCHASE_DETAILS
This view will list all the Purchase Orders in the system, it will also attach the Contract and related Supplier. This view will not include Purchase Order Items.
Supplier, Contract and Purchase Orders link.
CREATE VIEW VW_PURCHASE_DETAILS
AS SELECT *
FROM F_PO_HEAD, F_CONTRACT, FSUPPLY
WHERE
POH_FKEY_CTR_SEQ = CTR_SEQ AND
POH_FKEY_SUP_SEQ = SUP_SEQ AND
CTR_FKEY_SUP_SEQ = SUP_SEQ AND
CTR_SEQ > 0
VW_PURCHASE_ITEM_DETAILS
This view will list all the Purchase Orders in the system, similar to VW_PURCHASE_DETAILS except it goes one step further and displays every item assigned to each purchase order, it also has extra links to Cost Centre and Cost Codes.
Supplier, Contract, Cost Centre, Cost Code, Purchase Order Items, Stock and Purchase Orders link.
CREATE VIEW VW_PURCHASE_ITEM_DETAILS
AS SELECT *
FROM F_PO_ITEM, FSTOCK, F_PO_HEAD, F_CONTRACT, FSUPPLY, F_COST_CENTRE, F_COST_CODE
WHERE
POI_FKEY_FC_SEQ = FC_SEQ AND
POI_FKEY_FCC_SEQ = FCC_SEQ AND
POI_FKEY_POH_SEQ = POH_SEQ AND
POI_FKEY_STK_SEQ = STK_SEQ AND
STK_FKEY_CTR_SEQ = CTR_SEQ AND
POH_FKEY_CTR_SEQ = CTR_SEQ AND
POH_FKEY_SUP_SEQ = SUP_SEQ AND
CTR_FKEY_SUP_SEQ = SUP_SEQ AND
CTR_SEQ > 0
VW_ASSET_PROPERTIES
This will list all the assets in the system with each Asset Property, and Item displayed with their value.
This links to Asset, Property Header, and Property Item.
CREATE VIEW VW_ASSET_PROPERTIES
AS SELECT *
FROM FASSET,F_PROP_HEAD,F_PROP_ITEM,F_PROP_ASS
WHERE
PROP_ASS_FKEY_PROP_HEAD_SEQ = PROP_HEAD_SEQ AND
PROP_ASS_FKEY_PROP_ITEM_SEQ = PROP_ITEM_SEQ AND
PROP_ASS_FKEY_SEQ = AR_SEQ AND
PROP_ASS_TABLE = 'FASSET' AND
AR_SEQ > 0
VW_TASK_ANSWERS
List all the Tasks in the system displaying all the Answers to any questions that may have been asked about these tasks.
Links to Tasks and Tasks Answers.
CREATE VIEW VW_TASK_ANSWERS
AS SELECT *
FROM F_TASKS,F_TASK_ANS
WHERE
TA_ANS_FKEY_TA_SEQ = TA_SEQ AND
TA_SEQ > 0
VW_CTR_GRP_TASKS
This will list all the tasks in the system linking them to their respective Contract Group, this can then be used to filter and display only jobs for certain Groups.
Links to Tasks, Contracts and Contract Groups used.
CREATE VIEW VW_CTR_GRP_TASKS
AS SELECT *
FROM F_TASKS,F_CONTRACT,F_CONTRACT_GROUP
WHERE
TA_FKEY_CTR_SEQ = CTR_SEQ AND
CTR_FKEY_GRP_SEQ = GRP_SEQ AND
GRP_SEQ > 0
VW_RMB_BOOKINGS
This view will list all general details of Room Booking's including Room Details.
Links to Booking Header, Location and Booking Dates.
CREATE VIEW VW_RMB_BOOKINGS
AS SELECT *
FROM F_BOOK_HEADER, F_BOOK_DATES, FAREALO, F_LNK_BKD_INI, F_INVENTORY
WHERE
BKD_FKEY_BK_SEQ = BK_SEQ AND
BKD_FKEY_LO_SEQ = LO_SEQ AND
LNK_BKI_FKEY_BKD_SEQ = BKD_SEQ AND
LNK_BKI_FKEY_INI_SEQ = INI_SEQ
VW_RMB_ATTENDEE
This view will produce a general listing of all the people attending Bookings.
Links to Booking Header, Booking Dates and Booking Attendees.
CREATE VIEW VW_RMB_ATTENDEE
AS SELECT *
FROM F_BOOK_HEADER, F_BOOK_DATES, F_BOOK_ATT
WHERE
BKD_FKEY_BK_SEQ = BK_SEQ AND
BKA_FKEY_BKD_SEQ = BKD_SEQ