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
chalie001Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
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.
0
chalie001Author Commented:
but i what to compare the date from diffirent database column and display the latest record and what to know from which database
0
slightwv (䄆 Netminder) Commented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

awking00Commented:
Which date field determines the latest, trans_date or receipt_date?
0
Mark GeerlingsDatabase AdministratorCommented:
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.)
0
chalie001Author Commented:
i what something like this
 
   select empno_db1, trans_date_db1, trans_date_db2
          greatest(receipt_date_db1, receipt_date_db2) last_receipt,
          case when receipt_date_db1 = receipt_date_db2
                  then 'Equal'
                when receipt_date_db1 > receipt_date_db2
                  then 'Db1'
                else 'Db2' end last_source
    from your_table

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
>>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>
0
PortletPaulfreelancerCommented:
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.
0
chalie001Author Commented:
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)
0
PortletPaulfreelancerCommented:
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?"
0
slightwv (䄆 Netminder) Commented:
>>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.
0
chalie001Author Commented:
correct
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.