[ic] How to join tables with the [query] tag

[Javier Martin]

Hi,

I would like to know if there's someone who has experienced the same as me.

I'm trying to provide the admin UI with a new feature: report the total
revenue PER PRODUCT. This is useful if you want to know how products perform
independently from each other, and specially if the items are soft goods and
you have not such a thing as an inventory.

The problem comes when I try to join the tables 'transactions' and
'orderline' by the field 'order_number'. This allows knowing on which
campaign was sold the product, etc. It says "Parse error near .affiliate".
The exact parameters of the [query] tag are:

[query
        st=db
        table=transactions
        hashref=main
        sql="
             select transactions.affiliate,
              transactions.campaign,
              etc. etc (see the log below).
"][/query]

The error.log shows:

-----------------------------------------------------------


192.168.0.58 rxf5tZ3J:192.168.0.58 - [20/September/2001:16:51:09 +0000]
foundation /cgi-bin/foundation/admin/reports/per_item/ByAffiliate Bad SQL
statement: Parse error near .affiliate,
>                transactions.campaign,
>                transactions.order_date,
>                transactions.order_number,
>                orderline.order_number,
>                orderline.sku,
>                orderline.subtotal
>                       from  transactions, orderline
>                       WHERE
>                transactions.order_number = orderline.order_number and
>                orderline.sku = 00dc9066f52ad31184eb0080c70ea797
>                transactions.deleted != '1'
>                       order by affiliate, campaign, order_date
>        at /usr/lib/interchange/lib/Vend/Scan.pm line 586.
>
> Query was:
>               select transactions.affiliate,
>                transactions.campaign,
>                transactions.order_date,
>                transactions.order_number,
>                orderline.order_number,
>                orderline.sku,
>                orderline.subtotal
>                       from  transactions, orderline
>                       WHERE
>                transactions.order_number = orderline.order_number and
>                orderline.sku = 00dc9066f52ad31184eb0080c70ea797
>                transactions.deleted != '1'
>                       order by affiliate, campaign, order_date
>
192.168.0.58 rxf5tZ3J:192.168.0.58 - [20/September/2001:16:51:10 +0000]
foundation /cgi-bin/foundation/admin/reports/per_item/ByAffiliate Query
rerouted from table transactions failed: Bad SQL statement: Parse error near
.affiliate,
>                transactions.campaign,
>                transactions.order_date,
>                transactions.order_number,
>                orderline.order_number,
>                orderline.sku,
>                orderline.subtotal
>                       from  transactions, orderline
>                       WHERE
>                transactions.order_number = orderline.order_number and
>                orderline.sku = 00dc9066f52ad31184eb0080c70ea797
>                transactions.deleted != '1'
>                       order by affiliate, campaign, order_date
>        at /usr/lib/interchange/lib/Vend/Scan.pm line 586.
>
> Query was:
>               select transactions.affiliate,
>                transactions.campaign,
>                transactions.order_date,
>                transactions.order_number,
>                orderline.order_number,
>                orderline.sku,
>                orderline.subtotal
>                       from  transactions, orderline
>                       WHERE
>                transactions.order_number = orderline.order_number and
>                orderline.sku = 00dc9066f52ad31184eb0080c70ea797
>                transactions.deleted != '1'
>                       order by affiliate, campaign, order_date
>        at /usr/lib/interchange/lib/Vend/Table/DBI.pm line 1544
>
> Query was:
>               select transactions.affiliate,
>                transactions.campaign,
>                transactions.order_date,
>                transactions.order_number,
>                orderline.order_number,
>                orderline.sku,
>                orderline.subtotal
>                       from  transactions, orderline
>                       WHERE
>                transactions.order_number = orderline.order_number and
>                orderline.sku = 00dc9066f52ad31184eb0080c70ea797
>                transactions.deleted != '1'
>                       order by affiliate, campaign, order_date
>
192.168.0.58 rxf5tZ3J:192.168.0.58 - [20/September/2001:16:51:10 +0000]
foundation /cgi-bin/foundation/admin/reports/per_item/ByAffiliate query
returned ""



----------------------------------------------------------------

Thanks in advance,

Javier