Creating View from Another View

Question

I haven't found anything online or in any other resource after searching, and I was wondering if you could form a View by joining another view and some other tables? Something similar to this I guess with Server_ref.part_notification_view being the view getting joined.

Create View "PART_NOTIFICATION_VIEW" ("NOTIFICATION_IX", "PART_NBR", "MFG_CD", "PART_CLASS_CD", "LEADTIME", "BILLTO_CUST_NBR", "BILL_TO_ACCT_NM", "CUST_PART_NBR", "LAST_CUST_PO", "LAST_REQ_DT", "QTY_OPEN", "YEAR_USAGE", "AVAILABLE_SALE_STANDARD_QT", "ISSUE_DATE", "EFFECTIVE_DATE", "BRIEF_DESCRIPTION", "NOTIFICATION_TYPE", "ACTUAL_DOCUMENT_LINK", "AFFECTED_PARTS_LIST_DOC_LINK", "EMAIL_LINK", "FILE_FOLDER", "RECOMMENDED_REPLACEMENT", "PCN_TYPE", "IMPACT", "MANUFACTURER_NM", "LAST_BUY_DT", "LAST_SHIP_DT", "SALES_MIN_BUY_QTY", "SALES_MIN_PKG_QTY", "PART_DESC", "BOND_QOH", "BOND_QIT", "BRANCH_QOH", "BRANCH_QIT", "BOND_QTY", "BOND_PIPELINE", "BOND_OP", "BRAND_CD", "STATUS", "COMMENTS")
AS
  SELECT
    svr.notification_ix,
    svr.part_nbr,
    svr.mfg_cd,
    svr.part_class_cd,
    svr.leadtime,
    svr.billto_cust_nbr,
    svr.bill_to_acct_nm,
    svr.cust_part_nbr,
    svr.last_cust_po,
    svr.last_req_dt,
    svr.qty_open,
    svr.year_usage,
    svr.available_sale_standard_qt,
    svr.issue_date,
    svr.effective_date,
    svr.brief_description,
    svr.notification_type,
    svr.actual_document_link,
    svr.affected_parts_list_doc_link,
    svr.email_link,
    svr.file_folder,
    svr.recommended_replacement,
    svr.pcn_type,
    svr.impact,
    svr.manufacturer_nm,
    svr.last_buy_dt,
    svr.last_ship_dt,
    svr.sales_min_buy_qty,
    svr.sales_min_pkg_qty,
    svr.part_desc,
    NVL(svr.bond_qoh, 0)                                                                    AS bond_qoh,
    NVL(svr.bond_qit, 0)                                                                    AS bond_qit,
    NVL(svr.branch_qoh, 0)                                                                  AS branch_qoh,
    NVL(svr.branch_qit, 0)                                                                  AS branch_qit,
    NVL(svr.bond_qoh, 0)      + NVL(svr.bond_qit, 0) + NVL(svr.branch_qoh, 0) + NVL(svr.branch_qit, 0) AS bond_qty,
    NVL(svr.bond_pipeline, 0) + NVL(svr.po_qt, 0)                                                 AS bond_pipeline,
    svr.bond_op,
    svr.brand_cd,
    cs.status,
    cc.comments
  FROM part_notification_view svr
  JOIN css_status cs
  ON svr.part_nbr = cs.part_nbr
  AND svr.mfg_cd = cs.mfg_cd
  AND svr.billto_cust_nbr = cs.account
  JOIN css_comment cc
  ON svr.part_nbr = cc.part_nbr
  AND svr.mfg_cd = cc.mfg_cd
  AND svr.billto_cust_nbr = cc.account;

Answer 1

Long answer ...

It's one of the fundamental characteristics of a relational database that there should be no logical difference between tables, queries results, and views (which are simply stored queries). In fact the "relational" refers to the rows of data that are accessible through any one of these. http://en.wikipedia.org/wiki/Relation_%28database%29

There are of course differences imposed to different degrees by different RDBMSs, particularly when it comes to DDL commands (update, delete, insert), and they all impose restrictions on the kinds of object that DDL can be applied to.

Taking Oracle as an example, the system will allow updates and deletes on key-preserved views, and inserts are possible but rarely used in practice (an "instead of" trigger type is available to allow DDL against any view).

So given all that, you can run a select against:

1. A table
2. A set of joined tables
3. A view
4. A query (commonly referred to as an in-line view)
5. A query joined to a view and a table
6. etc

... and that select can therefore be encapsulated in a view definition.

Short answer: Yes