Qeury question PL/SQL

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....

        SELECT *
        --Bulk Collect Into l_POLICY_TAB
        FROM (
        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;

THANKS!!!
jknj72Asked:
Who is Participating?
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.

slightwv (䄆 Netminder) Commented:
Make the table an inline view.

Instead of:
INNER JOIN INSURED_ENTITY

do:
INNER JOIN (select * from INSURED_ENTITY where ... and rownum=1)

That said:
COV_EXPDT > '20-MAY-09'

Don't use implicit data type conversions.  They will bite you someday.

Use:
COV_EXPDT > to_date('20-MAY-09' ,'DD-MON-YY')
0
jknj72Author Commented:
yeah that date bombed for me so I will change...Thanks
let me try this logic..
0
jknj72Author Commented:
How would my Where clause look? Should it be the same as my join?

c.CVG_ID = i.CVG_ID
AND c.PolNBR = i.PolNBR
AND c.COV_EFFDT = i.COV_EFFDT
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) Commented:
Should have thought it through a little more...  You would need to reference outer columns in the where clause of t he inline view.  You can't do that...

Give me a few to rethink this or another Expert can correct my blunder...
0
jknj72Author Commented:
No worries Thanks
0
flow01Commented:
you could use an aggregate function like rownum or rank

select ...
from
(select yourcolumns,
 rownum() over (partition by  unique_identifier_of_i)  rn  --  unique_identifier_of_i =  i.CVG_ID, i.PolNBR,  i.COV_EFFDT ?
  from  yourinnerjoins
)
where rn = 1
0
slightwv (䄆 Netminder) Commented:
Sorry about the delay.  I got caught up at work.

I set up a simple test case that I hope mirrors what you are wanting to do.

If not, please add to it and explain where it doesn't work.

It generates the row_number based on the repeating rows based on whatever criteria you choose then uses the first row from that in the join.

drop table tab1 purge;
create table tab1(col1 number, col2 number);
insert into tab1 values(1,1);
insert into tab1 values(2,2);

drop table tab2 purge;
create table tab2(col1 number, col2 number, col3 char(1));
insert into tab2 values(1,1,'a');
insert into tab2 values(1,1,'a');
insert into tab2 values(2,2,'b');
commit;


select t2_outer.col3 from tab1 t1
	join
	(
		select t2.*, row_number() over(partition by col1 order by col1,col2) rn from tab2 t2
	) t2_outer
	on t2_outer.col1=t1.col1 and t2_outer.col2=t1.col2 and t2_outer.rn=1
/

Open in new window

0
jknj72Author Commented:
ok guys I think your on to something here. I will try this first thing in the morning. I was thinking before I checked your responses that if I created a view that brought back my INSURED_NAME from the INSURED_ENTITY table bringing back the 1st value of INSURED_NAME and then join to the view and add that column in the select, wouldn't that work?

Thanks to you both and will let you know.

JK
0
jknj72Author Commented:
Im not sure how to implement what you guys are saying...

Heres my query without the INSURED_ENTITY join...How do I implement what your trying to have me do?

        SELECT *
        --Bulk Collect Into l_POLICY_TAB
        FROM (
        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 > to_date('22-MAY-09' ,'DD-MON-YY')  AND (STATUS != 'V')  ORDER BY CARRID ASC, COV_EFFDT ASC) COVERAGE
        WHERE ROWNUM <= 1000
        ORDER BY ROWNUM;
0
slightwv (䄆 Netminder) Commented:
>>How do I implement what your trying to have me do?

I don't know.  I don't understand your requirements nor do I have your tables, data and expected results.

Where you want to join with INSURED_ENTITY in your system Use this:
(
            select t2.*, row_number() over(partition by col1 order by col1,col2) rn from INSURED_ENTITY t2
      ) t2_outer

Just change the partition by and order by to the columns that you need to return the row you want as #1.

Then the join should be whatever columns you need with the addition of:
and t2_outer.rn=1


Once you understand ROW_NUMBER() and what it is doing, the rest should fall into place.

Experiment with a simple query against INSURED_ENTITY using ROW_NUMBER to see what it is doing.  Once you get RN=1 for the row you want to return, place it in the join.
0
slightwv (䄆 Netminder) Commented:
I should explain mine:
I have table tab1 that holds the 'key' fields that drive everything.
I have table tab2 that should simulate your  INSURED_ENTITY.  the 'key' fields repeat.  I need to join the two BUT only have a single row from tab2 returned when the key values repeat.

That is what the ROW_NUMBER call does:  col1 and col2 are the 'key' columns but I want ONLY ONE col3 returned in the joined query.  I partition by col1 and col2 and then generate a unique row number.  In the join I only pick the ones with a RN=1 (the first).
0

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:
I just posted a different question for the same issue because I am trying another way to get this done. I am having trouble getting this to work so I tried another way.
0
jknj72Author Commented:
You actually answered another question that made this question unnecessary so the points are yours. Thanks again
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.