Thanks Emb, I had tried both

Kind regards
Bhavbhuti

On 28/09/2010 07:45, emb_blaster wrote:
>
>
>
> --- In firebird-support@yahoogroups.com
> <mailto:firebird-support%40yahoogroups.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?
>
>

--


Thanking you.

Yours Faithfully,
For Venus Software Operations
----
Bhavbhuti Nathwani
___________________________________________
Softwares for Indian Businesses at: http://www.venussoftop.com

venussoftop@...
venussoftop@...
___________________________________________

Please note: We reserve complete rights for policy changes in the future and the same will be applicable immediately as and when made. Attachments may get corrupted before reaching you, in such a situation please let us know and we will resend you the same at the earliest. We do not take any responsibility for data loss of any type and kind. Data safety remains the sole the responsibility of the users of our softwares.
___________________________________________

Internet email confidentiality:

This message may contain information that may be privileged or confidential. If you are not the addressee nor are you responsible for the delivery of the message to the addressee indicated in this email, then you may not copy or deliver this email to anyone and you should notify the sender by reply email and then destroy this message.

Please reply email immediately to this message with REMOVE in the subject, if you or your employer do not consent to email of this kind.

Opinions, conclusions and other information in this message that do not relate to the official business of my firm shall be understood as neither given nor endorsed by my company.