Solved

Non Unique Table Error

Posted on 2011-02-25
16
291 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
  • 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 100

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 34

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
 

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 100

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 100

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 100

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 100

Expert Comment

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

mlmcc
0
 
LVL 34

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 34

Accepted Solution

by:
James0628 earned 500 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 34

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

757 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now