Solved

Oracle PL/SQL syntax

Posted on 2016-10-05
4
49 Views
Last Modified: 2016-10-05
I have a coworker that wanted me to help him with this. This is being done in SQL Developer. What he ids doing is a Select statement and assigning the output into a Type and then looping thru that to get a status. Syntax below....What id like to do is include this in the intitial Select statement by creating a function that I can pass some arguments to that will take care of getting the status returned and not have to loop thru the first record set. From what I can see he only needs to get the status for that record back. It seems like a little overkill to me so I have marked the Select statement that I am trying to put into a function with *** so you know what Im trying to do. I will be trying to work on this but I have alot going on so hopefully I can get some help on this from the experts...
This is all done in a package and this is only one of the procedures so if anyone has any questions please let me know and I will answer you ASAP...

  select POL_TAPE_SUBMISSION.SUBMISSION_ID
      ,RCVD_DT
      ,STATUS
      ,STATUS_DT
      ,PROCESSED_DT
      ,TOT_NBR_RECORDS
      ,TOT_NBR_HEADERS
      ,GROUPID
      ,TOT_NBR_01_TXN
      ,TOT_NBR_02_TXN
      ,TOT_NBR_03_TXN
      ,TOT_NBR_04_TXN
      ,TOT_NBR_05_TXN
      ,TOT_NBR_06_TXN
      ,TOT_NBR_08_TXN
      ,TOT_NBR_10_TXN
      ,TOT_NBR_14_TXN
      ,TOT_NBR_15_TXN
      ,TOT_NBR_ERRORS
      ,CTRL_EARLIEST_TXN_ISSUE_DT
      ,CTRL_LATEST_TXN_ISSUE_DT
      ,RECORD_ID
      ,POL_TAPE_ERROR_MSG.MSG_ID
      ,POL_TAPE_ERROR_MSG.MSG_TEXT
      BULK COLLECT INTO [b]L_ERRORS[/b]
      from POL_TAPE_SUBMISSION 
        inner join POL_TAPE_ERROR on POL_TAPE_ERROR.SUBMISSION_ID = POL_TAPE_SUBMISSION.SUBMISSION_ID
        inner join POL_TAPE_ERROR_MSG on POL_TAPE_ERROR_MSG.MSG_ID = POL_TAPE_ERROR.EDIT_ID 
        where
        ((POL_TAPE_SUBMISSION.PROCESSED_DT >= STARTDATE and POL_TAPE_SUBMISSION.PROCESSED_DT <= ENDDATE) and
        POL_TAPE_ERROR_MSG.MSG_ID != 40180 or POL_TAPE_ERROR_MSG.MSG_ID != 40189 or POL_TAPE_ERROR_MSG.MSG_ID != 40172 or POL_TAPE_ERROR_MSG.MSG_ID != 40198
        or POL_TAPE_ERROR_MSG.MSG_ID != 30066 or POL_TAPE_ERROR_MSG.MSG_ID != 40177 or POL_TAPE_ERROR_MSG.MSG_ID != 77012 or POL_TAPE_ERROR_MSG.MSG_ID != 48031) order by POL_TAPE_SUBMISSION.GROUPID asc, POL_TAPE_SUBMISSION.SUBMISSION_ID asc, POL_TAPE_SUBMISSION.PROCESSED_DT asc;

    L_INITPULL_CNTR := L_ERRORS.count;    
    I_ERR_INDX := L_ERRORS.first;
    insert into ERROR_SUMMARY_LOG(LOGID,LOG_MSG, LOG_DATE,LOG_PROCESS_RESULT) values (ERR_ID_TAB_SEQ.NEXTVAL, 'BUILD MASTER WORK TABLE STARTED WITH INITIAL REC COUNT: ' || L_INITPULL_CNTR, systimestamp,'0'); 
    commit;

*** LOOPING THRU L_ERRORS HERE AND THIS IS WHERE ID LIKE TO PUT A FUNCTION IN THE SELECT STATEMENT ABOVE IF POSSIBLE..  DOESNT HAVE TO BE A FUNCTION EITHER, SO IF ANYONE HAS ANY OTHER THOUGHTS LET ME KNOW
***
WHILE (I_ERR_INDX is not null) LOOP
   L_RECSREAD := L_RECSREAD + 1;
    L_NUMBEROFERRORS := L_NUMBEROFERRORS + 1;
    
    if L_ERRORS(I_ERR_INDX).ERR_MSG_ID != L_MSGID_HOLD then
       L_MSGID_HOLD := L_ERRORS(I_ERR_INDX).ERR_MSG_ID;
       begin  
             select * bulk collect into L_POL_TAPE from POL_TAPE_EDIT_REPORT where EDIT_ID = L_MSGID_HOLD;
             PT_INDX := L_POL_TAPE.first;
             if (PT_INDX is not null) then
                 if L_MSGID_HOLD = L_POL_TAPE(PT_INDX).PT_EDIT_ID and L_POL_TAPE(PT_INDX).PT_REP_NAME = 'PT4' and L_POL_TAPE(PT_INDX).PT_INCL_IND = 'N' then
                       H_DQCOUNT := 'Y';
                 else
                      H_DQCOUNT := 'N';
                 end if;
             else
                H_DQCOUNT := 'N';
             end if;
            EXCEPTION 
                    when NO_DATA_FOUND then
                        H_DQCOUNT := 'N';
                    when OTHERS then
                        l_ENDTIME := SYSTIMESTAMP;
                        V_CODE := SQLCODE;
                        V_ERRM := SUBSTR(SQLERRM, 1, 2000);
                        insert into ERROR_SUMMARY_LOG(LOGID,LOG_MSG, LOG_DATE,LOG_PROCESS_RESULT) values (ERR_ID_TAB_SEQ.NEXTVAL, 'BAD**SELECT FOR DQ CHECK: ' || v_code || ': ' || v_errm || 'Recs Read: ' || L_RECSREAD, systimestamp,'1');
                        commit;
         end; -- Ends check fpr DQ edit errors
    end if;

---*** END OF STATEMENT I NEED HELP WITH

--*** I AM GOING TO POST ANOTHER QUESTION WITH THE SAME TYPE OF ISSUE AND THIS IS THE START OF IT. YOU CAN IGNORE THIS FOR NOW BUT I THINK I NEED TO CREATE A FUNCTION FOR THIS TOO SO THAT WE DONT HAVE TO LOOP THRU THE RECORDS AS WE ARE DOING ABOVE..
***
  
    L_SUBID_HOLD := L_ERRORS(I_ERR_INDX).ERR_SUBID;
    L_RECID_HOLD := L_ERRORS(I_ERR_INDX).ERR_REC_ID;
    
    begin
      select POL_TAPE_RECORD.SUBMISSION_ID, POL_TAPE_RECORD.RECORD_ID, POL_TAPE_RECORD.CARRID, POL_TAPE_RECORD.POLNBR, POL_TAPE_RECORD.TXN_CODE, POL_TAPE_RECORD.COV_EFFDT, POL_TAPE_RECORD.TXN_CVG_ID, POL_TAPE_RECORD.TXN_STATUS 
bulk collect into L_POL_REC  
from POL_TAPE_RECORD 
where POL_TAPE_RECORD.SUBMISSION_ID = L_SUBID_HOLD  and POL_TAPE_RECORD.RECORD_ID = L_RECID_HOLD; 

      PTR_INDX := L_POL_REC.first;
      WHILE (PTR_INDX is not null) LOOP
            H_DCOCONO := L_POL_REC(PTR_INDX).PTR_CONOID;
            H_POLNBR := L_POL_REC(PTR_INDX).PTR_POLNBR;
            H_TXNCODE := L_POL_REC(PTR_INDX).PTR_TRNS;
            H_COVEFFDT := L_POL_REC(PTR_INDX).PTR_COVDT;
            H_CVGID := L_POL_REC(PTR_INDX).PTR_CVGID;
            H_STATUS := L_POL_REC(PTR_INDX).PTR_TXNSTAT;
            if H_STATUS != 'R' then
               H_STATUS := 'P';
            end if;
            PTR_INDX := null;
      end LOOP;
      EXCEPTION 
            when OTHERS then
                l_ENDTIME := SYSTIMESTAMP;
                V_CODE := SQLCODE;
                V_ERRM := SUBSTR(SQLERRM, 1, 2000);
                insert into ERROR_SUMMARY_LOG(LOGID,LOG_MSG, LOG_DATE,LOG_PROCESS_RESULT) values (ERR_ID_TAB_SEQ.NEXTVAL, 'BAD**SELECT FOR CONO ID: ' || v_code || ': ' || v_errm || 'Recs Read: ' || L_RECSREAD, systimestamp,'1');
                commit;
    end;

Open in new window


I will post another question with the Same Title and I will have PART II after it...

THANKS EVERYONE FOR YOUR HELP!!!
0
Comment
Question by:jknj72
  • 2
4 Comments
 
LVL 20

Accepted Solution

by:
flow01 earned 500 total points
Comment Utility
Try this: (i can't check if the code is valid )  
(the L_ERRORS  definition wil have to change to contain the  DQCOUNT column

  select POL_TAPE_SUBMISSION.SUBMISSION_ID
      ,RCVD_DT
      ,STATUS
      ,STATUS_DT
      ,PROCESSED_DT
      ,TOT_NBR_RECORDS
      ,TOT_NBR_HEADERS
      ,GROUPID
      ,TOT_NBR_01_TXN
      ,TOT_NBR_02_TXN
      ,TOT_NBR_03_TXN
      ,TOT_NBR_04_TXN
      ,TOT_NBR_05_TXN
      ,TOT_NBR_06_TXN
      ,TOT_NBR_08_TXN
      ,TOT_NBR_10_TXN
      ,TOT_NBR_14_TXN
      ,TOT_NBR_15_TXN
      ,TOT_NBR_ERRORS
      ,CTRL_EARLIEST_TXN_ISSUE_DT
      ,CTRL_LATEST_TXN_ISSUE_DT
      ,RECORD_ID
      ,POL_TAPE_ERROR_MSG.MSG_ID
      ,POL_TAPE_ERROR_MSG.MSG_TEXT
--***
             ,(SELECT
                    DECODE(SUM(CASE
                         WHEN POL_TAPE_SUBMISSION.ERR_MSG_ID = POL_TAPE_EDIT_REPORT.PT_EDIT_ID
                             and POL_TAPE_EDIT_REPORT.PT_REP_NAME = 'PT4'
                             and POL_TAPE_EDIT_REPORT.PT_INCL_IND = 'N' then 1
                         ELSE 0
                         END)
                    ,0,'N','Y')
              from POL_TAPE_EDIT_REPORT where EDIT_ID = POL_TAPE_ERROR_MSG.MSG_ID
              ) DQCOUNT
--***
      BULK COLLECT INTO L_ERRORS
      from POL_TAPE_SUBMISSION
        inner join POL_TAPE_ERROR on POL_TAPE_ERROR.SUBMISSION_ID = POL_TAPE_SUBMISSION.SUBMISSION_ID
        inner join POL_TAPE_ERROR_MSG on POL_TAPE_ERROR_MSG.MSG_ID = POL_TAPE_ERROR.EDIT_ID
        where
        ((POL_TAPE_SUBMISSION.PROCESSED_DT >= STARTDATE and POL_TAPE_SUBMISSION.PROCESSED_DT <= ENDDATE) and
        POL_TAPE_ERROR_MSG.MSG_ID != 40180 or POL_TAPE_ERROR_MSG.MSG_ID != 40189 or POL_TAPE_ERROR_MSG.MSG_ID != 40172 or POL_TAPE_ERROR_MSG.MSG_ID != 40198
        or POL_TAPE_ERROR_MSG.MSG_ID != 30066 or POL_TAPE_ERROR_MSG.MSG_ID != 40177 or POL_TAPE_ERROR_MSG.MSG_ID != 77012 or POL_TAPE_ERROR_MSG.MSG_ID != 48031) order by POL_TAPE_SUBMISSION.GROUPID asc, POL_TAPE_SUBMISSION.SUBMISSION_ID asc, POL_TAPE_SUBMISSION.PROCESSED_DT asc;

or change the loop TO

*** LOOPING THRU L_ERRORS HERE AND THIS IS WHERE ID LIKE TO PUT A FUNCTION IN THE SELECT STATEMENT ABOVE IF POSSIBLE..  DOESNT HAVE TO BE A FUNCTION EITHER, SO IF ANYONE HAS ANY OTHER THOUGHTS LET ME KNOW
***
WHILE (I_ERR_INDX is not null) LOOP
   L_RECSREAD := L_RECSREAD + 1;
    L_NUMBEROFERRORS := L_NUMBEROFERRORS + 1;
   
    if L_ERRORS(I_ERR_INDX).ERR_MSG_ID != L_MSGID_HOLD then
       L_MSGID_HOLD := L_ERRORS(I_ERR_INDX).ERR_MSG_ID;
       begin  
            SELECT
                    DECODE(SUM(CASE
                         WHEN POL_TAPE_SUBMISSION.ERR_MSG_ID = L_MSGID_HOLD
                             and POL_TAPE_EDIT_REPORT.PT_REP_NAME = 'PT4'
                             and POL_TAPE_EDIT_REPORT.PT_INCL_IND = 'N' then 1
                         ELSE 0
                         END)
                    ,0,'N','Y')
              INTO H_DQCOUNT
              from POL_TAPE_EDIT_REPORT where EDIT_ID = POL_TAPE_ERROR_MSG.MSG_ID;
            EXCEPTION
                    when NO_DATA_FOUND then
                        H_DQCOUNT := 'N';
                    when OTHERS then
                        l_ENDTIME := SYSTIMESTAMP;
                        V_CODE := SQLCODE;
                        V_ERRM := SUBSTR(SQLERRM, 1, 2000);
                        insert into ERROR_SUMMARY_LOG(LOGID,LOG_MSG, LOG_DATE,LOG_PROCESS_RESULT) values (ERR_ID_TAB_SEQ.NEXTVAL, 'BAD**SELECT FOR DQ CHECK: ' || v_code || ': ' || v_errm || 'Recs Read: ' || L_RECSREAD, systimestamp,'1');
                        commit;
         end; -- Ends check fpr DQ edit errors
    end if;

---*** END OF STATEMENT I NEED HELP WITH
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Any particular reason you cannot join the 5 tables in a single select then just extract the data you want on the join line or in the where clause?

It also looks like the second loop only ever looks at the first row of the collection.

We also don't have the rest of the code to see what the final product is.  The second loop sets some H_ variables but what is done with those after they are set?
0
 

Author Closing Comment

by:jknj72
Comment Utility
Thanks flow. My co-worker thought this would work best for what he was doing. He created the other column for L_Errors and was able to get what he needed. Thank you for your help.
0
 

Author Comment

by:jknj72
Comment Utility
Thanks guys. My cowroekr didnt need the other portion of the package. He decided to follow the same logic as above..
Thank you everyone
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
levels for reporting 5 47
How to copu batch file to a network location 62 66
dates - loop 12 38
SQL Retrieve Values 4 37
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now