Why does an OUTER JOIN sometimes require new SQL syntax and sometimes not?

jensupetersen — Thu, 29 Oct 2015 11:51:26 GMT

Hello everyone,

I am facing a very weird problem. In my programs I am usually using the old SQL syntax because I dislike the new, more complicated format (with @-escape symbols and a comma-delimited result list). However, I find that when I am using an OUTER JOIN in my programs (one that IMHO does nto exceed what is allowed in the old syntax), I sometimes get a weird error message telling me that I need to delimit my select list with commas. If I do that, then another error message pops up telling me that if I use the new syntax, I need to use it consistently (with @-escapes and the like). I can understand the latter error message, but not the former one.

The thing that particularly puzzles me is that this error message occurs only in some instances, while other OUTER JOIN-Selects with the old syntax work flawlessly. This even goes for other OUTER JOIN-Selects in the very same program! In the instances when the error message pops up, I am also not using any special things like tests for inequality in the ON-clause.

Let me give you a few examples. This code:


SELECT SINGLE HRP1001~OBJID HRP1000~STEXT
 INTO (P_WURZELORGEINHEIT,P_WURZELORGEINHEITENTEXT) FROM HRP1001
 LEFT OUTER JOIN HRP1000 ON HRP1000~PLVAR = HRP1001~PLVAR
                        AND HRP1000~OTYPE = HRP1001~OTYPE
                        AND HRP1000~OBJID = HRP1001~OBJID
                        AND HRP1000~ISTAT = HRP1001~ISTAT
 WHERE HRP1001~SOBID = P_WURZELORGEINHEIT
  AND HRP1001~SCLAS = 'O'
  AND HRP1001~SUBTY = 'B002'
  AND HRP1001~PLVAR = PLVAR
  AND HRP1001~ENDDA >= P_STICHTAG
  AND HRP1001~BEGDA <= P_STICHTAG
  AND HRP1001~ISTAT = '1'
  AND HRP1001~OTYPE = 'O'
  AND HRP1000~BEGDA <= P_STICHTAG
  AND HRP1000~ENDDA >= P_STICHTAG
  AND HRP1000~LANGU = 'D'
  AND HRP1000~SHORT = KURZTEXT_VON_P_ORGEH.

results in the error message:

However, in the very same program this code:


SELECT SINGLE PA0016~BEGDA PA0016~ENDDA PA0016~CTTYP T547S~CTTXT PA0016~CTEDT INTO IT16 FROM PA0016
  LEFT OUTER JOIN T547S ON T547S~SPRSL = SY-LANGU
                       AND T547S~CTTYP = PA0016~CTTYP
 WHERE PERNR = M-PERNR
   AND SUBTY = SPACE
   AND OBJPS = SPACE
   AND SPRPS = SPACE
   AND ENDDA >= B-BEGDA
   AND BEGDA <= B-BEGDA.

compiles nicely!

The only syntactical difference between the two above examples that I can see is that in the latter case the target is a structure instead of an enumerated list of target variables. However, in another of my programs the code:


SELECT SINGLE PA0001~ENAME PA0001~PERSK T500L~INTCA ZEQUATEX_TAXBLND~TAX_STATE_ID
  INTO (M-PARTICIPANTS-BUSINESS_NAME,M-PARTICIPANTS-EMPLOYMENT_TYPE,M-PARTICIPANTS-TAX_COUNTRY,M-PARTICIPANTS-TAX_STATE_ID)
  FROM PA0001
  JOIN T500P ON T500P~PERSA = PA0001~WERKS
  JOIN T500L ON T500L~MOLGA = T500P~MOLGA
  JOIN T001P ON T001P~WERKS = PA0001~WERKS
            AND T001P~BTRTL = PA0001~BTRTL
  LEFT OUTER JOIN ZEQUATEX_TAXBLND ON ZEQUATEX_TAXBLND~TRFGB = T001P~TRFGB
 WHERE PA0001~PERNR = M-PERNR
   AND PA0001~SUBTY = SPACE
   AND PA0001~OBJPS = SPACE
   AND PA0001~SPRPS = SPACE
   AND PA0001~ENDDA >= STICHTAG
   AND PA0001~BEGDA <= STICHTAG.

compiles nicely as well, even though in this case I enumerate the target variables, too.

I really fail to see the difference. The only thing that I can say is that if I remove the "LEFT OUTER" from my first example, it compiles without error. But all the other examples contain a LEFT OUTER JOIN, too!

So it somehow has to do with the LEFT OUTER JOIN, but I cannot see the decisive criteria for the occurrence of this weird error message.

Please note that all of the above examples belong to purely procedural code, so no OO restrictions can possibly apply. Release is 7.40.

Re: Why does an OUTER JOIN sometimes require new SQL syntax and sometimes not?

christian102094 — Thu, 29 Oct 2015 14:22:17 GMT

I have found that the part that obligates the new syntax is this:

AND HRP1000~BEGDA <= ''

AND HRP1000~ENDDA >= ''

AND HRP1000~LANGU = 'D'

AND HRP1000~SHORT = ''.

In other words, the conditions of fields of the join table.

Re: Why does an OUTER JOIN sometimes require new SQL syntax and sometimes not?

jensupetersen — Thu, 29 Oct 2015 15:41:51 GMT

Sounds plausible (consistent with the other examples), thank you.

topic Why does an OUTER JOIN sometimes require new SQL syntax and sometimes not? in Application Development and Automation Discussions

Why does an OUTER JOIN sometimes require new SQL syntax and sometimes not?

Re: Why does an OUTER JOIN sometimes require new SQL syntax and sometimes not?

Re: Why does an OUTER JOIN sometimes require new SQL syntax and sometimes not?