Non Unique Table Error

Is there an actual database error code returned when you attempt to validate the command?

Also, I notice you're converting Tran_Run and Tran_Sequence to string user Cstr in the first query, but not in the second.  Each query in a unioned sql statement has to have the exact same number of columns and those columns have to be of the same data type.  You might try either adding the conversion to the second query or removing it from the first.

~Kurt

I assume the table name CMT_REGISTER__DEPOSIT_ITEM_DEAIL is correct and not missing a T as in
   CMT_REGISTER__DEPOSIT_ITEM_DETAIL

Can any of the fields be NULL? In oarticular the concatenated fields?

mlmcc

In addition to the things that were already mentioned, in the first SELECT, you have INNER JOIN CMT_REGISTER__WITHDRAWAL, but then you use CMT_REGISTER__WITHDRAWAL_DETAIL (with _DETAIL at the end) every else in that SELECT.

 James

The error message that pops up reads like this.

Failed to retrieve data from database
Details:42S02:[Sage Timberline Office][Sage Timberline Office ODBC Driver]Non Unique table referance:
CMT_Register__Transaction.

Sorry I was trying to remember the Select statements from my head. I have attched the correct Statements that I am trying to Union.

SELECT
  CMT_REGISTER__WITHDRAWAL_DETAIL.GL_Debit_Account AS Account,
  CONVERT(CMT_REGISTER__WITHDRAWAL_DETAIL.Tran_Run, SQL_VARCHAR) + CONVERT(CMT_REGISTER__WITHDRAWAL_DETAIL.Tran_Sequence, SQL_VARCHAR) AS ID,
  CMT_REGISTER__TRANSACTION.Accounting_Date AS Period_End_Date,
  CMT_REGISTER__WITHDRAWAL_DETAIL.Amount
FROM
  CMT_REGISTER__TRANSACTION
  INNER JOIN CMT_REGISTER__WITHDRAWAL_DETAIL ON CMT_REGISTER__TRANSACTION.Run = CMT_REGISTER__WITHDRAWAL_DETAIL.Tran_Run
  AND  CMT_REGISTER__TRANSACTION.Sequence = CMT_REGISTER__WITHDRAWAL_DETAIL.Tran_Sequence
WHERE
  CMT_REGISTER__WITHDRAWAL_DETAIL.GL_Debit_Account >= '1-C0-CEH-5AA'
  AND CMT_REGISTER__TRANSACTION.Accounting_Date >= {d'2011-01-01'}

UNION ALL

SELECT
  CMT_REGISTER__DEPOSIT_ITEM_DETAIL.GL_Debit_Account AS Account,
  CONVERT(CMT_REGISTER__DEPOSIT_ITEM_DETAIL.Tran_Run, SQL_VARCHAR) + CONVERT(CMT_REGISTER__DEPOSIT_ITEM_DETAIL.Tran_Sequence, SQL_VARCHAR) AS ID,
  CMT_REGISTER__TRANSACTION.Accounting_Date AS Period_End_Date,
  CMT_REGISTER__DEPOSIT_ITEM_DETAIL.Amount
FROM
  CMT_REGISTER__TRANSACTION
  INNER JOIN CMT_REGISTER__DEPOSIT_ITEM_DETAIL ON CMT_REGISTER__TRANSACTION.Run = CMT_REGISTER__DEPOSIT_ITEM_DETAIL.Tran_Run
  AND  CMT_REGISTER__TRANSACTION.Sequence = CMT_REGISTER__DEPOSIT_ITEM_DETAIL.Tran_Sequence
WHERE
  CMT_REGISTER__DEPOSIT_ITEM_DETAIL.GL_Debit_Account >= '1-C0-CEH-5AA'
  AND CMT_REGISTER__TRANSACTION.Accounting_Date >= {d'2011-01-01'}

Select allOpen in new window

Where are you entering this SQL?

If you try getting just the withdrawals or deposits does it work?

mlmcc

I enter it under the Add Command.

It does work if you only try one or the other.

How about if you select CMT_REGISTER__DEPOSIT_ITEM_DETAIL.Tran_Run and CMT_REGISTER__DEPOSIT_ITEM_DETAIL.Tran_Sequence without converting and do the concatenation in the report?

mlmcc

Once i get these two Select statements working i will be adding them to a bigger Union statement. that looks like this.

SELECT
  PRT_CURRENT__TIME.Expense_Account AS Account,
  PRT_CURRENT__TIME.Employee AS ID,
  PRT_CURRENT__TIME.Period_End_Date,
  Sum(PRT_CURRENT__TIME.Amount) AS Amount
FROM
  PRT_CURRENT__TIME
WHERE
  PRT_CURRENT__TIME.Period_End_Date>={d '2011-01-01'}
  AND  PRT_CURRENT__TIME.Expense_Account > '1-C0-CEH-5AA'
GROUP BY
  PRT_CURRENT__TIME.Expense_Account,
  PRT_CURRENT__TIME.Employee,
  PRT_CURRENT__TIME.Period_End_Date

UNION ALL

SELECT
  APT_CURRENT__TRANSACTION.Expense_Account AS Account,
  APT_CURRENT__TRANSACTION.Vendor AS ID,
  APT_CURRENT__TRANSACTION.Accounting_Date AS Period_End_Date,
  Sum(APT_CURRENT__TRANSACTION.Amount) AS Amount
FROM
  APT_CURRENT__TRANSACTION 
WHERE
  APT_CURRENT__TRANSACTION.Accounting_Date>={d '2011-01-01'}
  AND APT_CURRENT__TRANSACTION.Expense_Account>='1-C0-CEH-5AA'
  AND (APT_CURRENT__TRANSACTION.Transaction_Type='Invoice'
  OR APT_CURRENT__TRANSACTION.Transaction_Type='Reverse Invoice')
GROUP BY
  APT_CURRENT__TRANSACTION.Expense_Account,
  APT_CURRENT__TRANSACTION.Vendor,
  APT_CURRENT__TRANSACTION.Accounting_Date

UNION ALL

SELECT
  PRT_CURRENT__BURDEN_DETAIL.Expense_Account AS Account,
  PRT_CURRENT__BURDEN_DETAIL.Employee AS ID,
  PRT_CURRENT__BURDEN_DETAIL.Period_End_Date,
  Sum(PRT_CURRENT__BURDEN_DETAIL.Amount) AS Amount
FROM
  PRT_CURRENT__BURDEN_DETAIL 
WHERE
  PRT_CURRENT__BURDEN_DETAIL.Period_End_Date>={d '2011-01-01'}
  AND  PRT_CURRENT__BURDEN_DETAIL.Expense_Account > '1-C0-CEH-5AA'
GROUP BY
  PRT_CURRENT__BURDEN_DETAIL.Expense_Account,
  PRT_CURRENT__BURDEN_DETAIL.Employee,
  PRT_CURRENT__BURDEN_DETAIL.Period_End_Date


UNION ALL

SELECT
  PRT_CURRENT__CHECK_DEDUCT.Liability_Account AS Account,
  PRT_CURRENT__CHECK_DEDUCT.Employee AS ID,
  PRT_CURRENT__CHECK_DEDUCT.Period_End_Date,
  Sum(PRT_CURRENT__CHECK_DEDUCT.Amount*-1) AS Amount
FROM
  PRT_CURRENT__CHECK_DEDUCT 
WHERE
  PRT_CURRENT__CHECK_DEDUCT.Period_End_Date>={d '2011-01-01'}
  AND  PRT_CURRENT__CHECK_DEDUCT.Liability_Account > '1-C0-CEH-5AA'
GROUP BY
  PRT_CURRENT__CHECK_DEDUCT.Liability_Account,
  PRT_CURRENT__CHECK_DEDUCT.Employee,
  PRT_CURRENT__CHECK_DEDUCT.Period_End_Date

UNION ALL

SELECT
  CMT_REGISTER__WITHDRAWAL_DETAIL.GL_Debit_Account AS Account,
  CONVERT(CMT_REGISTER__WITHDRAWAL_DETAIL.Tran_Run, SQL_VARCHAR) + CONVERT(CMT_REGISTER__WITHDRAWAL_DETAIL.Tran_Sequence, SQL_VARCHAR) AS ID,
  CMT_REGISTER__TRANSACTION.Accounting_Date AS Period_End_Date,
  CMT_REGISTER__WITHDRAWAL_DETAIL.Amount
FROM
  CMT_REGISTER__TRANSACTION
  INNER JOIN CMT_REGISTER__WITHDRAWAL_DETAIL ON CMT_REGISTER__TRANSACTION.Run = CMT_REGISTER__WITHDRAWAL_DETAIL.Tran_Run
  AND  CMT_REGISTER__TRANSACTION.Sequence = CMT_REGISTER__WITHDRAWAL_DETAIL.Tran_Sequence
WHERE
  CMT_REGISTER__WITHDRAWAL_DETAIL.GL_Debit_Account >= '1-C0-CEH-5AA'
  AND CMT_REGISTER__TRANSACTION.Accounting_Date >= {d'2011-01-01'}

UNION ALL

SELECT
  CMT_REGISTER__DEPOSIT_ITEM_DETAIL.GL_Debit_Account AS Account,
  CONVERT(CMT_REGISTER__DEPOSIT_ITEM_DETAIL.Tran_Run, SQL_VARCHAR) + CONVERT(CMT_REGISTER__DEPOSIT_ITEM_DETAIL.Tran_Sequence, SQL_VARCHAR) AS ID,
  CMT_REGISTER__TRANSACTION.Accounting_Date AS Period_End_Date,
  CMT_REGISTER__DEPOSIT_ITEM_DETAIL.Amount
FROM
  CMT_REGISTER__TRANSACTION
  INNER JOIN CMT_REGISTER__DEPOSIT_ITEM_DETAIL ON CMT_REGISTER__TRANSACTION.Run = CMT_REGISTER__DEPOSIT_ITEM_DETAIL.Tran_Run
  AND  CMT_REGISTER__TRANSACTION.Sequence = CMT_REGISTER__DEPOSIT_ITEM_DETAIL.Tran_Sequence
WHERE
  CMT_REGISTER__DEPOSIT_ITEM_DETAIL.GL_Debit_Account >= '1-C0-CEH-5AA'
  AND CMT_REGISTER__TRANSACTION.Accounting_Date >= {d'2011-01-01'}

Select allOpen in new window

Try it with just one of the 2.  

We are trying to find out what is causing the error.  I wonder if the + is causing an issue.

mlmcc

The error message still pops up.

Try using a different name for Id like TransactionId

mlmcc

Have you tried each SELECT separately, to confirm that each one works by itself?

 I'm not familiar with Sage or Pervasive, but the query looks OK, in general.  I'm wondering if there might be a problem with using UNION.  You could try simplifying the query to see if the UNION itself might be the problem.

 Remove CMT_REGISTER__TRANSACTION from both SELECT's, so that each SELECT only reads a "DETAIL" table, and see if you still get an error.

 If not, then go back to the original query and remove the "DETAIL" table from each SELECT, so that each one only reads CMT_REGISTER__TRANSACTION, and see if you get an error.

 James

Both queries work by themselves.
when unioned together they work without CMT_REGISTER__TRANSACTION.  
I only get the error when I add the inner Join but I need that data field for both tables.

I also tried taking out CMT_REGISTER__TRANSACTION from one query and replacing it with 0 AS Period_End_Date and that worked fine.

Thanks James Adding the Alias did the trick.

Interesting.  I really didn't think that would work :-), but the theory was that if something (I still don't know what) was having a problem separating the two references to CMT_REGISTER__TRANSACTION, maybe giving each one a different alias would allow it to tell them apart.  Glad it worked.

 James