A005 - Internal table

[antonio_bruno]

Hi,

I need to create an internal table with A005 and KONP. I've tried with the attached code, but "For pooled tables, cluster tables, and projection views, JOIN is not allowed: "A005".

How i can join A005 and KONP?

Thanks and best regards.

Antonio

[Former Member]

Hi.

You can not do a join, need to sth like this:

DATA: BEGIN OF condl OCCURS 0,
      matnr4 LIKE a005-matnr,
      knumh4 LIKE a005-knumh,
      kbetr4 LIKE konp-kbetr,
      END OF condl.


DATA: wa_condl LIKE condl.


SELECT matnr knumh
INTO CORRESPONDING FIELDS OF TABLE condl
FROM a005
WHERE kschl = 'ZPNO' AND vkorg = 'Z001' AND
vtweg = 'CDS' AND   kunnr = 'CLI' AND datab <= 'VAL'
AND datbi >= 'VAL'.


LOOP AT condl INTO wa_condl.


  SELECT SINGLE kbetr INTO wa_condl-kbetr4
  FROM konp
  WHERE knumh = wa_condl-knumh4.


  MODIFY condl FROM wa_condl.


ENDLOOP.

Regards

Miguel

[antonio_bruno]

Hi Miguel,

Now I haven't error. But I haven't values in my custom field PRCLI.

clear PRCLI.

loop at CONDCL

where MATNR4 = MARA-MATNR.

PRCLI = CONDCL-KBETR4.

endloop.

How can I correct the code?

Thanks and best regards.

Antonio

[RaymondGiuseppi]

No join allowed for cluster/pool table...

So select in a temporary table from A005 and then select from KONP with a FOR ALL ENTRIES (after checking fisr select was successful) and merge both internal tables.

Regards,

Raymopnd

[antonio_bruno]

Hi,

Could you please give me an example?

Best regards.

Antonio

[Former Member]

Hi, I think you can't join them. You have to divide the query to two separate selects.

Br

Bohuslav

[Former Member]

Antonio,

As you have already said, you cannot use join on pooled/cluster tables. Why not try using FOR ALL entries after selecting data from one table?

Vikram.M

[Former Member]

if cli ne ' ' or cds = ' '.

select matnr knumh datab datbi

   from a005

   into TABLE it_a005 

   where kschl = 'zpno'

    and vkorg = 'z001'

    AND vtweg = cds

   AND kunnr = cli.

   SELECT kbetr kpein

     FROM konp

     INTO TABLE it_konp

     FOR ALL ENTRIES IN it_a005

     WHERE knumk = it_a005-knumk

     and datab <= val

     AND datbi >= val.

   endif.

[antonio_bruno]

Hi Nitin
,

Now I need a table that join it_a005 and it_konp. In particular, with the field matnr knumh, datab, datbi, kbetr, kpein.

I've add the follow code:

loop at it_a005.

loop at it_konp.

select A~MATNR4 A~KNUMH4 A~DATAB4 A~DATBI4 B~KBETR5 B~KPEIN5

into table CONDCL

from it_a005 as A inner join it_konp as B

on A~KNUMH4 = B~KNUMH5.

endloop.

endloop.

But IT_A005 is not defined in the ABAP Dictionary as a table, projection...

How can i obtain the table CONDCL?

Thanks and best regards

Antonio

[gurunathkumar_dadamu]

Hi Antonio,

Please try the below code and it will works for your scenario,

TYPES: BEGIN OF ty_a005,

kschl TYPE a005-kschl,

kunnr TYPE a005-kunnr,

matnr TYPE a005-matnr,

knumh TYPE a005-knumh,

END OF ty_a005.



TYPES:BEGIN OF ty_konp,

      knumh TYPE konp-knumh,

      kopos TYPE konp-kopos,

      kschl TYPE konp-kschl,

      kbetr TYPE konp-kbetr,

      kpein TYPE konp-kpein,

      END OF   ty_konp.



TYPES: BEGIN OF ty_final,

       knumh TYPE konp-knumh,

       kopos TYPE konp-kopos,

       kschl TYPE konp-kschl,

       kbetr TYPE konp-kbetr,

       kpein TYPE konp-kpein,

       kunnr TYPE a005-kunnr,

       matnr TYPE a005-matnr,

       END OF   ty_final.





DATA: it_a005 TYPE STANDARD TABLE OF ty_a005,

      it_konp TYPE STANDARD TABLE OF ty_konp,

      it_final TYPE STANDARD TABLE OF ty_final,

      wa_final TYPE ty_final,

      wa_a005 TYPE ty_a005,

      wa_konp TYPE ty_konp.





START-OF-SELECTION.



  SELECT kschl

         kunnr

         matnr

         knumh

         FROM a005

         INTO TABLE it_a005

         WHERE kschl = 'AZWP'

         AND   vkorg = 'ZPSO'

         AND   vtweg = 'ZD'

         AND   kunnr = 500011

         AND   datbi GE '20131201'

         AND   datab LE '20131128'.

  IF sy-subrc = 0.

    SORT  it_a005 BY knumh.



*-select the data from KONP.

    SELECT knumh

           kopos

           kschl

           kbetr

           kpein

           FROM konp

           INTO TABLE it_konp

           FOR ALL ENTRIES IN it_a005

           WHERE knumh = it_a005-knumh

           AND   kschl = it_a005-kschl.

    IF sy-subrc = 0.

      SORT it_konp BY knumh.

    ENDIF.

  ENDIF.



  LOOP AT  it_konp INTO wa_konp.

    READ TABLE it_a005 INTO wa_a005 WITH KEY knumh = wa_konp-knumh BINARY SEARCH.

    IF sy-subrc = 0.

      wa_final-knumh = wa_a005-knumh.

      wa_final-kopos = wa_konp-kopos.

      wa_final-kschl = wa_konp-kschl.

      wa_final-kbetr = wa_konp-kbetr.

      wa_final-kpein = wa_konp-kpein.

      wa_final-matnr = wa_a005-matnr.

      wa_final-kunnr = wa_a005-kunnr.



      APPEND wa_final TO it_final.

      CLEAR: wa_final,

             wa_konp,

             wa_a005.

    ENDIF.

  ENDLOOP.

Let me know if you find any issues.

Regards,

Gurunath Kumar D

[antonio_bruno]

Hi,

Now I need to transfer the unit price into my custom field PRCLI.

clear PRCLI

loop at it_final

where MATNR = mara-matnr.

PRCLI = it_final-KBETR / it_final-KPEIN.

endloop.

But Internal table "IT_FINAL" has no header line - specification of one...

How can i solve it?

Thanks and best regards.

Antonio

[gurunathkumar_dadamu]

This message was moderated.

[antonio_bruno]

Hi Gurunath,

Thanks a lot for you help. Now my query works.

I've only modify your last code as follow:

CLEAR: wa_final, prcli.

LOOP AT it_final INTO wa_final

WHERE matnr = mara-matnr.

prcli = wa_final-kbetr / wa_final-kpein.

ENDLOOP.

Best regards.

Antonio

[gurunathkumar_dadamu]

Hi Antonio,

Can you please use Clear Statement inside the loop because of after every loop we have to clear the wa_final and Prcli..

Please Mark this thread as answer.

Regards,

Gurunath Kumar D

[antonio_bruno]

Hi Gurunath,

I've debbuged the query. With your code you extract the value but at the end you clean it.

Only with my code, i can show the value in my report.

Thanks and regards.

Antonio