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?
cpursley1979Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cpursley1979Author Commented:
I did inter-link them in the link dialog fom the database expert.
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'
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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]'
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.