We have been messing around with Metabot and it seems like it could be a very useful feature for our users but the queries being created do not make any sense, at least in the context of how it is treating the field names.
This is an example of a query it creates:
WITH ALL_CONFIRMED_SALES AS (SELECT
"line_item_key" AS LINE_ITEM_KEY,
"sales_rep" AS SALES_REP,
"company_id" AS COMPANY_ID,
"company_name" AS COMPANY_NAME,
"ad_size" AS AD_SIZE,
"quantity" AS QUANTITY,
"gross" AS GROSS,
"net_cash" AS NET_CASH,
"order_id" AS ORDER_ID
FROM
{{#3487}} AS INNER_QUERY) SELECT
*
FROM
ALL_CONFIRMED_SALES
WHERE
AD_SIZE = 'full page';
Note that all of the select elements are "some string" which basically just always returns that as a literal string. it does not return the value of the field with that name but the literal string of the field name so we never get any results that match.
It is easily corrected by simply doing 'SELECT *' or even this:
WITH ALL_CONFIRMED_SALES AS (SELECT
line_item_key AS LINE_ITEM_KEY,
sales_rep AS SALES_REP,
company_id AS COMPANY_ID,
company_name AS COMPANY_NAME,
ad_size AS AD_SIZE,
quantity AS QUANTITY,
gross AS GROSS,
net_cash AS NET_CASH,
order_id AS ORDER_ID
FROM
{{#3487}} AS INNER_QUERY) SELECT
*
FROM
ALL_CONFIRMED_SALES
WHERE
AD_SIZE = 'full page';
which just replaces the literal " with a `.
Is there some setting we need to fix this SQL generation or is this a bug that needs to be addressed. It just doesn't work at all for us unless we edit the resulting SQL which kind of defeats the point.