toooki
asked on
Oracle query help
If you could please help with this Oracle (11gR2) query..
I have two tables T1: (about 1000 records)
F1 varchar2
F2 number
F3 number
F4 number
F5 date
And T2: (about 600 records)
F1 varchar2
F2 number
F3 number
F4 number
F5 date
All records in T2 is in T1.
I need to update T1 table's F5 field with the value of T1 table's F5 value when there is a match of T1.F1=T2.F1, T1.F2=T2.F2, T1.F3=T2.F3, T1.F4=T2.F4.
So the Output will return 1000 records (all T1 entries and possibly 600 rows updated with F5 values.)
Now problem is I call it a match if F1 is null in both tables and rest F2, F3, F4 values match.
If I write select T1.F1, T1.F2, T1.F3, T1.F4, T2.F5
from T1, T2
where T1.F1=T2.F1
and T1.F2=T2.F2
and T1.F3=T2.F3
and T1.F4=T2.F4
I do not get the correct output..
I have two tables T1: (about 1000 records)
F1 varchar2
F2 number
F3 number
F4 number
F5 date
And T2: (about 600 records)
F1 varchar2
F2 number
F3 number
F4 number
F5 date
All records in T2 is in T1.
I need to update T1 table's F5 field with the value of T1 table's F5 value when there is a match of T1.F1=T2.F1, T1.F2=T2.F2, T1.F3=T2.F3, T1.F4=T2.F4.
So the Output will return 1000 records (all T1 entries and possibly 600 rows updated with F5 values.)
Now problem is I call it a match if F1 is null in both tables and rest F2, F3, F4 values match.
If I write select T1.F1, T1.F2, T1.F3, T1.F4, T2.F5
from T1, T2
where T1.F1=T2.F1
and T1.F2=T2.F2
and T1.F3=T2.F3
and T1.F4=T2.F4
I do not get the correct output..
Last Comment
SOLUTION
THIS SOLUTION IS 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.
ASKER
Thank you.
No F1 is not the primary key.
F1, F2, F3, F4 combination is primary in both tables.
Yes if T2.F1 or T2.F1 are null. The where clause NULL will work.
No F1 is not the primary key.
F1, F2, F3, F4 combination is primary in both tables.
Yes if T2.F1 or T2.F1 are null. The where clause NULL will work.
What out put you are geting if do not check for f1 ?
select T1.F1, T1.F2, T1.F3, T1.F4, T2.F5
from T1, T2
where T1.F2=T2.F2
and T1.F3=T2.F3
and T1.F4=T2.F4
also try with trim option will work for you
select T1.F1, T1.F2, T1.F3, T1.F4, T2.F5
from T1, T2
where Trim(T1.F1)=Trim(T2.F1)
and T1.F2=T2.F2
and T1.F3=T2.F3
and T1.F4=T2.F4
select T1.F1, T1.F2, T1.F3, T1.F4, T2.F5
from T1, T2
where T1.F2=T2.F2
and T1.F3=T2.F3
and T1.F4=T2.F4
also try with trim option will work for you
select T1.F1, T1.F2, T1.F3, T1.F4, T2.F5
from T1, T2
where Trim(T1.F1)=Trim(T2.F1)
and T1.F2=T2.F2
and T1.F3=T2.F3
and T1.F4=T2.F4
ASKER
Thank you all.
OK if I try as enachemc suggested:
select T1.F1, T1.F2, T1.F3, T1.F4, T2.F5
from T1, T2
where (T1.F1=T2.F1 or (T1.f1 is null and t2.f1 is null))
and T1.F2=T2.F2
and T1.F3=T2.F3
and T1.F4=T2.F4
The the above query works for the F1 null issues.
But the above returns a record only when there is a matching record found in T2. So it returns 600 records.
But I will need remaining 400 records returned too. (that are there in T1 but not in T2.) T1 is the bigger table.
OK if I try as enachemc suggested:
select T1.F1, T1.F2, T1.F3, T1.F4, T2.F5
from T1, T2
where (T1.F1=T2.F1 or (T1.f1 is null and t2.f1 is null))
and T1.F2=T2.F2
and T1.F3=T2.F3
and T1.F4=T2.F4
The the above query works for the F1 null issues.
But the above returns a record only when there is a matching record found in T2. So it returns 600 records.
But I will need remaining 400 records returned too. (that are there in T1 but not in T2.) T1 is the bigger table.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS 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.
ASKER
Thank you.
Not sure why but the above query hangs...running for 5min...
let me see.
Not sure why but the above query hangs...running for 5min...
let me see.
try
select T1.F1, T1.F2, T1.F3, T1.F4, T2.F5
from T1
left outer join T2
on (T1.F1=T2.F1 or (T1.f1 is null and t2.f1 is null))
and T1.F2=T2.F2
and T1.F3=T2.F3
and T1.F4=T2.F4
select T1.F1, T1.F2, T1.F3, T1.F4, T2.F5
from T1
left outer join T2
on (T1.F1=T2.F1 or (T1.f1 is null and t2.f1 is null))
and T1.F2=T2.F2
and T1.F3=T2.F3
and T1.F4=T2.F4
make sure you have all the parathesis right
ASKER
Thank you.
Yes I am trying your query pratima_mcs you sent. Maybe it is just taking time..No syntax error.
Yes I am trying your query pratima_mcs you sent. Maybe it is just taking time..No syntax error.
it should not take that long
the tables are very small for oracle
it usualy takes that long when you make a join the wrong way (forget one field to join, put a paranthesis in the wrong place ... aso) that make oracle return 600 000 rows instead of just 1000
the tables are very small for oracle
it usualy takes that long when you make a join the wrong way (forget one field to join, put a paranthesis in the wrong place ... aso) that make oracle return 600 000 rows instead of just 1000
ASKER
I have a question. I kept the question simple. Actually the tables are really huge. 900K and 500K records
How do I improve the performance of the query.
Do I create unique index on F1, F2, F3 and F4 fields combination on both tables.
Both tables have an additional ID field that just works as a primary key..
How do I improve the performance of the query.
Do I create unique index on F1, F2, F3 and F4 fields combination on both tables.
Both tables have an additional ID field that just works as a primary key..
yes Indexes will improve performance
where nvl(t1.f1,'x') = nvl(t2.f1,'x')
only if 'x' is an illegal value for that column
ASKER
Thank you all. It worked.
Many thanks.
Many thanks.
enachemc,
Of course. I guess I should have offered the whole thing as to be more understandable -
select T1.F1, T1.F2, T1.F3, T1.F4, T2.F5
from T1
left join T2
on nvl(T1.F1,'somevaluenotfou
and T1.F2=T2.F2
and T1.F3=T2.F3
and T1.F4=T2.F4
Of course. I guess I should have offered the whole thing as to be more understandable -
select T1.F1, T1.F2, T1.F3, T1.F4, T2.F5
from T1
left join T2
on nvl(T1.F1,'somevaluenotfou
and T1.F2=T2.F2
and T1.F3=T2.F3
and T1.F4=T2.F4
Oracle Database
Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.
81K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
- will a WHERE clause on both T1F1 and T2.F1 <> NULL work?