HANA Left Outer Join syntax

Question

I am trying to build a query in HANA with a left outer join. I am using a (+) sign in the where clause. I do that often in Oracle; but in HANA it is returning a syntax error. Does HANA supports this syntax?

select * 
FROM
   "_SYS_BIC"."FinancialReporting.ReportingViews.Tech_Analytics/CV_LOA_FINANCIAL_DETAIL_GRA" F1,
   "_SYS_BIC"."FinancialReporting.MasterData/CV_D_ACCOUNT_GRA" F2,
   "_SYS_BIC"."FinancialReporting.MasterData/CV_D_COMPANY_GRA" F3,
   "_SYS_BIC"."FinancialReporting.MasterData/CV_D_COSTCENTER_GRA" F4,
   "_SYS_BIC"."FinancialReporting.ReportingViews/CV_LOA_MEASURES_LEVELS_TA_GRA" F5,
   "_SYS_BIC"."FinancialReporting.MasterData/CV_D_VENDOR_GRA" F6
WHERE
   (F1."acct_nmbr" = F2."acct_nmbr")
   AND (F1."company_cd" = F3."company_cd")
   AND (F1."cost_center_cd" = F4."cost_center_cd")
   AND (F1."acct_nmbr" = F5."acct_nmbr")
   AND F1."vendor_cd" = F6."vendor_cd"(+)
   AND (F1."acctg_dt" >= '2016-01-01'
   AND F1."acctg_dt" <= '2016-02-01'))

This is an old style syntax used by Oracle and it's nit standard. You need to use explicit JOINs: http://saphanatutorial.com/sap-hana-join-types/. Any reason why you didn't use Google (or any other) search first? — PM 77-1, Jun 06 '17 at 14:21

score 4 · Answer 1 · answered Jun 06 '17 at 14:30

You need to learn to use standard JOIN syntax:

select * 
FROM "_SYS_BIC"."FinancialReporting.ReportingViews.Tech_Analytics/CV_LOA_FINANCIAL_DETAIL_GRA" F1 JOIN
    "_SYS_BIC"."FinancialReporting.MasterData/CV_D_ACCOUNT_GRA" F2
    ON F1."acct_nmbr" = F2."acct_nmbr" JOIN
   "_SYS_BIC"."FinancialReporting.MasterData/CV_D_COMPANY_GRA" F3
    ON F1."company_cd" = F3."company_cd" JOIN
   "_SYS_BIC"."FinancialReporting.MasterData/CV_D_COSTCENTER_GRA" F4
    ON F1."cost_center_cd" = F4."cost_center_cd" JOIN
   "_SYS_BIC"."FinancialReporting.ReportingViews/CV_LOA_MEASURES_LEVELS_TA_GRA" F5
   ON F1."acct_nmbr" = F5."acct_nmbr" LEFT JOIN
   "_SYS_BIC"."FinancialReporting.MasterData/CV_D_VENDOR_GRA" F6
    ON F1."vendor_cd" = F6."vendor_cd"
WHERE F1."acctg_dt" >= '2016-01-01' AND F1."acctg_dt" <= '2016-02-01';

HANA Left Outer Join syntax

1 Answers1