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
