CODEWITHSUNDEEP
mysqlsql
Robert Talada
Robert Talada

Reputation: 372

Why does MySQL show Unknown column for a column that is clearly defined?

I am building a SQL query to produce a table of tickets which were accepted or closed late.

SELECT 
    svc_guid,
    svc_priority,
    cast(svc_minsinqueue AS SIGNED)-(pri_svc_accept_hours * 60) AS accept_overdue,
    cast(svc_minselapsed AS SIGNED)-(pri_svc_close_hours * 60) AS closure_overdue
FROM
    svcrequest
LEFT JOIN 
    priorities
ON 
    pri_id=svc_priority
WHERE
    accept_overdue > 0 OR
    closure_overdue > 0
ORDER BY 
    accept_overdue DESC;

The result is:

#1054 - Unknown column 'accept_overdue' in 'where clause'

The same exact query with WHERE 1 will produce no errors. (Even with ORDER BY accept_overdue DESC)

Upvotes: 0

Views: 208

Answers (5)

forpas
forpas

Reputation: 164089

You can use these aliased columns in a HAVING clause instead of WHERE:

HAVING
    accept_overdue > 0 OR
    closure_overdue > 0
ORDER BY 
   accept_overdue DESC

Upvotes: 1

Marcelo Soares
Marcelo Soares

Reputation: 1

Though both tables have different column names it is recommended to specify them adding alias, this way:

: FROM svcrequest as r LEFT JOIN priorities as p ON :

Then, before each column you should now add these alias. E.g.

SELECT p.svc_guid,

Of course if svc_guid belongs to priorities table

Upvotes: -1

Eric
Eric

Reputation: 3257

If you understand SQL order of operation, you will understand why your code won't work.

FROM, including JOINs
WHERE
GROUP BY
HAVING
WINDOW functions
SELECT
DISTINCT
UNION
ORDER BY
LIMIT and OFFSET

https://www.periscopedata.com/blog/sql-query-order-of-operations

WHERE is evaluated before SELECT. Change your code to 

SELECT 
    svc_guid,
    svc_priority,
    cast(svc_minsinqueue AS SIGNED)-(pri_svc_accept_hours * 60) AS accept_overdue,
    cast(svc_minselapsed AS SIGNED)-(pri_svc_close_hours * 60) AS closure_overdue
FROM
    svcrequest
LEFT JOIN 
    priorities
ON 
    pri_id=svc_priority
WHERE
    cast(svc_minsinqueue AS SIGNED)-(pri_svc_accept_hours * 60) > 0 OR
    cast(svc_minselapsed AS SIGNED)-(pri_svc_close_hours * 60) > 0
ORDER BY 
    accept_overdue DESC;

Upvotes: 1

Rendson Fernandes
Rendson Fernandes

Reputation: 34

try this

SELECT 
    svc_guid,
    svc_priority,
    cast(svc_minsinqueue AS SIGNED)-(pri_svc_accept_hours * 60) AS accept_overdue,
    cast(svc_minselapsed AS SIGNED)-(pri_svc_close_hours * 60) AS closure_overdue
FROM
    svcrequest
LEFT JOIN 
    priorities
ON 
    pri_id=svc_priority
WHERE
    cast(svc_minsinqueue AS SIGNED)-(pri_svc_accept_hours * 60)  > 0 OR
    cast(svc_minselapsed AS SIGNED)-(pri_svc_close_hours * 60) > 0
ORDER BY 
    3 DESC;

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

You can't use column name alias in where clause
the where clause is evaluated before the select clause so the db engine can't know this column alias for build a correct query you must copy the related column code 

SELECT 
    svc_guid,
    svc_priority,
    cast(svc_minsinqueue AS SIGNED)-(pri_svc_accept_hours * 60) AS accept_overdue,
    cast(svc_minselapsed AS SIGNED)-(pri_svc_close_hours * 60) AS closure_overdue
FROM
    svcrequest
LEFT JOIN 
    priorities
ON 
    pri_id=svc_priority
WHERE
    cast(svc_minsinqueue AS SIGNED)-(pri_svc_accept_hours * 60)  > 0 OR
    cast(svc_minselapsed AS SIGNED)-(pri_svc_close_hours * 60) > 0
ORDER BY 
accept_overdue DESC;

as you can see the ORDER BY clause do the fact is evaluated as last .. can use the column alias

Upvotes: 3

Related Questions