Trying to create an Insert statement from a few queries I have

This is all in SQL Developer....

I have a Source query below and I have this pseudo code to explain everything. Its kinda simple but I need help

 Select POLICY_KEY, POLICY_NUM, POLICY_EFF_DT, POLICY_EXP_DT, POLICY_CVG_ID, POLICY_CARR_ID, ISSUE_OFFICE_NAME, INSURED_MAIL_NAME, POLICY_LCF_IND
from TEMP_BASE_PCOV_POLICY p
ORDER BY POLICY_CARR_ID ASC, POLICY_EFF_DT ASC;

WHILE (I is not null) LOOP
    --Set some variables to run the next query
    Set l_Temp_POL_pCARRID =  POLICY_CARR_ID
    and l_Temp_POL_polNbr = POLICY_NUM
    and l_Temp_POL_covEffDate =  POLICY_EFF_DT
    and l_Temp_POL_polKey = POLICY_KEY

    --Run this query with the values from 1st query as parameters
    Select CRKEY, CARR_ID, POLNBR, COV_EFFDATE, SEQ, ACTION, ACTION_EFFDT                
    From TEMP_BASE_CANCREIN_TAB
    WHERE CARR_ID = l_Temp_POL_pCARRID AND POLNBR = TRIM(l_Temp_POL_polNbr) AND COV_EFFDATE =     l_Temp_POL_covEffDate;

     I then loop through this query and set a new variable from query 2
    WHILE LOOP(j is not null) LOOP
       
         l_inCovEFFDATE :=COV_EFFDATE;
   
           --Then check CovEffDt variable from first query and the CovEffDt  variable set from 2nd query  and do an Insert based off that                
        if l_Temp_POL_covEffDate = l_inCovEFFDATE then                        
       INSERT INTO TEMP_BASE_PCOV_CANCREIN(CR_ID, CR_KEY, CR_TYPE, CR_YR, CR_MO, CR_DA)
      Values
       (j, l_Temp_POL_polKey, l_CANCREIN_Temp(j).ACTION, GET_DATE_PART('y', l_CANCREIN_Temp(j).ACTION_EFFDT),     GET_DATE_PART('m', l_CANCREIN_Temp(j).ACTION_EFFDT), GET_DATE_PART('d', l_CANCREIN_Temp(j).ACTION_EFFDT));
COMMIT;  
      LOOP j          
         end if;            

LOOP i      

Just so you know I want to define the fields Im inserting into
I need to store an id that I use j for so don't worry about that. The next value is from the
CR_ID = j from the second loop
CR_KEY =  POLICY_KEY from the first query(which is a variable in the Insert above that I declared above
CR_TYPE = ACTION field from the 2nd query
CR_YR = ACTION_EFFDT(I parse the YEAR) from 2nd query) 4 chars
CR_MO = ACTION_EFFDT(I parse the MONTH) from 2nd query) 2 chars
CR_DA = ACTION_EFFDT(I parse the DA) from 2nd query) 2 chars

Id love to do this in 1 query with all the logic still in place. Any help would be appreciated!!

THANKS!!!
jknj72Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jknj72Author Commented:
I was thinking something like this

Insert based off of this query???

Select POLICY_KEY, POLICY_NUM      , POLICY_EFF_DT , POLICY_EXP_DT , POLICY_CVG_ID , POLICY_CARR_ID
from TEMP_BASE_PCOV_POLICY p
INNER JOIN TEMP_BASE_CANCREIN_TAB c ON p.POLICY_CARR_ID = c.CARR_ID
AND p.POLICY_NUM = c.POLNBR AND p.POLICY_EFF_DT = c.COV_EFFDATE
ORDER BY POLICY_CARR_ID ASC, POLICY_EFF_DT ASC;
Mark GeerlingsDatabase AdministratorCommented:
I think your comment  is closer to what you need than your question is.  The question (which doesn't actually describe the problem you are trying to solve) includes too much complexity: PL\SQL cursors and loops, etc., when what I think you actually need is a simple insert based on a select that joins the two tables, something like this:

insert into TEMP_BASE_PCOV_CANCREIN(CR_ID, CR_KEY, CR_TYPE, CR_YR, CR_MO, CR_DA)
select [whatever columns and/or derived values you need]
from TEMP_BASE_PCOV_POLICY p
 INNER JOIN TEMP_BASE_CANCREIN_TAB c ...

Usually, an insert based on a sub-query like this does *NOT* include an "order by" clause in the sub-query.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jknj72Author Commented:
yeah I think Ive got it...Thanks
jknj72Author Commented:
thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.