[ic] How to make a query disappear...or [query help] (is there a bug in IC's query ability?)

[Mike Heins]

Quoting Jordan (savage@troubie.com):
> Hi.
> I've tried every permutation that I can think of:
> I have tried leaving out the tablenames in the select statement as well as
> the [sql-param field] tags.  I've tried using the INNER JOIN ... ON syntax.
> basic code:
> 
> [query sql="SELECT orderline.sku, orderline.quantity, orderline.subtotal,
> transactions.lname, transactions.fname, transactions.status,
> transactions.comments FROM orderline, transactions WHERE
> orderline.order_number = transactions.order_number"
>       type=list
>       more=1
>         ml=50]
> 
>    [on_match]Matched<br>[/on_match]
>       [no_match]Not Found<br>[/no_match]
>       [list]
>         [sql-param sku] [sql-param lname] [sql-param therestofthefields]<BR>
>       [/list]
>       [more_list]
>         [more]
>       [/more_list]
>    [/query]
> 
> 
> 
> If the sql has "tablename.fieldname" as opposed to simply "fieldname", the
> query result is empty.  I can find no way to join the tables.  I've tried
> simply listing the field names, but then there is no way to specify the join
> field.  (ie.  "WHERE orderline.order_number = transactions.order_number"
> breaks the sql statement.)   As soon as I add the tablename1.fieldname =
> tablename2.fieldname, I get nothing.  As a test, I tried doing a one-table
> query using tablename.fieldname.  That comes up empty too whereas the
> fieldnames alone work just fine.

DBI simply does not support tablename.fieldname in the return value,
so please get it out of your head that you can refer to fields that
way in Interchange. This is documented.

You can just refer to them by the fieldname. In the case of field names
that are the same but need to be selected from different tables, you
can use "SELECT orderline.quantity as o_quan,..." and refer to it
as [sql-param o_quan].

-- 
Mike Heins
Perusion -- Expert Interchange Consulting    http://www.perusion.com/
phone +1.513.523.7621      <mike@perusion.com>

Research is what I'm doing when I don't know what I'm doing.
-- Wernher Von Braun