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

I cannot seem to get my query to join

I have a query:

 SELECT "emp1"."emp-name", "emp1"."emp-title", "emp1"."emp-phone", "emp1"."emp-code"
 FROM   "PUB"."emp" "emp1"


 SELECT "ProfitCenters"."CreditManager", "ProfitCenters"."charPCNumber"
 FROM   "GLData"."dbo"."ProfitCenters" "ProfitCenters"
 WHERE  "ProfitCenters"."CreditManager" LIKE '99017'


 SELECT "cust1"."cust-name", "cust1"."cust-addr1", "cust1"."cust-city", "cust1"."cust-state", "cust1"."cust-zip", "whs1"."whs-desc", "whs1"."whs-addr1", "whs1"."whs-city", "whs1"."whs-state", "whs1"."whs-zip", "cust1"."cust-num", "cust1"."profit-center"
 FROM   "PUB"."cust" "cust1" INNER JOIN "PUB"."whs" "whs1" ON "cust1"."whs-num"="whs1"."whs-num"
 WHERE  "cust1"."cust-num" LIKE '0299000'

that is pulling from two seperate database servers 1. Progress OpenEdge 10B 2. SQL Server 2000  How can I get them to join in Crystal 11?
0
cpursley1979
Asked:
cpursley1979
  • 3
  • 2
1 Solution
 
cpursley1979Author Commented:
I did inter-link them in the link dialog fom the database expert.
0
 
cpursley1979Author Commented:
Is there any way to go in and manually edit the query to do something like this:

 SELECT
"emp1"."emp-name", "emp1"."emp-title", "emp1"."emp-phone", "emp1"."emp-code", "ProfitCenters"."CreditManager", "ProfitCenters"."charPCNumber", "cust1"."cust-name", "cust1"."cust-addr1", "cust1"."cust-city", "cust1"."cust-state", "cust1"."cust-zip", "whs1"."whs-desc", "whs1"."whs-addr1", "whs1"."whs-city", "whs1"."whs-state", "whs1"."whs-zip", "cust1"."cust-num", "cust1"."profit-center"
 FROM  
"GLData"."dbo"."ProfitCenters" "ProfitCenters"
INNER JOIN
"PUB"."cust" "cust1" ON "cust1"."profit-center"="ProfitCenters"."charPCNumber"
INNER Join
"PUB"."whs" "whs1" ON "cust1"."whs-num"="whs1"."whs-num"
INNER JOIN
"PUB"."emp" "emp1" ON "emp1"."emp-code"="ProfitCenters"."CreditManagers"
WHERE
"ProfitCenters"."CreditManager" LIKE '99017' and "cust1"."cust-num" LIKE '0299000'
0
 
Kurt ReinhardtCommented:
This is how Crystal XI represents the SQL when you've joined tables from disparate data sources.  You can customize the SQL through a SQL Command Object.  This is conceptually the same as  a stored procedured, except that it is built entirely within Crystal Reports.

In your case, you would drill down to your datasource in the Database Expert and click 'Add Command'.  This will let you write your own SQL query to be used as the datasource for the report.  If you're going to do this, I would recommend that you include all datasources in the command so that you only use this one, single datasource for your report.

~Kurt
0
 
cpursley1979Author Commented:
I did as you said...started new report drilled down to the SQL Server Datasource and Clicked on add command.  I added this query:

SELECT
"emp1"."emp-name", "emp1"."emp-title", "emp1"."emp-phone", "emp1"."emp-code", "ProfitCenters"."CreditManager", "ProfitCenters"."charPCNumber", "cust1"."cust-name", "cust1"."cust-addr1", "cust1"."cust-city", "cust1"."cust-state", "cust1"."cust-zip", "whs1"."whs-desc", "whs1"."whs-addr1", "whs1"."whs-city", "whs1"."whs-state", "whs1"."whs-zip", "cust1"."cust-num", "cust1"."profit-center"
 FROM  
"GLData"."dbo"."ProfitCenters" "ProfitCenters"
INNER JOIN
"PUB"."cust" "cust1" ON "cust1"."profit-center"="ProfitCenters"."charPCNumber"
INNER Join
"PUB"."whs" "whs1" ON "cust1"."whs-num"="whs1"."whs-num"
INNER JOIN
"PUB"."emp" "emp1" ON "emp1"."emp-code"="ProfitCenters"."CreditManagers"
WHERE
"ProfitCenters"."CreditManager" LIKE '99017' and "cust1"."cust-num" LIKE '0299000'

with parameters for the where statment.  BTW the "GLData"."dbo"."ProfitCenters" "ProfitCenters" is the only table from SQL Server 2000 and the rest of the tables are from the Progress DB

and recieved these errors in 4 separate OK dialogs
Failed to retrieve data from the database
Database Connector Error: '42S02:[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'PUB.cust'.[Database Vendor Code:208]'
Database Connector Error: '42S02:[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'PUB.whs'.[Database Vendor Code:208]'
Database Connector Error: '42S02:[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'PUB.emp'.[Database Vendor Code:208]'
0
 
Kurt ReinhardtCommented:
I must have missed the fact that your tables were from different DB vendors.  The simplest method would be for you to create linked databases for Progress in SQL Server.  You'd have to install the progress ODBC driver on  your SQL Server machine.  Once you do so, you can choose to link SQL Server tables to the linked Progress DB tables in SQL or DTS the Progress tables into SQL Server.  Here are some links to point  you in the right direction:

http://www.databasejournal.com/features/mssql/article.php/3085211
http://www.sql-server-performance.com/linked_server.asp
http://www.dbazine.com/sql/sql-articles/sharma2
http://support.microsoft.com/default.aspx?scid=kb;en-us;326839

~Kurt
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now