data from tow databases

[Macho123]

Hi,

I am in a situation where I have to pull the data from tow different databases say a and b both of them are oracle. Now what I ma doing is using two connections to two databases at the database expert,and linking a column between the two tables in the two databases and putting the required fields in the reports from the both of them. But this is taking along time to retrive the data is there a way to fasten the retrivel of data when pulling from two different databases.
Will using a subreport insted will speed this up I ma not sure but any suggestion would be great.

Thnaks...

 

[dgillz]

Using a subreport is never a way to speed anything up.

What does your report look like, what are the table relationships and what is your record selection logic?

After we get a handle on this we can talk performance.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[lbass]

Are the fields from each database the same? If so, you could be using a command with a union statement. Even if not exactly the same, this might be the route to go, but it depends upon what you want to do with the data once returned to the report. Perhaps you could explain that further identifying which fields come from which db.

-LB

 

[Macho123]

This is how the situation is.

I have all the customer details(service type,supplierid,arrears,....) in one database (db1). There one attribute for the customer saying customer_type in the other database (db2) which also has to be in the report. I do not have any db link between the both databases. I am pulling all the details of the customers by joining 3 tables in db1 and the customer_type from a table in db2. Out of the three tabeles I am joining in db1 has a column name customer_id and the table form db1 also has customer_id. Customer_id is not a key but that is only way I could connect the ables in both the db's. I hope this is clear if not please let me know.

LB because there is no db link I can not go with a union, I believe.

Thanks...

 

[lbass]

No, it's not clear.Are you or are you not linking the second database to the first, and if so, on what field? If you are doing no linking, how do you know what customer the type from db2 refers to?

-LB

 

[Macho123]

LB, I am linking both the (customer_id) fields from two db's at the links tab in the database expert. But this is taking long time to retrive the data.

Thanks...

 

[lbass]

It might help if you go to database->show SQL query and copy the query and paste it into this thread. Also go to report->selection formula->record and copy that and paste it in. Then we can see what part of the query is getting passed to the server.

-LB

 

[Macho123]

Here are the 2 queries and there is no record selection formula. Those custid's which match from the both databases will be shown in th report(because i linked both the queries on custid at the link tab of database expert.
Db1 query:

select /*+ PARALLEL(cad, 8) PARALLEL(crs, 8) USE_HASH(cad, crs, casa) */ cad.CAD_ENERGY_TYPE,
cad.cad_account_type,
cad.CAD_ACCOUNT_STATUS,
cad.cad_status_code_curr,
crs.crs_segment,
cad.CAD_IDENTIFIER,
cad.cad_account_number,
to_number(SUBSTR( cad.cad_account_number ,1,7)) cust_idn,
cad.CAD_TOTAL_ENERGY_ARR,
sysdate Run_Date,
b.current_bal
from table1cad,
table2crs,
(select /*+ PARALLEL(casa, 8) */ casa.casa_cad_id, sum(casa.CASA_CURRENT_BALANCE) current_bal
from table3casa
group by casa.casa_cad_id) b
where cad.CAD_ENERGY_TYPE in ('…','…','…')
and crs.crs_model = '…'
and cad.CAD_ACCOUNT_STATUS in ('…','…')
and cad.cad_account_type in ('…','…')
and cad.CAD_IDENTIFIER = CRS.CRS_CAD_ID
and cad.CAD_IDENTIFIER = b.CASA_CAD_ID
and ((b.current_bal)>0 or(cad.CAD_TOTAL_ENERGY_ARR)>0)


db2 query :

select /*+ PARALLEL(customer_profiles, 8) */ cusp_cust_idn,count(cusp_cust_idn), max(decode(t4.cusp_type,'…','Y','N')) Low_Income
from table4 t4
group by t4.cusp_cust_idn

 

[lbass]

I am unclear how you generated these queries--two command objects? If you are then linking them in the linking expert, the linking will occur locally--which is what is slowing your report.

I'm not sure whether a subreport would speed your report, but it's probably worth a try.

-LB

 

[jizzycrystal]

I am also trying to add combine data from two different databases. Can i set up two connections to two databases at the database expert?
The fields from each database are the same. Whats a union statement?.
It is a profit and Loss account ......I want to add amounts that have the same general ledger account.

 

[PatriciaObreja]

An UNION operator helps us to combine data from 2 or more SELECT statements.

Table1 Table2
aaa ccc
bbb ddd

Select * From Table1
UNION
Select * From Table2

will return:
aaa
bbb
ccc
ddd

The "combined" fields should have compatible data types.

 

[lbass]

I think you would have to have the databases set up with the same driver, since you would be using a command to combine the two databases into one. If the databases have a different owner that distinguishes them as separate databases, but are found on the same server, then you could set up a union like this:

select "table"."number", "table"."string","table"."date"
from "owner1"."table" "table"
union all
select "table"."number", "table"."string","table"."date"
from "owner2"."table" "table"

This essentially combines the fields that are in the same sequential position on each half of the union all into one field, so there have to be the same number of fields, and they must be of the same datatype.

My experience is limited to working with databases with different owners on the same server, same driver, so I am not sure what's possible in other situations.

-LB