/*
-- crview.sql

-- version 1.0

-- SQL File to create view into the data base.

-- Programmed by Snehavadan Macwan

-- Last Update:		18 APRIL 	1995, VERSION 1.0

-- ===========================================================================

*/
-- This SQL script file creates a view in the database. Before view is created
-- the previous view in the database is cleared.  This will ensure that after 
-- this script file is run the database contains a known view.
-- Delete the previous view

DROP VIEW OBS_CATALOG ;

-- Create view in the database from main and experiment tables

CREATE VIEW OBS_CATALOG 
	( INSTRUME,
	  PROG_NUM,
	  SEQ_NUM,
	  WAVE_IND,
	  PROG_ID,
	  OBS_PROG,
	  SCI_OBJ,
	  SCI_SPEC,
	  CMP_NO,
	  OBJECT,
	  OBJ_ID,
	  DATE_OBS,
	  DATE_END,
	  XCEN,
	  YCEN,
	  ANGLE,
	  IXWIDTH,
	  IYWIDTH,
	  SEQ_FROM,
	  SEQ_TO,
	  MN_COMMENTS,
	  MN_DATE_MOD,
	  FILENAME,
	  OBS_SEQ,
	  OBS_MODE,
	  COUNT,
	  SEQVALID,
	  DETECTOR,
	  EXPTIME,
	  EXPCOUNT,
	  WAVE_ID,
	  EXP_COMMENTS,
	  EXP_DATE_MOD,
	  WAVEMIN,
	  WAVEMAX,
	  WV_DATE_MOD ) 
	AS SELECT E.INSTRUME,
	          E.PROG_NUM,
	          E.SEQ_NUM,
		  W.WAVE_IND,
	          M.PROG_ID,
	          M.OBS_PROG,
	          M.SCI_OBJ,
	          M.SCI_SPEC,
	          M.CMP_NO,
	          M.OBJECT,
	          M.OBJ_ID,
	          E.DATE_OBS,
	          E.DATE_END,
	          E.XCEN,
	          E.YCEN,
	          E.ANGLE,
	          E.IXWIDTH,
	          E.IYWIDTH,
	          M.SEQ_FROM,
	          M.SEQ_TO,
	          M.COMMENTS,
	          M.DATE_MOD,
	          E.FILENAME,
	          E.OBS_SEQ,
	          E.OBS_MODE,
	          E.COUNT,
	          E.SEQVALID,
	          E.DETECTOR,
	          E.EXPTIME,
	          E.EXPCOUNT,
		  E.WAVE_ID,
	          E.COMMENTS,
	          E.DATE_MOD,
	          W.WAVEMIN,
	          W.WAVEMAX,
	          W.DATE_MOD
	FROM MAIN M, 
	     EXPERIMENT E,
	     WAVELENGTHS W
	WHERE ( M.PROG_NUM = E.PROG_NUM AND
		M.INSTRUME = E.INSTRUME AND
		E.INSTRUME = W.INSTRUME AND
		E.SEQ_NUM = W.SEQ_NUM ) ;

-- Commit View

COMMIT;


