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