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@yahoogroups.com] On Behalf Of Venus Software Operations
Sent: 29. september 2010 16:06
To: firebird-support@yahoogroups.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