[ic] Complex/SQL Search help!

Russ Riggs interchange-users@lists.akopia.com
Thu Jul 12 21:39:01 2001


I don't know how the IC engine interprets sql but I thought I'd
give you a couple of notes.

1.  the subquery must return exactly one row.  If it doesn't you'll
generate a sql error.

2.  if the field zip_code is a character field then it may need to be
put in single quotes.

3.  As an aside...if you don't need ALL of the fields in the
products table then list each field you do need in the select
statement.  Doing a "select *" is very expensive on large
tables if you only need a field or two.

If all else fails then use mysql and key in your query replacing
'zip_code' with a valid zip and see what you get.  In fact, I'd
cut and paste straight from your code to get exactly the results
you should get from your code.  I do this
all the time when I can't get queries to work.  Usually JDBC
based but IC should be essentially the same.

russ....

Brian Kosick wrote:

> Hello all,
>         I have been trying to get a search to work all day.  First let me
> explain.  I have a two tables my products table and a zip_zones table with
> two columns zip and zone
> I need to do a search on a user inputted zip to return which should return
> the zone that it's in then do a lookup on the products table to match the
> products in that zone.  The docs say no nested searches allowed, so I have
> to figure out how to do it with SQL
>
> <psuedo code>
> <input type="text" name="zip_code">
> <input type="mv_sql_query" VALUE="
>                         select * from products where zone = (
>                                 SELECT zone from zip_zone
>                                 where zip = zip_code
>                                 )">
>
> All I get is an error usually stating "No match for zip_code" or invalid
> search string "zip_code"
>
> What am I missing?
>
>
> Brian Kosick
> Web Programmer
> New Age Consulting Service, Inc.
> 216-619-2000
> briank@nacs.net
>
> _______________________________________________
> Interchange-users mailing list
> Interchange-users@lists.akopia.com
> http://lists.akopia.com/mailman/listinfo/interchange-users