CODEWITHSUNDEEP
sqlpostgresql
Jk041
Jk041

Reputation: 982

Syntax error with aliases in PostgreSQL subqueries

I'm new to SQL and trying to do a certain query in which I need two subqueries. I have to give them aliases, but when I do it says:

ERROR:  syntax error at or near "as"

But I don't see anything wrong in the syntax. I'm having a lot of trouble with this kind of queries and I don't find much information or examples. Would you help me?

Select
    ...
from
    turno,
    (select * from
        (
            select 
                ...
             from 
                accion
            where
                accion.tipo = 4 or accion.tipo = 5
        ) as part1
        union
        (
            select 
                ...
             from 
                accion
            where
                accion.tipo <> 4 and accion.tipo <> 5
        ) as part2
    ) as accion
where 
    ...
;

Thanks a lot.

Upvotes: 0

Views: 3940

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657777

You have more query levels than necessary and mix them up. Try:

SELECT ...
FROM   turno,
      ( 
    SELECT ...
    FROM   accion
    WHERE  accion.tipo = 4 OR accion.tipo = 5

    UNION
    SELECT ...
    FROM   accion
    WHERE  accion.tipo <> 4 AND accion.tipo <> 5
   ) AS accion
WHERE ...

Better:

SELECT ...
FROM   turno
JOIN   (    
    SELECT ...
    FROM   accion
    WHERE  accion.tipo = 4 OR accion.tipo = 5

    UNION
    SELECT ...
    FROM   accion
    WHERE  accion.tipo <> 4 AND accion.tipo <> 5
   ) AS accion ON <join condition>
WHERE ...

Better yet, simplify to:

SELECT ...
FROM   turno
JOIN   (    
    SELECT ...
    FROM   accion
    WHERE  accion.tipo  = 4
       OR  accion.tipo  = 5
       OR (accion.tipo <> 4 AND accion.tipo <> 5)
   ) AS accion ON <join condition>
WHERE ...

Yields the same result, only faster & simpler.

The conditions qualify all rows (except a.tipo IS NULL) in accion. So in this particular case you can further simplify:

SELECT ...
FROM   turno
JOIN   accion a ON a.tipo IS NOT NULL AND <join condition>
WHERE  ...

But that's probably due to over-simplification of the problem, right?

This last example also demonstrates how the keyword AS is just noise in this context.

Upvotes: 2

user359040
user359040

Reputation:

Try:

Select
    ...
from
    turno,
    (select * from
        (
            select 
                ...
             from 
                accion
            where
                accion.tipo = 4 or accion.tipo = 5
         union
            select 
                ...
             from 
                accion
            where
                accion.tipo <> 4 and accion.tipo <> 5
        ) as part1_2
    ) as accion
where 
    ...
;

Upvotes: 1

Related Questions