topic Re: Help with a join in Application Development and Automation Discussions

Help with a join

Former Member — Tue, 08 Jul 2008 02:15:36 GMT

I have the following code and it is giving me a syntax error. I originally had it as a single join (I didn't have coep in it) and it worked. This tells me that the issue is with the second join (coep). I have no idea what it could be.

  SELECT a~bukrs a~budat a~blart a~belnr a~xblnr b~zuonr b~wrbtr b~hkont b~bseg c~gkont
    INTO CORRESPONDING FIELDS OF TABLE it_working FROM 
    ( ( bkpf AS a INNER JOIN bseg AS b ON
      a~bukrs = b~bukrs AND
      a~belnr = b~belnr AND
      a~gjahr = b~gjahr ) INNER JOIN coep as c on 
      b~kokrs = c~kokrs AND
      b~belnr = c~belnr AND
      b~buzei = c~buzei ) WHERE
      a~bukrs = p_bukrs AND
      a~budat IN s_budat AND
      b~zuonr IN s_zuonr AND
      b~kunnr IN s_kunnr AND
      c~kgont = p_gkont.

Do you have any (easy) ideas to get around this?

Regards,

Davis

This is the syntax error:

For pooled tables, cluster tables and projection views, JOIN is not allowed: 'BSEG'

Edited by: Davis on Jul 7, 2008 10:18 PM

Re: Help with a join

peter_ruiz2 — Tue, 08 Jul 2008 02:20:58 GMT

hi davis,

There is no KGONT field in COEP. You might be referring to GKONT.

SELECT a_{bukrs a}budat a_{blart a}belnr a_{xblnr b}zuonr b_{wrbtr b}hkont b_{bseg c}gkont

INTO CORRESPONDING FIELDS OF TABLE it_working FROM

( ( bkpf AS a INNER JOIN bseg AS b ON

a_{bukrs = b}bukrs AND

a_{belnr = b}belnr AND

a_{gjahr = b}gjahr ) INNER JOIN coep as c on

b_{kokrs = c}kokrs AND

b_{belnr = c}belnr AND

b_{buzei = c}buzei ) WHERE

a~bukrs = p_bukrs AND

a~budat IN s_budat AND

b~zuonr IN s_zuonr AND

b~kunnr IN s_kunnr AND

c~kgont = p_gkont.

regards,

Peter

Re: Help with a join

Former Member — Tue, 08 Jul 2008 02:23:27 GMT

Peter,

Thanks for pointing that out; I didn't catch it. However, the issue is on the join of BSEG and COEP.

Regards,

Davis

Re: Help with a join

Former Member — Tue, 08 Jul 2008 02:28:17 GMT

OK, I thought that it would accept a join between bkpf and bseg but in fact it doesn't Am I doing something wrong?

I am now trying:

  SELECT a~bukrs a~budat a~blart a~belnr a~xblnr b~zuonr b~wrbtr b~hkont b~bseg
    INTO CORRESPONDING FIELDS OF TABLE it_working FROM
      bkpf AS a INNER JOIN bseg AS b ON
      a~bukrs = b~bukrs AND
      a~belnr = b~belnr AND
      a~gjahr = b~gjahr WHERE
      a~bukrs = p_bukrs AND
      a~budat IN s_budat AND
      b~zuonr IN s_zuonr AND
      b~kunnr IN s_kunnr.

Regards,

Davis

Re: Help with a join

peter_ruiz2 — Tue, 08 Jul 2008 02:52:00 GMT

Hi Davis,

You will not be able to use BSEG in a join statement with BKPF for it is a clustered table and BKPF is a transparent table. I suggest you use two SELECT statements for each table or use other tables aside from BSEG. take a look at BSIS, BSAS, BSIK, BSAK, BSID, and BSAD.

regards,

Peter

Re: Help with a join

Former Member — Tue, 08 Jul 2008 02:59:39 GMT

Thanks. I assumed that would be the answer so I went ahead and coded it with three select statements (BKPF leading them) with two being inside their own loop. I then go through the table and use delete statements to get rid of the unwanted records. That isn't the best as far as performance goes but I will see how it works (performance wise) and, if need be, I'll look at other tables.

Regards,

Davis