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.