display the latest data

Use the MAX in the SQL

select empno,max(trans_date), max(receipt_date) from table@db1 group by empno

same for the db2 query.

but i what to compare the date from diffirent database column and display the latest record and what to know from which database

I understand that from your previous question.  Myself and others suggested MINUS.  That is how I would do it.

It appears you went with a join option.  Using that will require additional logic because the columns will be side-by-side so I'm not sure what you are thinking the next step is...

Anyway, just use inline views with the MAX

instead of something like:
select * from table@db1;

you would use:
select * from (select empno,max(trans_date) trans_date, max(receipt_date) receipt_date from table@db1 group by empno)

Then use the joins as before.

Which date field determines the latest, trans_date or receipt_date?

Trying to display and compare these values side-by-side as multiple columns in the same record will be very complex.  It is much simpler to display the data from the two different system by using two queries, one from each database, joined by the MINUS operator.  That will highlight the differences very quickly and simply.  (This assumes that you have a database link defined between the two systems.)

>>i what something like this

Then use that.


You'll need to join the columns something like:
select empno_db1,emp_no_db2
from
(select pk_col, empno as empno_db1 from table@db1) db1
join
(select pk_col, empno as empno_db2 from table@db1) db2
on(db1.pk_col=db2.pk_col)
/

If you want something different please provide a test case with sample data and expected results.

something like:
create table db1_table(pk_col number, empno number);
create table db2_table(pk_col number, empno number);

insert into db1_table values(1,1);
insert into db2_table values(1,2);
commit;

Expected results:
<insert what you want back here>

Provide:

a. "sample data" (from each table involved)
b. the "expected result" (derived from the sample)

from these items we get a much better understanding of what you are seeking.

select empno_db1,emp_no_db2
from
(select pk_col, empno as empno_db1 from table@db1) db1
join -- what must i write hear must i put minus
(select pk_col, empno as empno_db2 from table@db1) db2
on(db1.pk_col=db2.pk_col)

Please provide sample data, from each table, and the expected result you want from that sample. When we have those we can propose definite queries. Without those items we can watch this question rotate a few more times and still not know for sure what it is you are asking for.

awking00 has also asked a pertinent question:

"Which date field determines the latest, trans_date or receipt_date?"

>>join -- what must i write hear must i put minus

I cannot tell you what you need to put there since you have not provided sample data and expected results.

From your previous question, I have an idea about what you are wanting and I'm not sure it is possible with the path you have chosen but we'll continue that discussion over there.

correct