• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1080
  • 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 ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
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 ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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