Using table alias in order by of window expressions - ABAP open SQL

[saurabh__khare]

Hi,

I'm trying to use window expressions in open SQL. The query has joins with table aliases. When I try to use a table in the order by clause of the window expression, there's a syntax error: "~" is not allowed here. " ) " is expected.

If I remove the alias, it says that the field is ambiguous. I'm missing something obvious. Can someone help here?

      SELECT
       p~laufd,      " Run Date
       p~laufi,      " Run ID
       chect,        " Check number
       p~zbukr,      " Company code
       pridt,        " Check date
       p~lifnr,      " Vendor code
       p~kunnr,      " Customer
       rp~belnr,     " Document number
       rp~bldat,     " Document date
       p~rwbtr,      " Check amount
       b~sgtxt,      " Item Text
       rp~waers,     " Currency
       p~voidd,      " Voided Check Date
       rp~shkzg,     " Debit/Credit Indicator
       p~vblnr,      " Payment Document Number
       zwels,
       p~hbkid,
       p~hktid,
       p~rzawe,
       text2,
       xblnr,
       rp~zfbdt,
       rp~zbd1t,
       rp~zbd2t,
       rp~zbd3t,
       rp~dmbtr,
       zpstl,
       zort1,
       zstra,
       zpfac,
       landx,
       bezei,
       xbanc,
       bancd,
       FIRST_VALUE( a~ebeln ) OVER( PARTITION BY a~belnr, a~rbukrs, a~gjahr
                                    ORDER BY a~ebeln DESCENDING ) AS ebeln "<=== SYNTAX ERROR
    FROM payr AS p
    INNER JOIN regup AS rp
      ON  p~laufd = rp~laufd
      AND p~laufi = rp~laufi
      AND p~lifnr = rp~lifnr
      AND p~kunnr = rp~kunnr
      AND p~vblnr = rp~vblnr
    INNER JOIN bseg AS b
      ON  rp~belnr = b~belnr
      AND rp~bukrs = b~bukrs
      AND rp~gjahr = b~gjahr
      AND rp~buzei = b~buzei
    INNER JOIN t042zt AS t
      ON  t~zlsch = p~rzawe
      AND t~spras = @sy-langu
      AND t~land1 = @lc_us
    INNER JOIN t005u AS t005u
      ON  p~zland     = t005u~land1
      AND p~zregi     = t005u~bland
      AND t005u~spras = @sy-langu
    INNER JOIN t005t AS t005t
      ON  p~zland     = t005t~land1
      AND t005t~spras = @sy-langu
    LEFT OUTER JOIN acdoca AS a  ##DB_FEATURE_MODE[TABLE_LEN_MAX1]
      ON  rp~belnr = a~belnr
      AND rp~bukrs = a~rbukrs
      AND rp~gjahr = a~gjahr
      AND a~rldnr = @lc_01
      AND a~blart = @lc_re
      AND a~ebeln <> @space
    WHERE p~zbukr =  @p_bukrs
    AND   p~hbkid =  @p_hbkid
    AND   p~hktid =  @p_accid
    AND   p~rzawe IN @s_paym
    AND   p~laufd IN @s_runon
    AND   p~laufi IN @s_runid
    AND   p~gjahr =  @p_gjahr
    AND   p~vblnr IN @s_paydn
    AND   p~chect IN @s_chkno
    AND ( p~kunnr IN @s_cust
     OR   p~lifnr IN @s_cust )    ##TOO_MANY_ITAB_FIELDS
    INTO TABLE @DATA(lt_check)
    BYPASSING BUFFER.

[PrabhaG]

Hi  saurabh__khare,

Facing same issue. By any chance, Have you found any solution for this issue? I will also post if I found one. TIA.

[Marian_Zeis]

Something wrong here with the window expression, try a little bit here like

FIRST_VALUE( ebeln ) OVER( PARTITION BY a~belnr, a~rbukrs, a~gjahr
                           ORDER BY ebeln DESCENDING ) AS ebeln

[PrabhaG]

Thank you for the reply @Marian_Zeis. The above syntax will work perfectly as long as only one table has EBELN field in the Table join.

However, if both Table-A & Table-B has EBELN field, then the system throws the error

'The column name or association "EBELN" is ambiguous, which means it occurs in multiple tables'.

To overcome this, I provide alias name of the table to the field EBELN [Ex: a~ebeln].

Example

FIRST_VALUE( ebeln ) OVER( PARTITION BY a~belnr, a~rbukrs, a~gjahr
ORDER BY a~ebeln DESCENDING ) AS ebeln

Then the system throws the below error

"~" is not allowed here. " ) " is expected.

Any leads will be appreciated. TIA.

Regards,

Prabha.

[Marian_Zeis]

yeah, its not easy, i cant try now, but do subquery work?

SELECT
    subquery~laufd,
    subquery~laufi,
    subquery~chect,
    subquery~zbukr,
    subquery~pridt,
    subquery~lifnr,
    subquery~kunnr,
    subquery~belnr,
    subquery~bldat,
    subquery~rwbtr,
    subquery~sgtxt,
    subquery~waers,
    subquery~voidd,
    subquery~shkzg,
    subquery~vblnr,
    subquery~zwels,
    subquery~hbkid,
    subquery~hktid,
    subquery~rzawe,
    subquery~text2,
    subquery~xblnr,
    subquery~zfbdt,
    subquery~zbd1t,
    subquery~zbd2t,
    subquery~zbd3t,
    subquery~dmbtr,
    subquery~zpstl,
    subquery~zort1,
    subquery~zstra,
    subquery~zpfac,
    subquery~landx,
    subquery~bezei,
    subquery~xbanc,
    subquery~bancd,
    FIRST_VALUE( ebeln ) OVER( PARTITION BY belnr, rbukrs, gjahr
                               ORDER BY ebeln DESCENDING ) AS ebeln
FROM (
    SELECT
        p~laufd,
        p~laufi,
        chect,
        p~zbukr,
        pridt,
        p~lifnr,
        p~kunnr,
        rp~belnr,
        rp~bldat,
        p~rwbtr,
        b~sgtxt,
        rp~waers,
        p~voidd,
        rp~shkzg,
        p~vblnr,
        zwels,
        p~hbkid,
        p~hktid,
        p~rzawe,
        text2,
        xblnr,
        rp~zfbdt,
        rp~zbd1t,
        rp~zbd2t,
        rp~zbd3t,
        rp~dmbtr,
        zpstl,
        zort1,
        zstra,
        zpfac,
        landx,
        bezei,
        xbanc,
        bancd,
        a~ebeln
    FROM payr AS p
    INNER JOIN regup AS rp
      ON  p~laufd = rp~laufd
      AND p~laufi = rp~laufi
      AND p~lifnr = rp~lifnr
      AND p~kunnr = rp~kunnr
      AND p~vblnr = rp~vblnr
    INNER JOIN bseg AS b
      ON  rp~belnr = b~belnr
      AND rp~bukrs = b~bukrs
      AND rp~gjahr = b~gjahr
      AND rp~buzei = b~buzei
    LEFT OUTER JOIN acdoca AS a
      ON  rp~belnr = a~belnr
      AND rp~bukrs = a~rbukrs
      AND rp~gjahr = a~gjahr
      AND a~rldnr = @LC_01
      AND a~blart = @LC_re
      AND a~ebeln <> @space
) AS subquery
INTO TABLE @DATA(lt_check).

[miguel_motta]

Hi,

Try some like this.

  WITH 
    +table1 AS (
	SELECT
       p~laufd,      " Run Date
       p~laufi,      " Run ID
       chect,        " Check number
       p~zbukr,      " Company code
       pridt,        " Check date
       p~lifnr,      " Vendor code
       p~kunnr,      " Customer
       rp~belnr,     " Document number
       rp~bldat,     " Document date
       p~rwbtr,      " Check amount
       b~sgtxt,      " Item Text
       rp~waers,     " Currency
       p~voidd,      " Voided Check Date
       rp~shkzg,     " Debit/Credit Indicator
       p~vblnr,      " Payment Document Number
       zwels,
       p~hbkid,
       p~hktid,
       p~rzawe,
       text2,
       xblnr,
       rp~zfbdt,
       rp~zbd1t,
       rp~zbd2t,
       rp~zbd3t,
       rp~dmbtr,
       zpstl,
       zort1,
       zstra,
       zpfac,
       landx,
       bezei,
       xbanc,
       bancd,
	   a~belnr AS belnr_aux,
	   a~rbukrs AS rbukrs_aux,
	   a~gjahr AS gjahr_aux,
	   a~ebeln AS ebeln_aux
    FROM payr AS p
    INNER JOIN regup AS rp
      ON  p~laufd = rp~laufd
      AND p~laufi = rp~laufi
      AND p~lifnr = rp~lifnr
      AND p~kunnr = rp~kunnr
      AND p~vblnr = rp~vblnr
    INNER JOIN bseg AS b
      ON  rp~belnr = b~belnr
      AND rp~bukrs = b~bukrs
      AND rp~gjahr = b~gjahr
      AND rp~buzei = b~buzei
    INNER JOIN t042zt AS t
      ON  t~zlsch = p~rzawe
      AND t~spras = @SY-langu
      AND t~land1 = @LC_us
    INNER JOIN t005u AS t005u
      ON  p~zland     = t005u~land1
      AND p~zregi     = t005u~bland
      AND t005u~spras = @SY-langu
    INNER JOIN t005t AS t005t
      ON  p~zland     = t005t~land1
      AND t005t~spras = @SY-langu
    LEFT OUTER JOIN acdoca AS a  ##DB_FEATURE_MODE[TABLE_LEN_MAX1]
      ON  rp~belnr = a~belnr
      AND rp~bukrs = a~rbukrs
      AND rp~gjahr = a~gjahr
      AND a~rldnr = @LC_01
      AND a~blart = @LC_re
      AND a~ebeln <> @space
    WHERE p~zbukr =  @p_bukrs
    AND   p~hbkid =  @p_hbkid
    AND   p~hktid =  @p_accid
    AND   p~rzawe IN @s_paym
    AND   p~laufd IN @s_runon
    AND   p~laufi IN @s_runid
    AND   p~gjahr =  @p_gjahr
    AND   p~vblnr IN @s_paydn
    AND   p~chect IN @s_chkno
    AND ( p~kunnr IN @s_cust
     OR   p~lifnr IN @s_cust )    ##TOO_MANY_ITAB_FIELDS
    BYPASSING BUFFER )
	
	SELECT 
	   laufd,  
       laufi,  
       chect,    
       zbukr,  
       pridt,    
       lifnr,  
       kunnr,  
       belnr, 
       bldat, 
       rwbtr,  
       sgtxt,  
       waers, 
       voidd,  
       shkzg, 
       vblnr,  
       zwels,
       hbkid,
       hktid,
       rzawe,
       text2,
       xblnr,
       zfbdt,
       zbd1t,
       zbd2t,
       zbd3t,
       dmbtr,
       zpstl,
       zort1,
       zstra,
       zpfac,
       landx,
       bezei,
       xbanc,
       bancd,
	   FIRST_VALUE( ebeln ) OVER( PARTITION BY belnr_aux, rbukrs_aux, gjahr_aux
                                    ORDER BY ebeln_aux DESCENDING ) AS ebeln
	FROM +table1
	INTO TABLE @DATA(lt_check).