Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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
my goal is to see which record is the latest one and from which database, how can i approch this,sql
empno_db1,emp_no_db2,trans
my goal is to see which record is the latest one and from which database, how can i approch this,sql
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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.
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.
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
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
>>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>
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.
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)
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?"
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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial