displayed data with use 2 databases

[marek_moravcik]

Dear all.

I using 2 databases with UNION ALL using "Add command" like this:

SELECT "t_customer"."s_name", "t_customer"."d_sys_created"

FROM  "database1"."dbo"."t_customer" "t_customer"

UNION ALL

SELECT "t_customer"."s_name", "t_customer"."d_sys_created"

FROM  "database2"."dbo"."t_customer" "t_customer"

database1 was active (valid) until 30.april.2016 and

database2 is active from              1.may.2016

database2 is copy from database1

now please open attach file here question:

thanks very much

[former_member205840 · Accepted solution]

Hi Marek,

You can eliminate duplicates in crystal reports like :

option 1

Create a group on S_name, suppress details, and place all required fields in group footer.

option 2

Go in section expert--details-- suppress condition

If s_name=Previous(s_name) then true else false

Thanks,

Sastry

[marek_moravcik · Accepted solution]

thanks Sastry very much

These are possible solutions

[abhilash_kumar]

Hi Marek,

Simply modify the query as:

SELECT "t_customer"."s_name", "t_customer"."d_sys_created"

FROM  "database1"."dbo"."t_customer" "t_customer"

UNION

SELECT "t_customer"."s_name", "t_customer"."d_sys_created"

FROM  "database2"."dbo"."t_customer" "t_customer"

-Abhilash

[marek_moravcik]

thanks Abhilash so easy and I did not know 

[marek_moravcik]

this one is ok, but I have else one question.

It is poossible write query with use UNION on  "Add command" like this?

SELECT            t_customer.name, t_user.firstname

FROM               t_customer LEFT OUTER JOIN

                         t_users ON t_customer.id = t_users.id

WHERE            t_user.firstname='mark'

GROUP BY       t_customer.name, t_user.firstname

UNION

SELECT       t_customer.name, t_user.firstname

FROM          t_customer LEFT OUTER JOIN

                         t_users ON t_customer.id = t_users.id

WHERE       t_user.firstname='mark'

GROUP BY  t_customer.name, t_user.firstname

Query writen over UNION is database1.dbo

Query writen under UNION is database2.dbo

It is possible this way to join two databases? I dont know where (how)  I write "database1"."dbo"

"database2"."dbo"

thank for help

[abhilash_kumar]

Hi Marek,

You cannot query Database 2 while logged into Database 1.

If you're reporting against an Oracle database, you can setup a db link on one of the database servers pointing to the other database.

Once a db link is setup, you can use the two databases together in a SQL query referring to the linked database using the @db_link_name.

More on db links here:

CREATE DATABASE LINK - Examples - Oracle Wiki - Oracle - Toad World

If this is not possible, you could try creating views on each database and then Join them in CR - depending on how many records are being returned, this approach could potentially slow down the report as such Joins are not processed by the database server.

-Abhilash

[marek_moravcik]

thanks for help Abhilash