SQL & PL/SQL

I have COUNT_STATISTICS table that needs to be updated daily.

so on and so forth...

Sometimes I have a new MODEL name coming in and I have to account for that and create a row in the table in that case.

Other times, all I have to do is INSERT new row in the table with new values.

Simple right?

Well, I was trying to use MERGE INTO but it is giving me problems...

This is what I did assuming I manually went in the table and added a new MODEL name and zeros for other columns before I did insertion...

#!/bin/bash
#
MODEL=$1


sqlplus -S username/password << EOF
whenever sqlerror exit 1;
set echo on
set verify off
INSERT INTO SEMANTIC.COUNT_STATISTICS(MODEL,NEW_COUNT,NEW_DATE,OLD_COUNT,OLD_DATE)
SELECT MODEL,
    (
        SELECT COUNT(*)
        FROM TABLE(SEM_MATCH(
        '{
                ?s ?p ?o
         }',SEM_Models('$MODEL'),NULL,
        SEM_ALIASES(SEM_ALIAS('','http://VISION/DataSource/SEMANTIC#')),NULL))
    ),
    SYSDATE,
    NEW_COUNT,
    NEW_DATE
FROM SEMANTIC.COUNT_STATISTICS
WHERE MODEL = '$MODEL'
AND NEW_DATE = (
                select max(NEW_DATE)
                from SEMANTIC.COUNT_STATISTICS
                where MODEL = '$MODEL'
               )
;
update SEMANTIC.COUNT_STATISTICS
set prnct_change =
(
   (NEW_COUNT-OLD_COUNT)/NULLIF(OLD_COUNT,0)*100
)
where model = '$MODEL'
AND NEW_DATE = (
                select max(NEW_DATE)
                from SEMANTIC.COUNT_STATISTICS
                where MODEL = '$MODEL'
               )
;
COMMIT;
exit;
EOF

Now I added MERGE in front of INSERT so it checks if the MODEL name doesn't exist-it creates it:

MERGE INTO SEMANTIC.COUNT_STATISTICS s
USING (SELECT '${MODEL}' AS MODEL, 0 AS NEW_COUNT, SYSDATE AS NEW_DATE,
  0 AS OLD_COUNT, SYSDATE AS OLD_DATE FROM dual) t
on (s.MODEL = t.MODEL)
when not matched then
INSERT (s.MODEL, s.NEW_COUNT, s.NEW_DATE, s.OLD_COUNT, s.OLD_DATE)
VALUES (t.MODEL, t.NEW_COUNT, t.NEW_DATE, t.OLD_COUNT, t.OLD_DATE);

My code combined...

#!/bin/bash
#

MODEL=$1

  sqlplus -S username/password << EOF
  whenever sqlerror exit 1;
  set echo on
  set verify off
  MERGE INTO SEMANTIC.COUNT_STATISTICS s
  USING (SELECT '${MODEL}' AS MODEL, 0 AS NEW_COUNT, SYSDATE AS NEW_DATE, 0 AS OLD_COUNT, SYSDATE AS OLD_DATE FROM dual) t
  on (s.MODEL = t.MODEL)
  when not matched then
  INSERT (s.MODEL, s.NEW_COUNT, s.NEW_DATE, s.OLD_COUNT, s.OLD_DATE)
  VALUES (t.MODEL, t.NEW_COUNT, t.NEW_DATE, t.OLD_COUNT, t.OLD_DATE);
  exit;
EOF


model_exists () {
  sqlplus -s username/password <<!
    set heading off
    set feedback off
    set verify off
    set pages 0
    SELECT count(MODEL)
    FROM SEMANTIC.COUNT_STATISTICS
    where MODEL ='$MODEL' AND TO_DATE(NEW_DATE, 'DD-MON-YY') = TO_DATE(SYSDATE, 'DD-MON-YY');
    exit;
!
}
  
modelcount=$(model_exists $1)
  
if [ "${modelcount:-0}" -ne 0 ]; then
    sqlplus -S username/password << EOF
    whenever sqlerror exit 1;
    set echo on
    set verify off
    INSERT INTO SEMANTIC.COUNT_STATISTICS(MODEL,NEW_COUNT,NEW_DATE,OLD_COUNT,OLD_DATE)
    SELECT MODEL,
    (
  SELECT COUNT(*)
  FROM TABLE(SEM_MATCH(
  '{?s ?p ?o }',SEM_Models('$MODEL'),NULL,
  SEM_ALIASES(SEM_ALIAS('','http://VISION/DataSource/SEMANTIC#')),NULL))
  ),
    SYSDATE,
    NEW_COUNT,
    NEW_DATE
    FROM SEMANTIC.COUNT_STATISTICS
    WHERE MODEL = '$MODEL'
    AND NEW_DATE = (
  select max(NEW_DATE)
  from SEMANTIC.COUNT_STATISTICS
  where MODEL = '$MODEL'
    )
  ;
  COMMIT;
  exit;
EOF
else
   sqlplus -S  username/password << EOF
  whenever sqlerror exit 1;
  set echo on
  set verify off
  UPDATE COUNT_STATISTICS
  SET  MODEL = '$MODEL',
  NEW_COUNT = (SELECT COUNT(*) FROM TABLE(SEM_MATCH('{?s ?p ?o}',SEM_Models('$MODEL'),NULL,
  SEM_ALIASES(SEM_ALIAS('','http://VISION/DataSource/SEMANTIC#')),NULL))
  ),
  NEW_DATE  = SYSDATE,
  OLD_COUNT = NEW_COUNT,
  OLD_DATE  = NEW_DATE
  WHERE MODEL = '$MODEL'
  AND NEW_DATE = (
  select max(NEW_DATE)
  from SEMANTIC.COUNT_STATISTICS
  where MODEL = '$MODEL'
    )
  ;
  COMMIT;
  exit;
EOF
fi


sqlplus -S username/password << EOF
  whenever sqlerror exit 1;
  set echo on
  set verify off
  update COUNT_STATISTICS
  set prnct_change =
  (
    (NEW_COUNT-OLD_COUNT)/NULLIF(OLD_COUNT,0)*100
  )
  where model = '$MODEL'
  AND NEW_DATE = (
  select max(NEW_DATE)
  from SEMANTIC.COUNT_STATISTICS
  where MODEL = '$MODEL'
    )
  ;
  COMMIT;
  exit;
EOF

But this is creating two rows if it is a new MODEL, ie:

./load_myScript.sh  MODELNAME

This is not what I want. I want to create a new row if it doesn't exist and then update that same one for that day. But tomorrow it should INSERT a new one for that same MODEL name.

Does anyone see what I am doing wrong?