Re: Inner queries

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Tue, 03 Mar 2009 10:44:13 +0100
Message-ID: <49acfbf0$0$184$e4fe514c_at_news.xs4all.nl>

Jens Müller schreef:
> Hi,
>
> I have this query:
>
> select u1.user_id, TO_CHAR(u1.LOCATION_DATE, 'YYYY-MM-DD HH24:MI:SS'),
> (select min(sdo_geom.sdo_distance(u1.location, u2.location, 0.005,
> 'unit=M'))
> from user_tracks u2
> where u1.user_id <> u2.user_id and
> u2.user_id between 9172 and 9181
> and u2.location_date in
> (select max(u3.location_date)
> from user_tracks u3
> where u3.location_date < u1.location_date
> and u3.user_id = u2.user_id
> )
> ) min_distance
> from user_tracks u1
> where u1.user_id between 9172 and 9181;
>
>
> user_tracks contains position information about users, and I want the
> nearest neighbor for each position (based on the latest position of that
> user before u1.location_date).
>
> That one above works fine - but how to find the 2nd-nearest neighbor?
>
> This:
>
> select u1.user_id, TO_CHAR(u1.LOCATION_DATE, 'YYYY-MM-DD HH24:MI:SS'),
> u1.aufenthaltsdauer,
> (select * from
>
> (select sdo_geom.sdo_distance(u1.location, u2.location, 0.005,
> 'unit=M') abcde
> from user_tracks u2
> where u1.userid <> u2.user_id and
> u2.user_id between 9172 and 9181
> and u2.location_date in
> (select max(u3.location_date)
> from user_tracks u3
> where u3.location_date < u1.location_date
> and u3.user_id = u2.user_id
> )
> and rownum < 2
> order by abcde
> ) min_distance
> )
> from user_tracks u1
> where u1.user_id between 9172 and 9181;
>
> does not work - invalid identifier "U1"."USERID" at this point: "where
> u1.userid <> u2.user_id" ...

That is because you forgot an underscore in userid, should be user_id....

Shakespeare Received on Tue Mar 03 2009 - 03:44:13 CST