Nested queries, wildcards and distributed databases

We've come across a rather odd quirk with Oracle v 7.1.4.1.0 and/or SQL*Plus 2.1.4.0.0 (under AIX 3.2). We have a query similar to the following:

    SELECT *
    FROM routes
    WHERE rt_country IN (

      SELECT code
      FROM   countries_at_CORP
      WHERE  name LIKE 'AUS%'

This works just fine... In our case, returning only those rows from routes with country codes whose country's name matches 'AUS%'. Fine. Now, if we replace the the search string 'AUS%' with something like '_US%' or even '%US%', the subquery no longer constrains the parent query. It is as if there was no subquery.

    SELECT *
    FROM routes
    WHERE rt_country IN (

      SELECT code
      FROM   countries_at_CORP
      WHERE  name LIKE '_US%'

Does this make sense? Is there something I've missed with respect to the rules of subqueries?

Please reply in e-mail to the address below.

    Thanx in advance.

        ep

+---------------------------------------------------------------------+
|  Curtis Hanson, chanson_at_gpu.srv.ualberta.ca, a Motif apprentice     |
+---------------------------------------------------------------------+