[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Non Unique Table Error

Posted on 2011-02-25
16
Medium Priority
?
311 Views
Last Modified: 2012-08-13
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
Comment
Question by:CBlaisdell
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 4
  • +1
16 Comments
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 34980152
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 34980224
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
 
LVL 35

Expert Comment

by:James0628
ID: 34986845
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
How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

 

Author Comment

by:CBlaisdell
ID: 35017566
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 35018239
Where are you entering this SQL?

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

mlmcc
0
 

Author Comment

by:CBlaisdell
ID: 35018428
I enter it under the Add Command.

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

Expert Comment

by:mlmcc
ID: 35018741
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
 

Author Comment

by:CBlaisdell
ID: 35018844
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 35019486
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
 

Author Comment

by:CBlaisdell
ID: 35019593
The error message still pops up.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35021456
Try using a different name for Id like TransactionId

mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 35025381
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
 

Author Comment

by:CBlaisdell
ID: 35026648
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
 
LVL 35

Accepted Solution

by:
James0628 earned 2000 total points
ID: 35035260
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
 

Author Comment

by:CBlaisdell
ID: 35038728
Thanks James Adding the Alias did the trick.
0
 
LVL 35

Expert Comment

by:James0628
ID: 35043193
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: 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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question