VERY long query

microsoft.public.sqlserver.programming

16 May 2005 1:02 PM

Bob Castleman

Below is a query written by one of the developers here. He showed in to me,
I think as a joke, because he was going to run it in our hosted environment
against all of our customers. Ichoked, because it was such a long query, but
it really isn't terribly complex after a look at it. But I was curious if
there was a better way to accomplish what it does.

Including DDL is impractical, but I think it's pretty clear without it. It
is looking for problems in general ledger references.

SELECT
'GL_Subaccount' as Tablename,
GL_Subaccount.GL_Account,
'(n/a)' as Subaccount,
count(*) as DupCount
FROM
(GL_Subaccount
    LEFT OUTER JOIN GL_AccountMaster
      ON (GL_AccountMaster.GL_Account=GL_Subaccount.GL_Account))
WHERE
(GL_Subaccount.GL_Account is not null)
and (GL_AccountMaster.GL_Account is null)
GROUP BY
GL_Subaccount.GL_Account
UNION
SELECT
'AdjustCode' as Tablename,
AdjustCode.GL_Account,
AdjustCode.Subledger as Subaccount,
count(*) as DupCount
FROM
(AdjustCode
    LEFT OUTER JOIN GL_Subaccount
      ON (GL_Subaccount.GL_Account=AdjustCode.GL_Account)
        and (GL_Subaccount.Subaccount=AdjustCode.Subledger))
WHERE
(AdjustCode.GL_Account is not null)
    and (AdjustCode.Subledger is not null)
    and (AdjustCode.Subledger<>'')
    and (GL_Subaccount.GL_Account is null)
GROUP BY
AdjustCode.GL_Account,
AdjustCode.Subledger
UNION
SELECT
'AdjustCode' as Tablename,
AdjustCode.GL_Account,
'(none)' as Subaccount,
count(*) as DupCount
FROM
(AdjustCode
    LEFT OUTER JOIN GL_AccountMaster
      ON (GL_AccountMaster.GL_Account=AdjustCode.GL_Account))
WHERE
(AdjustCode.GL_Account is not null)
    and ((AdjustCode.Subledger is null) or (AdjustCode.Subledger=''))
    and (GL_AccountMaster.GL_Account is null)
GROUP BY
AdjustCode.GL_Account
UNION
SELECT
'AP_Item' as Tablename,
AP_Item.GL_Account,
AP_Item.Subaccount,
count(*) as DupCount
FROM
(AP_Item
    LEFT OUTER JOIN GL_Subaccount
      ON (GL_Subaccount.GL_Account=AP_Item.GL_Account)
        and (GL_Subaccount.Subaccount=AP_Item.Subaccount))
WHERE
(AP_Item.GL_Account is not null)
    and (AP_Item.Subaccount is not null)
    and (AP_Item.Subaccount<>'')
    and (GL_Subaccount.GL_Account is null)
GROUP BY
AP_Item.GL_Account,
AP_Item.Subaccount
UNION
SELECT
'AP_Item' as Tablename,
AP_Item.GL_Account,
'(none)' as Subaccount,
count(*) as DupCount
FROM
(AP_Item
    LEFT OUTER JOIN GL_AccountMaster
      ON (GL_AccountMaster.GL_Account=AP_Item.GL_Account))
WHERE
(AP_Item.GL_Account is not null)
    and ((AP_Item.Subaccount is null) or (AP_Item.Subaccount=''))
    and (GL_AccountMaster.GL_Account is null)
GROUP BY
AP_Item.GL_Account
UNION
SELECT
'CB_Dist' as Tablename,
CB_Dist.GL_Account,
CB_Dist.Subaccount,
count(*) as DupCount
FROM
(CB_Dist
    LEFT OUTER JOIN GL_Subaccount
      ON (GL_Subaccount.GL_Account=CB_Dist.GL_Account)
        and (GL_Subaccount.Subaccount=CB_Dist.Subaccount))
WHERE
(CB_Dist.GL_Account is not null)
    and (CB_Dist.Subaccount is not null)
    and (CB_Dist.Subaccount<>'')
    and (GL_Subaccount.GL_Account is null)
GROUP BY
CB_Dist.GL_Account,
CB_Dist.Subaccount
UNION
SELECT
'CB_Dist' as Tablename,
CB_Dist.GL_Account,
'(none)' as Subaccount,
count(*) as DupCount
FROM
(CB_Dist
    LEFT OUTER JOIN GL_AccountMaster
      ON (GL_AccountMaster.GL_Account=CB_Dist.GL_Account))
WHERE
(CB_Dist.GL_Account is not null)
    and ((CB_Dist.Subaccount is null) or (CB_Dist.Subaccount=''))
    and (GL_AccountMaster.GL_Account is null)
GROUP BY
CB_Dist.GL_Account
UNION
SELECT
'CB_Register' as Tablename,
CB_Register.GL_Account,
CB_Register.Subaccount,
count(*) as DupCount
FROM
(CB_Register
    LEFT OUTER JOIN GL_Subaccount
      ON (GL_Subaccount.GL_Account=CB_Register.GL_Account)
        and (GL_Subaccount.Subaccount=CB_Register.Subaccount))
WHERE
(CB_Register.GL_Account is not null)
    and (CB_Register.Subaccount is not null)
    and (CB_Register.Subaccount<>'')
    and (GL_Subaccount.GL_Account is null)
GROUP BY
CB_Register.GL_Account,
CB_Register.Subaccount
UNION
SELECT
'CB_Register' as Tablename,
CB_Register.GL_Account,
'(none)' as Subaccount,
count(*) as DupCount
FROM
(CB_Register
    LEFT OUTER JOIN GL_AccountMaster
      ON (GL_AccountMaster.GL_Account=CB_Register.GL_Account))
WHERE
(CB_Register.GL_Account is not null)
    and ((CB_Register.Subaccount is null) or (CB_Register.Subaccount=''))
    and (GL_AccountMaster.GL_Account is null)
GROUP BY
CB_Register.GL_Account
UNION
SELECT
'CB_Statement' as Tablename,
CB_Statement.GL_Account,
CB_Statement.Subaccount,
count(*) as DupCount
FROM
(CB_Statement
    LEFT OUTER JOIN GL_Subaccount
      ON (GL_Subaccount.GL_Account=CB_Statement.GL_Account)
        and (GL_Subaccount.Subaccount=CB_Statement.Subaccount))
WHERE
(CB_Statement.GL_Account is not null)
    and (CB_Statement.Subaccount is not null)
    and (CB_Statement.Subaccount<>'')
    and (GL_Subaccount.GL_Account is null)
GROUP BY
CB_Statement.GL_Account,
CB_Statement.Subaccount
UNION
SELECT
'CB_Statement' as Tablename,
CB_Statement.GL_Account,
'(none)' as Subaccount,
count(*) as DupCount
FROM
(CB_Statement
    LEFT OUTER JOIN GL_AccountMaster
      ON (GL_AccountMaster.GL_Account=CB_Statement.GL_Account))
WHERE
(CB_Statement.GL_Account is not null)
    and ((CB_Statement.Subaccount is null) or (CB_Statement.Subaccount=''))
    and (GL_AccountMaster.GL_Account is null)
GROUP BY
CB_Statement.GL_Account
UNION
SELECT
'ExpenseType' as Tablename,
ExpenseType.GL_Account,
ExpenseType.Subledger as Subaccount,
count(*) as DupCount
FROM
(ExpenseType
    LEFT OUTER JOIN GL_Subaccount
      ON (GL_Subaccount.GL_Account=ExpenseType.GL_Account)
        and (GL_Subaccount.Subaccount=ExpenseType.Subledger))
WHERE
(ExpenseType.GL_Account is not null)
    and (ExpenseType.Subledger is not null)
    and (ExpenseType.Subledger<>'')
    and (GL_Subaccount.GL_Account is null)
GROUP BY
ExpenseType.GL_Account,
ExpenseType.Subledger
UNION
SELECT
'ExpenseType' as Tablename,
ExpenseType.GL_Account,
'(none)' as Subaccount,
count(*) as DupCount
FROM
(ExpenseType
    LEFT OUTER JOIN GL_AccountMaster
      ON (GL_AccountMaster.GL_Account=ExpenseType.GL_Account))
WHERE
(ExpenseType.GL_Account is not null)
    and ((ExpenseType.Subledger is null) or (ExpenseType.Subledger=''))
    and (GL_AccountMaster.GL_Account is null)
GROUP BY
ExpenseType.GL_Account
UNION
SELECT
'GL_AccountDivision' as Tablename,
GL_AccountDivision.GL_Account,
'(n/a)' as Subaccount,
count(*) as DupCount
FROM
(GL_AccountDivision
    LEFT OUTER JOIN GL_AccountMaster
      ON (GL_AccountMaster.GL_Account=GL_AccountDivision.GL_Account))
WHERE
(GL_AccountDivision.GL_Account is not null)
and (GL_AccountMaster.GL_Account is null)
GROUP BY
GL_AccountDivision.GL_Account
UNION
SELECT
'GL_Budget' as Tablename,
GL_Budget.GL_Account,
GL_Budget.Subaccount,
count(*) as DupCount
FROM
(GL_Budget
    LEFT OUTER JOIN GL_Subaccount
      ON (GL_Subaccount.GL_Account=GL_Budget.GL_Account)
        and (GL_Subaccount.Subaccount=GL_Budget.Subaccount))
WHERE
(GL_Budget.GL_Account is not null)
    and (GL_Budget.Subaccount is not null)
    and (GL_Budget.Subaccount<>'')
    and (GL_Subaccount.GL_Account is null)
GROUP BY
GL_Budget.GL_Account,
GL_Budget.Subaccount
UNION
SELECT
'GL_Budget' as Tablename,
GL_Budget.GL_Account,
'(none)' as Subaccount,
count(*) as DupCount
FROM
(GL_Budget
    LEFT OUTER JOIN GL_AccountMaster
      ON (GL_AccountMaster.GL_Account=GL_Budget.GL_Account))
WHERE
(GL_Budget.GL_Account is not null)
    and ((GL_Budget.Subaccount is null) or (GL_Budget.Subaccount=''))
    and (GL_AccountMaster.GL_Account is null)
GROUP BY
GL_Budget.GL_Account
UNION
SELECT
'GL_Default' as Tablename,
GL_Default.GL_Account,
GL_Default.Subaccount,
count(*) as DupCount
FROM
(GL_Default
    LEFT OUTER JOIN GL_Subaccount
      ON (GL_Subaccount.GL_Account=GL_Default.GL_Account)
        and (GL_Subaccount.Subaccount=GL_Default.Subaccount))
WHERE
(GL_Default.GL_Account is not null)
    and (GL_Default.Subaccount is not null)
    and (GL_Default.Subaccount<>'')
    and (GL_Subaccount.GL_Account is null)
GROUP BY
GL_Default.GL_Account,
GL_Default.Subaccount
UNION
SELECT
'GL_Default' as Tablename,
GL_Default.GL_Account,
'(none)' as Subaccount,
count(*) as DupCount
FROM
(GL_Default
    LEFT OUTER JOIN GL_AccountMaster
      ON (GL_AccountMaster.GL_Account=GL_Default.GL_Account))
WHERE
(GL_Default.GL_Account is not null)
    and ((GL_Default.Subaccount is null) or (GL_Default.Subaccount=''))
    and (GL_AccountMaster.GL_Account is null)
GROUP BY
GL_Default.GL_Account
UNION
SELECT
'GL_Journal' as Tablename,
GL_Journal.GL_Account,
GL_Journal.Subaccount,
count(*) as DupCount
FROM
(GL_Journal
    LEFT OUTER JOIN GL_Subaccount
      ON (GL_Subaccount.GL_Account=GL_Journal.GL_Account)
        and (GL_Subaccount.Subaccount=GL_Journal.Subaccount))
WHERE
(GL_Journal.GL_Account is not null)
    and (GL_Journal.Subaccount is not null)
    and (GL_Journal.Subaccount<>'')
    and (GL_Subaccount.GL_Account is null)
GROUP BY
GL_Journal.GL_Account,
GL_Journal.Subaccount
UNION
SELECT
'GL_Journal' as Tablename,
GL_Journal.GL_Account,
'(none)' as Subaccount,
count(*) as DupCount
FROM
(GL_Journal
    LEFT OUTER JOIN GL_AccountMaster
      ON (GL_AccountMaster.GL_Account=GL_Journal.GL_Account))
WHERE
(GL_Journal.GL_Account is not null)
    and ((GL_Journal.Subaccount is null) or (GL_Journal.Subaccount=''))
    and (GL_AccountMaster.GL_Account is null)
GROUP BY
GL_Journal.GL_Account
UNION
SELECT
'GL_Report' as Tablename,
GL_Report.GL_Account,
GL_Report.Subaccount,
count(*) as DupCount
FROM
(GL_Report
    LEFT OUTER JOIN GL_Subaccount
      ON (GL_Subaccount.GL_Account=GL_Report.GL_Account)
        and (GL_Subaccount.Subaccount=GL_Report.Subaccount))
WHERE
(GL_Report.GL_Account is not null)
    and (GL_Report.Subaccount is not null)
    and (GL_Report.Subaccount<>'')
    and (GL_Subaccount.GL_Account is null)
GROUP BY
GL_Report.GL_Account,
GL_Report.Subaccount
UNION
SELECT
'GL_Report' as Tablename,
GL_Report.GL_Account,
'(none)' as Subaccount,
count(*) as DupCount
FROM
(GL_Report
    LEFT OUTER JOIN GL_AccountMaster
      ON (GL_AccountMaster.GL_Account=GL_Report.GL_Account))
WHERE
(GL_Report.GL_Account is not null)
    and ((GL_Report.Subaccount is null) or (GL_Report.Subaccount=''))
    and (GL_AccountMaster.GL_Account is null)
GROUP BY
GL_Report.GL_Account
UNION
SELECT
'GL_Transaction' as Tablename,
GL_Transaction.GL_Account,
GL_Transaction.Subaccount,
count(*) as DupCount
FROM
(GL_Transaction
    LEFT OUTER JOIN GL_Subaccount
      ON (GL_Subaccount.GL_Account=GL_Transaction.GL_Account)
        and (GL_Subaccount.Subaccount=GL_Transaction.Subaccount))
WHERE
(GL_Transaction.GL_Account is not null)
    and (GL_Transaction.Subaccount is not null)
    and (GL_Transaction.Subaccount<>'')
    and (GL_Subaccount.GL_Account is null)
GROUP BY
GL_Transaction.GL_Account,
GL_Transaction.Subaccount
UNION
SELECT
'GL_Transaction' as Tablename,
GL_Transaction.GL_Account,
'(none)' as Subaccount,
count(*) as DupCount
FROM
(GL_Transaction
    LEFT OUTER JOIN GL_AccountMaster
      ON (GL_AccountMaster.GL_Account=GL_Transaction.GL_Account))
WHERE
(GL_Transaction.GL_Account is not null)
    and ((GL_Transaction.Subaccount is null) or
(GL_Transaction.Subaccount=''))
    and (GL_AccountMaster.GL_Account is null)
GROUP BY
GL_Transaction.GL_Account
UNION
SELECT
'IC_Tag' as Tablename,
IC_Tag.GL_Account,
IC_Tag.Subaccount,
count(*) as DupCount
FROM
(IC_Tag
    LEFT OUTER JOIN GL_Subaccount
      ON (GL_Subaccount.GL_Account=IC_Tag.GL_Account)
        and (GL_Subaccount.Subaccount=IC_Tag.Subaccount))
WHERE
(IC_Tag.GL_Account is not null)
    and (IC_Tag.Subaccount is not null)
    and (IC_Tag.Subaccount<>'')
    and (GL_Subaccount.GL_Account is null)
GROUP BY
IC_Tag.GL_Account,
IC_Tag.Subaccount
UNION
SELECT
'IC_Tag' as Tablename,
IC_Tag.GL_Account,
'(none)' as Subaccount,
count(*) as DupCount
FROM
(IC_Tag
    LEFT OUTER JOIN GL_AccountMaster
      ON (GL_AccountMaster.GL_Account=IC_Tag.GL_Account))
WHERE
(IC_Tag.GL_Account is not null)
    and ((IC_Tag.Subaccount is null) or (IC_Tag.Subaccount=''))
    and (GL_AccountMaster.GL_Account is null)
GROUP BY
IC_Tag.GL_Account
UNION
SELECT
'IC_Transaction' as Tablename,
IC_Transaction.GL_Account,
IC_Transaction.Subaccount,
count(*) as DupCount
FROM
(IC_Transaction
    LEFT OUTER JOIN GL_Subaccount
      ON (GL_Subaccount.GL_Account=IC_Transaction.GL_Account)
        and (GL_Subaccount.Subaccount=IC_Transaction.Subaccount))
WHERE
(IC_Transaction.GL_Account is not null)
    and (IC_Transaction.Subaccount is not null)
    and (IC_Transaction.Subaccount<>'')
    and (GL_Subaccount.GL_Account is null)
GROUP BY
IC_Transaction.GL_Account,
IC_Transaction.Subaccount
UNION
SELECT
'IC_Transaction' as Tablename,
IC_Transaction.GL_Account,
'(none)' as Subaccount,
count(*) as DupCount
FROM
(IC_Transaction
    LEFT OUTER JOIN GL_AccountMaster
      ON (GL_AccountMaster.GL_Account=IC_Transaction.GL_Account))
WHERE
(IC_Transaction.GL_Account is not null)
    and ((IC_Transaction.Subaccount is null) or
(IC_Transaction.Subaccount=''))
    and (GL_AccountMaster.GL_Account is null)
GROUP BY
IC_Transaction.GL_Account
UNION
SELECT
'IC_TrxControl' as Tablename,
IC_TrxControl.GL_Account,
IC_TrxControl.Subaccount,
count(*) as DupCount
FROM
(IC_TrxControl
    LEFT OUTER JOIN GL_Subaccount
      ON (GL_Subaccount.GL_Account=IC_TrxControl.GL_Account)
        and (GL_Subaccount.Subaccount=IC_TrxControl.Subaccount))
WHERE
(IC_TrxControl.GL_Account is not null)
    and (IC_TrxControl.Subaccount is not null)
    and (IC_TrxControl.Subaccount<>'')
    and (GL_Subaccount.GL_Account is null)
GROUP BY
IC_TrxControl.GL_Account,
IC_TrxControl.Subaccount
UNION
SELECT
'IC_TrxControl' as Tablename,
IC_TrxControl.GL_Account,
'(none)' as Subaccount,
count(*) as DupCount
FROM
(IC_TrxControl
    LEFT OUTER JOIN GL_AccountMaster
      ON (GL_AccountMaster.GL_Account=IC_TrxControl.GL_Account))
WHERE
(IC_TrxControl.GL_Account is not null)
    and ((IC_TrxControl.Subaccount is null) or
(IC_TrxControl.Subaccount=''))
    and (GL_AccountMaster.GL_Account is null)
GROUP BY
IC_TrxControl.GL_Account
UNION
SELECT
'IC_TrxControlItem' as Tablename,
IC_TrxControlItem.GL_Account,
IC_TrxControlItem.Subaccount,
count(*) as DupCount
FROM
(IC_TrxControlItem
    LEFT OUTER JOIN GL_Subaccount
      ON (GL_Subaccount.GL_Account=IC_TrxControlItem.GL_Account)
        and (GL_Subaccount.Subaccount=IC_TrxControlItem.Subaccount))
WHERE
(IC_TrxControlItem.GL_Account is not null)
    and (IC_TrxControlItem.Subaccount is not null)
    and (IC_TrxControlItem.Subaccount<>'')
    and (GL_Subaccount.GL_Account is null)
GROUP BY
IC_TrxControlItem.GL_Account,
IC_TrxControlItem.Subaccount
UNION
SELECT
'IC_TrxControlItem' as Tablename,
IC_TrxControlItem.GL_Account,
'(none)' as Subaccount,
count(*) as DupCount
FROM
(IC_TrxControlItem
    LEFT OUTER JOIN GL_AccountMaster
      ON (GL_AccountMaster.GL_Account=IC_TrxControlItem.GL_Account))
WHERE
(IC_TrxControlItem.GL_Account is not null)
    and ((IC_TrxControlItem.Subaccount is null) or
(IC_TrxControlItem.Subaccount=''))
    and (GL_AccountMaster.GL_Account is null)
GROUP BY
IC_TrxControlItem.GL_Account
UNION
SELECT
'InvoiceItem' as Tablename,
InvoiceItem.GL_Account,
InvoiceItem.Subledger as Subaccount,
count(*) as DupCount
FROM
(InvoiceItem
    LEFT OUTER JOIN GL_Subaccount
      ON (GL_Subaccount.GL_Account=InvoiceItem.GL_Account)
        and (GL_Subaccount.Subaccount=InvoiceItem.Subledger))
WHERE
(InvoiceItem.GL_Account is not null)
    and (InvoiceItem.Subledger is not null)
    and (InvoiceItem.Subledger<>'')
    and (GL_Subaccount.GL_Account is null)
GROUP BY
InvoiceItem.GL_Account,
InvoiceItem.Subledger
UNION
SELECT
'InvoiceItem' as Tablename,
InvoiceItem.GL_Account,
'(none)' as Subaccount,
count(*) as DupCount
FROM
(InvoiceItem
    LEFT OUTER JOIN GL_AccountMaster
      ON (GL_AccountMaster.GL_Account=InvoiceItem.GL_Account))
WHERE
(InvoiceItem.GL_Account is not null)
    and ((InvoiceItem.Subledger is null) or (InvoiceItem.Subledger=''))
    and (GL_AccountMaster.GL_Account is null)
GROUP BY
InvoiceItem.GL_Account
UNION
SELECT
'InvoiceItemDetail' as Tablename,
InvoiceItemDetail.GL_Account,
InvoiceItemDetail.Subaccount,
count(*) as DupCount
FROM
(InvoiceItemDetail
    LEFT OUTER JOIN GL_Subaccount
      ON (GL_Subaccount.GL_Account=InvoiceItemDetail.GL_Account)
        and (GL_Subaccount.Subaccount=InvoiceItemDetail.Subaccount))
WHERE
(InvoiceItemDetail.GL_Account is not null)
    and (InvoiceItemDetail.Subaccount is not null)
    and (InvoiceItemDetail.Subaccount<>'')
    and (GL_Subaccount.GL_Account is null)
GROUP BY
InvoiceItemDetail.GL_Account,
InvoiceItemDetail.Subaccount
UNION
SELECT
'InvoiceItemDetail' as Tablename,
InvoiceItemDetail.GL_Account,
'(none)' as Subaccount,
count(*) as DupCount
FROM
(InvoiceItemDetail
    LEFT OUTER JOIN GL_AccountMaster
      ON (GL_AccountMaster.GL_Account=InvoiceItemDetail.GL_Account))
WHERE
(InvoiceItemDetail.GL_Account is not null)
    and ((InvoiceItemDetail.Subaccount is null) or
(InvoiceItemDetail.Subaccount=''))
    and (GL_AccountMaster.GL_Account is null)
GROUP BY
InvoiceItemDetail.GL_Account
UNION
SELECT
'PR_MiscWage' as Tablename,
PR_MiscWage.GL_Account,
PR_MiscWage.Subaccount,
count(*) as DupCount
FROM
(PR_MiscWage
    LEFT OUTER JOIN GL_Subaccount
      ON (GL_Subaccount.GL_Account=PR_MiscWage.GL_Account)
        and (GL_Subaccount.Subaccount=PR_MiscWage.Subaccount))
WHERE
(PR_MiscWage.GL_Account is not null)
    and (PR_MiscWage.Subaccount is not null)
    and (PR_MiscWage.Subaccount<>'')
    and (GL_Subaccount.GL_Account is null)
GROUP BY
PR_MiscWage.GL_Account,
PR_MiscWage.Subaccount
UNION
SELECT
'PR_MiscWage' as Tablename,
PR_MiscWage.GL_Account,
'(none)' as Subaccount,
count(*) as DupCount
FROM
(PR_MiscWage
    LEFT OUTER JOIN GL_AccountMaster
      ON (GL_AccountMaster.GL_Account=PR_MiscWage.GL_Account))
WHERE
(PR_MiscWage.GL_Account is not null)
    and ((PR_MiscWage.Subaccount is null) or (PR_MiscWage.Subaccount=''))
    and (GL_AccountMaster.GL_Account is null)
GROUP BY
PR_MiscWage.GL_Account
UNION
SELECT
'PR_WageSummary' as Tablename,
PR_WageSummary.GL_Account,
PR_WageSummary.Subaccount,
count(*) as DupCount
FROM
(PR_WageSummary
    LEFT OUTER JOIN GL_Subaccount
      ON (GL_Subaccount.GL_Account=PR_WageSummary.GL_Account)
        and (GL_Subaccount.Subaccount=PR_WageSummary.Subaccount))
WHERE
(PR_WageSummary.GL_Account is not null)
    and (PR_WageSummary.Subaccount is not null)
    and (PR_WageSummary.Subaccount<>'')
    and (GL_Subaccount.GL_Account is null)
GROUP BY
PR_WageSummary.GL_Account,
PR_WageSummary.Subaccount
UNION
SELECT
'PR_WageSummary' as Tablename,
PR_WageSummary.GL_Account,
'(none)' as Subaccount,
count(*) as DupCount
FROM
(PR_WageSummary
    LEFT OUTER JOIN GL_AccountMaster
      ON (GL_AccountMaster.GL_Account=PR_WageSummary.GL_Account))
WHERE
(PR_WageSummary.GL_Account is not null)
    and ((PR_WageSummary.Subaccount is null) or
(PR_WageSummary.Subaccount=''))
    and (GL_AccountMaster.GL_Account is null)
GROUP BY
PR_WageSummary.GL_Account
UNION
SELECT
'PurchaseOrderItem' as Tablename,
PurchaseOrderItem.GL_Account,
PurchaseOrderItem.Subaccount,
count(*) as DupCount
FROM
(PurchaseOrderItem
    LEFT OUTER JOIN GL_Subaccount
      ON (GL_Subaccount.GL_Account=PurchaseOrderItem.GL_Account)
        and (GL_Subaccount.Subaccount=PurchaseOrderItem.Subaccount))
WHERE
(PurchaseOrderItem.GL_Account is not null)
    and (PurchaseOrderItem.Subaccount is not null)
    and (PurchaseOrderItem.Subaccount<>'')
    and (GL_Subaccount.GL_Account is null)
GROUP BY
PurchaseOrderItem.GL_Account,
PurchaseOrderItem.Subaccount
UNION
SELECT
'PurchaseOrderItem' as Tablename,
PurchaseOrderItem.GL_Account,
'(none)' as Subaccount,
count(*) as DupCount
FROM
(PurchaseOrderItem
    LEFT OUTER JOIN GL_AccountMaster
      ON (GL_AccountMaster.GL_Account=PurchaseOrderItem.GL_Account))
WHERE
(PurchaseOrderItem.GL_Account is not null)
    and ((PurchaseOrderItem.Subaccount is null) or
(PurchaseOrderItem.Subaccount=''))
    and (GL_AccountMaster.GL_Account is null)
GROUP BY
PurchaseOrderItem.GL_Account
UNION
SELECT
'SaleType' as Tablename,
SaleType.GL_Account,
SaleType.Subledger as Subaccount,
count(*) as DupCount
FROM
(SaleType
    LEFT OUTER JOIN GL_Subaccount
      ON (GL_Subaccount.GL_Account=SaleType.GL_Account)
        and (GL_Subaccount.Subaccount=SaleType.Subledger))
WHERE
(SaleType.GL_Account is not null)
    and (SaleType.Subledger is not null)
    and (SaleType.Subledger<>'')
    and (GL_Subaccount.GL_Account is null)
GROUP BY
SaleType.GL_Account,
SaleType.Subledger
UNION
SELECT
'SaleType' as Tablename,
SaleType.GL_Account,
'(none)' as Subaccount,
count(*) as DupCount
FROM
(SaleType
    LEFT OUTER JOIN GL_AccountMaster
      ON (GL_AccountMaster.GL_Account=SaleType.GL_Account))
WHERE
(SaleType.GL_Account is not null)
    and ((SaleType.Subledger is null) or (SaleType.Subledger=''))
    and (GL_AccountMaster.GL_Account is null)
GROUP BY
SaleType.GL_Account
UNION
SELECT
'TimecardEntry' as Tablename,
TimecardEntry.GL_Account,
TimecardEntry.Subaccount,
count(*) as DupCount
FROM
(TimecardEntry
    LEFT OUTER JOIN GL_Subaccount
      ON (GL_Subaccount.GL_Account=TimecardEntry.GL_Account)
        and (GL_Subaccount.Subaccount=TimecardEntry.Subaccount))
WHERE
(TimecardEntry.GL_Account is not null)
    and (TimecardEntry.Subaccount is not null)
    and (TimecardEntry.Subaccount<>'')
    and (GL_Subaccount.GL_Account is null)
GROUP BY
TimecardEntry.GL_Account,
TimecardEntry.Subaccount
UNION
SELECT
'TimecardEntry' as Tablename,
TimecardEntry.GL_Account,
'(none)' as Subaccount,
count(*) as DupCount
FROM
(TimecardEntry
    LEFT OUTER JOIN GL_AccountMaster
      ON (GL_AccountMaster.GL_Account=TimecardEntry.GL_Account))
WHERE
(TimecardEntry.GL_Account is not null)
    and ((TimecardEntry.Subaccount is null) or
(TimecardEntry.Subaccount=''))
    and (GL_AccountMaster.GL_Account is null)
GROUP BY
TimecardEntry.GL_Account

16 May 2005 1:47 PM

--CELKO--

>From a quick look, this seems to be an attempt to find the places where

someone failed to put in DRI that will relate various tables to the
General Ledger. Run this thing once, clean up the mess and then add
the proper constraints. I would tend to do this one table at a time to
have some control.

16 May 2005 2:32 PM

Bob Castleman

No DRI? I don't know what you mean!

Actually, that's exactly what this is. Unfortunately, adding the constraints
isn't likely. I like the idea of one table at a time, but the developer
isn't going to. And he has seniority.

Sigh.

Show quote

"--CELKO--" <jcelko***@earthlink.net> wrote in message
news:1116251231.640917.317530@g47g2000cwa.googlegroups.com...
> >From a quick look, this seems to be an attempt to find the places where
> someone failed to put in DRI that will relate various tables to the
> General Ledger. Run this thing once, clean up the mess and then add
> the proper constraints. I would tend to do this one table at a time to
> have some control.
>

16 May 2005 2:24 PM

Developers need to run complex queries from time to time to fullfill special
ad-hoc reporting requirements, and this is normal. Sometimes the deadline
for providing the results leaves little time for optimization and the query
will only be run once. What you need is a reporting server.

When you say your hosted environment, do you mean that your SQL Server is
hosted by an ISP on a shared or dedicated box?

Show quote

"Bob Castleman" <nomail@here> wrote in message
news:OaQsRbhWFHA.3140@TK2MSFTNGP14.phx.gbl...
> Below is a query written by one of the developers here. He showed in to
me,
> I think as a joke, because he was going to run it in our hosted
environment
> against all of our customers. Ichoked, because it was such a long query,
but
> it really isn't terribly complex after a look at it. But I was curious if
> there was a better way to accomplish what it does.
>
> Including DDL is impractical, but I think it's pretty clear without it. It
> is looking for problems in general ledger references.
>
> SELECT
>   'GL_Subaccount' as Tablename,
>   GL_Subaccount.GL_Account,
>   '(n/a)' as Subaccount,
>   count(*) as DupCount
> FROM
>   (GL_Subaccount
>     LEFT OUTER JOIN GL_AccountMaster
>       ON (GL_AccountMaster.GL_Account=GL_Subaccount.GL_Account))
> WHERE
>   (GL_Subaccount.GL_Account is not null)
>   and (GL_AccountMaster.GL_Account is null)
> GROUP BY
>   GL_Subaccount.GL_Account
> UNION
> SELECT
>   'AdjustCode' as Tablename,
>   AdjustCode.GL_Account,
>   AdjustCode.Subledger as Subaccount,
>   count(*) as DupCount
> FROM
>   (AdjustCode
>     LEFT OUTER JOIN GL_Subaccount
>       ON (GL_Subaccount.GL_Account=AdjustCode.GL_Account)
>         and (GL_Subaccount.Subaccount=AdjustCode.Subledger))
> WHERE
>   (AdjustCode.GL_Account is not null)
>     and (AdjustCode.Subledger is not null)
>     and (AdjustCode.Subledger<>'')
>     and (GL_Subaccount.GL_Account is null)
> GROUP BY
>   AdjustCode.GL_Account,
>   AdjustCode.Subledger
> UNION
> SELECT
>   'AdjustCode' as Tablename,
>   AdjustCode.GL_Account,
>   '(none)' as Subaccount,
>   count(*) as DupCount
> FROM
>   (AdjustCode
>     LEFT OUTER JOIN GL_AccountMaster
>       ON (GL_AccountMaster.GL_Account=AdjustCode.GL_Account))
> WHERE
>   (AdjustCode.GL_Account is not null)
>     and ((AdjustCode.Subledger is null) or (AdjustCode.Subledger=''))
>     and (GL_AccountMaster.GL_Account is null)
> GROUP BY
>   AdjustCode.GL_Account
> UNION
> SELECT
>   'AP_Item' as Tablename,
>   AP_Item.GL_Account,
>   AP_Item.Subaccount,
>   count(*) as DupCount
> FROM
>   (AP_Item
>     LEFT OUTER JOIN GL_Subaccount
>       ON (GL_Subaccount.GL_Account=AP_Item.GL_Account)
>         and (GL_Subaccount.Subaccount=AP_Item.Subaccount))
> WHERE
>   (AP_Item.GL_Account is not null)
>     and (AP_Item.Subaccount is not null)
>     and (AP_Item.Subaccount<>'')
>     and (GL_Subaccount.GL_Account is null)
> GROUP BY
>   AP_Item.GL_Account,
>   AP_Item.Subaccount
> UNION
> SELECT
>   'AP_Item' as Tablename,
>   AP_Item.GL_Account,
>   '(none)' as Subaccount,
>   count(*) as DupCount
> FROM
>   (AP_Item
>     LEFT OUTER JOIN GL_AccountMaster
>       ON (GL_AccountMaster.GL_Account=AP_Item.GL_Account))
> WHERE
>   (AP_Item.GL_Account is not null)
>     and ((AP_Item.Subaccount is null) or (AP_Item.Subaccount=''))
>     and (GL_AccountMaster.GL_Account is null)
> GROUP BY
>   AP_Item.GL_Account
> UNION
> SELECT
>   'CB_Dist' as Tablename,
>   CB_Dist.GL_Account,
>   CB_Dist.Subaccount,
>   count(*) as DupCount
> FROM
>   (CB_Dist
>     LEFT OUTER JOIN GL_Subaccount
>       ON (GL_Subaccount.GL_Account=CB_Dist.GL_Account)
>         and (GL_Subaccount.Subaccount=CB_Dist.Subaccount))
> WHERE
>   (CB_Dist.GL_Account is not null)
>     and (CB_Dist.Subaccount is not null)
>     and (CB_Dist.Subaccount<>'')
>     and (GL_Subaccount.GL_Account is null)
> GROUP BY
>   CB_Dist.GL_Account,
>   CB_Dist.Subaccount
> UNION
> SELECT
>   'CB_Dist' as Tablename,
>   CB_Dist.GL_Account,
>   '(none)' as Subaccount,
>   count(*) as DupCount
> FROM
>   (CB_Dist
>     LEFT OUTER JOIN GL_AccountMaster
>       ON (GL_AccountMaster.GL_Account=CB_Dist.GL_Account))
> WHERE
>   (CB_Dist.GL_Account is not null)
>     and ((CB_Dist.Subaccount is null) or (CB_Dist.Subaccount=''))
>     and (GL_AccountMaster.GL_Account is null)
> GROUP BY
>   CB_Dist.GL_Account
> UNION
> SELECT
>   'CB_Register' as Tablename,
>   CB_Register.GL_Account,
>   CB_Register.Subaccount,
>   count(*) as DupCount
> FROM
>   (CB_Register
>     LEFT OUTER JOIN GL_Subaccount
>       ON (GL_Subaccount.GL_Account=CB_Register.GL_Account)
>         and (GL_Subaccount.Subaccount=CB_Register.Subaccount))
> WHERE
>   (CB_Register.GL_Account is not null)
>     and (CB_Register.Subaccount is not null)
>     and (CB_Register.Subaccount<>'')
>     and (GL_Subaccount.GL_Account is null)
> GROUP BY
>   CB_Register.GL_Account,
>   CB_Register.Subaccount
> UNION
> SELECT
>   'CB_Register' as Tablename,
>   CB_Register.GL_Account,
>   '(none)' as Subaccount,
>   count(*) as DupCount
> FROM
>   (CB_Register
>     LEFT OUTER JOIN GL_AccountMaster
>       ON (GL_AccountMaster.GL_Account=CB_Register.GL_Account))
> WHERE
>   (CB_Register.GL_Account is not null)
>     and ((CB_Register.Subaccount is null) or (CB_Register.Subaccount=''))
>     and (GL_AccountMaster.GL_Account is null)
> GROUP BY
>   CB_Register.GL_Account
> UNION
> SELECT
>   'CB_Statement' as Tablename,
>   CB_Statement.GL_Account,
>   CB_Statement.Subaccount,
>   count(*) as DupCount
> FROM
>   (CB_Statement
>     LEFT OUTER JOIN GL_Subaccount
>       ON (GL_Subaccount.GL_Account=CB_Statement.GL_Account)
>         and (GL_Subaccount.Subaccount=CB_Statement.Subaccount))
> WHERE
>   (CB_Statement.GL_Account is not null)
>     and (CB_Statement.Subaccount is not null)
>     and (CB_Statement.Subaccount<>'')
>     and (GL_Subaccount.GL_Account is null)
> GROUP BY
>   CB_Statement.GL_Account,
>   CB_Statement.Subaccount
> UNION
> SELECT
>   'CB_Statement' as Tablename,
>   CB_Statement.GL_Account,
>   '(none)' as Subaccount,
>   count(*) as DupCount
> FROM
>   (CB_Statement
>     LEFT OUTER JOIN GL_AccountMaster
>       ON (GL_AccountMaster.GL_Account=CB_Statement.GL_Account))
> WHERE
>   (CB_Statement.GL_Account is not null)
>     and ((CB_Statement.Subaccount is null) or
(CB_Statement.Subaccount=''))
>     and (GL_AccountMaster.GL_Account is null)
> GROUP BY
>   CB_Statement.GL_Account
> UNION
> SELECT
>   'ExpenseType' as Tablename,
>   ExpenseType.GL_Account,
>   ExpenseType.Subledger as Subaccount,
>   count(*) as DupCount
> FROM
>   (ExpenseType
>     LEFT OUTER JOIN GL_Subaccount
>       ON (GL_Subaccount.GL_Account=ExpenseType.GL_Account)
>         and (GL_Subaccount.Subaccount=ExpenseType.Subledger))
> WHERE
>   (ExpenseType.GL_Account is not null)
>     and (ExpenseType.Subledger is not null)
>     and (ExpenseType.Subledger<>'')
>     and (GL_Subaccount.GL_Account is null)
> GROUP BY
>   ExpenseType.GL_Account,
>   ExpenseType.Subledger
> UNION
> SELECT
>   'ExpenseType' as Tablename,
>   ExpenseType.GL_Account,
>   '(none)' as Subaccount,
>   count(*) as DupCount
> FROM
>   (ExpenseType
>     LEFT OUTER JOIN GL_AccountMaster
>       ON (GL_AccountMaster.GL_Account=ExpenseType.GL_Account))
> WHERE
>   (ExpenseType.GL_Account is not null)
>     and ((ExpenseType.Subledger is null) or (ExpenseType.Subledger=''))
>     and (GL_AccountMaster.GL_Account is null)
> GROUP BY
>   ExpenseType.GL_Account
> UNION
> SELECT
>   'GL_AccountDivision' as Tablename,
>   GL_AccountDivision.GL_Account,
>   '(n/a)' as Subaccount,
>   count(*) as DupCount
> FROM
>   (GL_AccountDivision
>     LEFT OUTER JOIN GL_AccountMaster
>       ON (GL_AccountMaster.GL_Account=GL_AccountDivision.GL_Account))
> WHERE
>   (GL_AccountDivision.GL_Account is not null)
>   and (GL_AccountMaster.GL_Account is null)
> GROUP BY
>   GL_AccountDivision.GL_Account
> UNION
> SELECT
>   'GL_Budget' as Tablename,
>   GL_Budget.GL_Account,
>   GL_Budget.Subaccount,
>   count(*) as DupCount
> FROM
>   (GL_Budget
>     LEFT OUTER JOIN GL_Subaccount
>       ON (GL_Subaccount.GL_Account=GL_Budget.GL_Account)
>         and (GL_Subaccount.Subaccount=GL_Budget.Subaccount))
> WHERE
>   (GL_Budget.GL_Account is not null)
>     and (GL_Budget.Subaccount is not null)
>     and (GL_Budget.Subaccount<>'')
>     and (GL_Subaccount.GL_Account is null)
> GROUP BY
>   GL_Budget.GL_Account,
>   GL_Budget.Subaccount
> UNION
> SELECT
>   'GL_Budget' as Tablename,
>   GL_Budget.GL_Account,
>   '(none)' as Subaccount,
>   count(*) as DupCount
> FROM
>   (GL_Budget
>     LEFT OUTER JOIN GL_AccountMaster
>       ON (GL_AccountMaster.GL_Account=GL_Budget.GL_Account))
> WHERE
>   (GL_Budget.GL_Account is not null)
>     and ((GL_Budget.Subaccount is null) or (GL_Budget.Subaccount=''))
>     and (GL_AccountMaster.GL_Account is null)
> GROUP BY
>   GL_Budget.GL_Account
> UNION
> SELECT
>   'GL_Default' as Tablename,
>   GL_Default.GL_Account,
>   GL_Default.Subaccount,
>   count(*) as DupCount
> FROM
>   (GL_Default
>     LEFT OUTER JOIN GL_Subaccount
>       ON (GL_Subaccount.GL_Account=GL_Default.GL_Account)
>         and (GL_Subaccount.Subaccount=GL_Default.Subaccount))
> WHERE
>   (GL_Default.GL_Account is not null)
>     and (GL_Default.Subaccount is not null)
>     and (GL_Default.Subaccount<>'')
>     and (GL_Subaccount.GL_Account is null)
> GROUP BY
>   GL_Default.GL_Account,
>   GL_Default.Subaccount
> UNION
> SELECT
>   'GL_Default' as Tablename,
>   GL_Default.GL_Account,
>   '(none)' as Subaccount,
>   count(*) as DupCount
> FROM
>   (GL_Default
>     LEFT OUTER JOIN GL_AccountMaster
>       ON (GL_AccountMaster.GL_Account=GL_Default.GL_Account))
> WHERE
>   (GL_Default.GL_Account is not null)
>     and ((GL_Default.Subaccount is null) or (GL_Default.Subaccount=''))
>     and (GL_AccountMaster.GL_Account is null)
> GROUP BY
>   GL_Default.GL_Account
> UNION
> SELECT
>   'GL_Journal' as Tablename,
>   GL_Journal.GL_Account,
>   GL_Journal.Subaccount,
>   count(*) as DupCount
> FROM
>   (GL_Journal
>     LEFT OUTER JOIN GL_Subaccount
>       ON (GL_Subaccount.GL_Account=GL_Journal.GL_Account)
>         and (GL_Subaccount.Subaccount=GL_Journal.Subaccount))
> WHERE
>   (GL_Journal.GL_Account is not null)
>     and (GL_Journal.Subaccount is not null)
>     and (GL_Journal.Subaccount<>'')
>     and (GL_Subaccount.GL_Account is null)
> GROUP BY
>   GL_Journal.GL_Account,
>   GL_Journal.Subaccount
> UNION
> SELECT
>   'GL_Journal' as Tablename,
>   GL_Journal.GL_Account,
>   '(none)' as Subaccount,
>   count(*) as DupCount
> FROM
>   (GL_Journal
>     LEFT OUTER JOIN GL_AccountMaster
>       ON (GL_AccountMaster.GL_Account=GL_Journal.GL_Account))
> WHERE
>   (GL_Journal.GL_Account is not null)
>     and ((GL_Journal.Subaccount is null) or (GL_Journal.Subaccount=''))
>     and (GL_AccountMaster.GL_Account is null)
> GROUP BY
>   GL_Journal.GL_Account
> UNION
> SELECT
>   'GL_Report' as Tablename,
>   GL_Report.GL_Account,
>   GL_Report.Subaccount,
>   count(*) as DupCount
> FROM
>   (GL_Report
>     LEFT OUTER JOIN GL_Subaccount
>       ON (GL_Subaccount.GL_Account=GL_Report.GL_Account)
>         and (GL_Subaccount.Subaccount=GL_Report.Subaccount))
> WHERE
>   (GL_Report.GL_Account is not null)
>     and (GL_Report.Subaccount is not null)
>     and (GL_Report.Subaccount<>'')
>     and (GL_Subaccount.GL_Account is null)
> GROUP BY
>   GL_Report.GL_Account,
>   GL_Report.Subaccount
> UNION
> SELECT
>   'GL_Report' as Tablename,
>   GL_Report.GL_Account,
>   '(none)' as Subaccount,
>   count(*) as DupCount
> FROM
>   (GL_Report
>     LEFT OUTER JOIN GL_AccountMaster
>       ON (GL_AccountMaster.GL_Account=GL_Report.GL_Account))
> WHERE
>   (GL_Report.GL_Account is not null)
>     and ((GL_Report.Subaccount is null) or (GL_Report.Subaccount=''))
>     and (GL_AccountMaster.GL_Account is null)
> GROUP BY
>   GL_Report.GL_Account
> UNION
> SELECT
>   'GL_Transaction' as Tablename,
>   GL_Transaction.GL_Account,
>   GL_Transaction.Subaccount,
>   count(*) as DupCount
> FROM
>   (GL_Transaction
>     LEFT OUTER JOIN GL_Subaccount
>       ON (GL_Subaccount.GL_Account=GL_Transaction.GL_Account)
>         and (GL_Subaccount.Subaccount=GL_Transaction.Subaccount))
> WHERE
>   (GL_Transaction.GL_Account is not null)
>     and (GL_Transaction.Subaccount is not null)
>     and (GL_Transaction.Subaccount<>'')
>     and (GL_Subaccount.GL_Account is null)
> GROUP BY
>   GL_Transaction.GL_Account,
>   GL_Transaction.Subaccount
> UNION
> SELECT
>   'GL_Transaction' as Tablename,
>   GL_Transaction.GL_Account,
>   '(none)' as Subaccount,
>   count(*) as DupCount
> FROM
>   (GL_Transaction
>     LEFT OUTER JOIN GL_AccountMaster
>       ON (GL_AccountMaster.GL_Account=GL_Transaction.GL_Account))
> WHERE
>   (GL_Transaction.GL_Account is not null)
>     and ((GL_Transaction.Subaccount is null) or
> (GL_Transaction.Subaccount=''))
>     and (GL_AccountMaster.GL_Account is null)
> GROUP BY
>   GL_Transaction.GL_Account
> UNION
> SELECT
>   'IC_Tag' as Tablename,
>   IC_Tag.GL_Account,
>   IC_Tag.Subaccount,
>   count(*) as DupCount
> FROM
>   (IC_Tag
>     LEFT OUTER JOIN GL_Subaccount
>       ON (GL_Subaccount.GL_Account=IC_Tag.GL_Account)
>         and (GL_Subaccount.Subaccount=IC_Tag.Subaccount))
> WHERE
>   (IC_Tag.GL_Account is not null)
>     and (IC_Tag.Subaccount is not null)
>     and (IC_Tag.Subaccount<>'')
>     and (GL_Subaccount.GL_Account is null)
> GROUP BY
>   IC_Tag.GL_Account,
>   IC_Tag.Subaccount
> UNION
> SELECT
>   'IC_Tag' as Tablename,
>   IC_Tag.GL_Account,
>   '(none)' as Subaccount,
>   count(*) as DupCount
> FROM
>   (IC_Tag
>     LEFT OUTER JOIN GL_AccountMaster
>       ON (GL_AccountMaster.GL_Account=IC_Tag.GL_Account))
> WHERE
>   (IC_Tag.GL_Account is not null)
>     and ((IC_Tag.Subaccount is null) or (IC_Tag.Subaccount=''))
>     and (GL_AccountMaster.GL_Account is null)
> GROUP BY
>   IC_Tag.GL_Account
> UNION
> SELECT
>   'IC_Transaction' as Tablename,
>   IC_Transaction.GL_Account,
>   IC_Transaction.Subaccount,
>   count(*) as DupCount
> FROM
>   (IC_Transaction
>     LEFT OUTER JOIN GL_Subaccount
>       ON (GL_Subaccount.GL_Account=IC_Transaction.GL_Account)
>         and (GL_Subaccount.Subaccount=IC_Transaction.Subaccount))
> WHERE
>   (IC_Transaction.GL_Account is not null)
>     and (IC_Transaction.Subaccount is not null)
>     and (IC_Transaction.Subaccount<>'')
>     and (GL_Subaccount.GL_Account is null)
> GROUP BY
>   IC_Transaction.GL_Account,
>   IC_Transaction.Subaccount
> UNION
> SELECT
>   'IC_Transaction' as Tablename,
>   IC_Transaction.GL_Account,
>   '(none)' as Subaccount,
>   count(*) as DupCount
> FROM
>   (IC_Transaction
>     LEFT OUTER JOIN GL_AccountMaster
>       ON (GL_AccountMaster.GL_Account=IC_Transaction.GL_Account))
> WHERE
>   (IC_Transaction.GL_Account is not null)
>     and ((IC_Transaction.Subaccount is null) or
> (IC_Transaction.Subaccount=''))
>     and (GL_AccountMaster.GL_Account is null)
> GROUP BY
>   IC_Transaction.GL_Account
> UNION
> SELECT
>   'IC_TrxControl' as Tablename,
>   IC_TrxControl.GL_Account,
>   IC_TrxControl.Subaccount,
>   count(*) as DupCount
> FROM
>   (IC_TrxControl
>     LEFT OUTER JOIN GL_Subaccount
>       ON (GL_Subaccount.GL_Account=IC_TrxControl.GL_Account)
>         and (GL_Subaccount.Subaccount=IC_TrxControl.Subaccount))
> WHERE
>   (IC_TrxControl.GL_Account is not null)
>     and (IC_TrxControl.Subaccount is not null)
>     and (IC_TrxControl.Subaccount<>'')
>     and (GL_Subaccount.GL_Account is null)
> GROUP BY
>   IC_TrxControl.GL_Account,
>   IC_TrxControl.Subaccount
> UNION
> SELECT
>   'IC_TrxControl' as Tablename,
>   IC_TrxControl.GL_Account,
>   '(none)' as Subaccount,
>   count(*) as DupCount
> FROM
>   (IC_TrxControl
>     LEFT OUTER JOIN GL_AccountMaster
>       ON (GL_AccountMaster.GL_Account=IC_TrxControl.GL_Account))
> WHERE
>   (IC_TrxControl.GL_Account is not null)
>     and ((IC_TrxControl.Subaccount is null) or
> (IC_TrxControl.Subaccount=''))
>     and (GL_AccountMaster.GL_Account is null)
> GROUP BY
>   IC_TrxControl.GL_Account
> UNION
> SELECT
>   'IC_TrxControlItem' as Tablename,
>   IC_TrxControlItem.GL_Account,
>   IC_TrxControlItem.Subaccount,
>   count(*) as DupCount
> FROM
>   (IC_TrxControlItem
>     LEFT OUTER JOIN GL_Subaccount
>       ON (GL_Subaccount.GL_Account=IC_TrxControlItem.GL_Account)
>         and (GL_Subaccount.Subaccount=IC_TrxControlItem.Subaccount))
> WHERE
>   (IC_TrxControlItem.GL_Account is not null)
>     and (IC_TrxControlItem.Subaccount is not null)
>     and (IC_TrxControlItem.Subaccount<>'')
>     and (GL_Subaccount.GL_Account is null)
> GROUP BY
>   IC_TrxControlItem.GL_Account,
>   IC_TrxControlItem.Subaccount
> UNION
> SELECT
>   'IC_TrxControlItem' as Tablename,
>   IC_TrxControlItem.GL_Account,
>   '(none)' as Subaccount,
>   count(*) as DupCount
> FROM
>   (IC_TrxControlItem
>     LEFT OUTER JOIN GL_AccountMaster
>       ON (GL_AccountMaster.GL_Account=IC_TrxControlItem.GL_Account))
> WHERE
>   (IC_TrxControlItem.GL_Account is not null)
>     and ((IC_TrxControlItem.Subaccount is null) or
> (IC_TrxControlItem.Subaccount=''))
>     and (GL_AccountMaster.GL_Account is null)
> GROUP BY
>   IC_TrxControlItem.GL_Account
> UNION
> SELECT
>   'InvoiceItem' as Tablename,
>   InvoiceItem.GL_Account,
>   InvoiceItem.Subledger as Subaccount,
>   count(*) as DupCount
> FROM
>   (InvoiceItem
>     LEFT OUTER JOIN GL_Subaccount
>       ON (GL_Subaccount.GL_Account=InvoiceItem.GL_Account)
>         and (GL_Subaccount.Subaccount=InvoiceItem.Subledger))
> WHERE
>   (InvoiceItem.GL_Account is not null)
>     and (InvoiceItem.Subledger is not null)
>     and (InvoiceItem.Subledger<>'')
>     and (GL_Subaccount.GL_Account is null)
> GROUP BY
>   InvoiceItem.GL_Account,
>   InvoiceItem.Subledger
> UNION
> SELECT
>   'InvoiceItem' as Tablename,
>   InvoiceItem.GL_Account,
>   '(none)' as Subaccount,
>   count(*) as DupCount
> FROM
>   (InvoiceItem
>     LEFT OUTER JOIN GL_AccountMaster
>       ON (GL_AccountMaster.GL_Account=InvoiceItem.GL_Account))
> WHERE
>   (InvoiceItem.GL_Account is not null)
>     and ((InvoiceItem.Subledger is null) or (InvoiceItem.Subledger=''))
>     and (GL_AccountMaster.GL_Account is null)
> GROUP BY
>   InvoiceItem.GL_Account
> UNION
> SELECT
>   'InvoiceItemDetail' as Tablename,
>   InvoiceItemDetail.GL_Account,
>   InvoiceItemDetail.Subaccount,
>   count(*) as DupCount
> FROM
>   (InvoiceItemDetail
>     LEFT OUTER JOIN GL_Subaccount
>       ON (GL_Subaccount.GL_Account=InvoiceItemDetail.GL_Account)
>         and (GL_Subaccount.Subaccount=InvoiceItemDetail.Subaccount))
> WHERE
>   (InvoiceItemDetail.GL_Account is not null)
>     and (InvoiceItemDetail.Subaccount is not null)
>     and (InvoiceItemDetail.Subaccount<>'')
>     and (GL_Subaccount.GL_Account is null)
> GROUP BY
>   InvoiceItemDetail.GL_Account,
>   InvoiceItemDetail.Subaccount
> UNION
> SELECT
>   'InvoiceItemDetail' as Tablename,
>   InvoiceItemDetail.GL_Account,
>   '(none)' as Subaccount,
>   count(*) as DupCount
> FROM
>   (InvoiceItemDetail
>     LEFT OUTER JOIN GL_AccountMaster
>       ON (GL_AccountMaster.GL_Account=InvoiceItemDetail.GL_Account))
> WHERE
>   (InvoiceItemDetail.GL_Account is not null)
>     and ((InvoiceItemDetail.Subaccount is null) or
> (InvoiceItemDetail.Subaccount=''))
>     and (GL_AccountMaster.GL_Account is null)
> GROUP BY
>   InvoiceItemDetail.GL_Account
> UNION
> SELECT
>   'PR_MiscWage' as Tablename,
>   PR_MiscWage.GL_Account,
>   PR_MiscWage.Subaccount,
>   count(*) as DupCount
> FROM
>   (PR_MiscWage
>     LEFT OUTER JOIN GL_Subaccount
>       ON (GL_Subaccount.GL_Account=PR_MiscWage.GL_Account)
>         and (GL_Subaccount.Subaccount=PR_MiscWage.Subaccount))
> WHERE
>   (PR_MiscWage.GL_Account is not null)
>     and (PR_MiscWage.Subaccount is not null)
>     and (PR_MiscWage.Subaccount<>'')
>     and (GL_Subaccount.GL_Account is null)
> GROUP BY
>   PR_MiscWage.GL_Account,
>   PR_MiscWage.Subaccount
> UNION
> SELECT
>   'PR_MiscWage' as Tablename,
>   PR_MiscWage.GL_Account,
>   '(none)' as Subaccount,
>   count(*) as DupCount
> FROM
>   (PR_MiscWage
>     LEFT OUTER JOIN GL_AccountMaster
>       ON (GL_AccountMaster.GL_Account=PR_MiscWage.GL_Account))
> WHERE
>   (PR_MiscWage.GL_Account is not null)
>     and ((PR_MiscWage.Subaccount is null) or (PR_MiscWage.Subaccount=''))
>     and (GL_AccountMaster.GL_Account is null)
> GROUP BY
>   PR_MiscWage.GL_Account
> UNION
> SELECT
>   'PR_WageSummary' as Tablename,
>   PR_WageSummary.GL_Account,
>   PR_WageSummary.Subaccount,
>   count(*) as DupCount
> FROM
>   (PR_WageSummary
>     LEFT OUTER JOIN GL_Subaccount
>       ON (GL_Subaccount.GL_Account=PR_WageSummary.GL_Account)
>         and (GL_Subaccount.Subaccount=PR_WageSummary.Subaccount))
> WHERE
>   (PR_WageSummary.GL_Account is not null)
>     and (PR_WageSummary.Subaccount is not null)
>     and (PR_WageSummary.Subaccount<>'')
>     and (GL_Subaccount.GL_Account is null)
> GROUP BY
>   PR_WageSummary.GL_Account,
>   PR_WageSummary.Subaccount
> UNION
> SELECT
>   'PR_WageSummary' as Tablename,
>   PR_WageSummary.GL_Account,
>   '(none)' as Subaccount,
>   count(*) as DupCount
> FROM
>   (PR_WageSummary
>     LEFT OUTER JOIN GL_AccountMaster
>       ON (GL_AccountMaster.GL_Account=PR_WageSummary.GL_Account))
> WHERE
>   (PR_WageSummary.GL_Account is not null)
>     and ((PR_WageSummary.Subaccount is null) or
> (PR_WageSummary.Subaccount=''))
>     and (GL_AccountMaster.GL_Account is null)
> GROUP BY
>   PR_WageSummary.GL_Account
> UNION
> SELECT
>   'PurchaseOrderItem' as Tablename,
>   PurchaseOrderItem.GL_Account,
>   PurchaseOrderItem.Subaccount,
>   count(*) as DupCount
> FROM
>   (PurchaseOrderItem
>     LEFT OUTER JOIN GL_Subaccount
>       ON (GL_Subaccount.GL_Account=PurchaseOrderItem.GL_Account)
>         and (GL_Subaccount.Subaccount=PurchaseOrderItem.Subaccount))
> WHERE
>   (PurchaseOrderItem.GL_Account is not null)
>     and (PurchaseOrderItem.Subaccount is not null)
>     and (PurchaseOrderItem.Subaccount<>'')
>     and (GL_Subaccount.GL_Account is null)
> GROUP BY
>   PurchaseOrderItem.GL_Account,
>   PurchaseOrderItem.Subaccount
> UNION
> SELECT
>   'PurchaseOrderItem' as Tablename,
>   PurchaseOrderItem.GL_Account,
>   '(none)' as Subaccount,
>   count(*) as DupCount
> FROM
>   (PurchaseOrderItem
>     LEFT OUTER JOIN GL_AccountMaster
>       ON (GL_AccountMaster.GL_Account=PurchaseOrderItem.GL_Account))
> WHERE
>   (PurchaseOrderItem.GL_Account is not null)
>     and ((PurchaseOrderItem.Subaccount is null) or
> (PurchaseOrderItem.Subaccount=''))
>     and (GL_AccountMaster.GL_Account is null)
> GROUP BY
>   PurchaseOrderItem.GL_Account
> UNION
> SELECT
>   'SaleType' as Tablename,
>   SaleType.GL_Account,
>   SaleType.Subledger as Subaccount,
>   count(*) as DupCount
> FROM
>   (SaleType
>     LEFT OUTER JOIN GL_Subaccount
>       ON (GL_Subaccount.GL_Account=SaleType.GL_Account)
>         and (GL_Subaccount.Subaccount=SaleType.Subledger))
> WHERE
>   (SaleType.GL_Account is not null)
>     and (SaleType.Subledger is not null)
>     and (SaleType.Subledger<>'')
>     and (GL_Subaccount.GL_Account is null)
> GROUP BY
>   SaleType.GL_Account,
>   SaleType.Subledger
> UNION
> SELECT
>   'SaleType' as Tablename,
>   SaleType.GL_Account,
>   '(none)' as Subaccount,
>   count(*) as DupCount
> FROM
>   (SaleType
>     LEFT OUTER JOIN GL_AccountMaster
>       ON (GL_AccountMaster.GL_Account=SaleType.GL_Account))
> WHERE
>   (SaleType.GL_Account is not null)
>     and ((SaleType.Subledger is null) or (SaleType.Subledger=''))
>     and (GL_AccountMaster.GL_Account is null)
> GROUP BY
>   SaleType.GL_Account
> UNION
> SELECT
>   'TimecardEntry' as Tablename,
>   TimecardEntry.GL_Account,
>   TimecardEntry.Subaccount,
>   count(*) as DupCount
> FROM
>   (TimecardEntry
>     LEFT OUTER JOIN GL_Subaccount
>       ON (GL_Subaccount.GL_Account=TimecardEntry.GL_Account)
>         and (GL_Subaccount.Subaccount=TimecardEntry.Subaccount))
> WHERE
>   (TimecardEntry.GL_Account is not null)
>     and (TimecardEntry.Subaccount is not null)
>     and (TimecardEntry.Subaccount<>'')
>     and (GL_Subaccount.GL_Account is null)
> GROUP BY
>   TimecardEntry.GL_Account,
>   TimecardEntry.Subaccount
> UNION
> SELECT
>   'TimecardEntry' as Tablename,
>   TimecardEntry.GL_Account,
>   '(none)' as Subaccount,
>   count(*) as DupCount
> FROM
>   (TimecardEntry
>     LEFT OUTER JOIN GL_AccountMaster
>       ON (GL_AccountMaster.GL_Account=TimecardEntry.GL_Account))
> WHERE
>   (TimecardEntry.GL_Account is not null)
>     and ((TimecardEntry.Subaccount is null) or
> (TimecardEntry.Subaccount=''))
>     and (GL_AccountMaster.GL_Account is null)
> GROUP BY
>   TimecardEntry.GL_Account
>
>
>

16 May 2005 3:19 PM

Bob Castleman

We have about 180 of our customers in a Citrix Farm and Clustered SQL
Servers. I would rather the query was run during a maintenance window.

Show quote

"JT" <some***@microsoft.com> wrote in message
news:eGO25MiWFHA.2128@TK2MSFTNGP15.phx.gbl...
> Developers need to run complex queries from time to time to fullfill
> special
> ad-hoc reporting requirements, and this is normal. Sometimes the deadline
> for providing the results leaves little time for optimization and the
> query
> will only be run once. What you need is a reporting server.
>
> When you say your hosted environment, do you mean that your SQL Server is
> hosted by an ISP on a shared or dedicated box?
>
> "Bob Castleman" <nomail@here> wrote in message
> news:OaQsRbhWFHA.3140@TK2MSFTNGP14.phx.gbl...
>> Below is a query written by one of the developers here. He showed in to
> me,
>> I think as a joke, because he was going to run it in our hosted
> environment
>> against all of our customers. Ichoked, because it was such a long query,
> but
>> it really isn't terribly complex after a look at it. But I was curious if
>> there was a better way to accomplish what it does.
>>
>> Including DDL is impractical, but I think it's pretty clear without it.
>> It
>> is looking for problems in general ledger references.
>>
>> SELECT
>>   'GL_Subaccount' as Tablename,
>>   GL_Subaccount.GL_Account,
>>   '(n/a)' as Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (GL_Subaccount
>>     LEFT OUTER JOIN GL_AccountMaster
>>       ON (GL_AccountMaster.GL_Account=GL_Subaccount.GL_Account))
>> WHERE
>>   (GL_Subaccount.GL_Account is not null)
>>   and (GL_AccountMaster.GL_Account is null)
>> GROUP BY
>>   GL_Subaccount.GL_Account
>> UNION
>> SELECT
>>   'AdjustCode' as Tablename,
>>   AdjustCode.GL_Account,
>>   AdjustCode.Subledger as Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (AdjustCode
>>     LEFT OUTER JOIN GL_Subaccount
>>       ON (GL_Subaccount.GL_Account=AdjustCode.GL_Account)
>>         and (GL_Subaccount.Subaccount=AdjustCode.Subledger))
>> WHERE
>>   (AdjustCode.GL_Account is not null)
>>     and (AdjustCode.Subledger is not null)
>>     and (AdjustCode.Subledger<>'')
>>     and (GL_Subaccount.GL_Account is null)
>> GROUP BY
>>   AdjustCode.GL_Account,
>>   AdjustCode.Subledger
>> UNION
>> SELECT
>>   'AdjustCode' as Tablename,
>>   AdjustCode.GL_Account,
>>   '(none)' as Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (AdjustCode
>>     LEFT OUTER JOIN GL_AccountMaster
>>       ON (GL_AccountMaster.GL_Account=AdjustCode.GL_Account))
>> WHERE
>>   (AdjustCode.GL_Account is not null)
>>     and ((AdjustCode.Subledger is null) or (AdjustCode.Subledger=''))
>>     and (GL_AccountMaster.GL_Account is null)
>> GROUP BY
>>   AdjustCode.GL_Account
>> UNION
>> SELECT
>>   'AP_Item' as Tablename,
>>   AP_Item.GL_Account,
>>   AP_Item.Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (AP_Item
>>     LEFT OUTER JOIN GL_Subaccount
>>       ON (GL_Subaccount.GL_Account=AP_Item.GL_Account)
>>         and (GL_Subaccount.Subaccount=AP_Item.Subaccount))
>> WHERE
>>   (AP_Item.GL_Account is not null)
>>     and (AP_Item.Subaccount is not null)
>>     and (AP_Item.Subaccount<>'')
>>     and (GL_Subaccount.GL_Account is null)
>> GROUP BY
>>   AP_Item.GL_Account,
>>   AP_Item.Subaccount
>> UNION
>> SELECT
>>   'AP_Item' as Tablename,
>>   AP_Item.GL_Account,
>>   '(none)' as Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (AP_Item
>>     LEFT OUTER JOIN GL_AccountMaster
>>       ON (GL_AccountMaster.GL_Account=AP_Item.GL_Account))
>> WHERE
>>   (AP_Item.GL_Account is not null)
>>     and ((AP_Item.Subaccount is null) or (AP_Item.Subaccount=''))
>>     and (GL_AccountMaster.GL_Account is null)
>> GROUP BY
>>   AP_Item.GL_Account
>> UNION
>> SELECT
>>   'CB_Dist' as Tablename,
>>   CB_Dist.GL_Account,
>>   CB_Dist.Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (CB_Dist
>>     LEFT OUTER JOIN GL_Subaccount
>>       ON (GL_Subaccount.GL_Account=CB_Dist.GL_Account)
>>         and (GL_Subaccount.Subaccount=CB_Dist.Subaccount))
>> WHERE
>>   (CB_Dist.GL_Account is not null)
>>     and (CB_Dist.Subaccount is not null)
>>     and (CB_Dist.Subaccount<>'')
>>     and (GL_Subaccount.GL_Account is null)
>> GROUP BY
>>   CB_Dist.GL_Account,
>>   CB_Dist.Subaccount
>> UNION
>> SELECT
>>   'CB_Dist' as Tablename,
>>   CB_Dist.GL_Account,
>>   '(none)' as Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (CB_Dist
>>     LEFT OUTER JOIN GL_AccountMaster
>>       ON (GL_AccountMaster.GL_Account=CB_Dist.GL_Account))
>> WHERE
>>   (CB_Dist.GL_Account is not null)
>>     and ((CB_Dist.Subaccount is null) or (CB_Dist.Subaccount=''))
>>     and (GL_AccountMaster.GL_Account is null)
>> GROUP BY
>>   CB_Dist.GL_Account
>> UNION
>> SELECT
>>   'CB_Register' as Tablename,
>>   CB_Register.GL_Account,
>>   CB_Register.Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (CB_Register
>>     LEFT OUTER JOIN GL_Subaccount
>>       ON (GL_Subaccount.GL_Account=CB_Register.GL_Account)
>>         and (GL_Subaccount.Subaccount=CB_Register.Subaccount))
>> WHERE
>>   (CB_Register.GL_Account is not null)
>>     and (CB_Register.Subaccount is not null)
>>     and (CB_Register.Subaccount<>'')
>>     and (GL_Subaccount.GL_Account is null)
>> GROUP BY
>>   CB_Register.GL_Account,
>>   CB_Register.Subaccount
>> UNION
>> SELECT
>>   'CB_Register' as Tablename,
>>   CB_Register.GL_Account,
>>   '(none)' as Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (CB_Register
>>     LEFT OUTER JOIN GL_AccountMaster
>>       ON (GL_AccountMaster.GL_Account=CB_Register.GL_Account))
>> WHERE
>>   (CB_Register.GL_Account is not null)
>>     and ((CB_Register.Subaccount is null) or (CB_Register.Subaccount=''))
>>     and (GL_AccountMaster.GL_Account is null)
>> GROUP BY
>>   CB_Register.GL_Account
>> UNION
>> SELECT
>>   'CB_Statement' as Tablename,
>>   CB_Statement.GL_Account,
>>   CB_Statement.Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (CB_Statement
>>     LEFT OUTER JOIN GL_Subaccount
>>       ON (GL_Subaccount.GL_Account=CB_Statement.GL_Account)
>>         and (GL_Subaccount.Subaccount=CB_Statement.Subaccount))
>> WHERE
>>   (CB_Statement.GL_Account is not null)
>>     and (CB_Statement.Subaccount is not null)
>>     and (CB_Statement.Subaccount<>'')
>>     and (GL_Subaccount.GL_Account is null)
>> GROUP BY
>>   CB_Statement.GL_Account,
>>   CB_Statement.Subaccount
>> UNION
>> SELECT
>>   'CB_Statement' as Tablename,
>>   CB_Statement.GL_Account,
>>   '(none)' as Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (CB_Statement
>>     LEFT OUTER JOIN GL_AccountMaster
>>       ON (GL_AccountMaster.GL_Account=CB_Statement.GL_Account))
>> WHERE
>>   (CB_Statement.GL_Account is not null)
>>     and ((CB_Statement.Subaccount is null) or
> (CB_Statement.Subaccount=''))
>>     and (GL_AccountMaster.GL_Account is null)
>> GROUP BY
>>   CB_Statement.GL_Account
>> UNION
>> SELECT
>>   'ExpenseType' as Tablename,
>>   ExpenseType.GL_Account,
>>   ExpenseType.Subledger as Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (ExpenseType
>>     LEFT OUTER JOIN GL_Subaccount
>>       ON (GL_Subaccount.GL_Account=ExpenseType.GL_Account)
>>         and (GL_Subaccount.Subaccount=ExpenseType.Subledger))
>> WHERE
>>   (ExpenseType.GL_Account is not null)
>>     and (ExpenseType.Subledger is not null)
>>     and (ExpenseType.Subledger<>'')
>>     and (GL_Subaccount.GL_Account is null)
>> GROUP BY
>>   ExpenseType.GL_Account,
>>   ExpenseType.Subledger
>> UNION
>> SELECT
>>   'ExpenseType' as Tablename,
>>   ExpenseType.GL_Account,
>>   '(none)' as Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (ExpenseType
>>     LEFT OUTER JOIN GL_AccountMaster
>>       ON (GL_AccountMaster.GL_Account=ExpenseType.GL_Account))
>> WHERE
>>   (ExpenseType.GL_Account is not null)
>>     and ((ExpenseType.Subledger is null) or (ExpenseType.Subledger=''))
>>     and (GL_AccountMaster.GL_Account is null)
>> GROUP BY
>>   ExpenseType.GL_Account
>> UNION
>> SELECT
>>   'GL_AccountDivision' as Tablename,
>>   GL_AccountDivision.GL_Account,
>>   '(n/a)' as Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (GL_AccountDivision
>>     LEFT OUTER JOIN GL_AccountMaster
>>       ON (GL_AccountMaster.GL_Account=GL_AccountDivision.GL_Account))
>> WHERE
>>   (GL_AccountDivision.GL_Account is not null)
>>   and (GL_AccountMaster.GL_Account is null)
>> GROUP BY
>>   GL_AccountDivision.GL_Account
>> UNION
>> SELECT
>>   'GL_Budget' as Tablename,
>>   GL_Budget.GL_Account,
>>   GL_Budget.Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (GL_Budget
>>     LEFT OUTER JOIN GL_Subaccount
>>       ON (GL_Subaccount.GL_Account=GL_Budget.GL_Account)
>>         and (GL_Subaccount.Subaccount=GL_Budget.Subaccount))
>> WHERE
>>   (GL_Budget.GL_Account is not null)
>>     and (GL_Budget.Subaccount is not null)
>>     and (GL_Budget.Subaccount<>'')
>>     and (GL_Subaccount.GL_Account is null)
>> GROUP BY
>>   GL_Budget.GL_Account,
>>   GL_Budget.Subaccount
>> UNION
>> SELECT
>>   'GL_Budget' as Tablename,
>>   GL_Budget.GL_Account,
>>   '(none)' as Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (GL_Budget
>>     LEFT OUTER JOIN GL_AccountMaster
>>       ON (GL_AccountMaster.GL_Account=GL_Budget.GL_Account))
>> WHERE
>>   (GL_Budget.GL_Account is not null)
>>     and ((GL_Budget.Subaccount is null) or (GL_Budget.Subaccount=''))
>>     and (GL_AccountMaster.GL_Account is null)
>> GROUP BY
>>   GL_Budget.GL_Account
>> UNION
>> SELECT
>>   'GL_Default' as Tablename,
>>   GL_Default.GL_Account,
>>   GL_Default.Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (GL_Default
>>     LEFT OUTER JOIN GL_Subaccount
>>       ON (GL_Subaccount.GL_Account=GL_Default.GL_Account)
>>         and (GL_Subaccount.Subaccount=GL_Default.Subaccount))
>> WHERE
>>   (GL_Default.GL_Account is not null)
>>     and (GL_Default.Subaccount is not null)
>>     and (GL_Default.Subaccount<>'')
>>     and (GL_Subaccount.GL_Account is null)
>> GROUP BY
>>   GL_Default.GL_Account,
>>   GL_Default.Subaccount
>> UNION
>> SELECT
>>   'GL_Default' as Tablename,
>>   GL_Default.GL_Account,
>>   '(none)' as Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (GL_Default
>>     LEFT OUTER JOIN GL_AccountMaster
>>       ON (GL_AccountMaster.GL_Account=GL_Default.GL_Account))
>> WHERE
>>   (GL_Default.GL_Account is not null)
>>     and ((GL_Default.Subaccount is null) or (GL_Default.Subaccount=''))
>>     and (GL_AccountMaster.GL_Account is null)
>> GROUP BY
>>   GL_Default.GL_Account
>> UNION
>> SELECT
>>   'GL_Journal' as Tablename,
>>   GL_Journal.GL_Account,
>>   GL_Journal.Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (GL_Journal
>>     LEFT OUTER JOIN GL_Subaccount
>>       ON (GL_Subaccount.GL_Account=GL_Journal.GL_Account)
>>         and (GL_Subaccount.Subaccount=GL_Journal.Subaccount))
>> WHERE
>>   (GL_Journal.GL_Account is not null)
>>     and (GL_Journal.Subaccount is not null)
>>     and (GL_Journal.Subaccount<>'')
>>     and (GL_Subaccount.GL_Account is null)
>> GROUP BY
>>   GL_Journal.GL_Account,
>>   GL_Journal.Subaccount
>> UNION
>> SELECT
>>   'GL_Journal' as Tablename,
>>   GL_Journal.GL_Account,
>>   '(none)' as Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (GL_Journal
>>     LEFT OUTER JOIN GL_AccountMaster
>>       ON (GL_AccountMaster.GL_Account=GL_Journal.GL_Account))
>> WHERE
>>   (GL_Journal.GL_Account is not null)
>>     and ((GL_Journal.Subaccount is null) or (GL_Journal.Subaccount=''))
>>     and (GL_AccountMaster.GL_Account is null)
>> GROUP BY
>>   GL_Journal.GL_Account
>> UNION
>> SELECT
>>   'GL_Report' as Tablename,
>>   GL_Report.GL_Account,
>>   GL_Report.Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (GL_Report
>>     LEFT OUTER JOIN GL_Subaccount
>>       ON (GL_Subaccount.GL_Account=GL_Report.GL_Account)
>>         and (GL_Subaccount.Subaccount=GL_Report.Subaccount))
>> WHERE
>>   (GL_Report.GL_Account is not null)
>>     and (GL_Report.Subaccount is not null)
>>     and (GL_Report.Subaccount<>'')
>>     and (GL_Subaccount.GL_Account is null)
>> GROUP BY
>>   GL_Report.GL_Account,
>>   GL_Report.Subaccount
>> UNION
>> SELECT
>>   'GL_Report' as Tablename,
>>   GL_Report.GL_Account,
>>   '(none)' as Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (GL_Report
>>     LEFT OUTER JOIN GL_AccountMaster
>>       ON (GL_AccountMaster.GL_Account=GL_Report.GL_Account))
>> WHERE
>>   (GL_Report.GL_Account is not null)
>>     and ((GL_Report.Subaccount is null) or (GL_Report.Subaccount=''))
>>     and (GL_AccountMaster.GL_Account is null)
>> GROUP BY
>>   GL_Report.GL_Account
>> UNION
>> SELECT
>>   'GL_Transaction' as Tablename,
>>   GL_Transaction.GL_Account,
>>   GL_Transaction.Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (GL_Transaction
>>     LEFT OUTER JOIN GL_Subaccount
>>       ON (GL_Subaccount.GL_Account=GL_Transaction.GL_Account)
>>         and (GL_Subaccount.Subaccount=GL_Transaction.Subaccount))
>> WHERE
>>   (GL_Transaction.GL_Account is not null)
>>     and (GL_Transaction.Subaccount is not null)
>>     and (GL_Transaction.Subaccount<>'')
>>     and (GL_Subaccount.GL_Account is null)
>> GROUP BY
>>   GL_Transaction.GL_Account,
>>   GL_Transaction.Subaccount
>> UNION
>> SELECT
>>   'GL_Transaction' as Tablename,
>>   GL_Transaction.GL_Account,
>>   '(none)' as Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (GL_Transaction
>>     LEFT OUTER JOIN GL_AccountMaster
>>       ON (GL_AccountMaster.GL_Account=GL_Transaction.GL_Account))
>> WHERE
>>   (GL_Transaction.GL_Account is not null)
>>     and ((GL_Transaction.Subaccount is null) or
>> (GL_Transaction.Subaccount=''))
>>     and (GL_AccountMaster.GL_Account is null)
>> GROUP BY
>>   GL_Transaction.GL_Account
>> UNION
>> SELECT
>>   'IC_Tag' as Tablename,
>>   IC_Tag.GL_Account,
>>   IC_Tag.Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (IC_Tag
>>     LEFT OUTER JOIN GL_Subaccount
>>       ON (GL_Subaccount.GL_Account=IC_Tag.GL_Account)
>>         and (GL_Subaccount.Subaccount=IC_Tag.Subaccount))
>> WHERE
>>   (IC_Tag.GL_Account is not null)
>>     and (IC_Tag.Subaccount is not null)
>>     and (IC_Tag.Subaccount<>'')
>>     and (GL_Subaccount.GL_Account is null)
>> GROUP BY
>>   IC_Tag.GL_Account,
>>   IC_Tag.Subaccount
>> UNION
>> SELECT
>>   'IC_Tag' as Tablename,
>>   IC_Tag.GL_Account,
>>   '(none)' as Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (IC_Tag
>>     LEFT OUTER JOIN GL_AccountMaster
>>       ON (GL_AccountMaster.GL_Account=IC_Tag.GL_Account))
>> WHERE
>>   (IC_Tag.GL_Account is not null)
>>     and ((IC_Tag.Subaccount is null) or (IC_Tag.Subaccount=''))
>>     and (GL_AccountMaster.GL_Account is null)
>> GROUP BY
>>   IC_Tag.GL_Account
>> UNION
>> SELECT
>>   'IC_Transaction' as Tablename,
>>   IC_Transaction.GL_Account,
>>   IC_Transaction.Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (IC_Transaction
>>     LEFT OUTER JOIN GL_Subaccount
>>       ON (GL_Subaccount.GL_Account=IC_Transaction.GL_Account)
>>         and (GL_Subaccount.Subaccount=IC_Transaction.Subaccount))
>> WHERE
>>   (IC_Transaction.GL_Account is not null)
>>     and (IC_Transaction.Subaccount is not null)
>>     and (IC_Transaction.Subaccount<>'')
>>     and (GL_Subaccount.GL_Account is null)
>> GROUP BY
>>   IC_Transaction.GL_Account,
>>   IC_Transaction.Subaccount
>> UNION
>> SELECT
>>   'IC_Transaction' as Tablename,
>>   IC_Transaction.GL_Account,
>>   '(none)' as Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (IC_Transaction
>>     LEFT OUTER JOIN GL_AccountMaster
>>       ON (GL_AccountMaster.GL_Account=IC_Transaction.GL_Account))
>> WHERE
>>   (IC_Transaction.GL_Account is not null)
>>     and ((IC_Transaction.Subaccount is null) or
>> (IC_Transaction.Subaccount=''))
>>     and (GL_AccountMaster.GL_Account is null)
>> GROUP BY
>>   IC_Transaction.GL_Account
>> UNION
>> SELECT
>>   'IC_TrxControl' as Tablename,
>>   IC_TrxControl.GL_Account,
>>   IC_TrxControl.Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (IC_TrxControl
>>     LEFT OUTER JOIN GL_Subaccount
>>       ON (GL_Subaccount.GL_Account=IC_TrxControl.GL_Account)
>>         and (GL_Subaccount.Subaccount=IC_TrxControl.Subaccount))
>> WHERE
>>   (IC_TrxControl.GL_Account is not null)
>>     and (IC_TrxControl.Subaccount is not null)
>>     and (IC_TrxControl.Subaccount<>'')
>>     and (GL_Subaccount.GL_Account is null)
>> GROUP BY
>>   IC_TrxControl.GL_Account,
>>   IC_TrxControl.Subaccount
>> UNION
>> SELECT
>>   'IC_TrxControl' as Tablename,
>>   IC_TrxControl.GL_Account,
>>   '(none)' as Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (IC_TrxControl
>>     LEFT OUTER JOIN GL_AccountMaster
>>       ON (GL_AccountMaster.GL_Account=IC_TrxControl.GL_Account))
>> WHERE
>>   (IC_TrxControl.GL_Account is not null)
>>     and ((IC_TrxControl.Subaccount is null) or
>> (IC_TrxControl.Subaccount=''))
>>     and (GL_AccountMaster.GL_Account is null)
>> GROUP BY
>>   IC_TrxControl.GL_Account
>> UNION
>> SELECT
>>   'IC_TrxControlItem' as Tablename,
>>   IC_TrxControlItem.GL_Account,
>>   IC_TrxControlItem.Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (IC_TrxControlItem
>>     LEFT OUTER JOIN GL_Subaccount
>>       ON (GL_Subaccount.GL_Account=IC_TrxControlItem.GL_Account)
>>         and (GL_Subaccount.Subaccount=IC_TrxControlItem.Subaccount))
>> WHERE
>>   (IC_TrxControlItem.GL_Account is not null)
>>     and (IC_TrxControlItem.Subaccount is not null)
>>     and (IC_TrxControlItem.Subaccount<>'')
>>     and (GL_Subaccount.GL_Account is null)
>> GROUP BY
>>   IC_TrxControlItem.GL_Account,
>>   IC_TrxControlItem.Subaccount
>> UNION
>> SELECT
>>   'IC_TrxControlItem' as Tablename,
>>   IC_TrxControlItem.GL_Account,
>>   '(none)' as Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (IC_TrxControlItem
>>     LEFT OUTER JOIN GL_AccountMaster
>>       ON (GL_AccountMaster.GL_Account=IC_TrxControlItem.GL_Account))
>> WHERE
>>   (IC_TrxControlItem.GL_Account is not null)
>>     and ((IC_TrxControlItem.Subaccount is null) or
>> (IC_TrxControlItem.Subaccount=''))
>>     and (GL_AccountMaster.GL_Account is null)
>> GROUP BY
>>   IC_TrxControlItem.GL_Account
>> UNION
>> SELECT
>>   'InvoiceItem' as Tablename,
>>   InvoiceItem.GL_Account,
>>   InvoiceItem.Subledger as Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (InvoiceItem
>>     LEFT OUTER JOIN GL_Subaccount
>>       ON (GL_Subaccount.GL_Account=InvoiceItem.GL_Account)
>>         and (GL_Subaccount.Subaccount=InvoiceItem.Subledger))
>> WHERE
>>   (InvoiceItem.GL_Account is not null)
>>     and (InvoiceItem.Subledger is not null)
>>     and (InvoiceItem.Subledger<>'')
>>     and (GL_Subaccount.GL_Account is null)
>> GROUP BY
>>   InvoiceItem.GL_Account,
>>   InvoiceItem.Subledger
>> UNION
>> SELECT
>>   'InvoiceItem' as Tablename,
>>   InvoiceItem.GL_Account,
>>   '(none)' as Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (InvoiceItem
>>     LEFT OUTER JOIN GL_AccountMaster
>>       ON (GL_AccountMaster.GL_Account=InvoiceItem.GL_Account))
>> WHERE
>>   (InvoiceItem.GL_Account is not null)
>>     and ((InvoiceItem.Subledger is null) or (InvoiceItem.Subledger=''))
>>     and (GL_AccountMaster.GL_Account is null)
>> GROUP BY
>>   InvoiceItem.GL_Account
>> UNION
>> SELECT
>>   'InvoiceItemDetail' as Tablename,
>>   InvoiceItemDetail.GL_Account,
>>   InvoiceItemDetail.Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (InvoiceItemDetail
>>     LEFT OUTER JOIN GL_Subaccount
>>       ON (GL_Subaccount.GL_Account=InvoiceItemDetail.GL_Account)
>>         and (GL_Subaccount.Subaccount=InvoiceItemDetail.Subaccount))
>> WHERE
>>   (InvoiceItemDetail.GL_Account is not null)
>>     and (InvoiceItemDetail.Subaccount is not null)
>>     and (InvoiceItemDetail.Subaccount<>'')
>>     and (GL_Subaccount.GL_Account is null)
>> GROUP BY
>>   InvoiceItemDetail.GL_Account,
>>   InvoiceItemDetail.Subaccount
>> UNION
>> SELECT
>>   'InvoiceItemDetail' as Tablename,
>>   InvoiceItemDetail.GL_Account,
>>   '(none)' as Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (InvoiceItemDetail
>>     LEFT OUTER JOIN GL_AccountMaster
>>       ON (GL_AccountMaster.GL_Account=InvoiceItemDetail.GL_Account))
>> WHERE
>>   (InvoiceItemDetail.GL_Account is not null)
>>     and ((InvoiceItemDetail.Subaccount is null) or
>> (InvoiceItemDetail.Subaccount=''))
>>     and (GL_AccountMaster.GL_Account is null)
>> GROUP BY
>>   InvoiceItemDetail.GL_Account
>> UNION
>> SELECT
>>   'PR_MiscWage' as Tablename,
>>   PR_MiscWage.GL_Account,
>>   PR_MiscWage.Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (PR_MiscWage
>>     LEFT OUTER JOIN GL_Subaccount
>>       ON (GL_Subaccount.GL_Account=PR_MiscWage.GL_Account)
>>         and (GL_Subaccount.Subaccount=PR_MiscWage.Subaccount))
>> WHERE
>>   (PR_MiscWage.GL_Account is not null)
>>     and (PR_MiscWage.Subaccount is not null)
>>     and (PR_MiscWage.Subaccount<>'')
>>     and (GL_Subaccount.GL_Account is null)
>> GROUP BY
>>   PR_MiscWage.GL_Account,
>>   PR_MiscWage.Subaccount
>> UNION
>> SELECT
>>   'PR_MiscWage' as Tablename,
>>   PR_MiscWage.GL_Account,
>>   '(none)' as Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (PR_MiscWage
>>     LEFT OUTER JOIN GL_AccountMaster
>>       ON (GL_AccountMaster.GL_Account=PR_MiscWage.GL_Account))
>> WHERE
>>   (PR_MiscWage.GL_Account is not null)
>>     and ((PR_MiscWage.Subaccount is null) or (PR_MiscWage.Subaccount=''))
>>     and (GL_AccountMaster.GL_Account is null)
>> GROUP BY
>>   PR_MiscWage.GL_Account
>> UNION
>> SELECT
>>   'PR_WageSummary' as Tablename,
>>   PR_WageSummary.GL_Account,
>>   PR_WageSummary.Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (PR_WageSummary
>>     LEFT OUTER JOIN GL_Subaccount
>>       ON (GL_Subaccount.GL_Account=PR_WageSummary.GL_Account)
>>         and (GL_Subaccount.Subaccount=PR_WageSummary.Subaccount))
>> WHERE
>>   (PR_WageSummary.GL_Account is not null)
>>     and (PR_WageSummary.Subaccount is not null)
>>     and (PR_WageSummary.Subaccount<>'')
>>     and (GL_Subaccount.GL_Account is null)
>> GROUP BY
>>   PR_WageSummary.GL_Account,
>>   PR_WageSummary.Subaccount
>> UNION
>> SELECT
>>   'PR_WageSummary' as Tablename,
>>   PR_WageSummary.GL_Account,
>>   '(none)' as Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (PR_WageSummary
>>     LEFT OUTER JOIN GL_AccountMaster
>>       ON (GL_AccountMaster.GL_Account=PR_WageSummary.GL_Account))
>> WHERE
>>   (PR_WageSummary.GL_Account is not null)
>>     and ((PR_WageSummary.Subaccount is null) or
>> (PR_WageSummary.Subaccount=''))
>>     and (GL_AccountMaster.GL_Account is null)
>> GROUP BY
>>   PR_WageSummary.GL_Account
>> UNION
>> SELECT
>>   'PurchaseOrderItem' as Tablename,
>>   PurchaseOrderItem.GL_Account,
>>   PurchaseOrderItem.Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (PurchaseOrderItem
>>     LEFT OUTER JOIN GL_Subaccount
>>       ON (GL_Subaccount.GL_Account=PurchaseOrderItem.GL_Account)
>>         and (GL_Subaccount.Subaccount=PurchaseOrderItem.Subaccount))
>> WHERE
>>   (PurchaseOrderItem.GL_Account is not null)
>>     and (PurchaseOrderItem.Subaccount is not null)
>>     and (PurchaseOrderItem.Subaccount<>'')
>>     and (GL_Subaccount.GL_Account is null)
>> GROUP BY
>>   PurchaseOrderItem.GL_Account,
>>   PurchaseOrderItem.Subaccount
>> UNION
>> SELECT
>>   'PurchaseOrderItem' as Tablename,
>>   PurchaseOrderItem.GL_Account,
>>   '(none)' as Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (PurchaseOrderItem
>>     LEFT OUTER JOIN GL_AccountMaster
>>       ON (GL_AccountMaster.GL_Account=PurchaseOrderItem.GL_Account))
>> WHERE
>>   (PurchaseOrderItem.GL_Account is not null)
>>     and ((PurchaseOrderItem.Subaccount is null) or
>> (PurchaseOrderItem.Subaccount=''))
>>     and (GL_AccountMaster.GL_Account is null)
>> GROUP BY
>>   PurchaseOrderItem.GL_Account
>> UNION
>> SELECT
>>   'SaleType' as Tablename,
>>   SaleType.GL_Account,
>>   SaleType.Subledger as Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (SaleType
>>     LEFT OUTER JOIN GL_Subaccount
>>       ON (GL_Subaccount.GL_Account=SaleType.GL_Account)
>>         and (GL_Subaccount.Subaccount=SaleType.Subledger))
>> WHERE
>>   (SaleType.GL_Account is not null)
>>     and (SaleType.Subledger is not null)
>>     and (SaleType.Subledger<>'')
>>     and (GL_Subaccount.GL_Account is null)
>> GROUP BY
>>   SaleType.GL_Account,
>>   SaleType.Subledger
>> UNION
>> SELECT
>>   'SaleType' as Tablename,
>>   SaleType.GL_Account,
>>   '(none)' as Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (SaleType
>>     LEFT OUTER JOIN GL_AccountMaster
>>       ON (GL_AccountMaster.GL_Account=SaleType.GL_Account))
>> WHERE
>>   (SaleType.GL_Account is not null)
>>     and ((SaleType.Subledger is null) or (SaleType.Subledger=''))
>>     and (GL_AccountMaster.GL_Account is null)
>> GROUP BY
>>   SaleType.GL_Account
>> UNION
>> SELECT
>>   'TimecardEntry' as Tablename,
>>   TimecardEntry.GL_Account,
>>   TimecardEntry.Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (TimecardEntry
>>     LEFT OUTER JOIN GL_Subaccount
>>       ON (GL_Subaccount.GL_Account=TimecardEntry.GL_Account)
>>         and (GL_Subaccount.Subaccount=TimecardEntry.Subaccount))
>> WHERE
>>   (TimecardEntry.GL_Account is not null)
>>     and (TimecardEntry.Subaccount is not null)
>>     and (TimecardEntry.Subaccount<>'')
>>     and (GL_Subaccount.GL_Account is null)
>> GROUP BY
>>   TimecardEntry.GL_Account,
>>   TimecardEntry.Subaccount
>> UNION
>> SELECT
>>   'TimecardEntry' as Tablename,
>>   TimecardEntry.GL_Account,
>>   '(none)' as Subaccount,
>>   count(*) as DupCount
>> FROM
>>   (TimecardEntry
>>     LEFT OUTER JOIN GL_AccountMaster
>>       ON (GL_AccountMaster.GL_Account=TimecardEntry.GL_Account))
>> WHERE
>>   (TimecardEntry.GL_Account is not null)
>>     and ((TimecardEntry.Subaccount is null) or
>> (TimecardEntry.Subaccount=''))
>>     and (GL_AccountMaster.GL_Account is null)
>> GROUP BY
>>   TimecardEntry.GL_Account
>>
>>
>>
>
>

16 May 2005 3:51 PM

Thomas Coleman

This is pretty ugly. Assuming you can't change the design, I'll bet that you
could get far better performance by populating a temp table in a stored proc
through a series of insert statements that match each query in the Union.
Further, by using the Union instead of Union All, you are requiring that results
be unique. If they are mutually exclusive, you can get a performance benefit by
using Union All.

Thomas

Show quote

"Bob Castleman" <nomail@here> wrote in message
news:OaQsRbhWFHA.3140@TK2MSFTNGP14.phx.gbl...
> Below is a query written by one of the developers here. He showed in to me, I
> think as a joke, because he was going to run it in our hosted environment
> against all of our customers. Ichoked, because it was such a long query, but
> it really isn't terribly complex after a look at it. But I was curious if
> there was a better way to accomplish what it does.
>
> Including DDL is impractical, but I think it's pretty clear without it. It is
> looking for problems in general ledger references.
>
> SELECT
> 'GL_Subaccount' as Tablename,
> GL_Subaccount.GL_Account,
> '(n/a)' as Subaccount,
> count(*) as DupCount
> FROM
> (GL_Subaccount
>    LEFT OUTER JOIN GL_AccountMaster
>      ON (GL_AccountMaster.GL_Account=GL_Subaccount.GL_Account))
> WHERE
> (GL_Subaccount.GL_Account is not null)
> and (GL_AccountMaster.GL_Account is null)
> GROUP BY
> GL_Subaccount.GL_Account
> UNION
> SELECT
> 'AdjustCode' as Tablename,
> AdjustCode.GL_Account,
> AdjustCode.Subledger as Subaccount,
> count(*) as DupCount
> FROM
> (AdjustCode
>    LEFT OUTER JOIN GL_Subaccount
>      ON (GL_Subaccount.GL_Account=AdjustCode.GL_Account)
>        and (GL_Subaccount.Subaccount=AdjustCode.Subledger))
> WHERE
> (AdjustCode.GL_Account is not null)
>    and (AdjustCode.Subledger is not null)
>    and (AdjustCode.Subledger<>'')
>    and (GL_Subaccount.GL_Account is null)
> GROUP BY
> AdjustCode.GL_Account,
> AdjustCode.Subledger
> UNION
> SELECT
> 'AdjustCode' as Tablename,
> AdjustCode.GL_Account,
> '(none)' as Subaccount,
> count(*) as DupCount
> FROM
> (AdjustCode
>    LEFT OUTER JOIN GL_AccountMaster
>      ON (GL_AccountMaster.GL_Account=AdjustCode.GL_Account))
> WHERE
> (AdjustCode.GL_Account is not null)
>    and ((AdjustCode.Subledger is null) or (AdjustCode.Subledger=''))
>    and (GL_AccountMaster.GL_Account is null)
> GROUP BY
> AdjustCode.GL_Account
> UNION
> SELECT
> 'AP_Item' as Tablename,
> AP_Item.GL_Account,
> AP_Item.Subaccount,
> count(*) as DupCount
> FROM
> (AP_Item
>    LEFT OUTER JOIN GL_Subaccount
>      ON (GL_Subaccount.GL_Account=AP_Item.GL_Account)
>        and (GL_Subaccount.Subaccount=AP_Item.Subaccount))
> WHERE
> (AP_Item.GL_Account is not null)
>    and (AP_Item.Subaccount is not null)
>    and (AP_Item.Subaccount<>'')
>    and (GL_Subaccount.GL_Account is null)
> GROUP BY
> AP_Item.GL_Account,
> AP_Item.Subaccount
> UNION
> SELECT
> 'AP_Item' as Tablename,
> AP_Item.GL_Account,
> '(none)' as Subaccount,
> count(*) as DupCount
> FROM
> (AP_Item
>    LEFT OUTER JOIN GL_AccountMaster
>      ON (GL_AccountMaster.GL_Account=AP_Item.GL_Account))
> WHERE
> (AP_Item.GL_Account is not null)
>    and ((AP_Item.Subaccount is null) or (AP_Item.Subaccount=''))
>    and (GL_AccountMaster.GL_Account is null)
> GROUP BY
> AP_Item.GL_Account
> UNION
> SELECT
> 'CB_Dist' as Tablename,
> CB_Dist.GL_Account,
> CB_Dist.Subaccount,
> count(*) as DupCount
> FROM
> (CB_Dist
>    LEFT OUTER JOIN GL_Subaccount
>      ON (GL_Subaccount.GL_Account=CB_Dist.GL_Account)
>        and (GL_Subaccount.Subaccount=CB_Dist.Subaccount))
> WHERE
> (CB_Dist.GL_Account is not null)
>    and (CB_Dist.Subaccount is not null)
>    and (CB_Dist.Subaccount<>'')
>    and (GL_Subaccount.GL_Account is null)
> GROUP BY
> CB_Dist.GL_Account,
> CB_Dist.Subaccount
> UNION
> SELECT
> 'CB_Dist' as Tablename,
> CB_Dist.GL_Account,
> '(none)' as Subaccount,
> count(*) as DupCount
> FROM
> (CB_Dist
>    LEFT OUTER JOIN GL_AccountMaster
>      ON (GL_AccountMaster.GL_Account=CB_Dist.GL_Account))
> WHERE
> (CB_Dist.GL_Account is not null)
>    and ((CB_Dist.Subaccount is null) or (CB_Dist.Subaccount=''))
>    and (GL_AccountMaster.GL_Account is null)
> GROUP BY
> CB_Dist.GL_Account
> UNION
> SELECT
> 'CB_Register' as Tablename,
> CB_Register.GL_Account,
> CB_Register.Subaccount,
> count(*) as DupCount
> FROM
> (CB_Register
>    LEFT OUTER JOIN GL_Subaccount
>      ON (GL_Subaccount.GL_Account=CB_Register.GL_Account)
>        and (GL_Subaccount.Subaccount=CB_Register.Subaccount))
> WHERE
> (CB_Register.GL_Account is not null)
>    and (CB_Register.Subaccount is not null)
>    and (CB_Register.Subaccount<>'')
>    and (GL_Subaccount.GL_Account is null)
> GROUP BY
> CB_Register.GL_Account,
> CB_Register.Subaccount
> UNION
> SELECT
> 'CB_Register' as Tablename,
> CB_Register.GL_Account,
> '(none)' as Subaccount,
> count(*) as DupCount
> FROM
> (CB_Register
>    LEFT OUTER JOIN GL_AccountMaster
>      ON (GL_AccountMaster.GL_Account=CB_Register.GL_Account))
> WHERE
> (CB_Register.GL_Account is not null)
>    and ((CB_Register.Subaccount is null) or (CB_Register.Subaccount=''))
>    and (GL_AccountMaster.GL_Account is null)
> GROUP BY
> CB_Register.GL_Account
> UNION
> SELECT
> 'CB_Statement' as Tablename,
> CB_Statement.GL_Account,
> CB_Statement.Subaccount,
> count(*) as DupCount
> FROM
> (CB_Statement
>    LEFT OUTER JOIN GL_Subaccount
>      ON (GL_Subaccount.GL_Account=CB_Statement.GL_Account)
>        and (GL_Subaccount.Subaccount=CB_Statement.Subaccount))
> WHERE
> (CB_Statement.GL_Account is not null)
>    and (CB_Statement.Subaccount is not null)
>    and (CB_Statement.Subaccount<>'')
>    and (GL_Subaccount.GL_Account is null)
> GROUP BY
> CB_Statement.GL_Account,
> CB_Statement.Subaccount
> UNION
> SELECT
> 'CB_Statement' as Tablename,
> CB_Statement.GL_Account,
> '(none)' as Subaccount,
> count(*) as DupCount
> FROM
> (CB_Statement
>    LEFT OUTER JOIN GL_AccountMaster
>      ON (GL_AccountMaster.GL_Account=CB_Statement.GL_Account))
> WHERE
> (CB_Statement.GL_Account is not null)
>    and ((CB_Statement.Subaccount is null) or (CB_Statement.Subaccount=''))
>    and (GL_AccountMaster.GL_Account is null)
> GROUP BY
> CB_Statement.GL_Account
> UNION
> SELECT
> 'ExpenseType' as Tablename,
> ExpenseType.GL_Account,
> ExpenseType.Subledger as Subaccount,
> count(*) as DupCount
> FROM
> (ExpenseType
>    LEFT OUTER JOIN GL_Subaccount
>      ON (GL_Subaccount.GL_Account=ExpenseType.GL_Account)
>        and (GL_Subaccount.Subaccount=ExpenseType.Subledger))
> WHERE
> (ExpenseType.GL_Account is not null)
>    and (ExpenseType.Subledger is not null)
>    and (ExpenseType.Subledger<>'')
>    and (GL_Subaccount.GL_Account is null)
> GROUP BY
> ExpenseType.GL_Account,
> ExpenseType.Subledger
> UNION
> SELECT
> 'ExpenseType' as Tablename,
> ExpenseType.GL_Account,
> '(none)' as Subaccount,
> count(*) as DupCount
> FROM
> (ExpenseType
>    LEFT OUTER JOIN GL_AccountMaster
>      ON (GL_AccountMaster.GL_Account=ExpenseType.GL_Account))
> WHERE
> (ExpenseType.GL_Account is not null)
>    and ((ExpenseType.Subledger is null) or (ExpenseType.Subledger=''))
>    and (GL_AccountMaster.GL_Account is null)
> GROUP BY
> ExpenseType.GL_Account
> UNION
> SELECT
> 'GL_AccountDivision' as Tablename,
> GL_AccountDivision.GL_Account,
> '(n/a)' as Subaccount,
> count(*) as DupCount
> FROM
> (GL_AccountDivision
>    LEFT OUTER JOIN GL_AccountMaster
>      ON (GL_AccountMaster.GL_Account=GL_AccountDivision.GL_Account))
> WHERE
> (GL_AccountDivision.GL_Account is not null)
> and (GL_AccountMaster.GL_Account is null)
> GROUP BY
> GL_AccountDivision.GL_Account
> UNION
> SELECT
> 'GL_Budget' as Tablename,
> GL_Budget.GL_Account,
> GL_Budget.Subaccount,
> count(*) as DupCount
> FROM
> (GL_Budget
>    LEFT OUTER JOIN GL_Subaccount
>      ON (GL_Subaccount.GL_Account=GL_Budget.GL_Account)
>        and (GL_Subaccount.Subaccount=GL_Budget.Subaccount))
> WHERE
> (GL_Budget.GL_Account is not null)
>    and (GL_Budget.Subaccount is not null)
>    and (GL_Budget.Subaccount<>'')
>    and (GL_Subaccount.GL_Account is null)
> GROUP BY
> GL_Budget.GL_Account,
> GL_Budget.Subaccount
> UNION
> SELECT
> 'GL_Budget' as Tablename,
> GL_Budget.GL_Account,
> '(none)' as Subaccount,
> count(*) as DupCount
> FROM
> (GL_Budget
>    LEFT OUTER JOIN GL_AccountMaster
>      ON (GL_AccountMaster.GL_Account=GL_Budget.GL_Account))
> WHERE
> (GL_Budget.GL_Account is not null)
>    and ((GL_Budget.Subaccount is null) or (GL_Budget.Subaccount=''))
>    and (GL_AccountMaster.GL_Account is null)
> GROUP BY
> GL_Budget.GL_Account
> UNION
> SELECT
> 'GL_Default' as Tablename,
> GL_Default.GL_Account,
> GL_Default.Subaccount,
> count(*) as DupCount
> FROM
> (GL_Default
>    LEFT OUTER JOIN GL_Subaccount
>      ON (GL_Subaccount.GL_Account=GL_Default.GL_Account)
>        and (GL_Subaccount.Subaccount=GL_Default.Subaccount))
> WHERE
> (GL_Default.GL_Account is not null)
>    and (GL_Default.Subaccount is not null)
>    and (GL_Default.Subaccount<>'')
>    and (GL_Subaccount.GL_Account is null)
> GROUP BY
> GL_Default.GL_Account,
> GL_Default.Subaccount
> UNION
> SELECT
> 'GL_Default' as Tablename,
> GL_Default.GL_Account,
> '(none)' as Subaccount,
> count(*) as DupCount
> FROM
> (GL_Default
>    LEFT OUTER JOIN GL_AccountMaster
>      ON (GL_AccountMaster.GL_Account=GL_Default.GL_Account))
> WHERE
> (GL_Default.GL_Account is not null)
>    and ((GL_Default.Subaccount is null) or (GL_Default.Subaccount=''))
>    and (GL_AccountMaster.GL_Account is null)
> GROUP BY
> GL_Default.GL_Account
> UNION
> SELECT
> 'GL_Journal' as Tablename,
> GL_Journal.GL_Account,
> GL_Journal.Subaccount,
> count(*) as DupCount
> FROM
> (GL_Journal
>    LEFT OUTER JOIN GL_Subaccount
>      ON (GL_Subaccount.GL_Account=GL_Journal.GL_Account)
>        and (GL_Subaccount.Subaccount=GL_Journal.Subaccount))
> WHERE
> (GL_Journal.GL_Account is not null)
>    and (GL_Journal.Subaccount is not null)
>    and (GL_Journal.Subaccount<>'')
>    and (GL_Subaccount.GL_Account is null)
> GROUP BY
> GL_Journal.GL_Account,
> GL_Journal.Subaccount
> UNION
> SELECT
> 'GL_Journal' as Tablename,
> GL_Journal.GL_Account,
> '(none)' as Subaccount,
> count(*) as DupCount
> FROM
> (GL_Journal
>    LEFT OUTER JOIN GL_AccountMaster
>      ON (GL_AccountMaster.GL_Account=GL_Journal.GL_Account))
> WHERE
> (GL_Journal.GL_Account is not null)
>    and ((GL_Journal.Subaccount is null) or (GL_Journal.Subaccount=''))
>    and (GL_AccountMaster.GL_Account is null)
> GROUP BY
> GL_Journal.GL_Account
> UNION
> SELECT
> 'GL_Report' as Tablename,
> GL_Report.GL_Account,
> GL_Report.Subaccount,
> count(*) as DupCount
> FROM
> (GL_Report
>    LEFT OUTER JOIN GL_Subaccount
>      ON (GL_Subaccount.GL_Account=GL_Report.GL_Account)
>        and (GL_Subaccount.Subaccount=GL_Report.Subaccount))
> WHERE
> (GL_Report.GL_Account is not null)
>    and (GL_Report.Subaccount is not null)
>    and (GL_Report.Subaccount<>'')
>    and (GL_Subaccount.GL_Account is null)
> GROUP BY
> GL_Report.GL_Account,
> GL_Report.Subaccount
> UNION
> SELECT
> 'GL_Report' as Tablename,
> GL_Report.GL_Account,
> '(none)' as Subaccount,
> count(*) as DupCount
> FROM
> (GL_Report
>    LEFT OUTER JOIN GL_AccountMaster
>      ON (GL_AccountMaster.GL_Account=GL_Report.GL_Account))
> WHERE
> (GL_Report.GL_Account is not null)
>    and ((GL_Report.Subaccount is null) or (GL_Report.Subaccount=''))
>    and (GL_AccountMaster.GL_Account is null)
> GROUP BY
> GL_Report.GL_Account
> UNION
> SELECT
> 'GL_Transaction' as Tablename,
> GL_Transaction.GL_Account,
> GL_Transaction.Subaccount,
> count(*) as DupCount
> FROM
> (GL_Transaction
>    LEFT OUTER JOIN GL_Subaccount
>      ON (GL_Subaccount.GL_Account=GL_Transaction.GL_Account)
>        and (GL_Subaccount.Subaccount=GL_Transaction.Subaccount))
> WHERE
> (GL_Transaction.GL_Account is not null)
>    and (GL_Transaction.Subaccount is not null)
>    and (GL_Transaction.Subaccount<>'')
>    and (GL_Subaccount.GL_Account is null)
> GROUP BY
> GL_Transaction.GL_Account,
> GL_Transaction.Subaccount
> UNION
> SELECT
> 'GL_Transaction' as Tablename,
> GL_Transaction.GL_Account,
> '(none)' as Subaccount,
> count(*) as DupCount
> FROM
> (GL_Transaction
>    LEFT OUTER JOIN GL_AccountMaster
>      ON (GL_AccountMaster.GL_Account=GL_Transaction.GL_Account))
> WHERE
> (GL_Transaction.GL_Account is not null)
>    and ((GL_Transaction.Subaccount is null) or (GL_Transaction.Subaccount=''))
>    and (GL_AccountMaster.GL_Account is null)
> GROUP BY
> GL_Transaction.GL_Account
> UNION
> SELECT
> 'IC_Tag' as Tablename,
> IC_Tag.GL_Account,
> IC_Tag.Subaccount,
> count(*) as DupCount
> FROM
> (IC_Tag
>    LEFT OUTER JOIN GL_Subaccount
>      ON (GL_Subaccount.GL_Account=IC_Tag.GL_Account)
>        and (GL_Subaccount.Subaccount=IC_Tag.Subaccount))
> WHERE
> (IC_Tag.GL_Account is not null)
>    and (IC_Tag.Subaccount is not null)
>    and (IC_Tag.Subaccount<>'')
>    and (GL_Subaccount.GL_Account is null)
> GROUP BY
> IC_Tag.GL_Account,
> IC_Tag.Subaccount
> UNION
> SELECT
> 'IC_Tag' as Tablename,
> IC_Tag.GL_Account,
> '(none)' as Subaccount,
> count(*) as DupCount
> FROM
> (IC_Tag
>    LEFT OUTER JOIN GL_AccountMaster
>      ON (GL_AccountMaster.GL_Account=IC_Tag.GL_Account))
> WHERE
> (IC_Tag.GL_Account is not null)
>    and ((IC_Tag.Subaccount is null) or (IC_Tag.Subaccount=''))
>    and (GL_AccountMaster.GL_Account is null)
> GROUP BY
> IC_Tag.GL_Account
> UNION
> SELECT
> 'IC_Transaction' as Tablename,
> IC_Transaction.GL_Account,
> IC_Transaction.Subaccount,
> count(*) as DupCount
> FROM
> (IC_Transaction
>    LEFT OUTER JOIN GL_Subaccount
>      ON (GL_Subaccount.GL_Account=IC_Transaction.GL_Account)
>        and (GL_Subaccount.Subaccount=IC_Transaction.Subaccount))
> WHERE
> (IC_Transaction.GL_Account is not null)
>    and (IC_Transaction.Subaccount is not null)
>    and (IC_Transaction.Subaccount<>'')
>    and (GL_Subaccount.GL_Account is null)
> GROUP BY
> IC_Transaction.GL_Account,
> IC_Transaction.Subaccount
> UNION
> SELECT
> 'IC_Transaction' as Tablename,
> IC_Transaction.GL_Account,
> '(none)' as Subaccount,
> count(*) as DupCount
> FROM
> (IC_Transaction
>    LEFT OUTER JOIN GL_AccountMaster
>      ON (GL_AccountMaster.GL_Account=IC_Transaction.GL_Account))
> WHERE
> (IC_Transaction.GL_Account is not null)
>    and ((IC_Transaction.Subaccount is null) or (IC_Transaction.Subaccount=''))
>    and (GL_AccountMaster.GL_Account is null)
> GROUP BY
> IC_Transaction.GL_Account
> UNION
> SELECT
> 'IC_TrxControl' as Tablename,
> IC_TrxControl.GL_Account,
> IC_TrxControl.Subaccount,
> count(*) as DupCount
> FROM
> (IC_TrxControl
>    LEFT OUTER JOIN GL_Subaccount
>      ON (GL_Subaccount.GL_Account=IC_TrxControl.GL_Account)
>        and (GL_Subaccount.Subaccount=IC_TrxControl.Subaccount))
> WHERE
> (IC_TrxControl.GL_Account is not null)
>    and (IC_TrxControl.Subaccount is not null)
>    and (IC_TrxControl.Subaccount<>'')
>    and (GL_Subaccount.GL_Account is null)
> GROUP BY
> IC_TrxControl.GL_Account,
> IC_TrxControl.Subaccount
> UNION
> SELECT
> 'IC_TrxControl' as Tablename,
> IC_TrxControl.GL_Account,
> '(none)' as Subaccount,
> count(*) as DupCount
> FROM
> (IC_TrxControl
>    LEFT OUTER JOIN GL_AccountMaster
>      ON (GL_AccountMaster.GL_Account=IC_TrxControl.GL_Account))
> WHERE
> (IC_TrxControl.GL_Account is not null)
>    and ((IC_TrxControl.Subaccount is null) or (IC_TrxControl.Subaccount=''))
>    and (GL_AccountMaster.GL_Account is null)
> GROUP BY
> IC_TrxControl.GL_Account
> UNION
> SELECT
> 'IC_TrxControlItem' as Tablename,
> IC_TrxControlItem.GL_Account,
> IC_TrxControlItem.Subaccount,
> count(*) as DupCount
> FROM
> (IC_TrxControlItem
>    LEFT OUTER JOIN GL_Subaccount
>      ON (GL_Subaccount.GL_Account=IC_TrxControlItem.GL_Account)
>        and (GL_Subaccount.Subaccount=IC_TrxControlItem.Subaccount))
> WHERE
> (IC_TrxControlItem.GL_Account is not null)
>    and (IC_TrxControlItem.Subaccount is not null)
>    and (IC_TrxControlItem.Subaccount<>'')
>    and (GL_Subaccount.GL_Account is null)
> GROUP BY
> IC_TrxControlItem.GL_Account,
> IC_TrxControlItem.Subaccount
> UNION
> SELECT
> 'IC_TrxControlItem' as Tablename,
> IC_TrxControlItem.GL_Account,
> '(none)' as Subaccount,
> count(*) as DupCount
> FROM
> (IC_TrxControlItem
>    LEFT OUTER JOIN GL_AccountMaster
>      ON (GL_AccountMaster.GL_Account=IC_TrxControlItem.GL_Account))
> WHERE
> (IC_TrxControlItem.GL_Account is not null)
>    and ((IC_TrxControlItem.Subaccount is null) or
> (IC_TrxControlItem.Subaccount=''))
>    and (GL_AccountMaster.GL_Account is null)
> GROUP BY
> IC_TrxControlItem.GL_Account
> UNION
> SELECT
> 'InvoiceItem' as Tablename,
> InvoiceItem.GL_Account,
> InvoiceItem.Subledger as Subaccount,
> count(*) as DupCount
> FROM
> (InvoiceItem
>    LEFT OUTER JOIN GL_Subaccount
>      ON (GL_Subaccount.GL_Account=InvoiceItem.GL_Account)
>        and (GL_Subaccount.Subaccount=InvoiceItem.Subledger))
> WHERE
> (InvoiceItem.GL_Account is not null)
>    and (InvoiceItem.Subledger is not null)
>    and (InvoiceItem.Subledger<>'')
>    and (GL_Subaccount.GL_Account is null)
> GROUP BY
> InvoiceItem.GL_Account,
> InvoiceItem.Subledger
> UNION
> SELECT
> 'InvoiceItem' as Tablename,
> InvoiceItem.GL_Account,
> '(none)' as Subaccount,
> count(*) as DupCount
> FROM
> (InvoiceItem
>    LEFT OUTER JOIN GL_AccountMaster
>      ON (GL_AccountMaster.GL_Account=InvoiceItem.GL_Account))
> WHERE
> (InvoiceItem.GL_Account is not null)
>    and ((InvoiceItem.Subledger is null) or (InvoiceItem.Subledger=''))
>    and (GL_AccountMaster.GL_Account is null)
> GROUP BY
> InvoiceItem.GL_Account
> UNION
> SELECT
> 'InvoiceItemDetail' as Tablename,
> InvoiceItemDetail.GL_Account,
> InvoiceItemDetail.Subaccount,
> count(*) as DupCount
> FROM
> (InvoiceItemDetail
>    LEFT OUTER JOIN GL_Subaccount
>      ON (GL_Subaccount.GL_Account=InvoiceItemDetail.GL_Account)
>        and (GL_Subaccount.Subaccount=InvoiceItemDetail.Subaccount))
> WHERE
> (InvoiceItemDetail.GL_Account is not null)
>    and (InvoiceItemDetail.Subaccount is not null)
>    and (InvoiceItemDetail.Subaccount<>'')
>    and (GL_Subaccount.GL_Account is null)
> GROUP BY
> InvoiceItemDetail.GL_Account,
> InvoiceItemDetail.Subaccount
> UNION
> SELECT
> 'InvoiceItemDetail' as Tablename,
> InvoiceItemDetail.GL_Account,
> '(none)' as Subaccount,
> count(*) as DupCount
> FROM
> (InvoiceItemDetail
>    LEFT OUTER JOIN GL_AccountMaster
>      ON (GL_AccountMaster.GL_Account=InvoiceItemDetail.GL_Account))
> WHERE
> (InvoiceItemDetail.GL_Account is not null)
>    and ((InvoiceItemDetail.Subaccount is null) or
> (InvoiceItemDetail.Subaccount=''))
>    and (GL_AccountMaster.GL_Account is null)
> GROUP BY
> InvoiceItemDetail.GL_Account
> UNION
> SELECT
> 'PR_MiscWage' as Tablename,
> PR_MiscWage.GL_Account,
> PR_MiscWage.Subaccount,
> count(*) as DupCount
> FROM
> (PR_MiscWage
>    LEFT OUTER JOIN GL_Subaccount
>      ON (GL_Subaccount.GL_Account=PR_MiscWage.GL_Account)
>        and (GL_Subaccount.Subaccount=PR_MiscWage.Subaccount))
> WHERE
> (PR_MiscWage.GL_Account is not null)
>    and (PR_MiscWage.Subaccount is not null)
>    and (PR_MiscWage.Subaccount<>'')
>    and (GL_Subaccount.GL_Account is null)
> GROUP BY
> PR_MiscWage.GL_Account,
> PR_MiscWage.Subaccount
> UNION
> SELECT
> 'PR_MiscWage' as Tablename,
> PR_MiscWage.GL_Account,
> '(none)' as Subaccount,
> count(*) as DupCount
> FROM
> (PR_MiscWage
>    LEFT OUTER JOIN GL_AccountMaster
>      ON (GL_AccountMaster.GL_Account=PR_MiscWage.GL_Account))
> WHERE
> (PR_MiscWage.GL_Account is not null)
>    and ((PR_MiscWage.Subaccount is null) or (PR_MiscWage.Subaccount=''))
>    and (GL_AccountMaster.GL_Account is null)
> GROUP BY
> PR_MiscWage.GL_Account
> UNION
> SELECT
> 'PR_WageSummary' as Tablename,
> PR_WageSummary.GL_Account,
> PR_WageSummary.Subaccount,
> count(*) as DupCount
> FROM
> (PR_WageSummary
>    LEFT OUTER JOIN GL_Subaccount
>      ON (GL_Subaccount.GL_Account=PR_WageSummary.GL_Account)
>        and (GL_Subaccount.Subaccount=PR_WageSummary.Subaccount))
> WHERE
> (PR_WageSummary.GL_Account is not null)
>    and (PR_WageSummary.Subaccount is not null)
>    and (PR_WageSummary.Subaccount<>'')
>    and (GL_Subaccount.GL_Account is null)
> GROUP BY
> PR_WageSummary.GL_Account,
> PR_WageSummary.Subaccount
> UNION
> SELECT
> 'PR_WageSummary' as Tablename,
> PR_WageSummary.GL_Account,
> '(none)' as Subaccount,
> count(*) as DupCount
> FROM
> (PR_WageSummary
>    LEFT OUTER JOIN GL_AccountMaster
>      ON (GL_AccountMaster.GL_Account=PR_WageSummary.GL_Account))
> WHERE
> (PR_WageSummary.GL_Account is not null)
>    and ((PR_WageSummary.Subaccount is null) or (PR_WageSummary.Subaccount=''))
>    and (GL_AccountMaster.GL_Account is null)
> GROUP BY
> PR_WageSummary.GL_Account
> UNION
> SELECT
> 'PurchaseOrderItem' as Tablename,
> PurchaseOrderItem.GL_Account,
> PurchaseOrderItem.Subaccount,
> count(*) as DupCount
> FROM
> (PurchaseOrderItem
>    LEFT OUTER JOIN GL_Subaccount
>      ON (GL_Subaccount.GL_Account=PurchaseOrderItem.GL_Account)
>        and (GL_Subaccount.Subaccount=PurchaseOrderItem.Subaccount))
> WHERE
> (PurchaseOrderItem.GL_Account is not null)
>    and (PurchaseOrderItem.Subaccount is not null)
>    and (PurchaseOrderItem.Subaccount<>'')
>    and (GL_Subaccount.GL_Account is null)
> GROUP BY
> PurchaseOrderItem.GL_Account,
> PurchaseOrderItem.Subaccount
> UNION
> SELECT
> 'PurchaseOrderItem' as Tablename,
> PurchaseOrderItem.GL_Account,
> '(none)' as Subaccount,
> count(*) as DupCount
> FROM
> (PurchaseOrderItem
>    LEFT OUTER JOIN GL_AccountMaster
>      ON (GL_AccountMaster.GL_Account=PurchaseOrderItem.GL_Account))
> WHERE
> (PurchaseOrderItem.GL_Account is not null)
>    and ((PurchaseOrderItem.Subaccount is null) or
> (PurchaseOrderItem.Subaccount=''))
>    and (GL_AccountMaster.GL_Account is null)
> GROUP BY
> PurchaseOrderItem.GL_Account
> UNION
> SELECT
> 'SaleType' as Tablename,
> SaleType.GL_Account,
> SaleType.Subledger as Subaccount,
> count(*) as DupCount
> FROM
> (SaleType
>    LEFT OUTER JOIN GL_Subaccount
>      ON (GL_Subaccount.GL_Account=SaleType.GL_Account)
>        and (GL_Subaccount.Subaccount=SaleType.Subled