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;
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;
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?
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.
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
The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.
One of a set of tools we're offering as a way of saying thank you for being a part of the community.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.