MySQL: Unknown column '%' in 'order clause' Error

Question

The following (unabridged) MySQL query:

/* May Report */
SELECT db.prod.id AS "Prod. Spec. ID (TEST)",
    db.prod.cycle_type AS "BiCode (TEST)",
    SUM(db.prod.cycles) "Number of Cycles (TEST)",
    db.instetail_d.id AS "Detail # (TEST)",
    db.cont.id AS "Unique ID (TEST)",
    db.sale.id AS "Non-U. ID (TEST)",
    DATE_FORMAT(db.prod.start_date,'%m-%d-%Y') AS "START DATE (TEST)",
    DATE_FORMAT(db.prod.end_date,'%m-%d-%Y') AS "END DATE (TEST)",
    db.sale.name AS "ESTIMATE NAME",
    db.pub_l.pub_name AS "PUBLICATION",
    db.pub_l.s AS "SECTION",
    CASE WHEN db.prod.market_code = "NNJ" THEN "NORTHERN NJ"
        WHEN db.prod.market_code = "HAT CT" THEN "HARTFORD-NEW HAVEN"
        ELSE NULL END AS "Market",
    db.prod.market_code AS "MARKET CODE (TEST)",
    db.instetail_d.market AS "MARKET (TEST)",
    CONCAT_WS(" - ", db.prod.format, DATE_FORMAT(db.prod.start_date,'%m/%d/%Y'), DATE_FORMAT(db.prod.end_date,'%m/%d/%Y')) AS "VEHICLE/QTY",
    db.temp.brand_name AS "PRODUCT",
    db.prod.product AS "PROD CODE",
    db.prod.sig AS "LOC DESCRIPTION",
    db.instetail_d.subset AS "EMP NAME",
    db.rd.target_audience AS "PACKAGE TARGET",
    (db.prod.cos * 0.37) AS "COSTS (NET)",
    (db.prod.comment5 / 4) AS "dynamicColumnHeader"
    FROM db.prod
    INNER JOIN temp
        ON temp.product_code = prod.product
    INNER JOIN db.cont_prod
        ON db.prod.id = db.cont_prod.prod
    INNER JOIN db.cont
        ON db.cont_prod.cont = db.cont.id
    INNER JOIN db.pub_l
        ON (db.cont.buying_company_id = db.pub_l.buying_company_id AND db.cont.selling_company_id = db.pub_l.selling_company_id )
    INNER JOIN db.sale
        ON db.cont.sale = db.sale.id
    INNER JOIN db.inst
        ON (db.cont.id = db.inst.cont AND db.sale.id = db.inst.sale)
    INNER JOIN db.instetails_d
        ON db.inst.id = db.instetails_d.inst
    INNER JOIN db.instetail_d
        ON db.instetails_d.detail = db.instetail_d.id
    LEFT JOIN db.rd
        ON db.cont.rfp_id = db.rd.id
    WHERE (db.sale.id = 1818 OR db.sale.id = 1857)
    AND db.prod.uni_hash_target = db.instetail_d.uni_hash_target
    AND (db.instetail_d.location_description = db.prod.sig OR db.instetail_d.location_description IS NULL OR db.instetail_d.location_description = "")
    AND db.prod.space NOT IN ("PRODUCTION","TAXES","CREDIT","INSTALLCHRG","MAINTENANCE","EXTCHARGE")
    AND (DATE_FORMAT(db.prod.start_date, "%m") = 5 OR (DATE_FORMAT(db.prod.start_date, "%m") = 6) AND DATE_FORMAT(db.prod.end_date, "%d") > 4)
    GROUP BY db.prod.sig
    /* ORDER BY DATE_FORMAT(db.prod.start_date, "%m") ASC, DATE_FORMAT(db.prod.start_date, "%d") ASC; */
UNION ALL
SELECT db.prod.id AS "Prod. Spec. ID (TEST)",
    db.prod.cycle_type AS "BiCode (TEST)",
    SUM(db.prod.cycles) "Number of Cycles (TEST)",
    db.instetail_d.id AS "Detail # (TEST)",
    db.cont.id AS "Unique ID (TEST)",
    db.sale.id AS "Non-U. ID (TEST)",
    DATE_FORMAT(db.prod.start_date,'%m-%d-%Y') AS "START DATE (TEST)",
    DATE_FORMAT(db.prod.end_date,'%m-%d-%Y') AS "END DATE (TEST)",
    db.sale.name AS "ESTIMATE NAME",
    db.pub_l.pub_name AS "PUBLICATION",
    db.pub_l.s AS "SECTION",
    CASE WHEN db.prod.market_code = "NNJ" THEN "NORTHERN NJ"
        WHEN db.prod.market_code = "HAT CT" THEN "HARTFORD-NEW HAVEN"
        ELSE NULL END AS "Market",
    db.prod.market_code AS "MARKET CODE (TEST)",
    db.instetail_d.market AS "MARKET (TEST)",
    CONCAT_WS(" - ", db.prod.format, DATE_FORMAT(db.prod.start_date,'%m/%d/%Y'), DATE_FORMAT(db.prod.end_date,'%m/%d/%Y')) AS "VEHICLE/QTY",
    db.temp.brand_name AS "PRODUCT",
    db.prod.product AS "PROD CODE",
    db.prod.sig AS "LOC DESCRIPTION",
    db.instetail_d.subset AS "EMP NAME",
    db.rd.target_audience AS "PACKAGE TARGET",
    (db.prod.cos * 0.37) AS "COSTS (NET)",
    (db.prod.comment5 / 4) AS "dynamicColumnHeader"
    FROM db.prod
    INNER JOIN temp
        ON temp.product_code = prod.product
    INNER JOIN db.cont_prod
        ON db.prod.id = db.cont_prod.prod
    INNER JOIN db.cont
        ON db.cont_prod.cont = db.cont.id
    INNER JOIN db.pub_l
        ON (db.cont.buying_company_id = db.pub_l.buying_company_id AND db.cont.selling_company_id = db.pub_l.selling_company_id )
    INNER JOIN db.sale
        ON db.cont.sale = db.sale.id
    INNER JOIN db.inst
        ON (db.cont.id = db.inst.cont AND db.sale.id = db.inst.sale)
    INNER JOIN db.instetails_d
        ON db.inst.id = db.instetails_d.inst
    INNER JOIN db.instetail_d
        ON db.instetails_d.detail = db.instetail_d.id
    LEFT JOIN db.rd
        ON db.cont.rfp_id = db.rd.id
    WHERE (db.sale.id = 1818 OR db.sale.id = 1857)
    AND db.prod.uni_hash_target = db.instetail_d.uni_hash_target
    AND (db.instetail_d.location_description = db.prod.sig OR db.instetail_d.location_description IS NULL OR db.instetail_d.location_description = "")
    AND db.prod.space NOT IN ("PRODUCTION","TAXES","CREDIT","INSTALLCHRG","MAINTENANCE","EXTCHARGE")
    AND (DATE_FORMAT(db.prod.start_date, "%m") = 5 OR (DATE_FORMAT(db.prod.start_date, "%m") = 6) AND DATE_FORMAT(db.prod.end_date, "%d") > 4)
    GROUP BY db.prod.sig
    HAVING SUM(db.prod.cycles) >= 0.5
    ORDER BY DATE_FORMAT(db.prod.start_date, "%m") ASC, DATE_FORMAT(db.prod.start_date, "%d") ASC;

produces the following error:

Unknown column 'Unknown column 'db.prod.start_date' in 'order clause'

using Sequel Pro.

I have the goal of duplicating every column that fits the following criteria:

SUM(db.prod.cycles) >= 0.5

The entire query will complete before giving me the error (a minute or so on a query this large).

I have tried to create a subquery and use a UNION instead of a UNION ALL to fix the issue without success.

score 1 · Accepted Answer · answered Feb 25 '16 at 21:36

1

After the two queries are in UNION the field "db.prod.start_date" is now known as "START DATE (TEST)". Try changing your ORDER BY clause to reference the new "as column name."

Example: ORDER BY START DATE (TEST)

answered Feb 25 '16 at 21:36

ale8oneboy

127
4

the query works but now both tables are `ORDER BY` not at one complete table but two tables where one follows the other. For example, I wanted to have the first entry of my report doubled. It did double, but the second row can be found after the last row of the first table in the `UNION ALL`. How can I fix this? – Mike Feb 25 '16 at 22:05
1

A quick an dirty way is to add a column to both queries and sort by that first, then by the rest. The column in the first query may be like '1 as QueryNumber'. In the second query '2 as QueryNumber'. Sort by Query Number, etc... – ale8oneboy Feb 25 '16 at 22:08
Worked again! I just used `sort` as the column title but it worked! Thank you so much! – Mike Feb 25 '16 at 22:12

MySQL: Unknown column '%' in 'order clause' Error

1 Answers1