Query to return only 1 row 1

[conrads57]

I have a query that will pull a member's line of business desc from their current eligibility sequence. In most cases the members have only 1 current sequence, however we have a subset of the population that will have 2 current sequences. In this case when the member is chosen, if they have 2 sequences

For example here is my query.

SELECT LOB_DESC,MPG_UID
FROM MPG_MEM_PLAN_GROUP,
OPP_ORG_PAYER_PLAN,
LOB_LINE_OF_BUSINESS
WHERE MPG_OPP_ID = OPP_ID
AND OPP_LOB_CODE = LOB_CODE
AND MPG_MEM_UID = 1046133

This query will return 2 rows

LOB Desc MPG UID
Medi-cal 4589195
Medicare 4584694

I need to pull the LOB DESC showing Medicare, and if a member with 1 sequence is chosen then show whatever the current LOB DESC is. I am using Oracle 10g.

How can I limit my query to just 1 row no matter what member is chosen?

 

[Turkbear]

Hi,
How are you going to determine which record you want?
In your example, you state you want the one with Medicare as the LOB Desc ; without specifying that in the where clause, how will Oracle 'know' you want it and not the other record.
One way to get a single record would be to use:

SELECT MAX(LOB_DESC),MPG_UID
FROM     MPG_MEM_PLAN_GROUP,
               OPP_ORG_PAYER_PLAN,
               LOB_LINE_OF_BUSINESS
WHERE  MPG_OPP_ID = OPP_ID
               AND OPP_LOB_CODE = LOB_CODE
               AND MPG_MEM_UID = 1046133 ;

BUT this will not return, necessarily, the one you want.






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[jimirvine]

The above code will actually return an error I assume though, that you just missed out your group by clause by mistake.

 

[conrads57]

I want it to always pick Medicare when that line exists or if only 1 line exists display just that line. So for instance

Member # 1 has on Medi-cal
but
Member # 2 has both Medicare and Medi-cal.

In this instance if I was choosing member#1 then Medi-cal would be displayed.

If I choose Member # 2 then Medicare should display. Does this help?

 

[SantaMufasa]

Conrads57,

How many DISTINCT LOB_DESC values exist?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[taupirho]

Well, assuming there are only 2 distinct LOB_DESC i.e Medi-cal and Medicare, the following analytic query should do it for you

select * from
(
SELECT LOB_DESC,MPG_UID,
count(*) over(partition by mpg_mem_uid order by lob_desc desc) cnt
FROM MPG_MEM_PLAN_GROUP,
OPP_ORG_PAYER_PLAN,
LOB_LINE_OF_BUSINESS
WHERE MPG_OPP_ID = OPP_ID
AND OPP_LOB_CODE = LOB_CODE
)
where cnt = 1


In order to understand recursion, you must first understand recursion.

 

[conrads57]

Thanks to taupirho, I took your query and revised it somewhat and was able to come up with what I needed. Here it is:

select lob_desc from
(
SELECT LOB_DESC,
row_number( ) over (partition by mpg_mem_uid order by lob_desc desc) rn
FROM MPG_MEM_PLAN_GROUP,
OPP_ORG_PAYER_PLAN,
LOB_LINE_OF_BUSINESS
WHERE MPG_OPP_ID = OPP_ID(+)
AND OPP_LOB_CODE = LOB_CODE(+)
AND trunc(SYSDATE) BETWEEN MPG_DATE_BEGIN AND MPG_DATE_END
AND MPG_MEM_UID = :VL_MEM_UID
)
where rn = 1