L1Trigger/scripts/getNextTscKeyByTimestamp.sh

0001 #!/bin/sh
0002
0003 # Look for the first of the next new L1 keys which has not been validated.
0004 # Reference ~l1emulator/o2o/scripts/getRecentKeys.sh
0005 #
0006 # The time format must be precise to nano seconds.
0007 # Otherwise, the effect of creation_date > and <= would be unexpected.
0008
0009 lastCreationDate=$1
0010
0011 DB="cms_omds_lb"
0012 USER="cms_trg_r"
0013 PASSWORD_FILE=/nfshome0/centraltspro/secure/$USER.txt
0014 PASSWORD=`cat $PASSWORD_FILE`
0015
0016 sqlplus -S ${USER}/${PASSWORD}@${DB} <<EOF
0017 set linesize 500
0018 set wrap on
0019 set heading off
0020 set pagesize 0
0021 set feedback off
0022 SELECT TO_CHAR(CREATION_DATE, 'YYYY.MM.DD_HH24:MI:SS_FF9'), TSC_KEY
0023 FROM
0024 (
0025  SELECT MAX(CREATION_DATE) CREATION_DATE, TSC_KEY
0026  FROM CMS_TRG_L1_CONF.L1_CONF
0027  WHERE CREATION_DATE >
0028  TO_TIMESTAMP('${lastCreationDate}', 'YYYY.MM.DD_HH24:MI:SS_FF9')
0029  AND TSC_KEY IS NOT NULL
0030  AND TSC_KEY NOT IN
0031  (
0032   SELECT DISTINCT TSC_KEY
0033   FROM CMS_TRG_L1_CONF.L1_CONF
0034   WHERE CREATION_DATE <=
0035   TO_TIMESTAMP('${lastCreationDate}', 'YYYY.MM.DD_HH24:MI:SS_FF9')
0036   AND TSC_KEY IS NOT NULL
0037  )
0038  GROUP BY TSC_KEY
0039  ORDER BY CREATION_DATE
0040 )
0041 WHERE ROWNUM = 1
0042 ;
0043 EOF
0044
0045 exit