In SELECT can we use to Uppercase in ON condition of INNER/LEFT OUTER JOIN

[former_member599006]

Hi All,

Can we use kind of Translate word in ON of INNER/LEFT OUTER JOIN? Like TABLE A columns 1 2 3 4 and TABLE B columns 3 4 5 6. 3 is case sensitive in Table A and uppercased in Table B. Can we use like below? I do not want to use this condition in WHERE clause because I need data of table A though there is no data in table B, so trying to use in ON condition. Please guide..

Select a~1, a~2, a~3, a~4, b~5, b~6
from A left outer join B ON ( b~3 eq upper(a~3) and b~4 eq a~4 ) 
into table @data(c)
where 5 = @lc_rv.

[matt]

Did you try it? What happened?

[rajucd81]

Hi VM Kumar,

try with in quotes as below

Select a~1, a~2, a~3, a~4, b~5, b~6from A left outer join B ON( b~3 eq 'upper(a~3)'
                                       and b~4eq a~4)into table @data(c) where5=@lc_rv.

[former_member599006]

This one I tried but not working, tried with quotes as mentioned. But not fetching the values ( no syntax error).

[former_member182550]

That is saying b~3 = the string 'upper(a~3)' and not the upper case value of a~3.....

[rajucd81]

I tried this i didn't get any syntax error but i don't have any scenario to test it, can you share the code which you are trying ?

[former_member599006]

Hi Raju, here is code which am trying

      SELECT                               ##DB_FEATURE_MODE[TABLE_LEN_MAX1]
        FROM vbak   AS sh RIGHT OUTER JOIN
           ( vbap   AS si INNER JOIN
           ( vbrp   AS ii LEFT OUTER JOIN
           ( vbrk   AS ih LEFT OUTER JOIN
             bsad   AS c
                  ON c~bukrs EQ ih~bukrs AND c~gjahr EQ ih~gjahr AND
                     c~xblnr EQ 'upper(ih~xblnr)' and
                     c~blart EQ ih~blart AND c~belnr EQ ih~belnr )
                  ON ih~vbeln EQ ii~vbeln )
                  ON ii~aubel EQ si~vbeln AND ii~aupos EQ si~posnr )
                  ON si~vbeln EQ sh~vbeln
        FIELDS sh~vbeln AS svbeln, sh~bsark, sh~netwr, si~posnr AS sposnr,
               ii~vbeln AS ivbeln, ii~posnr AS iposnr,
               ih~fkart, ih~xblnr, ih~bukrs, ih~gjahr, ih~fkdat, ih~blart, ih~sfakn,
               ih~belnr AS ibelnr, ih~netwr AS inetwr,
               c~bukrs AS cbukrs, c~belnr, c~gjahr AS cgjahr, c~xblnr AS cxblnr,
               c~augdt, c~wrbtr
        WHERE sh~vbeln IN @s_vbeln
          AND sh~bsark EQ @p_bsark
          AND ih~fkart EQ @me->gc_z4f2
          AND ih~sfakn EQ @space
          AND c~augdt  EQ @sy-datum
          AND c~wrbtr  EQ sh~netwr
         INTO TABLE @DATA(lt_vbak).

In BSAD the XBLNR value is POS_1234 for xblnr where as in VBRK it is Pos_1234

[Sandra_Rossi]

The ABAP documentation answers positively your question cf https://help.sap.com/http.svc/rc/abapdocu_753_index_htm/7.53/en-US/index.htm?file=abapselect_join.ht...

You just need to do a little exchange.

[former_member599006]

"UPPER(B" is unknown in the current FROM clause (obscured by an alias name or unknown in this position) if provide with A~3, if removed A~ and provide only field name upper(3) then BELOW ERROR:
Field "UPPER(3)" is unknown.

[former_member599006]

Hi Sandra,

Thank you but the link is briefing about the Joins and conditions, my ask is -can I translate or use UPPER in ON condition of JOIN as I have seen it can be used in where clause.

[cenkay]

Hi,

You can use upper keyword in left side on your join condition if your system supports.

Like;

UPPER( ih~xblnr ) = c~xblnr and

Regards,

Cenkay

[former_member599006]

If I try giving spaces in braces, the below is syntax error 'Use the associated entity "BSAD_VIEW".'

      SELECT                               ##DB_FEATURE_MODE[TABLE_LEN_MAX1]
        FROM vbak   AS sh RIGHT OUTER JOIN
           ( vbap   AS si INNER JOIN
           ( vbrp   AS ii LEFT OUTER JOIN
           ( vbrk   AS ih LEFT OUTER JOIN
             bsad   AS c
                  ON c~bukrs EQ ih~bukrs AND c~gjahr EQ ih~gjahr
                  AND UPPER( ih~xblnr ) EQ c~xblnr

And If I try the same in quotes with spaces braces, it is not retrieving the values.

      SELECT                               ##DB_FEATURE_MODE[TABLE_LEN_MAX1]
        FROM vbak   AS sh RIGHT OUTER JOIN
           ( vbap   AS si INNER JOIN
           ( vbrp   AS ii LEFT OUTER JOIN
           ( vbrk   AS ih LEFT OUTER JOIN
             bsad   AS c
                  ON c~bukrs EQ ih~bukrs AND c~gjahr EQ ih~gjahr
                  AND 'UPPER( ih~xblnr )' EQ c~xblnr

[Jelena]

BSAD_VIEW? Are you in S/4HANA? If so then BSAD is obsolete, all the accounting tables were replaced by a single one and old tables turned into views. You might be looking in a wrong place here to begin with...

[Sandra_Rossi]

vmohan.kumar, read the documentation cautiously, it says: "SQL expressions are used on the left side of the ON condition of any join." (as you can see in answer by Ahmet Cenkay Arkan)