PL/SQL WHERE NOT EXISTS to do Insert

Hello expert,

An ETL system receives CRM_CASE_ID  from system CRM database and
writes CRM_CASE_ID  into CCM system database in the CRM_CASE table.

Sometimes the system fails to write CRM_CASE_ID  into CCM system database in the CRM_CASE table
Cases get stuck. We find the Cases. A data ad hoc is run to insert the cases.

The data ad hoc selects the  CRM_CASE_ID  and other records from a temp table
and inserts the records into CRM_CASE.

The issue is the CRM_CASE_ID is a primary key in CRM_CASE. Often by the time
the missing CRM_CASE_ID is identified, inserted into the temp table, tested,
sent to the DBAs and acted upon by the DBAs one of the CRM_CASE_ID
manages to get through and the DBA sends me a nasty gram that the
script failed:  
'Received the following errors:
SQL> @INC1793077.sql
INSERT INTO ccm.crm_case(
*
ERROR at line 1:
ORA-00001: unique constraint (CCM.PK_CRM_CASE) violated'

Script copied herewith below.

I was telling one of the developers the problem and he said
a 'where exists' statement could be used to avoid the problem.
After Googling  'where exists'  I think he meant 'WHERE NOT EXISTS'.

Wrote new script and copied herewith below.
Does the syntax look right?

Thanks.

Allen in Dallas

++++++++++++ begin existing script +++++++++++++++++
 INSERT INTO ccm.crm_case(
  CRM_CASE_ID,
  CRM_GUID,
  CRM_FRIENDLY,
  CRM_REQUESTER_ID,
  CRM_REQUESTER_NAME,
  CRM_ACTION_CODE,
  CRM_NAME,
  CRM_REQUEST_DATE,
  CASE_USER_ID,
  USER_DRAFT_ID,
  CCM_ID,
  IS_APPROVED ,
  CREATION_USER,
  CREATION_DATE,
  LAST_UPDATE_USER,
  LAST_UPDATE_DATE,
  CASE_SOLUTION_CODE,
  SOLUTION_DATE,
  CRM_CASE_GUID
)
SELECT
  CRM_CASE_ID,
  CRM_GUID,
  CRM_FRIENDLY,
  CRM_REQUESTER_ID,
  CRM_REQUESTER_NAME,
  CRM_ACTION_CODE,
  CRM_NAME,
  CRM_REQUEST_DATE,
  CASE_USER_ID,
  USER_DRAFT_ID,
  CCM_ID,
  IS_APPROVED ,
  CREATION_USER,
  CREATION_DATE,
  LAST_UPDATE_USER,
  LAST_UPDATE_DATE,
  CASE_SOLUTION_CODE,
  SOLUTION_DATE,
  CRM_CASE_GUID
FROM DEVUSER.TMP_CASE_INSRT_FNL
;

COMMIT;
+++++++++++++ end exiting script++++++++++++++++

============  begin new script ================
 INSERT INTO ccm.crm_case(
  CRM_CASE_ID,
  CRM_GUID,
  CRM_FRIENDLY,
  CRM_REQUESTER_ID,
  CRM_REQUESTER_NAME,
  CRM_ACTION_CODE,
  CRM_NAME,
  CRM_REQUEST_DATE,
  CASE_USER_ID,
  USER_DRAFT_ID,
  CCM_ID,
  IS_APPROVED ,
  CREATION_USER,
  CREATION_DATE,
  LAST_UPDATE_USER,
  LAST_UPDATE_DATE,
  CASE_SOLUTION_CODE,
  SOLUTION_DATE,
  CRM_CASE_GUID
)
SELECT
  CRM_CASE_ID,
  CRM_GUID,
  CRM_FRIENDLY,
  CRM_REQUESTER_ID,
  CRM_REQUESTER_NAME,
  CRM_ACTION_CODE,
  CRM_NAME,
  CRM_REQUEST_DATE,
  CASE_USER_ID,
  USER_DRAFT_ID,
  CCM_ID,
  IS_APPROVED ,
  CREATION_USER,
  CREATION_DATE,
  LAST_UPDATE_USER,
  LAST_UPDATE_DATE,
  CASE_SOLUTION_CODE,
  SOLUTION_DATE,
  CRM_CASE_GUID
FROM DEVUSER.TMP_CASE_INSRT_FNL
WHERE NOT EXISTS 
(SELECT 
  CRM_CASE_ID,
  CRM_GUID,
  CRM_FRIENDLY,
  CRM_REQUESTER_ID,
  CRM_REQUESTER_NAME,
  CRM_ACTION_CODE,
  CRM_NAME,
  CRM_REQUEST_DATE,
  CASE_USER_ID,
  USER_DRAFT_ID,
  CCM_ID,
  IS_APPROVED ,
  CREATION_USER,
  CREATION_DATE,
  LAST_UPDATE_USER,
  LAST_UPDATE_DATE,
  CASE_SOLUTION_CODE,
  SOLUTION_DATE,
  CRM_CASE_GUID from ccm.crm_case
        WHERE ccm.crm_case.crm_case_id = DEVUSER.TMP_CASE_INSRT_FNL.crm_case_id)
;

COMMIT;

Open in new window

LVL 1
Allen PittsBusiness analystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Philippe DamervalSenior Analyst ProgrammerCommented:
Personally, I would simply do a left join between TMP_CASE_INSRT_FNL and crm_case on crm_case_id and insert from the rows where crm_case.crm_case_ID IS NULL. Seems cleaner than a WHEN (NOT) EXISTS which doesn't work in all INFORMIX versions.
0
slightwv (䄆 Netminder) Commented:
Any reason you don't use a MERGE statement similar to your previous questions:
https://www.experts-exchange.com/questions/29111589/PL-SQL-Merge-source-to-destination.html
https://www.experts-exchange.com/questions/28956373/PL-SQL-Update-Location-Names.html
etc.

As far as if your new method works, set up a quick test and try it.  If you don't have a play database to develop on, use Oracle Live SQL:
https://livesql.oracle.com/apex/f?p=590:1000

You can set up a couple test tables, insert whatever you want and see how your proposed solution works.

Most of the time that is what we do before we post.  We set up test cases and make sure things run.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
>>which doesn't work in all INFORMIX versions.

Informix?  Where was that mentioned?
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

lcohanDatabase AnalystCommented:
That looks right however in the <<WHERE NOT EXISTS...>> you don't need to SELECT all columns but the KEY column that's causing the constraint violation - in your case I believe would be like:

INSERT INTO ccm.crm_case(
  CRM_CASE_ID,
  CRM_GUID,
  CRM_FRIENDLY,
  CRM_REQUESTER_ID,
  CRM_REQUESTER_NAME,
  CRM_ACTION_CODE,
  CRM_NAME,
  CRM_REQUEST_DATE,
  CASE_USER_ID,
  USER_DRAFT_ID,
  CCM_ID,
  IS_APPROVED ,
  CREATION_USER,
  CREATION_DATE,
  LAST_UPDATE_USER,
  LAST_UPDATE_DATE,
  CASE_SOLUTION_CODE,
  SOLUTION_DATE,
  CRM_CASE_GUID
)
SELECT
  CRM_CASE_ID,
  CRM_GUID,
  CRM_FRIENDLY,
  CRM_REQUESTER_ID,
  CRM_REQUESTER_NAME,
  CRM_ACTION_CODE,
  CRM_NAME,
  CRM_REQUEST_DATE,
  CASE_USER_ID,
  USER_DRAFT_ID,
  CCM_ID,
  IS_APPROVED ,
  CREATION_USER,
  CREATION_DATE,
  LAST_UPDATE_USER,
  LAST_UPDATE_DATE,
  CASE_SOLUTION_CODE,
  SOLUTION_DATE,
  CRM_CASE_GUID
FROM DEVUSER.TMP_CASE_INSRT_FNL
WHERE NOT EXISTS 
(SELECT  CRM_CASE_ID 
        FROM ccm.crm_case
        WHERE ccm.crm_case.crm_case_id = DEVUSER.TMP_CASE_INSRT_FNL.crm_case_id);

Open in new window


There's another option as well and you could look at MERGE instead of the NOT EXISTS as it may be better optimized for large record sets and you could skip the UPDATE if not needed - just use the MATCHED...INSERT as per this article:

https://oracle-base.com/articles/9i/merge-statement
0
PortletPaulfreelancerCommented:
purely as a note for future reference

WHERE | NOT | EXISTS ( ... )

does NOT need to return any data, it only needs to return true or false. The core of this approach is in the FROM & WHERE clauses of the subquery which tests if any row matches to data to the outer table(s). If there is any match it returns true, otherwise false.

In effect the select clause of the subquery is just there for decoration (and to comply with syntax rules :), so if using EXISTS don't over complicate the select clause of the subquery,
Either of these will work:

WHERE NOT EXISTS (
      SELECT  NULL
     FROM ccm.crm_case   /* important part is here */
      WHERE ccm.crm_case.crm_case_id = DEVUSER.TMP_CASE_INSRT_FNL.crm_case_id

      )

or

WHERE NOT EXISTS (
      SELECT  1
     FROM ccm.crm_case   /* important part is here */
      WHERE ccm.crm_case.crm_case_id = DEVUSER.TMP_CASE_INSRT_FNL.crm_case_id

      )
1
slightwv (䄆 Netminder) Commented:
I prefer creativity.  I mean how often to you get the opportunity?
...
WHERE NOT EXISTS (
      SELECT  'I like cupcakes'
...
2
Allen PittsBusiness analystAuthor Commented:
Thanks for the tip on the Oracle sandbox. I agree that hands on is the best way to figure things out and learn. Unfortunately I am in a fist fight with the DBAs over permissions in the dev environment so an online dev is helpful.
Also I thought I could use the primary key in the WHERE NOT EXISTS subquery and not list all the fields.
Finally, It is seen that merge would get the job done. Did not think of it. Will try it next time.
Thanks
0
PortletPaulfreelancerCommented:
thought I could use the primary key in the WHERE NOT EXISTS subquery

You can, but use it in the where clause. Not the select clause.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.