Using SQL

Following are a series of sample SQL statements.

Validation SQL Statements

This sample code shows validation statements:

select
    PS_QS_STREAM8.STREAM_ID,
    PS_QS_STREAM8.QS_APP_CONTEXT
  FROM PS_QS_STREAM8,PS_QS_STREAM_ROOT,PS_QS_APP_DEF
    WHERE PS_QS_STREAM8.STREAM_ID >= 164 AND PS_QS_STREAM8.STREAM_ID <= 164
      and PS_QS_STREAM8.STREAM_ROOT_ID = PS_QS_STREAM_ROOT.STREAM_ROOT_ID
      and PS_QS_APP_DEF.QS_APP_CONTEXT = PS_QS_STREAM_ROOT.QS_APP_CONTEXT
    ORDER BY STREAM_ID

select
    PS_QS_STREAM8.STREAM_ID
  FROM PS_QS_STREAM8,PS_QS_STREAM_ROOT
    WHERE PS_QS_STREAM8.STREAM_ID >= 164 AND PS_QS_STREAM8.STREAM_ID <= 164
      and PS_QS_STREAM8.STREAM_ROOT_ID = PS_QS_STREAM_ROOT.STREAM_ROOT_ID
    ORDER BY STREAM_ID

select
    STREAM_ID,
    CLIM_STS_CD,
    CLIM_ID,
    STR_SGRP_CNT,
    STR_SGRP_SEQ,
    CUR_SGRP_SIZE,
    CUR_SPEC_VIO_CNT,
    CUR_PLOT_VALUE1,
    CUR_PLOT_VALUE2,
    RUN_MOMENT,
    TSWIN_BUFFER1,
    TSWIN_BUFFER2
  FROM PS_QS_STREAM8
    WHERE STREAM_ID >= 164 AND STREAM_ID <= 164

select
    BUSINESS_UNIT,
    STREAM_ID,
    CLIM_ID,
    CLIM_STS_CD,
    CLIM_UPPER_X,
    CLIM_UX_IND,
    CLIM_MEAN_X,
    CLIM_MX_IND,
    CLIM_LOWER_X,
    CLIM_LX_IND,
    CLIM_UPPER_MRANGE,
    CLIM_UMRANGE_IND,
    CLIM_MEAN_RANGE,
    CLIM_MRANGE_IND,
    CLIM_LOWER_MRANGE,
    CLIM_LMRANGE_IND,
    CLIM_CRE_DTTM,
    CLIM_CRE_BY
  from PS_QS_CNTL_LIM_HIS
    WHERE STREAM_ID >= 164 AND STREAM_ID <= 164
    ORDER BY CLIM_ID

select
    BUSINESS_UNIT,
    SESSN_ID,
    STREAM_ID,
    SESSN_STREAM_SEQ,
    SESSN_SGRP_SEQ,
    SESSN_STRM_STS,
    SESSN_SGRP_SIZE,
    MFDS_DATA_TYPE_CD,
    SESSN_ANLZ_READY,
    CLIM_ID,
    SPEC_ID,
    SRSLT_P1,
    SRSLT_P1_IND,
    SRSLT_P2,
    SRSLT_P2_IND,
    CLIM_STS_CD,
    SPEC_VIO_SW,
    QS_ANALYZER_STATUS,
    QS_ALARM_CNT,
    QS_NVALUES,
    QS_VALUEREADING_1,
    QS_VALUEREADING_2,
    QS_VALUEREADING_3,
    QS_VALUEREADING_4,
    QS_VALUEREADING_5,
    DISPATCH_FLAG,
    SESSN_SUBMIT_DTTM,
    SRSLT_CRE_DTTM,
    QS_ACTION_CNT,
    QS_CAUSE_CNT,
    QS_COMM_CNT,
    OPERATOR,
    QS_UAID
  from PS_QS_SUBGROUP
    WHERE STREAM_ID >= 164 AND STREAM_ID <= 164
    ORDER BY SESSN_ID

select
    BUSINESS_UNIT,
    SESSN_ID,
    STREAM_ID,
    SAMPLE,
    TEST_SEQ,
    TEST_ID
  from PS_QS_SESSN_TEST8
    WHERE STREAM_ID >= 164 AND STREAM_ID <= 164
    ORDER BY SESSN_ID

Generic SQL Statements

This sample code shows generic statements:

select
  STREAM_ID,
  CLIM_STS_CD,
  CLIM_ID,
  STR_SGRP_CNT,
  STR_SGRP_SEQ,
  CUR_SGRP_SIZE,
  CUR_SPEC_VIO_CNT,
  CUR_PLOT_VALUE1,
  CUR_PLOT_VALUE2,
  RUN_MOMENT,
  TSWIN_BUFFER1,
  TSWIN_BUFFER2
  FROM PS_QS_STREAM8
  WHERE STREAM_ID >= 164 AND STREAM_ID <= 164

select * from PS_QS_CNTL_LIM_HIS WHERE STREAM_ID >= 164 AND STREAM_ID <= 164

select PS_QS_SESSN_HDR8.* from PS_QS_SUBGROUP,PS_QS_SESSN_HDR8
 WHERE STREAM_ID >= 164 AND STREAM_ID <= 164 
AND PS_QS_SESSN_HDR8.SESSN_ID = PS_QS_SUBGROUP.SESSN_ID
select PS_QS_SESSN_TRACE8.* from PS_QS_SUBGROUP,PS_QS_SESSN_TRACE8 
WHERE STREAM_ID >= 164 AND STREAM_ID <= 164 AND PS_QS_SESSN_TRACE8.SESSN_ID = PS_⇒
QS_SUBGROUP.SESSN_ID
select * from PS_QS_SUBGROUP WHERE STREAM_ID >= 164 AND STREAM_ID <= 164
select * from PS_QS_SESSN_DFCT8 WHERE STREAM_ID >= 164 AND STREAM_ID <= 164
select * from PS_QS_SESSN_TEST8 WHERE STREAM_ID >= 164 AND STREAM_ID <= 164

SQL to Reset Database Tables

This sample code shows statements to reset database tables:

update PS_QS_STREAM8 set
  CLIM_STS_CD = "NONE",
  CLIM_ID = "NONE",
  STR_SGRP_CNT = 0,
  STR_SGRP_SEQ = 0,
  CUR_SGRP_SIZE = 0,
  CUR_SPEC_VIO_CNT = 0,
  CUR_PLOT_VALUE1 = 0,
  CUR_PLOT_VALUE2 = 0,
  RUN_MOMENT = 0,
  TSWIN_BUFFER1 = "",
  TSWIN_BUFFER2 = ""
  WHERE STREAM_ID >= 164 AND STREAM_ID <= 164

delete from PS_QS_SESSN_HDR8 WHERE SESSN_ID in (select SESSN_ID from PS_QS_⇒
SUBGROUP 
where STREAM_ID >= 164 AND STREAM_ID <= 164)
delete from PS_QS_SESSN_TRACE8 WHERE SESSN_ID in (select SESSN_ID from PS_QS_⇒
SUBGROUP 
where STREAM_ID >= 164 AND STREAM_ID <= 164)

delete from PS_QS_SESSN_DFCT8 WHERE STREAM_ID >= 164 AND STREAM_ID <= 164
delete from PS_QS_CNTL_LIM_HIS WHERE STREAM_ID >= 164 AND STREAM_ID <= 164
delete from PS_QS_SESSN_TEST8 WHERE STREAM_ID >= 164 AND STREAM_ID <= 164
delete from PS_QS_SUBGROUP WHERE STREAM_ID >= 164 AND STREAM_ID <= 164

SQL to Retrieve Stream Information

This sample code shows statements to retrieve information:

select
    STREAM_ID,
    MFDS_ID,
    CLIM_STS_CD,
    STR_SGRP_CNT,
    STR_SGRP_SEQ,
    CLIM_ID,
    CUR_SGRP_SIZE,
    CUR_SPEC_VIO_CNT,
    CUR_PLOT_VALUE1,
    CUR_PLOT_VALUE2,
    RUN_MOMENT,
    TSWIN_BUFFER1,
    TSWIN_BUFFER2,
    SPEC_ID,
    SPEC_LSL,
    SPEC_LSL_IND,
    SPEC_USL,
    SPEC_USL_IND,
    SPEC_FACTOR1,
    SPEC_FACTOR1_IND,
    SPEC_FACTOR2,
    SPEC_FACTOR2_IND,
    MFDS_COLLECT_SW,
    MFDS_NAME,
    MFDS_SGRP_SIZE,
    MFDS_DATA_TYPE_CD,
    MFDS_LAL,
    MFDS_LAL_IND,
    MFDS_UAL,
    MFDS_UAL_IND,
    MFDS_INC_CD,
    MFDS_FORMULA,
    CHART_ID,
    PROC_ID,
    CLIM_UPPER_X,
    CLIM_UX_IND,
    CLIM_MEAN_X,
    CLIM_MX_IND,
    CLIM_LOWER_X,
    CLIM_LX_IND,
    CLIM_UPPER_MRANGE,
    CLIM_UMRANGE_IND,
    CLIM_MEAN_RANGE,
    CLIM_MRANGE_IND,
    CLIM_LOWER_MRANGE,
    CLIM_LMRANGE_IND
  FROM PS_QS_API_STR83_VW
    WHERE STREAM_ID >= 164 AND STREAM_ID <= 164
    ORDER BY STREAM_ID