Link to home
Start Free TrialLog in
Avatar of jknj72
jknj72

asked on

Approach to replacing procedure code

I have a procedure I am trying to simplify with a more simplified approach. I am reading from a source, manipulating the data and eventually inserting into another table.
So I am going to ask my first question which is to build my Key which consists of the following
l_FINAL_tabKey := Build_Table_Key(l_FINAL_EffYr, l_FINAL_EffMo, l_FINAL_EffDa, l_FINAL_CVGID, l_FINAL_SeqNum, l_FINAL_polNbr, l_FINAL_Cono);

In my code now I call a function Buiuld_Table_Key and pass a bunch of variables and have them all concatenated and return the string of values concatenated.
So if Im trying to eliminate the loping through of the source data how would I go about building this key

Heres my Insert and what Im trying to do is generate the first value
Im just going to put the value in the Insert statement that I am trying to figure out first.....So the other columns are not there for now(One thing at a time)..I have included the Select statement because that will have the values I need to build the Key
function is in bold.
My question is how can I generate the l_SeqNum (5th param in bold) to increment for each record? Should I do something in the function to increment the number. It needs to be 6 charaters in length starting with '000001' and increment each time?

Insert Into TEMP_BASE_PCOV_POLICY
(POLICY_KEY,
Values (
util.Build_Table_Key(util.GET_DATE_PART('y', c.COV_EFFDT), util.GET_DATE_PART('m', c.COV_EFFDT), util.GET_DATE_PART('d', c.COV_EFFDT),
c.CVG_ID, LPAD(l_SeqNum + 1, 6, '0'), RPAD(c.POLNBR, 18, ' '), SUBSTR(c.CARRID, 3, 3))

Here is the source query I want to use in the Insert statement
Select
c.POLNBR,
c.COV_EFFDT, c.COV_EXPDT, c.CVG_ID, CVG_POL_SEQ,
c.CARRID, car.NAME, c.CARR_ISSUING_STREET, c.CARR_ISSUING_CITY, c.CARR_ISSUING_STATE_CD, c.CARR_ISSUING_ZIP, c.CARR_ISSUING_ADDR_TYPE, c.LCF_IND, c.TXN_CODE,
c.WRAP_UP_IND, '' POLICY_SOLE_PROPRIETOR_IND
Bulk Collect Into l_POLICY_TAB
from COVERAGE c
INNER JOIN CARR car ON c.CARRID = car.CARRID
WHERE COV_EXPDT > l_CONST_covExpDate  AND (STATUS != 'V') ORDER BY CARRID ASC, COV_EFFDT ASC;
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Is the generated key something 'special' required by policy or just something you made up to get a key value?

For the additional later processing, do you get the key values based on data in the key itself?

In other words, something like:  Get all the 'key' values with an 'X' as the 6th character...

>>how can I generate the l_SeqNum

Use something like ROW_NUMBER which will be based on the result set
or
Use an Oracle Sequence:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_6015.htm#SQLRF01314


>>njcrib_util.GET_DATE_PART

I'm guessing at what this does and do you really need a user-defined function to do it when to_char is already written?
Avatar of jknj72

ASKER

>>Is the generated key something 'special' required by policy or just something you made up to get a key value?

This is required for the policy. It is a unique key made up of values that will give the record a unique identifier and this is somthing I have to produce and have no say with

Get_Date_Part is a function I wrote that depending on what you pass in 'M', 'D' or 'Y' I return the Month(2 chars), Day(2 chars) or Year(4 chars)
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jknj72

ASKER

ok sounds good...Thanks
Avatar of jknj72

ASKER

I've requested that this question be closed as follows:

Accepted answer: 0 points for jknj72's comment #a40837033

for the following reason:

Thanks
Avatar of jknj72

ASKER

I meant to accept this question and give points...Sorry
Avatar of jknj72

ASKER

hope this works!!
>>I meant to accept this question and give points

No problem.  You can just 'accept' as normal.  It will override the other accept.
Avatar of jknj72

ASKER

yes I think I did it right ...

I have gotten through the Insert and most of its values but I am starting the address logic now so Im not sure how Im gonna do it but I will show you what I have in a new question shortly...