subquery in FROM must have an alias

Question

I have this query I have written in PostgreSQL that returns an error saying:

[Err] ERROR:
LINE 3: FROM (SELECT DISTINCT (identifiant) AS made_only_recharge

This is the whole query:

SELECT COUNT (made_only_recharge) AS made_only_recharge
FROM (
    SELECT DISTINCT (identifiant) AS made_only_recharge
    FROM cdr_data
    WHERE CALLEDNUMBER = '0130'
    EXCEPT
    SELECT DISTINCT (identifiant) AS made_only_recharge
    FROM cdr_data
    WHERE CALLEDNUMBER != '0130'
)

I have a similar query in Oracle that works fine. The only change is where I have EXCEPT in Oracle I have replaced it with the MINUS key word. I am new to Postgres and don't know what it is asking for. What's the correct way of handling this?

It looks to me the exception is unnecessary as the first where clause already excepts it: `CALLEDNUMBER = '0130'`. — Clodoaldo Neto, Feb 08 '13 at 08:27

score 194 · Accepted Answer · edited May 18 '21 at 14:36

194

Add an ALIAS onto the subquery,

SELECT  COUNT(made_only_recharge) AS made_only_recharge
FROM    
    (
        SELECT DISTINCT (identifiant) AS made_only_recharge
        FROM cdr_data
        WHERE CALLEDNUMBER = '0130'
        EXCEPT
        SELECT DISTINCT (identifiant) AS made_only_recharge
        FROM cdr_data
        WHERE CALLEDNUMBER != '0130'
    ) AS derivedTable                           -- <<== HERE

edited May 18 '21 at 14:36

Mus

7,290
24
86
130

answered Feb 08 '13 at 06:50

John Woo

258,903
69
498
492

23

@JohnWoo thanks for this, but why is it needed (I guess I'm asking a theory question here)? – Andrew Cassidy Mar 23 '15 at 19:27
1

@AndrewCassidy You have to define so you will be able to add further constraints on your query (WHERE derivedTable. = 5). otherwise your db will not know how to refer to the subquery – stackhelper101 Nov 11 '15 at 15:21
54

@AndrewCassidy It is just unlucky syntax. As long as you are not referencing to that subquery, it does not matter what it's alias is. Personally, I'm using `AS pg_sucks`, meaning "well, here you have some redundant identifier, but you could generate some internally by yourself, damn postgres!" :) – Tregoreg Dec 16 '15 at 04:54
2

@Tregoreg My aliases always involve some sort of expletive to the same effect. – jbowman Nov 25 '20 at 19:12

score -1 · Answer 2 · answered Mar 26 '20 at 03:13

-1

In the case of nested tables, some DBMS require to use an alias like MySQL and Oracle but others do not have such a strict requirement, but still allow to add them to substitute the result of the inner query.

answered Mar 26 '20 at 03:13

Frank Cheng

77
2
3

3

Your wording suggest there **is** such a requirement for both Oracle and MySQL. Am I reading it right? – Scratte Mar 26 '20 at 07:21
@Scratte I think you're right and the wording is turned around. "MySQL and Oracle but others" should be "Postgresql, but other ssuch as MySQL and Oracle" I think. Of course it's still a run-on sentence, and could be further improved. The 2013 answer is fine and this answer adds nothing (comment on the 2013 answer if you must), so the latter should be removed. – lmat - Reinstate Monica Mar 26 '20 at 15:26
mysql does require aliases – ysth Oct 06 '21 at 22:28

subquery in FROM must have an alias

2 Answers2

Linked

Related