Address logic

looks like you have a plan,  what is the question?

Not quite what I was thinking but probably close...

What do these functions 'check'?

I was thinking more along the lines of:
select case when min(endorse_found)=1 then field1_from_endorse else  field1_from_insured end field1,
   case when min(endorse_found)=1 then field2_from_endorse else  field2_from_insured end field2
...
from
(
select 1 endorse_found, field1, field2 ... from endorse
   union all
select 2, field1, field2 ... from location
)


The more you can post about the actual check you need to do, the more accurate our suggestions.

Sample data and expected results would likely result in actual working code...

sdstuber - kinda just wanted to know if my plan was a good one...sometimes what I think is a good plan doesn't pan out...

slight - after I check the Endorse table, I set the values for the Address fields. If it goes to the Insured_Location table then I have to check for the Type of address and set variables for that specific type and move on. So there are a lot of checks that's why I was thinking a function because I can do all my checks and return what I need? I think.....

Remember what got you here:
Trying to move pl/sql code into the SQL engine.

The function just moves the code from the FOR LOOP into a FUNCTION.  Doesn't really save you that much.

I'm trying to get you away from writing any code at all.  Do it with the functions/logic Oracle gives you out of the box.

You should be able to write a select based on 'type' that gives you what you need without pl/sql code.

Should also point out:
My guess is CheckEndorseAddress would need to take those values and query the same ENDORSE and ENDORSE_WC290303 tables that the query after it accesses?

So to retrieve the values, you hit the tables twice?

Well this is the code I have now....
Select ENDORSE_WC290303.ADDRESS_OF_CLIENT
        FROM ENDORSE          
        INNER JOIN ENDORSE_WC290303 ON ENDORSE_WC290303.ENDORSE_ID = ENDORSE.ENDORSE_ID
        WHERE ENDORSE.CARRID = l_COV_pCARRID AND ENDORSE.POLNBR = l_COV_polNbr AND ENDORSE.COV_EFFDT = l_COV_covEffDate AND ENDORSE.ENDORSE_CD_NBR = l_CONST_ENDORSE_CD_NBR;
                                       
        l_TOTAL_END_ADD_FOUND_COUNT := 0;
        l_TOTAL_END_ADD_FOUND_COUNT := l_END_ADD.count;
       
        j:= l_END_ADD.last;     --Move to last record if more then one
                                       
        --Set address variables
        if l_TOTAL_END_ADD_FOUND_COUNT > 0 then  --Check for values returned                      
           
            l_END_AddressOfClient := l_END_Add(j).AddressOfCLient;
            l_END_AddressOfClient := UPPER(l_END_AddressOfClient);
           
            If INSTR(UPPER(l_END_AddressOfClient,'NO SPECIFIC')) > 0 Then
              l_END_STATE_CD := 'NJ';
            Else
              l_END_STATE_CD := RPAD(' ', 2, ' ');
            End If;
                                 
            l_END_AddressOfClient := njcrib_util.CLEAN_NON_ASCII(l_END_AddressOfClient);
            --streetAdd = chkForASCII(streetAdd)                    
            If LENGTH(l_END_AddressOfClient) < 40 Then
              l_END_STREET := RPAD(l_END_AddressOfClient, 40, ' ');
            Else
              l_END_STREET := SUBSTR(l_END_AddressOfClient, 1, 40);
            End If;
            l_END_CITY := RPAD(lcfCity,20, ' ');
            l_END_ZIP5 := '00000';
            l_END_ZIP4 := '0000';
            l_AddressUsed := 1;  
        end if;                


I would only have to query the Endorse table once and I was thinking to pass back the values concatenated and separated by a ~? How could I write that without the function?

and remember if this doesn't return values I would have to check another table and set the values based off of that..

>>j:= l_END_ADD.last;

Still using bulk collect...  :(

>> I was thinking to pass back the values concatenated and separated by a ~?

Yeah, don't do that...

>>How could I write that without the function?

I'm trying to mock something up based on what I think I understand...

>> I would have to check another table and set the values based off of that..

I get that.

"The first thing I do is check the Endorse table for an address...If that doesn't give me the values I need I check my Insured_Location table"

Is it as simple as if a matching record exists at all in the Endorse table?  If yes, then you can use simple nvl logic and an outer (or left) join, and not need a function at all.  But, if you expect the Endorse table record to exist in all cases and you need to evaluate it (or some other data?) to determine whether to use it, or not, then we would need to know what you need to check.

slight, you just saw some old code...No more Bulk Collect...serious!

I saw what you posted above.  That was a copy/paste from what you posted...

Mark, actually it doesn't have many of my values at all....To be honest, It usually comes from the Insured_location table, which is my 2nd check and where I usually get more than 1 value. I then have to figure out which record to choose by the Type which is additional logic.
So I started off asking about my first check(Endorse table) and maybe if I can figure out a good way to do that I can do the same for the next check.

trust me Slight...No more Bulk Collects

Here is a simplified test case based on the requirements I have put together from this and previous questions.

If this doesn't quite get you what you need, please add any columns/data/??? you need.

I did simplify the query against the ENDORSE tables and only used the one table.

I also made up the POLLIST table just so I could return two different 'customers'.

drop table myPOLLIST purge;
create table myPOLLIST(POLNBR number);
insert into myPOLLIST values(1);
insert into myPOLLIST values(2);
commit;


drop table myENDORSE_WC290303 purge;
create table myENDORSE_WC290303 (polnbr number, address_of_client varchar2(15));

insert into myENDORSE_WC290303 values(1,'from endorse');
commit;

drop table myLOCATION purge;
create table myLOCATION(
POLNBR number, ADDR_TYPE number, ADDR_FORMATTED char(1), Street char(1), City char(1), State_CD char(1), ZIP char(1)); 
insert into myLOCATION values(2,1,'a','a','a','a','a');
insert into myLOCATION values(2,2,'b','b','b','b','b');
insert into myLOCATION values(2,1,'c','c','c','c','c');
insert into myLOCATION values(2,1,'d','d','d','d','d');
commit;

select polnbr, address_to_use
from (
	select polnbr, address_to_use, row_number() over(partition by polnbr order by addr_type desc) add_rn
	from (
		select p.polnbr, nvl(addr_type,99999) addr_type, nvl(address_of_client,addr_formatted || ' ' || street || ' ' || city || ' ' || state_cd || ' ' || zip) address_to_use
		from
			myPOLLIST p
				left outer join myENDORSE_WC290303 E on p.polnbr=e.polnbr
				left outer join myLOCATION L on p.polnbr=l.polnbr
	)
)
where add_rn=1
/

Select allOpen in new window

>>trust me Slight...No more Bulk Collects

Maybe not bulk collects but still using in-memory tables.  Otherwise l_END_ADD would have no ".last".  Then again, I may be missing something...

I am looking at the old code and have eliminated that from my most recent code. I am just using this as a template and the BULK COLLECTS ARE GONE!!!!

I will check the new code and report back...Thanks

If you want me to show you my latest code, which has NO BULK COLLECTS I will. Just ask...

no the ".last" was from the old code and I just didn't remove it...New code = No more In memory tables

Hey slight I couldnt get that to run...I wanna put it all out there but didn't want it to be to much in a question but here it is...

I know my code sucks and is redundant so if your gonna ask me why I did this and why I did that please keep it to yourself. I m hear in the hopes I can do it better and not be criticized

Address code:
l_AddressUsed := 0;                    
                  --Set the address...Check Endorse first then INSURED_LOCATION
                  BEGIN
                    --Get the last address
                    Select ADDRESS_OF_CLIENT into l_END_AddressOfClient
                    FROM (Select ENDORSE_WC290303.ADDRESS_OF_CLIENT FROM ENDORSE            
                    INNER JOIN ENDORSE_WC290303 ON ENDORSE_WC290303.ENDORSE_ID = ENDORSE.ENDORSE_ID
                    WHERE ENDORSE.CARRID = l_COV_pCARRID AND ENDORSE.POLNBR = l_COV_polNbr AND ENDORSE.COV_EFFDT = l_COV_covEffDate AND ENDORSE.ENDORSE_CD_NBR = l_CONST_ENDORSE_CD_NBR
                    ORDER BY ENDORSE.COV_EFFDT DESC)
                    WHERE ROWNUM = 1;
                   
                  EXCEPTION
                    WHEN NO_DATA_FOUND THEN    
                      l_END_AddressOfClient := NULL;
                  END;
                                   
                  --Set address variables
                  if length(l_END_AddressOfClient) > 0 then          
                      l_END_AddressOfClient := UPPER(l_END_AddressOfClient);
                     
                      If INSTR(l_END_AddressOfClient,'NO SPECIFIC') > 0 Then
                        l_END_STATE_CD := 'NJ';
                      Else
                        l_END_STATE_CD := RPAD(' ', 2, ' ');
                      End If;
                                           
                      l_END_AddressOfClient := njcrib_util.CLEAN_NON_ASCII(l_END_AddressOfClient);
               
                      If LENGTH(l_END_AddressOfClient) < 40 Then
                        l_END_STREET := RPAD(l_END_AddressOfClient, 40, ' ');
                      Else
                        l_END_STREET := SUBSTR(l_END_AddressOfClient, 1, 40);
                      End If;
                      l_END_CITY := RPAD(lcfCity,20, ' ');
                      l_END_ZIP5 := '00000';
                      l_END_ZIP4 := '0000';
                      l_AddressUsed := 1;  
                  end if;                                    

                  --ENDORSE query brought back no records
                  if l_AddressUsed = 0 then
                      --Check Insured_Location table                                            
                      FOR x in (Select ADDR_TYPE, ADDR_FORMATTED, Street, City, State_CD, ZIP from INSURED_LOCATION
                                where POLNBR = l_COV_polNbr AND COV_EFFDT = l_COV_covEffDate
                                ORDER BY ADDR_TYPE DESC) LOOP      
                                                                 
                          l_Address_Confirm := 0;
                         
                          --Type 2 = Preferred and if more than 1 take the last address
                          --Type 1 = when there are no Type 2
   
                           --The current logic is that if there are multiple records we loop through and assign variables and
                          --then check the variables that are set to see which one we will be using
                          --If there are multiple Types(ex.. There are 2 Type 2s) then the last one is used
                          --Set variables for all records and then after Loop finishes set final variables                          
                          IF x.ADDR_TYPE = '2' THEN
                                --Assign address vars
                                l_InsFormattedWorkLocation_2 := TRIM(x.ADDR_FORMATTED);
                                l_InsStreetAddress_2 := x.Street;
                                l_InsCity_2 := x.City;
                                l_InsState_2 := x.State_CD;
                                l_InsZip5_2_Orig := x.ZIP;
                                l_InsZip5_2 := SUBSTR(x.ZIP, 1, 5);
                               
                                If LENGTH(l_InsZip5_2) = 0 OR util.Is_Number(l_InsZip5_2) = 0 OR l_InsZip5_2 IS NULL Then  
                                    l_InsZip5_2 := '00000';
                                End If;                                
                                if length(l_InsZip5_2_Orig) > 8 then
                                  l_InsZip4_2 := SUBSTR(l_InsZip5_2_Orig, 6, 4);
                                else
                                  l_InsZip4_2 := '0000';
                                end if;

                          ELSIF x.ADDR_TYPE = '1' THEN
                                --Assign address vars
                                l_InsFormattedMail_1 := x.ADDR_FORMATTED;
                                l_InsStreetAddress_1 := x.Street;
                                l_InsCity_1 := x.City;
                                l_InsState_1 := x.State_CD;
                                l_InsZip5_1_Orig := x.ZIP;
                                l_InsZip5_1 := SUBSTR(x.ZIP, 1, 5);
                               
                                if length(l_InsZip5_1) = 0 OR util.Is_Number(l_InsZip5_1) = 0 OR l_InsZip5_1 IS NULL Then
                                  l_InsZip5_1 := '00000';
                                end if;
                                if length(l_InsZip5_1_Orig) > 8 then
                                    l_InsZip4_1 := SUBSTR(l_InsZip5_1_Orig, 6, 4);
                                else
                                    l_InsZip4_1 := '0000';
                                End If;
                               
                          ELSE
                                ---No address so put in default values
                                l_InsStreetAddress := RPAD('NO SPECIFIC LOCATION', 40, ' ');
                                l_InsCity := RPAD('NS-LOCATION', 20, ' ');
                                l_InsState := 'NJ';
                                l_InsZip5 := '00000';
                                l_InsZip4 := RPAD(' ', 4, ' ');

                          END IF;    

                         
                      END LOOP;                                                                            
                     
                      --This will set which address we should use
                      --1st option
                      if LENGTH(l_InsStreetAddress_2) > 0 AND l_InsFormattedWorkLocation_2 = 'Y' then  --If these variables meet criteria then we use the ADDTYPE = 2                      
                           --These are for AddType 2 values
                           --Clean Non Ascii Address and City vars
                          l_InsStreetAddress_2 := util.CLEAN_NON_ASCII(l_InsStreetAddress_2);
                          If Length(l_InsStreetAddress_2) < 40 Then
                              l_InsStreetAddress_2 := RPAD(l_InsStreetAddress_2, 40, ' ');
                          Else
                              l_InsStreetAddress_2 := SUBSTR(l_InsStreetAddress_2, 1, 40);
                          End If;
                          --Clean Non Ascii City
                          l_InsCity_2 := util.CLEAN_NON_ASCII(l_InsCity_2);
                          If Length(l_InsCity_2) < 20 Then
                              l_InsCity_2 := RPAD(l_InsCity_2, 20, ' ');
                          Else
                              l_InsCity_2 := SUBSTR(l_InsCity_2, 1, 20);
                          End If;
                         
                          l_InsState_2 := RPAD(l_InsState_2, 2, ' ');
                          l_InsZip5_2 := LPAD(l_InsZip5_2, 5, '0');
                          l_InsZip4_2 := LPAD(l_InsZip4_2, 4, '0');
                         
                          l_Address_Confirm := 1;
                          l_AddressUsed := 2;
                      --2nd option
                      ELSIF Length(l_InsStreetAddress_1) > 0 And l_InsFormattedMail_1 = 'Y' Then    --If these variables meet criteria then we use the ADDTYPE = 1
                          --These are for AddType 1 values
                          --Clean Non Ascii Address and City vars
                          l_InsStreetAddress_1 := util.CLEAN_NON_ASCII(l_InsStreetAddress_1);
                          If Length(l_InsStreetAddress_1) < 40 Then
                              l_InsStreetAddress_1 := RPAD(l_InsStreetAddress_1, 40, ' ');
                          Else
                              l_InsStreetAddress_1 := SUBSTR(l_InsStreetAddress_1, 1, 40);
                          End If;
                         
                          l_InsCity_1 := util.CLEAN_NON_ASCII(l_InsCity_1);
                          If Length(l_InsCity_1) < 20 Then
                              l_InsCity_1 := RPAD(l_InsCity_1, 20, ' ');
                          Else
                              l_InsCity_1 := SUBSTR(l_InsCity_1, 1, 20);
                          End If;
                          l_InsState_1 := RPAD(l_InsState_1, 2, ' ');
                          l_InsZip5_1 := LPAD(l_InsZip5_1, 5, '0');
                          l_InsZip4_1 := LPAD(l_InsZip4_1, 4, '0');
                         
                          l_Address_Confirm := 1;
                          l_AddressUsed := 3;
                      --3rd option    
                      ELSIF LENGTH(l_InsStreetAddress_2) > 0 THEN     --Go back to AddType 2 to check for values without the Formatted Work Location
                       
                          l_InsStreetAddress_2 := util.CLEAN_NON_ASCII(l_InsStreetAddress_2);
                         
                          If Length(l_InsStreetAddress_2) < 40 Then
                              l_InsStreetAddress_2 := RPAD(l_InsStreetAddress_2, 40, ' ');
                          Else
                              l_InsStreetAddress_2 := SUBSTR(l_InsStreetAddress_2, 1, 40);
                          End If;
                         
                          l_InsCity_2 := util.CLEAN_NON_ASCII(l_InsCity_2);
                          If Length(l_InsCity_2) < 20 Then
                              l_InsCity_2 := RPAD(l_InsCity_2, 20, ' ');
                          Else
                              l_InsCity_2 := SUBSTR(l_InsCity_2, 1, 20);
                          End If;
                          l_InsState_2 := RPAD(l_InsState_2, 2, ' ');
                          l_InsZip5_2 := LPAD(l_InsZip5_2, 5, '0');
                          l_InsZip4_2 := LPAD(l_InsZip4_2, 4, '0');
                         
                          l_Address_Confirm := 1;
                         
                          l_AddressUsed := 2;
                      --4th option
                      ELSIF Length(l_InsStreetAddress_1) > 0 THEN
                            l_InsStreetAddress_1 := util.CLEAN_NON_ASCII(l_InsStreetAddress_1);
                             
                            If Length(l_InsStreetAddress_1) < 40 Then
                                l_InsStreetAddress_1 := RPAD(l_InsStreetAddress_1, 40, ' ');
                            Else
                                l_InsStreetAddress_1 := SUBSTR(l_InsStreetAddress_1, 1, 40);
                            End If;
                           
                            l_InsCity_1 := util.CLEAN_NON_ASCII(l_InsCity_1);
                            If Length(l_InsCity_1) < 20 Then
                                l_InsCity_1 := RPAD(l_InsCity_1, 20, ' ');
                            Else
                                l_InsCity_1 := SUBSTR(l_InsCity_1, 1, 20);
                            End If;
                            l_InsState_1 := RPAD(l_InsState_1, 2, ' ');
                            l_InsZip5_1 := LPAD(l_InsZip5_1, 5, '0');
                            l_InsZip4_1 := LPAD(l_InsZip4_1, 4, '0');
                           
                            l_Address_Confirm := 1;
                           
                            l_AddressUsed := 3;
                      ELSE
                          --last option
                            ---No address so put in default values
                            l_InsStreetAddress := RPAD('NO SPECIFIC LOCATION', 40, ' ');
                            l_InsCity := RPAD('NS-LOCATION', 20, ' ');
                            l_InsState := 'NJ';
                            l_InsZip5 := '00000';
                            l_InsZip4 := RPAD(' ', 4, ' ');
                           
                            l_Address_Confirm := 1;
                           
                            l_AddressUsed := 4;
                      end if;
                       
                  end if;

                --l_AddressedUsed is set--
                --SET THE FINAL ADDRESS      
                CASE
                  WHEN l_AddressUsed = 1 THEN
                    --l_FINAL_AddressOfClient := l_END_AddressOfClient;
                    l_FINAL_Street := l_END_STREET;
                    l_FINAL_City := l_END_CITY;
                    l_FINAL_State := l_END_STATE_CD;
                    l_FINAL_Zip5 := l_END_ZIP5;
                    l_FINAL_Zip4 := l_END_ZIP4;                                            
                  WHEN l_AddressUsed = 2  THEN                              
                    l_FINAL_Street := l_InsStreetAddress_2;
                    l_FINAL_City := l_InsCity_2;
                    l_FINAL_State := l_InsState_2;
                    l_FINAL_Zip5 := l_InsZip5_2;
                    l_FINAL_Zip4 := l_InsZip4_2;
                  WHEN l_AddressUsed = 3 THEN                      
                    l_FINAL_Street := l_InsStreetAddress_1;
                    l_FINAL_City := l_InsCity_1;
                    l_FINAL_State := l_InsState_1;
                    l_FINAL_Zip5 := l_InsZip5_1;
                    l_FINAL_Zip4 := l_InsZip4_1;    
                  WHEN l_AddressUsed = 4 THEN
                    ---No address so put in default values                              
                    l_FINAL_Street := l_InsStreetAddress;
                    l_FINAL_City :=   l_InsCity;
                    l_FINAL_State := l_InsState;
                    l_FINAL_Zip5 := l_InsZip5;
                    l_FINAL_Zip4 := l_InsZip4;                      
                END CASE;                          

                     
               --INSERT POLICY INFO HERE
               Insert Into TEMP_BASE_PCOV_POLICY (POLICY_KEY, POLICY_NUM,      BUREAU_FILE_NUM, POLICY_EFF_YR , POLICY_EFF_MO , POLICY_EFF_DA,      POLICY_EXP_YR,      POLICY_EXP_MO ,      POLICY_EXP_DA,      
               ISSUE_OFFICE_NAME, ISSUE_OFFICE_ADDR_STREET      , ISSUE_OFFICE_ADDR_CITY, ISSUE_OFFICE_ADDR_STATE, ISSUE_OFFICE_ADDR_ZIP5 , ISSUE_OFFICE_ADDR_ZIP4,
               POLICY_TRANS_IND,      POLICY_WRAP_UP_IND,
               INSURED_MAIL_NAME, INSURED_MAIL_ADDR_STREET, INSURED_MAIL_ADDR_CITY, INSURED_MAIL_ADDR_STATE, INSURED_MAIL_ADDR_ZIP5, INSURED_MAIL_ADDR_ZIP4,
               POLICY_EFF_DT, POLICY_EXP_DT, POLICY_CVG_ID, POLICY_CARR_ID, ADDR_TYPE_USED, ROWNUMBER)
                Values (l_FINAL_tabKey, l_FINAL_polNbr, l_COV_cvg_id, l_FINAL_EffYr, l_FINAL_EffMo, l_FINAL_EffDa, l_FINAL_ExpYr, l_FINAL_ExpMo, l_FINAL_ExpDa,
                l_COV_CARR_NAME, l_COV_ISSUING_STREET, l_COV_ISSUING_CITY, l_COV_ISSUING_STATE_CD, l_COV_ISSUING_ZIP5, l_COV_ISSUING_ZIP4,
                l_COV_POL_TRANSID_CORRECTED, l_COV_WRAP_UP_IND,
                l_COV_INSURED_NAME, l_FINAL_Street, l_FINAL_City, l_FINAL_State, l_FINAL_Zip5, l_FINAL_Zip4,
                l_COV_covEffDate, l_COV_covExpDate, l_COV_cvg_id, l_FINAL_CARRID, l_AddressUsed, l_RowNumber);            
                COMMIT;                                      

Hope its not too much!!!

>>Hey slight I couldnt get that to run...

What didn't run?
Did it generate an error?  If so, what was it?
I tested it using 11.2.0.2.  What version of the database are you on?

>> I know my code sucks and is redundant so if your gonna ask me why I did this and why I did that please keep it to yourself.

Then I'm afraid I cannot help.

It is my understanding that you have code that works and you are here to make it work better.

For us to help make it better, we need to understand the actual requirements before we can make recommendations.  To understand the requirements, we have to ask the why/what/who/etc...

>>Address code:

I went through the procedure from your previous question when I set up my test case.

Is what you posted above very much different as far as the requirements go?

I would try to do this yet in just SQL if possible first, by using a view (or two or three) to hide some of the complexity from your insert statement.  Using these statements as the basic requirements:
    --Type 2 = Preferred and if more than 1 take the last address
    --Type 1 = when there are no Type 2

I would start by creating a view to get the latest type 2 address, and a second view that uses the first view plus nvl's and an outer join to get the type 1 address for records that don't have a type 2 address.

That wasn't meant for you Slight, sdstuber or Mark. You guys have helped me out a lot but sometimes it seems like I get people that just come on these questions just to tear your stuff apart. That's what Im trying to avoid.

Anyway, I got the error
ORA-00904: "E"."POLNBR": invalid identifier
I went to the line it said where the problem was and its the NVL(addr_type, 99999). But it doesn't always give an accurate line number where the actual problem is.
The only place I see e.polnbr is on the join at the end?

The last code I posted is what I have in my latest package that I ran last night. Its not that different from the code I have posted before, other than the Types not being used anymore with the BULK Collects. Actually it is probably the same because the address logic hasn't been changed(and if it has its very minimal). I have my new piece of code(Insert statement) that I am trying to figure out if youd like to see that but the code I posted is what I am ultimately trying to make better.
It took over 2 hours to run and my boss is ok with it because it takes over 8 hours now. I just think I can do better and Im trying to give it a shot

ok Mark, that's a good idea. I was thinking on maybe a Union query that would bring back all the Endorse and Insurance Addresses(Type 1 and 2) but a view is a good idea....

>>The only place I see e.polnbr is on the join at the end?

Correct.  It is just for the join.

I just reran the script again and received no errors.

Can you save what I posted in a file, say bob.sql, and execute it from sqlplus?

>>it seems like I get people that just come on these questions just to tear your stuff apart. That's what Im trying to avoid.

From time to time, some like that might enter a question.  In this case, it is only the three of us you mentioned participating so I had to take it that the comment was directed at one of us.

never would say that to you. It seems like when I post some code is when that happens and that's why I said what I said. Sorry for the confusion.

I don't know if I even have SQLPlus...I have told myself I will try and figure that out in the near future just haven't had the time to do so yet..

The code should also run in SQL Developer.

Not sure where the error is unless you changed my test case to use your tables and didn't change the column names to correct my lack of knowledge?

yep your right, sorry. I get some values back and think I know where your going with this. I am going to put my tables and queries in now and see if I can get what I need. If im missing something please let me know. THANKS

one more question. What is myPOLLIST representing in the code as far as my tables go?

>>What is myPOLLIST representing in the code as far as my tables go?

I needed a 'source' table for multiple POLNBR/address combinations.

So, for POLNBR=1, I get the address from the ENDORSE logic.  For POLNBR=2, it goes into the LOCATION logic.

It should be a simple enough matter to add the additional join to the main ENDORSE.  I just didn't want to overcomplicate the test case.

I'm not sure how that fits into your actual logic.  I was hoping you would?

It should come from whatever query populates  l_COV_polNbr and l_COV_covEffDate.

I just needed something to JOIN to so I made up the source for it.

FYI:
If this hasn't already been presented to you, I apologize.

sdstuber wrote a very good article a while ago that I think would help you out:
https://www.experts-exchange.com/articles/9869/How-to-Unravel-a-Tricky-Query.html

It sort of gets you thinking about focusing on the immediate needs while keeping a bigger picture in mind.

I think that might help you with this project.

It appears the code you have now was written by individually solving a lot of small problems and wrapping all of those individual solutions up into a bundle.

I need X.  *writes code to get X*
I need Y.  *writes code to get Y*
...
I need to combine X and Y with some Z mixed in...  *writes code*

Nothing wrong with that.  We've ALL done it and I tend to still do it from time to time!

Now you have time to rethink all of this:
I know that eventually I'll need to combine, X, Y and Z.  Is there a way I can do all of this at once?

>>So, for POLNBR=1, I get the address from the ENDORSE logic.  For POLNBR=2, it goes into the LOCATION logic.

So then couldn't I just use the l_COV_polNbr and l_COV_covEffDate from the Select that is Inserting into my Dest table? Remember that I am doing an Insert and using a Select to populate my Insert table so the column names from my Select would be the variables for the l_PolNBR and l_CovEffDt right?

Something like this? (SHORT VERSION)
INSERT INTO TEMP_BASE_PCOV_POLICY
POLICY_NUM,
POLICY_EFF_YR , POLICY_EFF_MO , POLICY_EFF_DA,
INSURED_MAIL_ADDR_STREET, INSURED_MAIL_ADDR_CITY, INSURED_MAIL_ADDR_STATE, INSURED_MAIL_ADDR_ZIP5)
SELECT POLICYNUM,
 njcrib_util.GET_DATE_PART('y', c.COV_EFFDT),  njcrib_util.GET_DATE_PART('m', c.COV_EFFDT), njcrib_util.GET_DATE_PART('d', c.COV_EFFDT),
select polnbr, address_to_use
from (
      select polnbr, address_to_use, row_number() over(partition by polnbr order by addr_type desc) add_rn
      from (      
    select p.polnbr, nvl(addr_type,99999) addr_type, nvl(address_of_client ,addr_formatted || ' ' || street || ' ' || city || ' ' || state_cd || ' ' || zip) address_to_use
    from
      ENDORSE p
        left outer join ENDORSE_WC290303 E on p.endorse_id=E.endorse_id
                        left outer join INSURED_LOCATION L on p.polnbr=l.polnbr
        where p.polnbr IN('WC080033307')
      )
)
where add_rn=1

I would just have to figure out how to get the values(Address, City, State, etc... into the right place but your code tells me the right one to choose? Or would I make this a function and call it from a Case statement and have it return me which one to use and in the CASE statement it will set the values correctly? I like that this tells me which one in the background

Im just trying to think Ho wI can incorporate your logic into my Insert statement?

>>So then couldn't I just use the l_COV_polNbr and l_COV_covEffDate from the Select that is Inserting into my Dest table?

However you get the values, that is what you use.

Remember, I'm trying to get you out of multiple selects...  Just ONE massive insert into select that does everything at once.

If you want to continue with baby steps, then use l_COV_polNbr and l_COV_covEffDate just like you do now but remove ALL the address logic and replace it with this one select.

>>I would just have to figure out how to get the values(Address, City, State, etc... into the right place

That I'm afraid, I cannot help you with.  I can help you get the values.  How and what you need to do with them is up to you.

>>Or would I make this a function and call it from a Case statement

I'm also trying to get you out of the code writing game.

If you can do it with SQL, don't do it with code!  SO, No function.




Not to get you all side-tracked, but have you at least made your staging table in the current code, NOLOGGING?  If not, try it and see what happens to the two hour run time.

and I will definitely read stubers article

ok I will do that now

>>Im just trying to think Ho wI can incorporate your logic into my Insert statement?

Really can't help here either.

Yes, it is going to be a REALLY large select with MANY joins and likely some complex CASE logic but I'm still betting it is doable!

I haven't seen anything in the code I've browsed to convince me it isn't.

If the view idea from Mark makes sense to you, then make the address select a view.

Just add all the necessary columns so you can easily join it to what you already have.

ran this
ALTER TABLE TEMP_BASE_PCOV_POLICY_STAGE NOLOGGING;

>>ran this

That should help some.  Who knows, maybe a lot...

I was trying to help you get the address values by using a view or two, so in your insert statement, you could just use a simple select from the top-level address view (and keep all of the complex address determination logic out of this insert statement).

Can I pass parameters to the View?

I was trying to create the view and didn't see where I could pass the parameter. I was going to try and use a function but then I saw Slights code and was going to go that route but the View, with parameters would be great

>>Can I pass parameters to the View?

No, and there is no need to.

The view is just my select with the additional columns needed to perform the necessary JOINs to the other tables.

Oracle views do not accept parameters.  That's why slightwv added the suggestion to include: "all the necessary columns so you can easily join it to what you already have".  That may be as simple as one column, probably the column that matches the ENDORSE_ID value.

ok Im sure you guys are a little tired of me today but if I can get this logic down Im good to go...

I wrote the first View for Type 2 and took the first one descending by Date
Not actual code.....
CREATE OR REPLACE vw_ADDTYPE2
Select POLNBR, ADDR_TYPE,ADDR_FORMATTED,STREET,CITY,STATE_CD,ZIP from(
Select ADDR_TYPE, ADDR_FORMATTED, Street, City, State_CD, ZIP  
from INSURED_LOCATION
ORDER BY COV_EFFDT DESC) INSURED_LOCATION
WHERE ROWNUM = 1 AND ADDR_TYPE = 2

Is this correct??

Also for the next View, you want me to NVL every field and outer join it to this view?

Select NVL(ADDR_TYPE, 0), NVL(ADDR_FORMATTED,0), NVL(STREET,0), NVL(CITY, 0), NVL(STATE_CD,0),     NVL(ZIP, 0)
from vwADDTYPE2 v
OUTER JOIN INSURED_LOCATION i ON v.POLNBR = i.POLNBR
WHERE ADDR_TYPE = 1
ORDER BY COV_EFFDT DESC

How far off am I??

>>I wrote the first View for Type 2 and took the first one descending by Date

Writing a view for a single table query really doesn't buy you anything.

It was my intention to make the entire address selection logic a single view.

Take what I provided, make it ALL one view.

Why make TWO views to get ONE address?

That said:
I might move ADDR_TYPE = 2 to the inline view portion.

Personally, I like ROW_NUMBER() over the ROWNUM trick with the inline view.

>>Also for the next View, you want me to NVL every field and outer join it to this view?

I don't think you need this.  Notice, I didn't NVL any of the address data.  I just needed a value for ADDR_TYPE when the address came from the ENDORSE tables.

Here is what I have so far and where Im kinda stuck at the bottom...I need a break and will look at it when I get home again...If your interested in taking a look please feel free and let me know where Im screwing up...
Thanks again for all your help, its been very helpful!

Insert Into TEMP_BASE_PCOV_POLICY
(POLICY_KEY, POLICY_NUM,      BUREAU_FILE_NUM, POLICY_EFF_YR , POLICY_EFF_MO , POLICY_EFF_DA,      POLICY_EXP_YR,      POLICY_EXP_MO ,      POLICY_EXP_DA,      
ISSUE_OFFICE_NAME, ISSUE_OFFICE_ADDR_STREET      , ISSUE_OFFICE_ADDR_CITY, ISSUE_OFFICE_ADDR_STATE, ISSUE_OFFICE_ADDR_ZIP5 , ISSUE_OFFICE_ADDR_ZIP4,
POLICY_TRANS_IND,      POLICY_WRAP_UP_IND,
INSURED_MAIL_NAME, INSURED_MAIL_ADDR_STREET, INSURED_MAIL_ADDR_CITY, INSURED_MAIL_ADDR_STATE, INSURED_MAIL_ADDR_ZIP5, INSURED_MAIL_ADDR_ZIP4,
POLICY_EFF_DT, POLICY_EXP_DT, POLICY_CVG_ID, POLICY_CARR_ID, ADDR_TYPE_USED)
Values
(
SELECT
--Builds the Key and uses the COV_EFFDT, POLNBR and CARRID
njcrib_util.Build_Table_Key(njcrib_util.GET_DATE_PART('y', c.COV_EFFDT), njcrib_util.GET_DATE_PART('m', c.COV_EFFDT), njcrib_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)),
--POLICY_NUM          
RPAD(c.POLNBR, 18, ' '),
--BUREAU_FILE_NUM
c.CVG_ID,  
--POLICY_EFF_YR                                                            
njcrib_util.GET_DATE_PART('y', c.COV_EFFDT),
--POLICY_EFF_MO
njcrib_util.GET_DATE_PART('m', c.COV_EFFDT),
--POLICY_EFF_DA
njcrib_util.GET_DATE_PART('d', c.COV_EFFDT),
--POLICY_EXP_YR                                                          
njcrib_util.GET_DATE_PART('y', c.COV_EXPDT),
--POLICY_EXP_MO
njcrib_util.GET_DATE_PART('m', c.COV_EXPDT),
--POLICY_EXP_DA
njcrib_util.GET_DATE_PART('d', c.COV_EXPDT),
--ISSUE_OFFICE_NAME                                                
rpad(SUBSTR(njcrib_util.CLEAN_NON_ASCII(car.NAME, 1, 50),50,' '),
--ISSUE_OFFICE_ADDR_STREET
rpad(SUBSTR(njcrib_util.CLEAN_NON_ASCII(c.CARR_ISSUING_STREET, 1, 50),50,' '),
--CARR_ISSUING_CITY                                                          
rpad(SUBSTR(njcrib_util.CLEAN_NON_ASCII(c.CARR_ISSUING_CITY, 1, 50),50,' '),  
--CARR_ISSUING_CITY
rpad(SUBSTR(njcrib_util.CLEAN_NON_ASCII(c.CARR_ISSUING_STATE_CD, 1, 2),2,' '),
--CARR_ISSUING_ZIP
CASE
WHEN c.CARR_ISSUING_ZIP > 4 THEN SUBSTR(c.CARR_ISSUING_ZIP, 1, 5)
ELSE '00000'
END,
--ZIP4
CASE
WHEN c.CARR_ISSUING_ZIP > 8 THEN SUBSTR(c.CARR_ISSUING_ZIP, 6, 4)
ELSE '0000'
END,
--TRANSID
(Select njcrib_util.GET_TRANS_ID(c.CVG_ID, c.CARRID, to_date('01-JAN-' || extract(YEAR From (Add_Months(c.COV_EFFDT,-12))), 'dd-MON-yy'), c.TXN_CODE) FROM DUAL) TRANSID,
--WRAP_UP_IND
CASE
WHEN c.WRAP_UP_IND IS NULL THEN RPAD(' ', 1, ' ')
ELSE 'X'
END,
--INSURED_MAIL_NAME
(SELECT njcrib_util.GET_INSURED_NAME(c.CVG_ID, c.POLNBR, c.COV_EFFDT FROM DUAL) INSURED_MAIL_NAME,

-- ISSUE_OFFICE_ADDR_STREET      , ISSUE_OFFICE_ADDR_CITY, ISSUE_OFFICE_ADDR_STATE, ISSUE_OFFICE_ADDR_ZIP5 , ISSUE_OFFICE_ADDR_ZIP4,
--Set the address...
--Check Endorse first then INSURED_LOCATION
--Type 2 = Preferred and if more than 1 take the last address
--Type 1 = when there are no Type 2
CASE
WHEN Check_Endorse_Address(c.CARRID, c.POLNBR, c.COV_EFFDT) THEN
WHEN vw_ADDRESS_TYPE2 THEN
WHEN vw_ADDRESS_TYPE1 THEN
END,

"Writing a view for a single table query really doesn't buy you anything."
That depends.

In my opinion, a view can help here, because this one table can have multiple records, and we just want at most one row returned.  I wouldn't use ROWNUM, since that cannot be combined with an "order by" at the same level.  I would use something more like this in the "where" clause of that first view:
where [date_created] = (select max([date_created]) from INSURED_LOCATION tab2
  where tab2.[key_value] = tab1.[key_value])

And, using a view here allows moving a bunch of complexity out of the insert statement (making that easier to develop and manage).

I would expect the second view to look more like this:

select nvl(v1.INSURED_MAIL_ADDR_STREET,e.ENDORSED_MAIL_ADDR_STREET), [other columns...]
from [view1] v1, ENDORSE e
where v1.[key_column] (+) = e.[key_column]

And, your query from the second view will also need an nvl around each address column for those people who have no addresses in either of your two address tables.  This second view will also have to be referenced as an outer to allow for those people with no addresses.

>>I would use something more like this in the "where" clause of that first view:

I disagree.  Why hit the table twice for no reason?

>> I wouldn't use ROWNUM, since that cannot be combined with an "order by" at the same level.

I agree however that doesn't apply to what was posted.

If it was an issue, I would use ROW_NUMBER() over a select MAX from the same table.

>>In my opinion, a view can help here, because this one table can have multiple records,

I agree to a certain extent.  I don't think the complexity of the logic the single table view masks will reduce the overall complexity of the larger query all that much.

Sure it hides a little of the logic, but very little...

Why not create one view for the one "good" address and be done with it?

I'm just curious what is wrong with the logic for the query I posted that would make the single address view pretty simple and why we are even talking about breaking up that logic into two views?

>>Here is what I have so far and where Im kinda stuck at the bottom...

I still cannot get past all the functions that can likely be simplified.  Remember the TO_DATE versus your functions?  That not work?


Do the functions like njcrib_util.GET_TRANS_ID and njcrib_util.GET_INSURED_NAME reference the same tables used in the main query?  If so, think about my post above... I need X, write code for X...

You are likely accessing the same tables more times than you need to.  Each new access takes time.

If tableX has 1000 rows, and you write a function that does a full table scan every time.  You then write a select that calls that function 20 different times, how many rows did you actually look at?

Back to the code:
Which path are you choosing?
One GET_ADDRESS_VIEW or the Two view approach?  You know what my choice would be...


Anyway, I think the issue is how you are trying to add the address select into the select you have.

It isn't really a new CASE statement with it's own select like the selects from dual you have (which you very likely do not need).

This:
--INSURED_MAIL_NAME
(SELECT njcrib_util.GET_INSURED_NAME(c.CVG_ID, c.POLNBR, c.COV_EFFDT FROM DUAL) INSURED_MAIL_NAME,

Should be exactly the same as:
--INSURED_MAIL_NAME
njcrib_util.GET_INSURED_NAME(c.CVG_ID, c.POLNBR, c.COV_EFFDT
INSURED_MAIL_NAME,

But that would depend on how you write the function...


You use the new views in the FROM clause of the select and add JOINS.  Then the columns you need from the views are just added to the column list in the select.

Over simplified, what you have is:
select curr.col1, curr.col2 curr.col3
from existing_tables_and_joins curr

What you will end up with after adding the address SQL is:
select curr.col1, curr.col2 curr.col3, address_vw.current_address
from existing_tables_and_joins curr
    join some_new_address_view address_vw on address_vw.some_column=curr.come_column and ...

I tried your view slight and it is bringing back the address I need so I put it in a view.  I am just needing to do my insert of the Address, City, State and Zip so I think I would need to alter the view to bring back these fields, right?
Endorse table actually sends back the address_of_client and then sets the address based off of the string(ie, Street is SUBSTR(address_of_client, 1,40), etc...) but the insure_location table brings back the fields individually.
Im not done and I have not tested this outside of the view but I just want to see if Im even going in the right direction. Prob not but hopefully close....

select polnbr, addr_type, address_of_client, street, city, state_cd, zip
from (
      select polnbr, addr_type, address_of_client, street, city, state_cd, zip, row_number() over(partition by polnbr order by addr_type desc) add_rn
      from (
--            select p.polnbr, nvl(addr_type,99999) addr_type, nvl(address_of_client, addr_formatted || ' ' || street || ' ' || city || ' ' || state_cd || ' ' || zip) address_to_use
      select p.polnbr, nvl(addr_type,99999) addr_type, address_of_client, street, city, state_cd, zip
      from
        ENDORSE p
          left outer join ENDORSE_WC290303 E on p.endorse_id=E.endorse_id
          left outer join INSURED_LOCATION L on p.polnbr=l.polnbr  
          where p.POLNBR = '10WBAG2411'
      )
)
where add_rn=1

Or should I do this in the Select portion?

All of this "select..." action has to happen in either a view definition or in the "select..." portion of your insert statement.  

Are you asking which of these two locations it should be in?

I'm always a bit skeptical of nested queries that look like this:
select ... from ...
  ( select ... from ...
   ( select ... from ...))

They can cause performance problems, depending on how many records the inner selects return.  Maybe that will not be a problem in your case.

>>Endorse table actually sends back the address_of_client and then sets the address based off of the string(ie, Street is SUBSTR(address_of_client, 1,40), etc...) but the insure_location table brings back the fields individually.

Everything depends on what you want/need.

If you need to address in multiple fields, then the view should split up the ENDORSE field into multiple fields to match what you need and the Location table should manipulate the individual fields into what you need.

If you need everything in a single column, keep the ENDORSE field as is and concatenate the LOCATION fields in the view.

I think you are really overthinking this.

Look at what you need as a final result.
Look at what you need individually from each table to achieve that result.
Then combine those individual queries into the single query like I provided.
Add a CREATE VIEW in front of it.
Done.

>>I'm always a bit skeptical of nested queries that look like this:

I'm skeptical of nested views that takes me forever to debug performance issues with...

In older, now desupported, versions, I've also seen the Optimizer choose very poor execution plans when views were used.  I think it has gotten a lot better but I don't do a lot with views these days.

I like this summary (with a couple minor additions/adjustments):

 Look at what you need as a final result.
 Look at what you need individually from each table to achieve that result.
 Then combine those individual queries into the single query like I provided  (if that is feasible, and works efficiently)
Add a CREATE VIEW in front of it.  (if that helps performance, and/or helps you get the "insert into ... select ... from ..." statement down to a manageable/maintainable size).

Im totally over thinking this and agree totally. Im not half as good as you guys so for me to understand what I should do with your code its proving a little difficult for me.
TO answer your one question, which will help me out greatly is that I need to split up the columsisntead of the Endorese which has all values in one field. I have the code that splits that up though.
So what Im going to do, if you agree, is something like thi, in the view...
select polnbr, address_to_use
from (
      select polnbr, address_to_use, row_number() over(partition by polnbr order by addr_type desc) add_rn
      from (
            select p.polnbr, nvl(addr_type,99999) addr_type, nvl(address_of_client, addr_formatted || ' ' || street || ' ' || city || ' ' || state_cd || ' ' || zip) address_to_use
            from
                  ENDORSE p
                        left outer join ENDORSE_WC290303 E on p.endorse_id=E.endorse_id
                        left outer join INSURED_LOCATION L on p.polnbr=l.polnbr    
        where p.POLNBR = '10WBAG2411'
      )
)
where add_rn=1

The results are in the pic attached. Now, from the Insert statement I can call the view and check the results, if there is a value is the Address_OF_Client  and if that is the one to be used then I can parse out and set the Address fields. If it is Null or whatever we return from the NVL, then I  use the individual fields to make up my address from Insured_Location.
AM I on the right path?

Something like this with just the address logic

Insert Into TEMP_BASE_PCOV_POLICY
(
INSURED_MAIL_NAME, INSURED_MAIL_ADDR_STREET, INSURED_MAIL_ADDR_CITY, INSURED_MAIL_ADDR_STATE, INSURED_MAIL_ADDR_ZIP5, INSURED_MAIL_ADDR_ZIP4,
POLICY_EFF_DT, POLICY_EXP_DT, POLICY_CVG_ID, POLICY_CARR_ID, ADDR_TYPE_USED)
Select vw_Address.
AddressView.jpg

"..nested views ... performance issues..."  Yes, that is a possibility.  But, I'm always on the lookout for things that impact performance.  Oracle's query optimizer is pretty good in Oracle11, whether views are involved or not.

This particular case looks like a judgment call to me.  Is it best to try to put all of the logic into one complex statement?  Or, is it best to break it up into easier-to-troubleshoot portions by using a view (or two or three)?  I think either approach could work here.  What is best for getting something to work efficiently now, and be maintainable if/when business requirements change someday?  That is a decision for jknj72.

In my opinion, using a view (or two or three) here can help break up this complex business/data problem into easy-to-understand pieces.

>>So what Im going to do, if you agree, is something like thi, in the view...

If you need it split up, split it up in the view.

From the image you posted:
Only return the columns you need.

You shouldn't return address_of_client from endorse and all individual fields from location.

If you need the city column, parse it from endorse, take city from location, figure out which one to return, then return it.

>>In my opinion, using a view (or two or three) here can help break up this complex business/data problem into easy-to-understand pieces.

I agree.

Sorry I accidentally sent this before I finished the last part...Finishing it up now and will send in a minute

ok I was thinking that too. Let me make the changes and show you my outcome

>>So what Im going to do, if you agree, is something like thi, in the view...

Look back at the example I posted above: http:#a40837307

I return ONE and only ONE address.

In my example I chose to concatenate the LOCATION values to match the ENDORSE version.

Just reverse it:
Parse address_of_client to individual fields and return individual fields.

Well I would need all the fields from Endorse, if that has an address. Not one field from endrose and another from location. Its either all fields from one or all fields from other.

What "all fields"?

In the original post you only select one:
Select ENDORSE_WC290303.ADDRESS_OF_CLIENT
      FROM ENDORSE

yes if it uses the endorse table it only has the one field address_of_client.....If it doesn't use Endorse, it uses Insured_Location table where all the fields are separate columns. That's what Im trying to overcome now... So that's why I was returning the address_of_client to see if it had the value but I should be doing that in the view...And return the fields I need depending on what is being returned from the view. If Endorse, parse out the values and if not just return the fields from location...Is that correct?

Yes, that is correct: parse out the values from the Endorse table where they are held in a single field, but simply return the individual fields from the insured_location table.

yeah but in my initial post I had functions for Endorse and then a function for Insured_Location depending on what returned True? That was my initial approach, write a function for both and whichever one returned Tru then use the values from that table? Sorry if I confused you

trying mark...haha...what a nightmare and its so easy for you guys but im totally not on your levels, so Im trying ...And some days I come in, like this morning, and initially Im lost but its coming back to me and will stay here as long as I have to to get it to work...
Thanks to both of you guys for hanging in with me!!

was just debugging code that I had just written and it is very similar to what you sent(glad I was in the same ballpark). I will see where Im messing up and let you know how it goes.
Thanks

I was just looking over all this and Im trying to create a test environment from home. I think the view is going to work for me. Quick question, I want to see if replacing the myPOLLIST with my Endorse table and doing an Inner Join with the Endorse_WC290303 table on the endorse_id (which is the same as the first check in the old code) and then Left Join to the Insured_Location table on the polnbr, which should give me values like the second query in the original cod. Am I correct with this? I am setting up some test cases and wanna see if everything is working from my home test environment and hopefully I can plug it in when I get in tomorrow morning...That would be awesome...

So it would look something like this...

select polnbr, street, city, state_cd, zip
from (
      select polnbr, street, city, state_cd, zip, row_number() over(partition by polnbr order by addr_type desc) add_rn
      from (
            select p.polnbr, nvl(addr_type,99999) addr_type,
                  case when address_of_client is null then street else substr(address_of_client,1,40) end street,
                  case when address_of_client is null then city else substr(address_of_client,41,6) end city,
                  case when address_of_client is null then state_cd else substr(address_of_client,47,2) end state_cd,
                  case when address_of_client is null then zip else substr(address_of_client,49,5) end zip
            from
                  Endorse p
                        INNER JOIN ENDORSE_WC290303 E on p.endorse_id = e.endorse_id --First address check
                        LEFT OUTER JOIN INSURED_LOCATION L on p.polnbr=l.polnbr  --Second address check
      )
)
where add_rn=1

And fyi, Slight I know you were just using MyPollist as a join table but just wanted to know if I can replace it with my Endorse table

I don't think the endorse table is the correct table.  Yes, you need to join to that to get the correct address just like your original query but that isn't the reason for myPOLLIST.

myPOLLIST is the table you need to join with that populates  the variables:
l_COV_pCARRID
l_COV_polNbr
l_COV_covEffDate
l_CONST_ENDORSE_CD_NBR

I believe you need the correct address based on all those?

I just needed something that returns multiple addresses based on some outer query.

You have some query outer loop that based on those values, you return multiple rows from some address lookup.

That is what I tried to emulate:
myPOLLIST returns two rows (the OUTER query in your main loop).  Based on each of the individual rows, I return the 'correct' address.

ok just so I am on the same page, here is my Insert...The main queries table is the Coverage table and I would have to join via PolNbr (which you had in the MyPollist join)
Here is the orig query, with fields taken out to make more readable
SELECT
c.POLNBR
,v.polnbr, v.street, v.city, v.state_cd, v.zip
from COVERAGE c
INNER JOIN CARR car ON c.CARRID = car.CARRID
LEFT JOIN vw_Address v on c.polnbr = v.polnbr
WHERE c.COV_EXPDT = '22-JUN-09'  AND (c.STATUS != 'V')

and here is my view with the address logic in it...
--VW_ADDRESS
select polnbr, street, city, state_cd, zip
from (
      select polnbr, street, city, state_cd, zip, row_number() over(partition by polnbr order by addr_type desc) add_rn
      from (
            select p.polnbr, nvl(addr_type,99999) addr_type,
                  case when address_of_client is null then street else substr(address_of_client,1,40) end street,
                  case when address_of_client is null then city else substr(address_of_client,41,20) end city,
                  case when address_of_client is null then state_cd else 'NJ' end state_cd,
                  case when address_of_client is null then zip else '00000' end zip
      from
                  ENDORSE p
                        inner join ENDORSE_WC290303 E on p.endorse_id=E.endorse_id
                        left outer join Insured_LOCATION L on p.polnbr=l.polnbr
      )
)
where add_rn=1

How far off am I?

Thanks slight!!

It looks like what I was thinking was possible.

Big question:
Does it return the correct data?

slightly related question:
Did setting nologging on the staging table help any?

For "nologging" to improve response times for a process like this, any/all inserts into the "nologging" table *MUST* include the "/*+APPEND */" hint, like this:
insert /*+APPEND */ into [table_name]
select ...

I have 3 different variations of this process. The first one loads Source data into a Type and loops through the data and eventually does the Insert. The 2nd one sets the source data to a Staging table(with no logging) and loops through the Staging table and does the Insert. This 3rd option I am trying to do is an Insert from Source to Dest with the logic and everything included. So this version doesn't use the Staging table so I don't have to worry about the No Logging.
Slight, I am working on getting the data validated so I will let you know.

It looks like I am doing something wrong. I have the Source query doing a Left Join to my view and Im thinking in the view I created Im doing something wrong because it is not returning an address. It will always return something, either from Endorse(Inner joined to Endorse_WC290303 table) or the Insured_Location table which I have as a Left Join to the Endorse table(I think this is where I am causing my issue).
Then in the source query doing a Left join to the view from the Coverage table. Is it possible that the INNER join from Endorse to the Endorse_WC table in the view is maybe causing it to not return the record?

I cannot tell you if your joins are right or not.  I don't know your data.

select from the view itself for the polnbr in question.  If the correct row is returned, then the join issue is in the larger query.

If no row is returned, then jump into the view itself to see what is wrong.

Ive been moved to a different project and going to try and get this into my procedure on my free time. I am going to be using my first version of the procedure for now. It took the least amount of time  and gave me what I needed. I will be using Slights query for this question, so Im giving him the points. Thanks guys for all your help . Very much appreciated...

I wish you guys were .NET gurus as well cause that's my next task ;)
Thanks again!!

PS - I am doing some data validation on the production data versus my run and I was curious if you guys had any tips. Ill post as a question if I run into an issue, Ive been using EXCEPT and INTERSECTS (Ends up in SQL Server db)  to get data that's in one and not the other and vice versa.

Thanks for the he;p

>> wish you guys were .NET gurus as well cause that's my next task

Be careful what you wish for... did you check my profile?

Oddly enough, on this site, I am a "Guru" in .Net but do not consider myself one in the real world...  I do know more than enough to be dangerous...

>>data validation on the production data

INTERSECT, UNION and MINUS are typically my tools of choice.  They are typically quick and dirty...

Yeah Im in the same ball park. I know enough to be dangerous ;)
I had no idea your a .Net guy....
We are doing an MVC project in VB.NET so I'll be posting questions if your interested.
Hopefully talk soon!