[firebird-support] UNION prevents all records from being pulled to the results
Greetings All, The SQL that follows pulls these results: (These results are missing 5 records from the second select of the union) FEE_CODEAMT_EARNED_AGENCYSHOW_IN_PMT_DIST_PLANFEE_CATEGORY CCO27610 SVC1.1710 SVC3010 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_CODEAMT_EARNED_AGENCYSHOW_IN_PMT_DIST_PLANFEE_CATEGORY CCO27610 If I execute the second select in the union it pulls these results FEE_CODEAMT_EARNED_AGENCYSHOW_IN_PMT_DIST_PLANFEE_CATEGORY SVC3010 SVC3010 SVC3010 SVC3010 SVC3010 SVC1.1710 SVC3010 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
Re: [firebird-support] UNION prevents all records from being pulled to the results
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_CODEAMT_EARNED_AGENCYSHOW_IN_PMT_DIST_PLANFEE_CATEGORY CCO27610 SVC1.1710 SVC3010 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_CODEAMT_EARNED_AGENCYSHOW_IN_PMT_DIST_PLANFEE_CATEGORY CCO27610 If I execute the second select in the union it pulls these results FEE_CODEAMT_EARNED_AGENCYSHOW_IN_PMT_DIST_PLANFEE_CATEGORY SVC3010 SVC3010 SVC3010 SVC3010 SVC3010 SVC1.1710 SVC3010 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
