Chandan, you're very welcome, thank you for your generous feedback.

This exercise, resolving a simple query from a more complex query referencing many more objects, often isn't successful because of cardinality differences between tables. In this case we were lucky as your findings indicate that is has worked, although I'm sure you've had to make a few small changes.

Typically the approach is most likely to work if left-joined tables introduce only a single row to the rest of the query. If a left-joined table marked for removal from the query returns more than one row per row on the left hand side (the FROM-list prior to the table), then removing it will eliminate rows from the output, which would then be incorrect.

I started by reformatting the view definition for VIEW_GENERALLEDGER to make it easier to read, then marked the required output columns and their source tables in the FROM-list, as follows;

SELECT

h.numJournal_Id,

h.datEntry_Date,

h.varDescription,

ISNULL(dbo.VIEW_BIZPAYMENT.Narration,

h.varDescription) AS BizPayment,

d.numTransactionId,

CASE

WHEN isnull(h.numReturnID, 0) <> 0

THEN ' Chek No: ' + isnull( CAST(CH.numCheckNo AS VARCHAR(50)),'No-Cheq')

ELSE dbo.VIEW_JOURNALCHEQ.Narration END AS CheqNo,

h.numDomainId, -- ########################

'Biz Doc Id: ' + dbo.OpportunityBizDocs.vcBizDocID AS BizDocID,

h.numOppBizDocsId,

d.vcReference AS TranRef,

d.varDescription AS TranDesc,

d.numDebitAmt, -- ############################

d.numCreditAmt, -- ###############################

coa.numAccountId, -- #################################

coa.vcAccountName,

CASE

WHEN isnull(h.numCheckHeaderID, 0) <> 0 THEN + 'Checks'

WHEN isnull(h.numCashCreditCardId, 0) <> 0 AND ccc.bitMoneyOut = 0 THEN 'Cash'

WHEN isnull(h.numCashCreditCardId, 0) <> 0 AND ccc.bitMoneyOut = 1 AND ccc.bitChargeBack = 1 THEN 'Charge'

WHEN isnull(h.numDepositId, 0) <> 0 AND DM.tintDepositePage = 1 THEN 'Deposit'

WHEN isnull(h.numDepositId, 0) <> 0 AND DM.tintDepositePage = 2 THEN 'Receved Pmt'

--WHEN isnull(h.numBizDocsPaymentDetId, 0) <> 0 AND dbo.OpportunityMaster.tintOppType = 1 THEN ''Receive Amt''

--WHEN isnull(h.numBizDocsPaymentDetId, 0) <> 0 AND dbo.OpportunityMaster.tintOppType = 2 THEN ''Vendor Amt''

WHEN isnull(h.numOppId, 0) <> 0 AND isnull(h.numOppBizDocsId, 0) <> 0 AND isnull(h.numBizDocsPaymentDetId, 0) = 0 AND dbo.OpportunityMaster.tintOppType = 1 THEN 'BizDocs Invoice'

WHEN isnull(h.numOppId, 0) <> 0 AND isnull(h.numOppBizDocsId, 0) <> 0 AND isnull(h.numBizDocsPaymentDetId, 0) = 0 AND dbo.OpportunityMaster.tintOppType = 2 THEN 'BizDocs Purchase'

WHEN isnull(h.numCategoryHDRID, 0) <> 0 THEN 'Time And Expenses'

WHEN isnull(h.numBillID, 0) <> 0 THEN 'Bill'

WHEN isnull(h.numBillPaymentID, 0) <> 0 THEN 'Pay Bill'

WHEN isnull(h.numReturnID, 0) <> 0 THEN

CASE WHEN RH.tintReturnType=1 AND RH.tintReceiveType=1 THEN 'Sales Return Refund'

WHEN RH.tintReturnType=1 AND RH.tintReceiveType=2 THEN 'Sales Return Credit Memo'

WHEN RH.tintReturnType=2 AND RH.tintReceiveType=2 THEN 'Purchase Return Credit Memo'

WHEN RH.tintReturnType=3 AND RH.tintReceiveType=2 THEN 'Credit Memo'

WHEN RH.tintReturnType=4 AND RH.tintReceiveType=1 THEN 'Refund' END

WHEN isnull(h.numOppId, 0) <> 0 AND isnull(h.numOppBizDocsId, 0) = 0 THEN 'PO Fulfillment'

WHEN h.numJournal_Id <> 0 THEN 'Journal' END AS TransactionType,

dbo.CompanyInfo.vcCompanyName AS CompanyName, -- ###############################

h.numCheckHeaderID,

h.numCashCreditCardId,

h.numOppId,

h.numDepositId,

h.numCategoryHDRID,

dbo.TimeAndExpense.tintTEType,

dbo.TimeAndExpense.numCategory,

dbo.TimeAndExpense.dtFromDate,

dbo.TimeAndExpense.numUserCntID,

dbo.DivisionMaster.numDivisionID, -- ################################

ISNULL(d.bitCleared,0) AS bitCleared,

ISNULL(d.bitReconcile,0) AS bitReconcile,

isnull(h.numBillID, 0) AS numBillID,

ISNULL(h.numBillPaymentID,0) AS numBillPaymentID,

ISNULL(d.numClassID,0) AS numClassID,

ISNULL(d.numProjectID,0) AS numProjectID,

isnull(h.numReturnID, 0) AS numReturnID,

d.numCurrencyID

FROM dbo.General_Journal_Header h -- KEEP

INNER JOIN dbo.General_Journal_Details d -- KEEP

ON h.numJournal_Id = d.numJournalId

INNER JOIN dbo.Chart_Of_Accounts coa -- KEEP

ON d.numChartAcntId = coa.numAccountId

LEFT OUTER JOIN dbo.TimeAndExpense

ON h.numCategoryHDRID = dbo.TimeAndExpense.numCategoryHDRID

LEFT OUTER JOIN dbo.DivisionMaster -- KEEP

LEFT OUTER JOIN dbo.CompanyInfo -- KEEP

ON dbo.DivisionMaster.numCompanyID = dbo.CompanyInfo.numCompanyId

ON d.numCustomerId = dbo.DivisionMaster.numDivisionID

LEFT OUTER JOIN dbo.OpportunityMaster

ON h.numOppId = dbo.OpportunityMaster.numOppId

LEFT OUTER JOIN dbo.CashCreditCardDetails ccc

ON h.numCashCreditCardId = ccc.numCashCreditId

LEFT OUTER JOIN dbo.OpportunityBizDocs

ON h.numOppBizDocsId = dbo.OpportunityBizDocs.numOppBizDocsId

LEFT OUTER JOIN dbo.VIEW_JOURNALCHEQ

ON (h.numCheckHeaderID = dbo.VIEW_JOURNALCHEQ.numCheckHeaderID

or (h.numBillPaymentID=dbo.VIEW_JOURNALCHEQ.numReferenceID and dbo.VIEW_JOURNALCHEQ.tintReferenceType=8))

LEFT OUTER JOIN dbo.VIEW_BIZPAYMENT

ON h.numBizDocsPaymentDetId = dbo.VIEW_BIZPAYMENT.numBizDocsPaymentDetId

LEFT OUTER JOIN dbo.DepositMaster DM

ON DM.numDepositId = h.numDepositId

LEFT OUTER JOIN dbo.ReturnHeader RH

ON RH.numReturnHeaderID = h.numReturnID

LEFT OUTER JOIN dbo.CheckHeader CH

ON CH.numReferenceID = RH.numReturnHeaderID AND CH.tintReferenceType=10

Finally I checked that Chart of Accounts (coa) was referenced only once in the FROMlist.

That's pretty much it. Remove all the stuff you don't need, make sure all the necessary references are there, and give it a whirl.

Don't forget the comments in the code. There's a join order enforcement

LEFT OUTER JOIN dbo.DivisionMaster -- KEEP

LEFT OUTER JOIN dbo.CompanyInfo -- KEEP

ON dbo.DivisionMaster.numCompanyID = dbo.CompanyInfo.numCompanyId

ON d.numCustomerId = dbo.DivisionMaster.numDivisionID

which is designed to ensure a left join between DivisionMaster and General_Journal_Details and an inner join between CompanyInfo and DivisionMaster, however the join between DivisionMaster and General_Journal_Details is converted into an inner join by the WHERE clause: AND dm.numDivisionID IS NOT null -- converts dm to IJ

Cheers

ChrisM