0

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?

0

1 Answer 1

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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.