Error when using a specific alias

Hello, everyone.
I was stuck on this screen for a while, that’s why I’d like to share my doubt with you.

On this screen my code was almost exactly like the answer. The only difference is that I named the query contained in the WITH clause ‘all_customers’ instead of ‘customers_usa_gt_90’.

Then, in the main query I gave it the alias ‘all’. And this seemed to be a mistake because I got over and over the error message that you see below.
I tried changing different things till I tried changing the alias, which I thought could be any word. But just by changing the alias to ‘al’ (removing one L) the code worked just fine.

So my guess is that the word ALL is reserved for another function and can’t be used as an alias.
Sorry if this is obvious to more advanced users, and please let me know if I’m on the right path.

Thanks!

My Code:

WITH all_customers AS
    (
     SELECT * FROM customer_usa
     INTERSECT
     SELECT * FROM customer_gt_90_dollars
    )

SELECT
    e.first_name || ' ' || e.last_name employee_name,
    COUNT(all.customer_id) customers_usa_gt_90
FROM  employee e
LEFT JOIN all_customers all ON all.support_rep_id = e.employee_id
WHERE e.title = 'Sales Support Agent'
GROUP BY 1
ORDER BY 1;

What actually happened:

(sqlite3.OperationalError) near ".": syntax error
[SQL: WITH all_customers AS     (      SELECT * FROM customer_usa      INTERSECT      SELECT * FROM customer_gt_90_dollars     )  SELECT     e.first_name || ' ' || e.last_name employee_name,     COUNT(all.customer_id) customers_usa_gt_90 FROM  employee e LEFT JOIN all_customers all ON all.support_rep_id = e.employee_id WHERE e.title = 'Sales Support Agent' GROUP BY 1 ORDER BY 1;]
(Background on this error at: http://sqlalche.me/e/e3q8)

Your diagnosis is correct, ALL is a reserved keyword in SQLite.

Thank you very much!

1 Like