Hi Bhavbhuti!

I'm actually surprised that your query executes at all, 0 is not an aggregate function, so I would expect you to have to either SUM these columns or GROUP BY them.

I would expect

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
, SUM(0) AS bDrAmt
, SUM(0) AS bDrCENVATAmt
, SUM(0) AS bDrECESSAmt
, SUM(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
, SUM(0) AS bCrAmt
, SUM(0) AS bCrCENVATAmt
, SUM(0) AS bCrECESSAmt
, SUM(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

to work, but I do find it a bit cumbersome to read. I think I would have considered two things - using relatively short aliases, and possibly CTEs. Maybe something like:

WITH Purchase (iID, cBk, iNo, tDt, bCrAmt, bCrCENVATAmt, bCrECESSAmt, bCrSHCESSAmt) as
(SELECT PB.iID, PB.cBk, PB.iNo, PB.tDt,
SUM(IIF(PBF.iSrNo = -1, PBF.bAmt, 0)), SUM(IIF(FA.cCode = 'CENVAT', PBF.bAmt, 0)),
SUM(IIF(FA.cCode = 'ECESS', PBF.bAmt, 0)), SUM(IIF(FA.cCode = 'SHCESS', PBF.bAmt, 0))
FROM sPurchaseBillFooter PBF
JOIN mAccounts FA
ON FA.iID = PBF.iAccountID
JOIN tPurchaseBill PB
ON PB.iID = PBF.iPID
WHERE PBF.iPID = :iID
AND :cFlag = 'Purchase'
GROUP BY 1, 2, 3, 4),

Sale (iID, cBk, iNo, tDt, bDrAmt, bDrCENVATAmt, bDrECESSAmt, bDrSHCESSAMT) as
(SELECT SI.iID, SI.cBk, SI.iNo, SI.tDt,
SUM(IIF(SIF.iSrNo = -1, SIF.bAmt, 0)), SUM(IIF(FA.cCode = 'CENVAT', SIF.bAmt, 0)),
SUM(IIF(FA.cCode = 'ECESS', SIF.bAmt, 0)), SUM(IIF(FA.cCode = 'SHCESS', SIF.bamt, 0))
FROM sSaleInvoiceFooter SIF
JOIN mAccounts FA
ON FA.iID = SIF.iAccountID
JOIN tSaleInvoice SI
ON SI.iID = SIF.iPID
WHERE SIF.iPID = :iID
AND :cFlag = 'Sale'
GROUP BY 1, 2, 3, 4)

SELECT iID, cBK, iNo, tDt, bCrAmt, bCrCENVATAmt, bCrECESSAmt, bCrSHCESSAmt, 0 as bDrAmt, 0 as bDrCENVATAmt, 0 as bDrECESSAmt, 0 as bDRSHCESSAmt
FROM Purchase
UNION
SELECT iID, cBK, iNo, tDt, 0, 0, 0, 0, bDrAmt, bDrCENVATAmt, bDrECESSAmt, bDRSHCESSAmt
FROM Sale

HTH,
Set