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.

Answer 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)