Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

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

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.

Tags:
replied

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.

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question