Trying to use join to aggregate data and getting odd output

Question

When running this query been getting an odd error I've not yet encountered, this query is executed within Netezza

      SELECT    a.CALENDAR_DATE,
  b.NewBusinessUnit,
  b.ResellerID,
  b.ResellerName,
  a.PRODUCT,
  a.CLI
  FROM  
  dbo.ADMIN.ODS_DICECNT_TTB_OUTPUT_ONE a
  left inner join ODS_LAYER.ADMIN.ODS_OSS_BFD_BFDAGENT AS C  A.agentid = C.AGENTID
  left inner join ODS_LAYER.ADMIN.ODS_DISECNT_NETWORK_CLI_TEST  b on C.AGENTID = b.ResellerID 
  WHERE NewBusinessUnit <> 'Inter-Co'
  AND b.NewBusinessUnit = 'Business Wholesale'

which produces the following error

Status: ERROR

---

SELECT a.CALENDAR_DATE, b.NewBusinessUnit, b.ResellerID, b.ResellerName, a.PRODUCT, a.CLI FROM
dbo.ADMIN.ODS_DICECNT_TTB_OUTPUT_ONE a left inner join ODS_LAYER.ADMIN.ODS_OSS_BFD_BFDAGENT AS C A.agentid = C.AGENTID left inner join ODS_LAYER.ADMIN.ODS_DISECNT_NETWORK_CLI_TEST b on C.AGENTID = b.ResellerID WHERE NewBusinessUnit <> 'Inter-Co' AND b.NewBusinessUnit = 'Business Wholesale'

---

210ms 2023-03-24T11:36:39.077Z

---

(SQLSTATE: HY000, SQLCODE: 1100): ERROR: 'SELECT a.CALENDAR_DATE, b.NewBusinessUnit, b.ResellerID, b.ResellerName, a.PRODUCT, a.CLI FROM dbo.ADMIN.ODS_DICECNT_TTB_OUTPUT_ONE a left inner join ODS_LAYER.ADMIN.ODS_OSS_BFD_BFDAGENT AS C A.agentid = C.AGENTID left inner join ODS_LAYER.ADMIN.ODS_DISECNT_NETWORK_CLI_TEST b on C.AGENTID = b.ResellerID WHERE NewBusinessUnit <> 'Inter-Co' AND b.NewBusinessUnit = 'Business Wholesale'' error ^ found "INNER" (at char 141) expecting `JOIN'

Answer 1

There is nothing called as left inner join

You should change it to left join

And left join is an outer join.

So you can even change to left outer join

SELECT a.CALENDAR_DATE, 
       b.NewBusinessUnit, 
       b.ResellerID, 
       b.ResellerName, 
       a.PRODUCT, 
       a.CLI 
FROM   dbo.ADMIN.ODS_DICECNT_TTB_OUTPUT_ONE a 
       LEFT JOIN ODS_LAYER.ADMIN.ODS_OSS_BFD_BFDAGENT AS C 
              ON A.agentid = C.AGENTID 
       LEFT JOIN ODS_LAYER.ADMIN.ODS_DISECNT_NETWORK_CLI_TEST b 
              ON C.AGENTID = b.ResellerID 
WHERE  NewBusinessUnit <> 'Inter-Co' 
       AND b.NewBusinessUnit = 'Business Wholesale';