Solved

PL/SQL - How do I compare two values in a table and then execute a SP if 'true'?

Posted on 2009-05-04
5
1,871 Views
Last Modified: 2013-12-18
Hello Gurus.
I am writing a stored procedure that will execute another stored procedure if the source_count = target_count in my process log table for the latest job number.  The CREATE table script for my process table is shown below.  I'm thinking I need to find the MAX job_number or the MAX end_date first and then compare the source_count and the target count.  If they are equal, then execute the SP.  If not, then update the process log table.  I'm stuck.  Can someone please nudge [or shove] me in the right direction?
Many thanks!
CREATE TABLE DDR_PROCESS_LOG
(
  PROGRAM_ID     VARCHAR2(30 BYTE),
  ERROR_SQLCODE  VARCHAR2(30 BYTE),
  ERROR_SQLERRM  VARCHAR2(250 BYTE),
  COMMENTS       VARCHAR2(500 BYTE),
  SOURCE_COUNT   VARCHAR2(20 BYTE),
  TARGET_COUNT   VARCHAR2(20 BYTE),
  START_TIME     DATE,
  END_TIME       DATE,
  JOB_NBR        NUMBER(18,1)
)

Open in new window

0
Comment
Question by:hypermac
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 14

Expert Comment

by:ajexpert
ID: 24299846
Assuming that JOB_NBR is first priority,
Here is the procedure.  You might need a little modification on exception handling

CREATE OR REPLACE PROCEDURE pr_exec_procedure
 
v_max_job_nbr     DDR_PROCESS_LOG.JOB_NBR%TYPE;
v_max_job_end_date   DDR_PROCESS_LOG.end_time%TYPE;
v_max_date        DDR_PROCESS_LOG.end_time%TYPE;
v_program_id       DDR_PROCESS_LOG.program_id%TYPE;
v_source_count    DDR_PROCESS_LOG.source_count%TYPE;
v_target_count    DDR_PROCESS_LOG.target_count%TYPE;
AS
 
BEGIN
   SELECT   MJB, END_TIME
     INTO   v_max_job_nbr, v_max_job_end_date, v_program_id
     FROM   (  SELECT   MAX (JOB_NBR) MJB, END_TIME, PROGRAM_ID
                 FROM   DDR_PROCESS_LOG
             GROUP BY   END_TIME
             ORDER BY   MJB DESC)
    WHERE   ROWNUM < 2;
 
   SELECT   MAX (end_time) INTO v_max_date FROM DDR_PROCESS_LOG;
 
   IF v_max_job_end_date <> v_max_date
   THEN
      DBMSOUTPUT.PUT_LINE (
         'MAX JOB NBR AND END_DATE are different records please check'
      );
      RETURN; -- this will not proceed further.  If you want to move ahead, comment this
   END IF;
 
   SELECT   source_count, target_count
     INTO   v_source_count, v_target_count
     FROM   DDR_PROCESS_LOG
    WHERE   program_id = v_program_id;
 
   IF v_source_count = v_target_count
   THEN
      DBMSOUTPUT.PUT_LINE ('call different stored procedure here');
   ELSE
      DBMSOUTPUT.PUT_LINE ('UPDATE DDR_PROCESS_LOG HERE');
   END IF;
END;

Open in new window

0
 

Author Comment

by:hypermac
ID: 24301333
Thanks!  I've attached the entire SP including your recommendations.  My issue now is that I can't get the ADPDDR_HIST_DATA_USD_SP to execute.  I confirmed that the v_source_count = v_target_count.  I've been looking at this for a while and I'm getting nowhere!  Any help would be greatly appreciated!

Thanks Again!
CREATE OR REPLACE PROCEDURE ADPDDR.ADPDDR_MONTHLY_DATALOAD_SP2 IS
tmpVar NUMBER;
/******************************************************************************
   NAME:       ADPDDR_MONTHLY_DATALOAD
   PURPOSE:    
 
   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        4/22/2009          1. Created this procedure.
 
   NOTES:
 
   Automatically available Auto Replace Keywords:
      Object Name:     ADPDDR_MONTHLY_DATALOAD
      Sysdate:         4/22/2009
      Date and Time:   4/22/2009, 5:03:48 PM, and 4/22/2009 5:03:48 PM
      Username:         (set in TOAD Options, Procedure Editor)
      Table Name:       (set in the "New PL/SQL Object" dialog)
 
******************************************************************************/
 
PROGRAM_ID  VARCHAR2(30) := 'ADPDDR_MONTHLY_DATALOAD_SP';
l_source_count			 VARCHAR2(20);
l_target_count           VARCHAR2(20);
l_start_time		     DATE;
l_max_job_nbr            NUMBER;
l_source_count2			 VARCHAR2(20);
l_target_count2          VARCHAR2(20);
v_source_count           VARCHAR2(20);
v_target_count           VARCHAR2(20);
v_max_date               DATE;
v_max_job_nbr            NUMBER;
v_max_job_end_date       DATE;
v_program_id             VARCHAR2(30);
 
 
 
BEGIN
 
    l_start_time   := SYSDATE;
 
    SELECT   COUNT( * )
    INTO   l_source_count
    FROM   adp_rpt.adpds_rpt_curprd;
 
 
    DELETE FROM ADPDDR.ADPDDR_CURPRD;
    COMMIT;
    
    INSERT INTO ADPDDR.ADPDDR_CURPRD(MONTH)
    (SELECT MONTH FROM ADP_RPT.ADPDS_RPT_CURPRD);
    
    COMMIT;
    
    SELECT   COUNT( * )
    INTO   l_target_count
    FROM   adpddr.adpddr_curprd;
    
ADPDDR_PROCESS_LOG_SP(PROGRAM_ID,0,NULL,'Monthly DataLoad - Load Current Period - Completed Successfully', l_source_count, l_target_count, l_start_time, SYSDATE); 
 EXCEPTION
    WHEN OTHERS THEN
       ADPDDR_PROCESS_LOG_SP('ADPDDR_MONTHLY_DATALOAD_SP',SQLCODE,SQLERRM,'Raise Application Error - 20013');
  RAISE_APPLICATION_ERROR(-20013, SQLERRM);
  
  COMMIT;
    
   
    SELECT MJB, END_TIME, MJB||program_id
    INTO v_max_job_nbr, v_max_job_end_date, v_program_id
    FROM ( SELECT MAX (JOB_NBR) MJB, END_TIME, program_id
    FROM ADPDDR_PROCESS_LOG
    GROUP BY END_TIME, program_id
    ORDER BY MJB DESC)
    WHERE ROWNUM < 2;
 
    SELECT MAX (end_time) INTO v_max_date FROM ADPDDR_PROCESS_LOG;
 
    IF v_max_job_end_date <> v_max_date
    THEN
    DBMS_OUTPUT.PUT_LINE (
    'MAX JOB NBR AND END_DATE are different records please check'
    );
 
    --RETURN; -- this will not proceed further. If you want to move ahead, comment this
    END IF;
    
    SELECT source_count, target_count
    INTO v_source_count, v_target_count
    FROM ADPDDR_PROCESS_LOG
    WHERE job_nbr = v_max_job_nbr;
    
    IF v_source_count = v_target_count
    THEN
    ADPDDR_HIST_DATA_USD_SP;
    
    END IF;
    
    END;
/

Open in new window

0
 
LVL 14

Expert Comment

by:ajexpert
ID: 24304854
OK,
Lets do tracing.
Compile the following SP.  I have put DBMS_OUTPUT at various points.
Execute the procedure but do remember to put on SET SERVEROUTPUT ON first
Let me know what you get while you execute the SP

CREATE OR REPLACE PROCEDURE ADPDDR.ADPDDR_MONTHLY_DATALOAD_SP2 IS
tmpVar NUMBER;
/******************************************************************************
   NAME:       ADPDDR_MONTHLY_DATALOAD
   PURPOSE:    
 
   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        4/22/2009          1. Created this procedure.
 
   NOTES:
 
   Automatically available Auto Replace Keywords:
      Object Name:     ADPDDR_MONTHLY_DATALOAD
      Sysdate:         4/22/2009
      Date and Time:   4/22/2009, 5:03:48 PM, and 4/22/2009 5:03:48 PM
      Username:         (set in TOAD Options, Procedure Editor)
      Table Name:       (set in the "New PL/SQL Object" dialog)
 
******************************************************************************/
 
PROGRAM_ID  VARCHAR2(30) := 'ADPDDR_MONTHLY_DATALOAD_SP';
l_source_count          VARCHAR2(20);
l_target_count           VARCHAR2(20);
l_start_time           DATE;
l_max_job_nbr            NUMBER;
l_source_count2          VARCHAR2(20);
l_target_count2          VARCHAR2(20);
v_source_count           VARCHAR2(20);
v_target_count           VARCHAR2(20);
v_max_date               DATE;
v_max_job_nbr            NUMBER;
v_max_job_end_date       DATE;
v_program_id             VARCHAR2(30);
 
 
 
BEGIN
 
    l_start_time   := SYSDATE;
 
    SELECT   COUNT( * )
    INTO   l_source_count
    FROM   adp_rpt.adpds_rpt_curprd;
 
 
    DELETE FROM ADPDDR.ADPDDR_CURPRD;
    COMMIT;
    
    INSERT INTO ADPDDR.ADPDDR_CURPRD(MONTH)
    (SELECT MONTH FROM ADP_RPT.ADPDS_RPT_CURPRD);
    
    COMMIT;
    
    SELECT   COUNT( * )
    INTO   l_target_count
    FROM   adpddr.adpddr_curprd;
    
ADPDDR_PROCESS_LOG_SP(PROGRAM_ID,0,NULL,'Monthly DataLoad - Load Current Period - Completed Successfully', l_source_count, l_target_count, l_start_time, SYSDATE); 
 EXCEPTION
    WHEN OTHERS THEN
       ADPDDR_PROCESS_LOG_SP('ADPDDR_MONTHLY_DATALOAD_SP',SQLCODE,SQLERRM,'Raise Application Error - 20013');
  RAISE_APPLICATION_ERROR(-20013, SQLERRM);
  
  COMMIT;
    
   
  DBMS_OUTPUT.PUT_LINE('getting max job_nbr and end date');
  
    SELECT MJB, END_TIME, MJB||program_id
    INTO v_max_job_nbr, v_max_job_end_date, v_program_id
    FROM ( SELECT MAX (JOB_NBR) MJB, END_TIME, program_id
    FROM ADPDDR_PROCESS_LOG
    GROUP BY END_TIME, program_id
    ORDER BY MJB DESC)
    WHERE ROWNUM < 2;
   
    DBMS_OUTPUT.PUT_LINE('getting the max date');
    
    SELECT MAX (end_time) INTO v_max_date FROM ADPDDR_PROCESS_LOG;
 
   
    DBMS_OUTPUT.PUT_LINE('comparing dates');
 
    IF v_max_job_end_date <> v_max_date
    THEN
    DBMS_OUTPUT.PUT_LINE (
    'MAX JOB NBR AND END_DATE are different records please check'
    );
 
    --RETURN; -- this will not proceed further. If you want to move ahead, comment this
    END IF;
    
    DBMS_OUTPUT.PUT_LINE('getting source target counts');
    
    SELECT source_count, target_count
    INTO v_source_count, v_target_count
    FROM ADPDDR_PROCESS_LOG
    WHERE job_nbr = v_max_job_nbr;
    
    DBMS_OUTPUT.PUT_LINE('v_source_count => ' || v_source_count);
    DBMS_OUTPUT.PUT_LINE('v_target_count => ' || v_target_count);
    
    IF v_source_count = v_target_count
    THEN
    ADPDDR_HIST_DATA_USD_SP;
    
    END IF;
    
    
    EXCEPTION 
    
    WHEN others THEN
    
      DBMS_OUTPUT.PUT_LINE('EXCEPTION OCCURED '|| SQLERRM);
      
    
    END;
/
 

Open in new window

0
 

Author Comment

by:hypermac
ID: 24305744
Thanks for contiuning to help!  When I try to compile in TOAD I receive the following errors:
ERROR line 112, col 5, ending_line 112, ending_col 13, Found 'EXCEPTION', Expecting CASE or another statement
ERROR line 119, col 8, ending_line 119, ending_col 8, Found ';', Invalid identifier: ;
ERROR line 119, col 8, ending_line 119, ending_col 8, Found ';', Expecting CASE
ERROR line 119, col 8, ending_line 119, ending_col 8,Expecting: ;   -or-   identifier  string

I'll try to start workig through the errors.  Your assistance is greatly appreciated!
0
 
LVL 14

Accepted Solution

by:
ajexpert earned 500 total points
ID: 24305852
Try this

/* Formatted on 5/5/2009 11:35:41 AM (QP5 v5.115.810.9015) */
CREATE OR REPLACE PROCEDURE ADPDDR.ADPDDR_MONTHLY_DATALOAD_SP2
IS
   tmpVar               NUMBER;
   /******************************************************************************
      NAME:       ADPDDR_MONTHLY_DATALOAD
      PURPOSE:
 
      REVISIONS:
      Ver        Date        Author           Description
      ---------  ----------  ---------------  ------------------------------------
      1.0        4/22/2009          1. Created this procedure.
 
      NOTES:
 
      Automatically available Auto Replace Keywords:
         Object Name:     ADPDDR_MONTHLY_DATALOAD
         Sysdate:         4/22/2009
         Date and Time:   4/22/2009, 5:03:48 PM, and 4/22/2009 5:03:48 PM
         Username:         (set in TOAD Options, Procedure Editor)
         Table Name:       (set in the "New PL/SQL Object" dialog)
 
   ******************************************************************************/
 
   PROGRAM_ID           VARCHAR2 (30) := 'ADPDDR_MONTHLY_DATALOAD_SP';
   l_source_count       VARCHAR2 (20);
   l_target_count       VARCHAR2 (20);
   l_start_time         DATE;
   l_max_job_nbr        NUMBER;
   l_source_count2      VARCHAR2 (20);
   l_target_count2      VARCHAR2 (20);
   v_source_count       VARCHAR2 (20);
   v_target_count       VARCHAR2 (20);
   v_max_date           DATE;
   v_max_job_nbr        NUMBER;
   v_max_job_end_date   DATE;
   v_program_id         VARCHAR2 (30);
BEGIN
   l_start_time := SYSDATE;
 
   SELECT   COUNT ( * ) INTO l_source_count FROM adp_rpt.adpds_rpt_curprd;
 
 
   DELETE FROM   ADPDDR.ADPDDR_CURPRD;
 
   COMMIT;
 
   INSERT INTO ADPDDR.ADPDDR_CURPRD (MONTH)
      (  SELECT   MONTH FROM ADP_RPT.ADPDS_RPT_CURPRD);
 
   COMMIT;
 
   SELECT   COUNT ( * ) INTO l_target_count FROM adpddr.adpddr_curprd;
 
   ADPDDR_PROCESS_LOG_SP (
      PROGRAM_ID,
      0,
      NULL,
      'Monthly DataLoad - Load Current Period - Completed Successfully',
      l_source_count,
      l_target_count,
      l_start_time,
      SYSDATE
   );
 
 
 
   DBMS_OUTPUT.PUT_LINE ('getting max job_nbr and end date');
 
   SELECT   MJB, END_TIME, MJB || program_id
     INTO   v_max_job_nbr, v_max_job_end_date, v_program_id
     FROM   (  SELECT   MAX (JOB_NBR) MJB, END_TIME, program_id
                 FROM   ADPDDR_PROCESS_LOG
             GROUP BY   END_TIME, program_id
             ORDER BY   MJB DESC)
    WHERE   ROWNUM < 2;
 
   DBMS_OUTPUT.PUT_LINE ('getting the max date');
 
   SELECT   MAX (end_time) INTO v_max_date FROM ADPDDR_PROCESS_LOG;
 
 
   DBMS_OUTPUT.PUT_LINE ('comparing dates');
 
   IF v_max_job_end_date <> v_max_date
   THEN
      DBMS_OUTPUT.PUT_LINE (
         'MAX JOB NBR AND END_DATE are different records please check'
      );
   --RETURN; -- this will not proceed further. If you want to move ahead, comment this
   END IF;
 
   DBMS_OUTPUT.PUT_LINE ('getting source target counts');
 
   SELECT   source_count, target_count
     INTO   v_source_count, v_target_count
     FROM   ADPDDR_PROCESS_LOG
    WHERE   job_nbr = v_max_job_nbr;
 
   DBMS_OUTPUT.PUT_LINE ('v_source_count => ' || v_source_count);
   DBMS_OUTPUT.PUT_LINE ('v_target_count => ' || v_target_count);
 
   IF v_source_count = v_target_count
   THEN
      ADPDDR_HIST_DATA_USD_SP;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      ADPDDR_PROCESS_LOG_SP ('ADPDDR_MONTHLY_DATALOAD_SP',
                             SQLCODE,
                             SQLERRM,
                             'Raise Application Error - 20013');
      RAISE_APPLICATION_ERROR (-20013, SQLERRM);
 
      COMMIT;
END ADPDDR_MONTHLY_DATALOAD_SP2;

Open in new window

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question