select device, area
from devicetbl d, iptable i
where ( d.oc1 between i.ocb1(+) and i.oce1(+) )
and (d.oc2 between i.ocb2(+) and i.oce2(+))
and (d.oc3 between i.ocb3(+) and i.oce3(+))
and (d.oc4 between i.ocb4(+) and i.oce4(+))
/
<enjoy/>
Anurag
"Rob Edwards" <raedwa01_at_hotmail.com> wrote in message
news:8ea2ecb0.0207091152.cea3f2d_at_posting.google.com...
> I have a database that we prototyped in Access originally. The tables
> were built with the idea of specifing that specific IP ranges covered
> a set area.
>
> Example:
>
> Area Ocb1 Ocb2 Ocb3 Ocb4 Oce1 Oce2 Oce3 Oce4
> A1 10 100 1 1 10 100 224 255
> A2 10 100 225 1 10 100 225 255
> A3 10 120 1 1 10 120 1 255
>
> Where Ocb1-4 and Oce1-4 are the beginning and ending ranges of each IP
> octet respectively.
>
> Each device with an IP was stored in its table as such.
>
> Device Oc1 Oc2 Oc2 Oc4
> D1 10 100 105 2
> D2 10 100 225 70
> D3 10 110 210 12
>
> Now, i need a query that would return something like:
>
> Device Area
> D1 A1
> D2 A2
> D3 NULL
>
> In access we wrote the query as such
>
> Select Device, Area from DeviceTbl D Left join IPTable I ON ((D.Oc1
> between I.Ocb1 and Oce1) AND (D.Oc2 between I.Ocb2 and Oce2) AND
> (D.Oc3 between I.Ocb3 and Oce3) AND (D.Oc4 between I.Ocb4 and Oce4))
>
> This worked in access, but i cannot seem to figure out how to do it in
> Oracle.
>
> Currently all i can do is
>
> Select Device, Area from DeviceTbl D , IPTable I Where ((D.Oc1 between
> I.Ocb1 and Oce1) AND (D.Oc2 between I.Ocb2 and Oce2) AND (D.Oc3
> between I.Ocb3 and Oce3) AND (D.Oc4 between I.Ocb4 and Oce4))
>
> But this doesn't give me the null results.
>
> Any ideas?
>
> Thanks,
>
> Rob Edwards
Received on Tue Jul 09 2002 - 19:55:28 CDT
