Sorry Svein and Michael, I have been meaning to read and reply to both
of you but I have not read your suggestions yet, properly, but I will
reply interim just to let you know I am aware of your messages and will
reply again soon after reading them.

Thanks and regards.
Bhavbhuti

On 29/09/2010 10:17, Svein Erling Tysvær wrote:
>
> Actually, Bhavbhuti, I don't have much experience with CTEs. I'm still
> mostly using Firebird 1.5, so when I write CTEs, I normally post them
> to this list.
>
> I'd say CTE's is a bit like normalization, try to break things into
> logical pieces. If things seem to be repeating, I wonder whether they
> can somehow be put together in one place and reused. Your query simply
> seemed a bit longer than necessary when I saw it, and I decided to try
> using CTEs. I have a cold and was a bit tired when I tried and I
> actually failed to produce the kind of CTE I envisioned (if my
> solution is the best CTE you get, I'd say your particular problem can
> be equally well solved with or without CTEs), but I still decided to
> post it.
>
> Recursive CTEs are different, they generally solve problems that often
> cannot be solved otherwise simply using SQL (although many can be
> solved using stored procedures).
>
> Set
>
> -----Original Message-----
> From: firebird-support@yahoogroups.com
> <mailto:firebird-support%40yahoogroups.com>
> [mailto:firebird-support@yahoogroups.com
> <mailto:firebird-support%40yahoogroups.com>] On Behalf Of Venus
> Software Operations
> Sent: 29. september 2010 16:06
> To: firebird-support@yahoogroups.com
> <mailto:firebird-support%40yahoogroups.com>
> Subject: Re: [firebird-support] Re: UNION to return one record only
> not two
>
> Hi Sven
>
> Thanks a lot for your suggestion. As always you give a great CTE
> examples which suit the needs very well, thanks.
>
> Please let me know this. I can find all documents on CTE on the web and
> when I see your examples, I say oh, yes, I can do this a next time, but
> when a new problem needs solving, like the current one I cannot see a
> CTE implementation. Of course your experience counts, but I think there
> is a mindset that I need to have, a way to break up the problem that
> compliments a CTE. Is this something that reading theory can help me
> with? Is there some kind of question, way of looking that you can put
> in words that can help me in visioning CTEs?
>
> If you can answer, hopefully I asked the right question, without taking
> too much of your time, that would be great.
>
> Thanks and regards
> Bhavbhuti
>
> On 29/09/2010 01:53, Svein Erling wrote:
> > 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
>
>

--


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.