topic Re: SELECT * FROM LEFT OUTER JOIN in Application Development and Automation Discussions

SELECT * FROM LEFT OUTER JOIN

dennis_janezic2 — Fri, 25 Sep 2015 07:43:52 GMT

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

Re: SELECT * FROM LEFT OUTER JOIN

Former Member — Fri, 25 Sep 2015 07:53:00 GMT

Hi,

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

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

Regards,

Klaus

Re: SELECT * FROM LEFT OUTER JOIN

dennis_janezic2 — Fri, 25 Sep 2015 08:01:14 GMT

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

Re: SELECT * FROM LEFT OUTER JOIN

RaymondGiuseppi — Fri, 25 Sep 2015 08:03:54 GMT

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 from inheriting the same name when * is specified.

In recent versions could be some


SELECT bkpf~* bseg~buzei etc

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

Regards,

Raymond

Re: SELECT * FROM LEFT OUTER JOIN

Former Member — Fri, 25 Sep 2015 08:09:40 GMT

Hi Raymond,

a wrong table type should cause a syntax error.

Regards,

Klaus

Re: SELECT * FROM LEFT OUTER JOIN

dennis_janezic2 — Fri, 25 Sep 2015 08:50:47 GMT

Thank you Raymond,

SELECT bkpf~* does not work for me.

Version is 7.40.

Thanks,

Dennis

Re: SELECT * FROM LEFT OUTER JOIN

RaymondGiuseppi — Fri, 25 Sep 2015 09:36:34 GMT

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

Re: SELECT * FROM LEFT OUTER JOIN

dennis_janezic2 — Wed, 30 Sep 2015 16:01:46 GMT

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.