[ic] RE: [query] help ... still

[Actormailings.com info]

Sorry for the abundance of info, but I want to be perfectly clear.
My original message and two responses follow.
> 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.

>From Mike: (thanks!)

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].
_________________________________
>From Kevin: (thanks!)

You could rewrite your query as the following:

    [query type=list more=1 ml=50 sql=|
        SELECT  o.sku AS sku,
                o.quantity AS quantity,
                o.subtotal AS subtotal,
                t.lname AS lname,
                t.fname AS fname,
                t.status AS status,
                t.comments AS comments
        FROM    orderline o, transactions t
        WHERE   o.order_number = t.order_number
    |]
        ...
    [/query]

Or you could use [sql-pos 0], [sql-pos 1] etc in your existing code.
[sql-pos 0] refers to the first column in the resultset, [sql-pos 1]
refers to the second etc.

I personally prefer the re-written query.
_________________________________

I am not trying to put tablename.fieldname into the return values; I'm
simply trying to get it to work in the sql.  Putting in a "tablename."
corrupts the sql.  Even adding "fieldname AS alternate_fieldname" breaks the
query.  And "Group by" still breaks the query.  I have tested this ONE PIECE
AT A TIME.  I wouldn't even post it here if none of it were working.  I'd
continue until I found my mistake.  But as I test each component, the sql
breaks when any instance of "tablename.", "field AS alternate", or "GROUP
BY" appears in the sql statement.
I've tested the query with one table as well as two.

Kevin, I tried using your code above EXACTLY.

I get a blank response.  I've tried

[query sql="SELECT sku, quantity, subtotal, order_number FROM orderline
ORDER BY sku"
      type=list
      more=1
        ml=50]
...
[/query]


This works great.

This, however does NOT work:
[query sql="SELECT orderline.sku as o_sku, quantity, subtotal, order_number
FROM orderline ORDER BY sku"
      type=list
      more=1
        ml=50]
... (of course listing [sql-param o_sku] as one of the return fields.)
[/query]

I try even the benign:
[query sql="SELECT sku AS sku, quantity, subtotal, order_number FROM
orderline ORDER BY sku"
      type=list
      more=1
        ml=50]
...
[/query]

I get nothing back.

Even [query sql="SELECT sku, quantity, subtotal, order_number FROM orderline
GROUP BY sku"
      type=list
      more=1
        ml=50]
...
[/query]
returns a blank result.
___________________________

I'm going to try (AGAIN) to accomplish this with the IC search tags, but...
SQL should work.  IC is so amazing and so sophisticated.  It seems ludicrous
to me that I can't create a simple 2-table report.  I'm even considering
downloading my tables into Access so I can be guaranteed access to the
information.  Eventhough I own Access, I'm morally opposed to using it!!
(aside from the fact that I'll have to download my tables 4 times each
weekend.) Please help me.  I KNOW there's a way to fix it in IC.

Thanks again for wading through this.
Jordan

Donate to the Troubadour Theater Company - without spending a dime!!
Save money on Long Distance, Internet, or cell phone service.  A portion of
the proceeds are donated to Troubadour
http://www.excelir.com/thesavage