Im loading a TYPE from the query below. Everything is working fine but I wanted to add a Join to the INSURED_ENTITY table to bring back INSURED information like NAME, Address CITY, STATE, etc...
My question is that this logic is to Select from the INSURED_ENTITY table where the ROWNUM = 1 because it may contain more than 1 record. How could I add this to my existing query only bringing back the 1 INSURED_ENTITY record?
--This is what I want to add to the query and would like to add the ROWNUM= 1 logic to this portion of the query, if I can...
--INNER JOIN INSURED_ENTITY i on c.CVG_ID = i.CVG_ID
-- AND c.PolNBR = i.PolNBR
-- AND c.COV_EFFDT = i.COV_EFFDT
--PS - I am only bringing back the first 100 rows in this query so the ROWNUM logic below has nothing to --do with tha ROWNUM logic I am looking to put in place....
--Bulk Collect Into l_POLICY_TAB
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
from COVERAGE c
INNER JOIN CARR car ON c.CARRID = car.CARRID
WHERE COV_EXPDT > '20-MAY-09' AND (STATUS != 'V') ORDER BY CARRID ASC, COV_EFFDT ASC) COVERAGE
WHERE ROWNUM <= 1000
ORDER BY ROWNUM;