Postgresql – Error running PostgreSQL query from SQL Server 2016

postgresqlsyntax

The below query runs well in SQL Server 2016:

select ResellerId, vCompanyName ,x.* 
from wlt_tblReseller AS main 
outer apply ( select (count (ipkReportTypeId)) AS "count", vReportTypeName  
              from wlt_tblReseller _all 
              inner join wlt_tblClient clients  on clients.ifkParentResellerId = _all.ResellerId
              inner join wlt_tblReport_CompanyMaster reporslogs on reporslogs.ifkCompanyId = clients.ClientId
              inner join wlt_tblReports_TypeMaster rpt_types on rpt_types.ipkReportTypeId = reporslogs.ifkReportTypeId 
              where RootResellerId = main.ResellerId 
                and rpt_types.bStatus =1 
                and bIsStatic =1 
                and vReportTypeName  is not null
              group by ipkReportTypeId,vReportTypeName
            ) AS x 
WHERE IsMiniReseller = 0 
  and ResellerId <> 1  
  and vReportTypeName is not null 
order by  vCompanyName desc

But when I take it to PostgreSQL and change OUTER APPLY to LEFT JOIN LATERAL, it does not run and produces the following error:

ERROR: syntax error at or near "WHERE" LINE 9: )AS x WHERE
IsMiniReseller = 0 and ResellerId <> 1 and v…
SQL state: 42601 Character: 649

What could I be missing?

Best Answer

select main.ResellerId, main.vCompanyName , x."count", x.vReportTypeName
from wlt_tblReseller AS main 
LEFT JOIN   ( select (count (ipkReportTypeId)) AS "count", vReportTypeName, RootResellerId  
              from wlt_tblReseller _all 
              inner join wlt_tblClient clients  on clients.ifkParentResellerId = _all.ResellerId
              inner join wlt_tblReport_CompanyMaster reporslogs on reporslogs.ifkCompanyId = clients.ClientId
              inner join wlt_tblReports_TypeMaster rpt_types on rpt_types.ipkReportTypeId = reporslogs.ifkReportTypeId 
              where 
                    rpt_types.bStatus =1 
                and bIsStatic =1 
                and vReportTypeName  is not null
              group by ipkReportTypeId,vReportTypeName
            ) AS x ON x.RootResellerId = main.ResellerId
WHERE IsMiniReseller = 0 
  and ResellerId <> 1  
  and vReportTypeName is not null 
order by  vCompanyName desc

PS. Add table aliases to all column names for to remove potential ambiguity...