>Hi All,
>I need to create a cursor from a 1-to-Many join that only shows the $ amounts on the 1 side one time. I can do a scan...endscan on the cursor and zero out the second and following amounts, but... Isn't there an easier way?
>
>Consider:
>

>Claims Table
>Mem_Num  Clm_Id  Clm_Amt
>1        10      $50
>2        20      $25
>3        30      $15
>
>Eligibility Table
>Mem_Num  Pln_Id
>1        AETNA
>1        BCBS
>2        CIGNA
>
>Results from Left Outer Join on Mem_Num
>Mem_Num  Clm_Id  Clm_Amt  Pln_Id
>1        10      $50      AETNA
>1        10      $50      BCBS
>2        20      $25      CIGNA
>3        30      $15      N/A
>
>Required Results
>Mem_Num  Clm_Id  Clm_Amt  Pln_Id
>1        10      $50      AETNA
>1        10      <B><I>$0</B></I>       BCBS
>2        20      $25      CIGNA
>3        30      $15      N/A
>

>Am I dreaming? Any ideas welcome.

I would expect a multiple-insurance plan situation to be more complicated, but if the immediate goal is to identify the first record in each group in order not to zero out its claim amount, I can think of two ways to do it with SQL. You can try them both and see which is faster, and whether either is faster than SCAN (which I'm not promising). You may also have to adjust them a bit in case I got a detail wrong:

Way #1 - Select all the records in the eligibility table in the order you want:

SELECT * FROM elig INTO CURSOR eligord ORDER BY mem_num

If you really just want the first plan_id for each member to pay, I suppose your record number order reflects that. Now use RECNO() to number them all:

SELECT *, RECNO AS recnums FROM eligord INTO CURSOR elignumbers ORDER BY mem_num, recnums DESC

Notice the DESC. That will end up selecting the first plan from each group in this next query, which groups on mem_num and has no aggregate function for plan_id:

SELECT mem_num, plan_id FROM elignumbers INTO CURSOR firstplan GROUP BY mem_num

This cursor contains the first plan for each member. You can now LEFT OUTER JOIN your other stuff to this cursor and include, say, firstplan.plan_id which will be null in the resulting cursor for plans that aren't the first one. Then make clm_amt = IIF(ISNULL(firstplan.plan_id,0,claims.clm_amt)

Way #2:

SELECT mem_num, plan_id, SUM(1) AS plannum FROM elig INNER JOIN elig AS elig1 ON elig.mem_num = elig1.mem_num AND elig.plan_id >= elig1.plan_id INTO CURSOR planord GROUP BY 1,2

You might have to reverse the direction of the non-equi-join - I often get those backwards. These non-equi-join queries are slow, but with just this one you have already identified the first plan for each member. You can INNER JOIN this cursor with your other stuff and make clm_amt = IIF(firstplan.plannum>0,0,claims.clm_amt)

On both these queries I have some details to be worked out because my own test examples don't exactly match yours and because it's lunchtime. :)