--- In firebird-support@yahoogroups.com, Venus Software Operations <venussoftop@...> wrote:

>
> Hi all
>
> I have the following query (see below) What I really want to do is
> return one record from either of the SELECT that is applicable but not
> from both. The reason being is that this query is dependent on (by :iID
> and :cFlag) a record set that has ID and Flag being either 'Purchase' or
> 'Sale' and has records something like this
>
> iID, cFlag
> 55, 'Sale'
> 3, 'Purchase'
> 56, 'Sale'
> 57, 'Sale'
> 4, 'Purchase'
>
> so each record in the above triggers the SQL statement in question below
> and expects it to return one row from either of the UNION queries but it
> seems not to return any rows for the 'Sale' records. I can split up the
> UNION and I can get individual SQL working fine with but together it
> just will not work for sale side of the UNION.
>
> Please advise
>
> Thanks and regards
> Bhavbhuti
>
>
> SELECT tPurchaseBill.iID AS iID
> , tPurchaseBill.cBk AS cBk
> , tPurchaseBill.iNo AS iNo
> , tPurchaseBill.tDt AS tDt
> , SUM(IIF(sPurchaseBillFooter.iSrNo = -1,
> sPurchaseBillFooter.bAmt, 0)) AS bCrAmt
> , SUM(IIF(mFooterAccounts.cCode = 'CENVAT',
> sPurchaseBillFooter.bAmt, 0)) AS bCrCENVATAmt
> , SUM(IIF(mFooterAccounts.cCode = 'ECESS',
> sPurchaseBillFooter.bAmt, 0)) AS bCrECESSAmt
> , SUM(IIF(mFooterAccounts.cCode = 'SHCESS',
> sPurchaseBillFooter.bAmt, 0)) AS bCrSHCESSAmt
> , 0 AS bDrAmt
> , 0 AS bDrCENVATAmt
> , 0 AS bDrECESSAmt
> , 0 AS bDrSHCESSAmt
> FROM sPurchaseBillFooter
> JOIN mAccounts mFooterAccounts
> ON mFooterAccounts.iID = sPurchaseBillFooter.iAccountID
> JOIN tPurchaseBill
> ON tPurchaseBill.iID = sPurchaseBillFooter.iPID
> WHERE sPurchaseBillFooter.iPID = :iID
> AND :cFlag = 'Purchase'
> GROUP BY tPurchaseBill.iID, tPurchaseBill.cBk, tPurchaseBill.iNo,
> tPurchaseBill.tdt
> UNION
> SELECT tSaleInvoice.iID AS iID
> , tSaleInvoice.cBk AS cBk
> , tSaleInvoice.iNo AS iNo
> , tSaleInvoice.tDt AS tDt
> , 0 AS bCrAmt
> , 0 AS bCrCENVATAmt
> , 0 AS bCrECESSAmt
> , 0 AS bCrSHCESSAmt
> , SUM(IIF(sSaleInvoiceFooter.iSrNo = -1, sSaleInvoiceFooter.bAmt,
> 0)) AS bDrAmt
> , SUM(IIF(mFooterAccounts.cCode = 'CENVAT',
> sSaleInvoiceFooter.bAmt, 0)) AS bDrCENVATAmt
> , SUM(IIF(mFooterAccounts.cCode = 'ECESS',
> sSaleInvoiceFooter.bAmt, 0)) AS bDrECESSAmt
> , SUM(IIF(mFooterAccounts.cCode = 'SHCESS',
> sSaleInvoiceFooter.bamt, 0)) AS bDrSHCESSamt
> FROM sSaleInvoiceFooter
> JOIN mAccounts mFooterAccounts
> ON mFooterAccounts.iID = sSaleInvoiceFooter.iAccountID
> JOIN tSaleInvoice
> ON tSaleInvoice.iID = sSaleInvoiceFooter.iPID
> WHERE sSaleInvoiceFooter.iPID = :iID
> AND :cFlag = 'Sale'
> GROUP BY tSaleInvoice.iID, tSaleInvoice.cBk, tSaleInvoice.iNo,
> tSaleInvoice.tdt
>

it is not that you need to use UNION ALL instead of UNION in this case?