subquery in FROM must have an alias

SqlOraclePostgresqlSubquery

Sql Problem Overview


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?

Sql Solutions


Solution 1 - Sql

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

Solution 2 - Sql

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.

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionroykasaView Question on Stackoverflow
Solution 1 - SqlJohn WooView Answer on Stackoverflow
Solution 2 - SqlFrank ChengView Answer on Stackoverflow