• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 318
  • Last Modified:

Non Unique Table Error

Could someone explain how I can get the following code to work correctly so i do not get the Non Unique table Error from Crystal Reports XI connecting to Sage Timberline data source I believe they use Pervasive SQL.
SELECT
  CMT_REGISTER__WITHDRAWAL_DETAIL.GL_Debit_Account AS Account,
  CONCAT(CStr(CMT_REGISTER__WITHDRAWAL_DETAIL.Tran_Run),CStr(CMT_REGISTER__WITHDRAWAL_DETAIL.Tran_Sequence)) 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 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_DEAIL.GL_Debit_Account AS Account,
  CONCAT(CMT_REGISTER__DEPOSIT_ITEM_DEAIL.Tran_Run, CMT_REGISTER__DEPOSIT_ITEM_DEAIL.Tran_Sequence) AS ID,
  CMT_REGISTER__TRANSACTION.Accounting_Date AS Period_End_Date,
  CMT_REGISTER__DEPOSIT_ITEM_DEAIL.Amount
FROM
  CMT_REGISTER__TRANSACTION
  INNER JOIN CMT_REGISTER__DEPOSIT_ITEM_DEAIL ON CMT_REGISTER__TRANSACTION.Run = CMT_REGISTER__DEPOSIT_ITEM_DEAIL.Tran_Run
  AND  CMT_REGISTER__TRANSACTION.Sequence = CMT_REGISTER__DEPOSIT_ITEM_DEAIL.Tran_Sequence
WHERE
  CMT_REGISTER__DEPOSIT_ITEM_DEAIL.GL_Debit_Account >= '1-C0-CEH-5AA'
  AND CMT_REGISTER__TRANSACTION.Accounting_Date >= {d'2011,01,01'}

Open in new window

0
CBlaisdell
Asked:
CBlaisdell
  • 6
  • 5
  • 4
  • +1
1 Solution
 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
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
0
 
mlmccCommented:
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
0
 
James0628Commented:
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
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
CBlaisdellAuthor Commented:
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'}

Open in new window

0
 
mlmccCommented:
Where are you entering this SQL?

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

mlmcc
0
 
CBlaisdellAuthor Commented:
I enter it under the Add Command.

It does work if you only try one or the other.
0
 
mlmccCommented:
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
0
 
CBlaisdellAuthor Commented:
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'}

Open in new window

0
 
mlmccCommented:
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
0
 
CBlaisdellAuthor Commented:
The error message still pops up.
0
 
mlmccCommented:
Try using a different name for Id like TransactionId

mlmcc
0
 
James0628Commented:
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
0
 
CBlaisdellAuthor Commented:
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.
0
 
James0628Commented:
It really sounds like there's a problem with using the same table in two UNION'd SELECT's.  I don't know if the problem is in CR, or in the db, or somewhere in between (the connection).

 This is probably a longshot, but how about if you use an alias for CMT_REGISTER__TRANSACTION in one or both SELECT's (different alias in each SELECT).

 If that doesn't work, can you create something like a view or stored procedure in the db?  If so, try the query there and see if it works.

 If so, the problem would appear to be in CR, or the connection to the db, in which case, maybe you can use the view/procedure as the datasource for your report.

 If not, either there is something wrong with the query that I'm just not seeing, or you just can't use the same table in two UNION'd SELECT's, which seems pretty unlikely.  If that's the case, maybe there's another way to handle this kind of thing in that db.

 James
0
 
CBlaisdellAuthor Commented:
Thanks James Adding the Alias did the trick.
0
 
James0628Commented:
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
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now