On Nov 21, 10:08 pm, sashog <[EMAIL PROTECTED]> wrote:
> Oracle table t1 contains grp and 4 scores.
>
> grp
> grr1
> grr2
> grr3
> grr4
>
> Number of recs in t1 is 3 billion.
>
> Oracle table t2 contains grp and MIN and MAX vales for each of the 4
> scores and also segment.
>
> grp
> grr1_min_sc
> grr1_max_sc
> grr2_min_sc
> grr2_max_sc
> grr3_min_sc
> grr3_max_sc
> grr4_min_sc
> grr4_max_sc
> segment
>
> Number of recs in t2 is 200K
>
> In Oracle, I am performing a table lookup of segment using the
> following
> statement. Segment is unique on t2.
>
> select t2.segment
> from t1, t2
> where t1.grp = t2.grp and
> (t1.grr1 >= t2.grr1_min_sc and
> t1.grr1 < t2.grr1_max_sc) and
> (t1.grr2 >= t2.grr2_min_sc and
> t1.grr2 < t2.grr2_max_sc) and
> (t1.grr3 >= t2.grr3_min_sc and
> t1.grr3 < t2.grr3_max_sc) and
> (t1.grr4 >= t2.grr4_min_sc and
> t1.grr4 < t2.grr4_max_sc)
>
> The lookup table (t2) is set up such that each row of t1 will lookup
> exactly one segment in t2.
>
> How can I write the above code in PL/SQL?
> Since each row of t1 will match with only one row of t2, once there is
> a match
> I guess I would want to goto next record. How can I code that up in PL/
> SQL?
>
> Thanks
>
> Tom
what are you trying to accomplish? do you just want a list of the
segments that are hit? if so, you have it already. Remember that sql
operates on SETS of data.
if your t1.grr4 < t2.grr4_max_sc was instead t1.grr4 <=
t2.grr4_max_sc then your query reduces to
select segment
from t1
join t2 on t1.grr1 between t2.grr1_min_sc and t2.grr1_max_sc
and t1.grr1 between t2.grr2_min_sc and t2.grr2_max_sc
and t1.grr1 between t2.grr3_min_sc and t2.grr3_max_sc
and t1.grr1 between t2.grr4_min_sc and t2.grr4_max_sc
I am also not seeing why you want to do this in PL/SQL ... since
straignt SQL is more than adequate.
I see no reason for you to worry about there being inequalities. you
seem to have the answer in front of you. Now, I will say that you
will have 3 billion rows in your output so you might want to ensure
that your lookup table is properly indexed so you avoid 3 billion full
table scans of a 200,000 record table (do the math on that one...).
You might also want to stick a distinct on there to minimize the
amount of output you get.
Again though, without knowing what you expect for output it is hard to
say what the right answer to your question is.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
[EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
