Link to home
Start Free TrialLog in
Avatar of cpursley1979
cpursley1979

asked on

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?
Avatar of cpursley1979
cpursley1979

ASKER

I did inter-link them in the link dialog fom the database expert.
Avatar of cpursley1979
cpursley1979

ASKER

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'
ASKER CERTIFIED SOLUTION
Avatar of Kurt Reinhardt
Kurt Reinhardt
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of cpursley1979
cpursley1979

ASKER

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]'
Avatar of Kurt Reinhardt
Kurt Reinhardt
Flag of United States of America image
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