Get data from two different database

[yangbing1008]

I am trying to retrieve data from two tables which are from two datasources (db1 & db2)and some columns need to be joined from these two tables. eg, table1: col1,col2,col3 table2:col4,col5,col6, I need col1,col2,col4,col5 where col3=col6. Do anyone know how to implement it in CRXI?

 

[synapsevampire]

Post your database type and connectivity used. Not sure why you think the database has nothing to do with joining tables...

You can just connect to the 2 databases and join them within Crystal, but this will be slow, I would suggest doing so on the datbase side, or if that isn't an option, try using MS Access and LINKing (not importing) the datasources and then building an Access query as the source for the report.

-k

 

[RobbieB]

As Synapse suggests you can select tables from multiple data sources as long as they will link togther. Make sure you have both of them set up as DSN's on your machine to do this. Another option would be writing a stored procedure in Crystal.

 

[yangbing1008]

Both are Oracle database. I created links between two tables from different database. It looks fine. I run my report and I got nothing. I checked SQL in CR and it showed two separate queries not a join query between two tables.

 

[RobbieB]

Do you get an error when you run it in Crystal? When you go to update the information does it warn youo that you have used two different data sources? Are you linking the tables in the database expert in crystal?

 

[synapsevampire]

The joins aren't working correctly, again you post without specifics, but check how they are joined, it may be something as simple as one being padded with spaces or some such.

You can also use a SQL Expression to CAST one of the data types to the same type if they are different.

-k

 

[yangbing1008]

Thanks both of you. I used database export to create links, it showed warning "more than one datasource or store procedure has been used in this report. please make sure that no SQL expression added or no server-side group-by performed". When I run report, no errors but got nothing. Check SQL showed two separate quries.

 

[yangbing1008]

Do you have any more comments?

 

[RobbieB]

I'm not sure what to say. I just created a report with two tables from different data sources and it worked fine. I did have to use a left outer join though...

 

[yangbing1008]

RobbieB: You are right. Left outer join works. But why inner join doesn't work. Thanks.

 

[RobbieB]

To be completely honest, I'm not sure why it doesn't work with an equal join but I am guessing becasue not all records from the table in one database do not equal all records from the table in the other. Glad it worked for you.

 

[yangbing1008]

RobbieB: I was happy too early. I got records from DB2 but column value didn't show up. eg. col4, col5 value is empty. Do you have this problem? Thanks a lot.

 

[RobbieB]

No, I wasn't having this problem but I am also not using Oracle. Can you give more detail about what col4, col5 are? Are they feilds in the table from the second database?

 

[yangbing1008]

table1: col1,col2 and col3 from Database1. Table2:col4,col5 and col6 from Database2. When I was using equal inner, I got no record. After I used left outer join, I got records and all values from col1, col2 but any values from col4, col5. I want list col1, col2, co4, col5 in my report. (BTW, I imported table2 into DB1, it showed up.) Thanks RobbieB.

 

[RobbieB]

The only things I can think of are, make sure your select criteria is not filtering out items from the missing columns and try creating fromulas for col4 and col5 to check for NULL or "" values.
Like:

if isnull (col4) or col4 = "" then
" Empty"
else col4

create the same kind of formula for col5. This will let you know if you are really getting nothing or if the values are just coming across as null or empty strings.

 

[kskid]

Since they are Oracle tables, I believe you will need to set up a link between DB1 and DB2 using CREATE DATABASE LINK command in DB1

-lw