double table listing in joins
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2022
Forum Name: Data Connectivity
Forum Discription: How to connect to data sources and export reports
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=19547
Printed Date: 20 Apr 2025 at 8:10am
Topic: double table listing in joins
Posted By: choppyfireballs
Subject: double table listing in joins
Date Posted: 17 May 2013 at 6:38am
I am writing a custom report, using an odbc connection to a "SQL" database. (I say that because it's more of a pseudo SQL database.) Anyway I'm connected fine and as long as I only pull data from one table.
I have my tables linked throgh the database expert as enforce both and left outer join.
The problem that I ran into is that the sql query looks like this
SELECT "table1"."ID", "table2"."ID" FROM "table1" "table1" LEFT OUTER JOIN "table2" "table2" ON "table1"."ID" = "table2"."ID" WHERE "table1"."ID" = 2
The issue with this query is that my table are duplicated in the FROM secetion and my database is not handling the query properly because of it. I have restarted building the query from the ground up and gotten the same result, I have deleted the header fields to see if that would make a difference. Then to make sure it was the query causing my heartache, I ran the query through mysql on my server without the double tables in the join and it behaved properly.
So my question becomes, why does crystal double the tables, and how do I get rid of the double entry?
|
Replies:
Posted By: lockwelle
Date Posted: 20 May 2013 at 10:49am
while it is poorly formatted, the:
FROM "table1" "table1" LEFT OUTER JOIN "table2" "table2"
is really just this:
FROM "table1" AS "table1" LEFT OUTER JOIN "table2" AS "table2"
I am not sure if removing the second value (the alias) will help....
|
Posted By: choppyfireballs
Date Posted: 21 May 2013 at 4:30am
|
I will test it out and I will get back to youw. Thanks.
|
|