If I understood this correctly, the only way I can see to do it is by using two separate queries and union.

Use the first query to get all the plans for the member. Use the second query to get the clm_amt for each member. Use union to join them in one cursor.

At this point, I say the sql is as complicated as doing it via scan, and for purposes of maintainability I would go with a scan/endscan.


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