Getting error while retrieving data from a sub query and using that data source in another sub query above that

0 votes
asked Sep 19, 2016 by ms13
select id,proc_name,p_date,p_no,p_count
from (
       (Select id,proc_name,p_date,p_no from aa) x 
          join
       (select id,count(p_no) p_count from aa group by mrn) y 
          on x.id=y.id 
     ) a 
FROM (select id,proc_name,p_date,p_no from zz) aa  

getting the error code 42601 at the position of FROM (in upper case).

1 Answer

0 votes
answered Sep 19, 2016 by laurenz-albe

A SELECT statement can only have one FROM clause.

Instead of using multiple FROM clauses, you should JOIN the tables.

There is no need to have two SELECTs from table aa, you can do that with a single SELECT using window functions:

SELECT id, proc_name, p_date, p_no,
       count(p_no) OVER (PARTITION BY mrn) p_count
FROM aa;

You didn't tell over which columns you want to join aa and zz, but your statement could look something like this:

SELECT a.id, a.proc_name, a.p_date, a.p_no, a.p_count
FROM
   (SELECT id, proc_name, p_date, p_no,
           count(p_no) OVER (PARTITION BY mrn) p_count
    FROM aa) a
   JOIN zz
      ON <join condition for a and zz>;
Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
Website Online Counter

...