Avatar of chalie001
chalie001
 asked on

display the latest data

hi i have table which got same data but from diffirent database in difiirent time stamp, i what to view which data is the latest,in my table i got folowing column

 

 

empno_db1,emp_no_db2,trans_date_db1,trans_date_db2,receipt_date_db1,receipt_date_db2

 

 

 

 

my goal is to see which record is the latest one and from which database, how can i approch this,sql
DatabasesOracle Database

Avatar of undefined
Last Comment
chalie001

8/22/2022 - Mon
slightwv (䄆 Netminder)

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.
chalie001

ASKER
but i what to compare the date from diffirent database column and display the latest record and what to know from which database
slightwv (䄆 Netminder)

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
awking00

Which date field determines the latest, trans_date or receipt_date?
Mark Geerlings

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.)
ASKER CERTIFIED SOLUTION
chalie001

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
slightwv (䄆 Netminder)

>>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>
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
PortletPaul

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.
chalie001

ASKER
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)
PortletPaul

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?"
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
slightwv (䄆 Netminder)

>>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.
chalie001

ASKER
correct