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?Any help will be much appreciated.
Regards Chris.