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_Ef fYr, 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;
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_Ef
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;
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)
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok sounds good...Thanks
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
Accepted answer: 0 points for jknj72's comment #a40837033
for the following reason:
Thanks
ASKER
I meant to accept this question and give points...Sorry
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.
No problem. You can just 'accept' as normal. It will override the other accept.
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...
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...
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_PAR
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?