Create View from multiple tables/views in oracle

How to create view from Multiple tables or Views in Oracle.
We have one static Oracle table "APM3_SAPTABLE_METADATA" contains static data. One column "DCS_CODE" among them contains column names of another View "APM3_PTS_TEMP_DATA".

Now we are going to create one view "APM3_PTS_DATA_V" using above two.

 CREATE OR REPLACE FORCE VIEW "APM3_SAP_USER"."APM3_PTS_DATA_V" ("DATETIME", "TAGNAME", "DESCRIPTION", "VALUE", "UNIT") AS   
  SELECT  
  a.idate DATETIME,  
  B.dcs_tagref TAGNAME,  
  B.description,  
  case(B.dcs_code)  
 when 'DCS0068' then TO_CHAR(A.DCS0068)  
 when 'DCS0069' then TO_CHAR(A.DCS0069)  
 when 'DCS0070' then TO_CHAR(A.DCS0070)  
 when 'DCS0071' then TO_CHAR(A.DCS0071)  
 when 'DCS0072' then TO_CHAR(A.DCS0072)  
 when 'DCS0073' then TO_CHAR(A.DCS0073)  
 --when 'DCS0074' then TO_CHAR(A.DCS0074)  
 --when 'DCS0075' then TO_CHAR(A.DCS0075)  
 --when 'DCS0076' then TO_CHAR(A.DCS0076)  
 when 'DCS0077' then TO_CHAR(A.DCS0077)  
 when 'DCS0078' then TO_CHAR(A.DCS0078)  
 when 'DCS0079' then TO_CHAR(A.DCS0079)  
 when 'DCS0080' then TO_CHAR(A.DCS0080)  
 --when 'DCS0081' then TO_CHAR(A.DCS0081)  
 when 'DCS0082' then TO_CHAR(A.DCS0082)  
 when 'DCS0083' then TO_CHAR(A.DCS0083)  
 --when 'DCS0084' then TO_CHAR(A.DCS0084)  
 --when 'DCS0085' then TO_CHAR(A.DCS0085)  
 when 'DCS0086' then TO_CHAR(A.DCS0086)  
 --when 'DCS0087' then TO_CHAR(A.DCS0087)  
 when 'DCS0088' then TO_CHAR(A.DCS0088)  
 when 'DCS0089' then TO_CHAR(A.DCS0089)  
 --when 'DCS0090' then TO_CHAR(A.DCS0090)  
 --when 'DCS0091' then TO_CHAR(A.DCS0091)  
 --when 'DCS0092' then TO_CHAR(A.DCS0092)  
 when 'DCS0093A' then TO_CHAR(A.DCS0093A)  
 when 'DCS0093B' then TO_CHAR(A.DCS0093B)  
 when 'DCS0094' then TO_CHAR(A.DCS0094)  
 when 'DCS0095' then TO_CHAR(A.DCS0095)  
 when 'DCS0096' then TO_CHAR(A.DCS0096)  
 --when 'DCS0097' then TO_CHAR(A.DCS0097)  
 --when 'DCS0098' then TO_CHAR(A.DCS0098)  
 when 'DCS0099' then TO_CHAR(A.DCS0099)  
 when 'DCS0100' then TO_CHAR(A.DCS0100)  
 end,  
 b.unit  
  FROM APM3_PTS_TEMP_DATA A,APM3_SAPTABLE_METADATA B  
  WHERE B.dcs_code in (select column_name from all_tab_columns where table_name=upper('apm3_pts_temp_data'))  
  ORDER BY a.rindex,b.dcs_code;  

" -- " indicates comments.
So we have written the following CODE to create  the Oracle view.

 

Our expected View is created and it has following columns.

View data is shown below.

Data Extraction from an EXPERT 9017F AI Module

Purpose:

           1. CEMs Data viewer (COD, BOD, NOX etc.).

2. Extract data in order to show in a customized HMI and archive it to a Database for trending.

3. Develop an TCP/IP server application to share these data across different area.

Module Details are given below.
 

Command List:

Command	Description
$01M	Check Module name(01--> Module Address)
$01P	Mode checking (!0110-->Normal, !0111--> MODBUS)
#01	To get string of all channels output.
#010, #011, #012 etc.	Channel 1, 2, 3 individual output etc.

EXPERT 9017F manual

A simple interface is developed for testing purpose. By providing commands in the command text box we need to press Start button.

                          Fig: A command “#01” is pressed to see all 8 analog data.

 

A data provider is implemented by following Expert Module Manual. Here are snapshots of UML class diagrams.

                                  Fig: Serial Port Manager UML class diagram.

                     Fig: Serial Port Settings UML class diagram.

References:

1. Visual Studio C#.net programming.

2. TOPSCC EXPERT 9000 product manual.