SELECT * FROM LEFT OUTER JOIN

[dennis_janezic2]

Hi developers,

here comes a short issue I'm dealing with without finding an answer.

Please have a look at the select statement:

  SELECT *
    INTO CORRESPONDING FIELDS OF TABLE @gt_data
    FROM bkpf AS k
            LEFT OUTER JOIN bseg AS s
            ON    k~bukrs = s~bukrs
              AND k~belnr = s~belnr
              AND k~gjahr = s~gjahr
    WHERE k~blart IN @sd_blart
      AND k~bukrs IN @sd_bukrs
      AND k~gjahr IN @sd_gjahr.

No big deal at all I think, but there is a - like I think - strange behavior in the result set gt_data for results coming without data from the second table BSEG.

If BSEG is empty, all fields from gt_data which exists in both tables BKPF and BSEG are empty in gt_data. Here, the key fields BUKRS, BELNR and GJAHR are affected.

Can anyone please explain how to handle this?

Thanks in advance,

Dennis

[Former Member]

Hi,

please pass all source informations into the SELECT statement. For example

SELECT k~bukrs k~belnr k~gjahr s~buzei  ...

Regards,

Klaus

[dennis_janezic2]

Good morning Klaus,

thanks for your quick response.

Figuring out that "*" is the problem here was my first thought, too. But in case of BKPF and BSEG, we are talking about more than 300 fields to be handled to the Select Statement directly.

Your suggestion will work I think, but isn't there a more elegant solution for it?

Thanks,

Dennis

[RaymondGiuseppi]

If you use "*" as field list, the fields from BKPF will be overlapped by those of BSEG, so cleared when no record. You may be required to explicitly list required fields : k~BUKRS ... s~BUZEI etc.

If multiple database tables are specified after FROM, it is not possible to prevent multiple columns...

In recent versions could be some

SELECT bkpf~* bseg~buzei etc

Question: What is your version (BSEG no longer a cluster table)

Regards,

Raymond

[Former Member]

Hi Raymond,

a wrong table type should cause a syntax error.

Regards,

Klaus

[dennis_janezic2]

Thank you Raymond,

SELECT bkpf~* does not work for me.

Version is 7.40.

Thanks,

Dennis

[RaymondGiuseppi]

Try to remove the AS in "BKPF AS K"  or try K~* (but not sure)

Else you could build your field list programmatically ("K~BUKRS", "S~BUZEI") from ddic information removing duplicate fields.

Regards,

Raymond

[dennis_janezic2]

Thanks all for your quick and helpful answers.

At the end, my solution does not work 100% like desired, but using a programmatically selection (thanks Raymond) of the field list in combination with the join (thanks Klaus) brings up the result like expected.