In the manual views, the SELECT statement is created by the developer, and can be in any form.
The statement is anyway included in a schema, that once created can have the same properties of a table schema, including connections, and can be used in any part of the program in the same way.
This kind of view can be used in many cases, for example to show a record twice.
For example, we could have stock movements between two different warehouses of the same company.
In this case, we should insert two movements:
- A download from warehouse 1
- An upload to warehouse 2
This can be done in a very effective way if we include in the movement the connection to warehouse 2.
The movements could be in a table with these columns (simplified)
- ITEM_ID (connection to the item
- QT_UPLOAD (quantity upload)
- QT_DOWNLOAD (quantity download)
- WH_1_ID (connection to warehouse 1)
- WH_2_ID (connection to warehouse 2)
In this way we can create a view with this statement (aliases in orange)
SELECT ITEM ID, QT_UPLOAD,QT_DOWNLOAD,WH_1_ID WH_ID
UNION ALL
SELECT ITEM ID, QT_DOWNLOAD QT_UPLOAD,QT_UPLOAD QT_DOWNLOAD,WH_2_ID WH_ID WHERE WH_2_ID IS NOT NULL
This will create a view with this structure, tha will be copied in the schema:
- ITEM_ID (connection to the item
- QT_UPLOAD (quantity upload)
- QT_DOWNLOAD (quantity download)
- WH_ID (connection to warehouse)
in which the internal movements will be seen twice:
- One (real movement) as a download from warehouse 1
- And one (virtual movement) as an upload to warehouse 2, that will include only the movements that have the connections to a second warehouse (internal movement), with the quantities in upload and download inverted.
In this way, not having a “phisical” second movement, the upload and download are automatically always aligned, since the second movement does not really exist as a record, but it is a sort of “mirror” of the first one.