CODEWITHSUNDEEP
mysqlsql
furgs09
furgs09

Reputation: 11

No column name specified for column number in derived table

I am getting this error message, can someone advise on a solution?

I have looked over this forum and cant find a solution

Dynamic SQL Error
SQL error code = -104
Invalid command
no column name specified for column number 1 in derived table ACCRG

SELECT DISTINCT locationgroup.name AS MainGroup, product.num AS ProductNumber,
    product.description AS ProductDescription, uom.code AS UOM, company.name AS company,
    sum(((SELECT SUM(productsSold.qtyfulfilled) FROM soitem productsSold WHERE soitem.id = productsSold.id AND productsSold.productid = product.id) * COALESCE(uomconversion.multiply,1)) / COALESCE(uomconversion.factor,1)) AS uomqty

FROM soitem
    LEFT OUTER JOIN so ON (so.id = soitem.soid)
    LEFT OUTER JOIN product ON (soitem.productid = product.id)
    LEFT OUTER JOIN uom ON (product.uomid = uom.id)
    LEFT OUTER JOIN locationgroup ON (so.locationgroupid = locationgroup.id)
    LEFT OUTER JOIN qbclass ON (qbclass.id = soitem.qbclassid)
    LEFT OUTER JOIN customer ON (customer.id = so.customerid)
    LEFT JOIN (SELECT max(accountgrouprelation.id) AS relationID, accountgrouprelation.accountid
                        FROM  accountgrouprelation
            LEFT JOIN accountGroup ON accountGroupRelation.groupId = accountGroup.id
           WHERE UPPER(COALESCE (accountgroup.name, '%')) LIKE UPPER('%')
                        GROUP BY 2) accrg ON customer.accountid = accrg.accountid
    LEFT JOIN accountgrouprelation ON accrg.relationid = accountgrouprelation.id
    LEFT OUTER JOIN accountgroup ON (accountgroup.id = accountgrouprelation.groupid)
    LEFT OUTER JOIN uomconversion ON ((product.uomid = uomconversion.touomid) AND (soitem.uomid = uomconversion.fromuomid))
    JOIN company ON company.id = 1

WHERE soitem.qtyfulfilled > 0
  AND soitem.typeid in (10,12,80)
  AND locationgroup.id IN (1,2,3,4)
  AND qbclass.id LIKE '%'
  AND UPPER(so.salesman) LIKE UPPER('%')
  AND UPPER(COALESCE (accountgroup.name, '%')) LIKE UPPER('%')
  AND soitem.datelastfulfillment BETWEEN '2015-11-30 23:59:59.0' AND '2015-12-31 23:59:59.0'
GROUP BY maingroup, productnumber, productdescription, uom, company
ORDER BY 1,product.num ASCENDING

Upvotes: 1

Views: 736

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521457

Believe it or not, I think the error is coming from your ORDER BY statement:

ORDER BY 1,product.num ASCENDING

I don't believe it is legal to ORDER BY a constant number in MySQL. Telling by the error, it appears that MySQL is trying to find a column called 1 in the ACCRG derived table. No such column exists, hence you are getting this error. Instead, why not just ORDER BY the product number?:

ORDER BY product.num ASCENDING

Upvotes: 1

Related Questions