Error running PostgreSQL query from SQL Server 2016

Question

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?

Answer 1

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...