Automation: April 2014

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.