How can I use alias as join table condition?

[former_member818023]

SELECT Z~BUKRS,
Z~BUDAT,
Z~BLDAT,
Z~HWAER AS WAERS,
Z~XBLNR,
Z~BLART,
Z~BELNR,
A~BUZEI,
A~MWSKZ,
A~SHKZG,
B~HKONT,
B~KOART,
E~MWART,
F~TEXT1,

CASE WHEN E~MWART EQ 'A' AND B~KOART EQ 'D'
THEN COALESCE( B~KUNNR , B~LIFNR )
WHEN E~MWART EQ 'V' AND B~KOART EQ 'K'
THEN COALESCE( B~LIFNR , B~KUNNR )
END AS PARTNER, <=

........

LEFT JOIN BUT020 AS H
ON PARTNER = H~PARTNER <=

ALIAS 'PARTNER' WHICH I ANNOUNCED CANT BE USED AS JOIN CONDITION WITH BUT020-PARTNER??

The program is activated well, but that join condion is not working in the end.

When using CASE in SQL Select, the alias cant be used in Table Join condition????

[xiaosanyu]

"When using CASE in SQL Select, the alias cant be used in Table Join condition????"

YES, you are right!

you need code like this

ON CASE WHEN E~MWART EQ 'A' AND B~KOART EQ 'D'
THEN COALESCE( B~KUNNR , B~LIFNR )
WHEN E~MWART EQ 'V' AND B~KOART EQ 'K'
THEN COALESCE( B~LIFNR , B~KUNNR ) END = H~PARTNER

[Sandra_Rossi]

Below a Minimal Reproducible Example with your code, which compiles using ABAP 7.57:

SELECT *
    FROM bseg
    LEFT JOIN but020
    ON CASE WHEN bseg~koart EQ 'D'
    THEN coalesce( bseg~kunnr , bseg~lifnr )
    WHEN bseg~koart EQ 'K'
    THEN coalesce( bseg~lifnr , bseg~kunnr ) END = but020~partner
    INTO TABLE @DATA(b).

[Sandra_Rossi]

Please edit your question (Actions>Edit), select your code and press the button [CODE], which makes the code appear colored/indented, it'll be easier for people to look at it. Thanks!

[Sandra_Rossi]

What you can do highly depends on your ABAP version.

Which ABAP version is it?

Can you post the exact syntax error message? (use COPY/PASTE)

Why don't you provide a Minimal Reproducible Example?

For information, this Minimal Reproducible Example compiles in ABAP 7.57:

SELECT CASE WHEN bseg~koart EQ 'D'
    THEN coalesce( bseg~kunnr , bseg~lifnr )
    WHEN bseg~koart EQ 'K'
    THEN coalesce( bseg~lifnr , bseg~kunnr ) END AS partner
    FROM bseg
    LEFT JOIN but020
    ON partner = but020~partner
    INTO TABLE @DATA(a).

SELECT *
    FROM bseg
    LEFT JOIN but020
    ON CASE WHEN bseg~koart EQ 'D'
    THEN coalesce( bseg~kunnr , bseg~lifnr )
    WHEN bseg~koart EQ 'K'
    THEN coalesce( bseg~lifnr , bseg~kunnr ) END = but020~partner
    INTO TABLE @DATA(b).

[Sandra_Rossi]

I posted too fast, my first "MRE" is wrong because I use the alias name PARTNER, it doesn't work if I choose a non-conflicting column name like XPARTNER:

SELECT CASE WHEN bseg~koart EQ 'D'
    THEN coalesce( bseg~kunnr , bseg~lifnr )
    WHEN bseg~koart EQ 'K'
    THEN coalesce( bseg~lifnr , bseg~kunnr ) END AS xpartner
    FROM bseg
    LEFT JOIN but020
    ON xpartner = but020~partner               " <========== syntax error
    INTO TABLE @DATA(a).

Unknown column name "XPARTNER".