Em 29/10/2012 18:39, SoftTech escreveu:

> Greetings All,
>
> The SQL that follows pulls these results: (These results are missing 5
> records from the second select of the union)
> FEE_CODE AMT_EARNED_AGENCY SHOW_IN_PMT_DIST_PLAN FEE_CATEGORY
> CCO2 76 1 0
> SVC 1.17 1 0
> SVC 30 1 0
>
> This is the SQL statement:
> SELECT FC.FEE_CODE,
> COALESCE(PCF.CASE_FEE_AMT_EARNED_AGENCY_1 +
> PCF.CASE_FEE_AMT_EARNED_AGENCY_2, 0) AS AMT_EARNED_AGENCY,
> FC.SHOW_IN_PMT_DIST_PLAN,
> FC.FEE_CATEGORY
> FROM PMT_CASE_FEE PCF
> JOIN PMT_CASE PC ON PC.ACCT_ID = PCF.ACCT_ID
> AND PC.CASE_ID = PCF.CASE_ID
> AND PC.PMT_ID = PCF.PMT_ID
> AND PC.PMT_NO = PCF.PMT_NO
> JOIN CASE_FEE_HISTORY CFH ON CFH.CASE_FEE_ID =
> PCF.CASE_FEE_ID
> AND CFH.PMT_ID = PCF.PMT_ID
> JOIN CASE_FEE CF ON CF.CASE_FEE_ID = PCF.CASE_FEE_ID
> JOIN FEE_CODE FC ON FC.FEE_ID = CF.FEE_ID
> WHERE PCF.PMT_ID = :iPmtID
> AND PCF.PMT_NO = :iPmtNo
> AND PCF.ACCT_ID = :iAcctID
> AND PCF.CASE_ID = :iCaseID
>
> UNION
>
> SELECT FC.FEE_CODE,
> COALESCE(PDF.DEBT_FEE_AMT_EARNED_AGENCY_1 +
> PDF.DEBT_FEE_AMT_EARNED_AGENCY_2, 0) AS AMT_EARNED_AGENCY,
> FC.SHOW_IN_PMT_DIST_PLAN,
> FC.FEE_CATEGORY
> FROM PMT_DEBT_FEE PDF
> JOIN PMT_DEBT PD ON PD.ACCT_ID = PDF.ACCT_ID
> AND PD.DEBT_NO = PDF.DEBT_NO
> AND PD.PMT_ID = PDF.PMT_ID
> AND PD.PMT_NO = PDF.PMT_NO
> JOIN DEBT_FEE_HISTORY DFH ON DFH.DEBT_FEE_ID =
> PDF.DEBT_FEE_ID
> AND DFH.PMT_ID = PDF.PMT_ID
> JOIN DEBT_FEE DF ON DF.DEBT_FEE_ID = PDF.DEBT_FEE_ID
> JOIN FEE_CODE FC ON FC.FEE_ID = DF.FEE_ID
> JOIN DEBT D ON D.ACCT_ID = PDF.ACCT_ID
> AND D.DEBT_NO = PDF.DEBT_NO
> WHERE PDF.PMT_ID = :iPmtID
> AND PDF.PMT_NO = :iPmtNo
> AND D.ACCT_ID = :iAcctID
> AND D.CASE_ID = :iCaseID
>
> If I execute just the first select in the union it pulls these results
> FEE_CODE AMT_EARNED_AGENCY SHOW_IN_PMT_DIST_PLAN FEE_CATEGORY
> CCO2 76 1 0
>
> If I execute the second select in the union it pulls these results
> FEE_CODE AMT_EARNED_AGENCY SHOW_IN_PMT_DIST_PLAN FEE_CATEGORY
> SVC 30 1 0
> SVC 30 1 0
> SVC 30 1 0
> SVC 30 1 0
> SVC 30 1 0
> SVC 1.17 1 0
> SVC 30 1 0
>
> Again, executing the select statements separately results in the correct
> records being returned, executing both selects with the union omits 5
> records.
>
> Using Firebird 1.5.3
>
> Any ideas?
>
> Thanks,
> Mike
>

Use UNION ALL to avoid removing duplicates