Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Compare Database table and Internal Table on SAP Query

Former Member
0 Kudos

Hi Guys, how are you doing? hope you're doing good.

First of all, newbie detected here!! I'm developing a SAP Query between two tables, SKB1 (Database Table) and T030 (Pooled Table). I Know that is not possible to join these two tables, because pooled tables cannot be used on normal Join Methods (and i need a left outer Join).

On the way to do that query, i've tried a lot of things on the infoset programming. Some Stuff bring-me some result, but, because i have to use this query as data source on GRC Process Control, i have to obey some paramenters:

  • I Can't generate a new report through a WA (I Know that, programatically talking, is possible, but PC does not work with code created WA);
  • I Have to create a custom field to perform a test on the retrieved data;
  • PC only works with fields or tables created manually, not by code (as far as I know).

What i need, is to bring to my report all records from SKB1 and a custom field that brings the fields from t030, if this field (KONTS) is equal to field SAKNR from SKB1.

So, my last try was to do a loop on the internal table that recieves the content of T030, and compare it with SKB1. This code was placed inside the custom field code, that i`ve named as Z_t030_konts. But, unfortunatell, it doesn't work.

Could you help please to figure out how can I do that?

Here is the code that i've deployed until now:

*This code was placed at the DATA Section

DATA: t030 TYPE t030.

Data: i_t030 TYPE STANDARD TABLE OF t030 WITH HEADER LINE INITIAL SIZE 0.

DATA: temp_saknr TYPE KONTS.

*This code was placed at the custom field code Z_T030_KONTS

CLEAR: z_t030_konts.

IF i_t030 IS NOT INITIAL.

   LOOP AT i_t030.

     IF i_t030-konts = skb1-saknr.

       z_t030_konts = skb1-saknr.

     ELSE.

       z_t030_konts = 'i_T030 IS INITIAL'.

     ENDIF.

   ENDLOOP.

ENDIF.

And this is the result i've expect:

Could you, guys, help me?

Kindly Regards:

1 ACCEPTED SOLUTION

Former Member
0 Kudos

Hi Caio,

Welcome to the wonderful world of ABAP development!

I want to clarify two things.  First, T030 and SKB1 are both database tables, you meant to say SKB1 is a transparent table because it is not pooled (or cluster).  Also, it is no longer recommended to declare internal tables WITH HEADER LINE.

Your requirement can be accomplished with this code:

DATA: BEGIN OF l_line.

  INCLUDE STRUCTURE skb1 AS gl_account  RENAMING WITH SUFFIX _s.

  INCLUDE STRUCTURE t030 AS std_account RENAMING WITH SUFFIX _t.

DATA: END OF l_line.

DATA: output LIKE TABLE OF l_line.

FIELD-SYMBOLS: <line> LIKE l_line.

SELECT * FROM skb1 INTO TABLE output.

LOOP AT output ASSIGNING <line>.

  SELECT SINGLE * FROM t030 INTO <line>-std_account

    WHERE konts = <line>-gl_account-saknr.

ENDLOOP.

Best,

Eric

7 REPLIES 7

Former Member
0 Kudos

Just a little correction. Here follow the complete code:

*DATA SECTION (Declaração de variáveis)

DATA: t030 TYPE t030.

Data: i_t030 TYPE STANDARD TABLE OF t030 WITH HEADER LINE INITIAL SIZE 0.

DATA: temp_saknr TYPE KONTS.

*START-OF-SELECTION SECTION

SELECT *

  INTO CORRESPONDING FIELDS OF TABLE I_T030

  FROM T030.

*CUSTOM FIELD CODE (Z_T030_KONTS)

CLEAR: z_t030_konts.

IF i_t030 IS NOT INITIAL.

  LOOP AT i_t030.

    IF i_t030-konts = skb1-saknr.

      z_t030_konts = skb1-saknr.

    ELSE.

      z_t030_konts = 'i_T030 IS INITIAL'.

    ENDIF.

  ENDLOOP.

ENDIF.

Former Member
0 Kudos

Hi Caio,

Welcome to the wonderful world of ABAP development!

I want to clarify two things.  First, T030 and SKB1 are both database tables, you meant to say SKB1 is a transparent table because it is not pooled (or cluster).  Also, it is no longer recommended to declare internal tables WITH HEADER LINE.

Your requirement can be accomplished with this code:

DATA: BEGIN OF l_line.

  INCLUDE STRUCTURE skb1 AS gl_account  RENAMING WITH SUFFIX _s.

  INCLUDE STRUCTURE t030 AS std_account RENAMING WITH SUFFIX _t.

DATA: END OF l_line.

DATA: output LIKE TABLE OF l_line.

FIELD-SYMBOLS: <line> LIKE l_line.

SELECT * FROM skb1 INTO TABLE output.

LOOP AT output ASSIGNING <line>.

  SELECT SINGLE * FROM t030 INTO <line>-std_account

    WHERE konts = <line>-gl_account-saknr.

ENDLOOP.

Best,

Eric

0 Kudos

Hi Eric, thanks for the Quick Answer, for the tips and code and for the welcome to this crazy world.

I Know that what i'm looking for is specilly dificult because I can't create anything on ABAP dictionary, and the options on SQ01, SQ02 and SQ03 are kind restrictive.

I've tried your code, but i think that i lost myself on where to place it.

First, i've tried to place it on the SQ02 sections. I realize that my query dont show all fields, because it is delimitted by the standard fields of a table, and, because of the code created structure, SQ01 didn't find the fields.

Then, i've tried to create a new infoset, which i choosed the Data Retrieval by program option on it's creation.On the main code, I've placed the code that you suggest me, but, even the syntax check on SQ02 telling me that there was no Syntax Error, i got an ABAP Syntax Error on the Query execution.

Thanks again for the attention.

0 Kudos

I see.  When you said "query" I didn't know you meant SQ01 and such.  The code I suggested was for an ABAP report.  It would be very easy to put the whole thing in an ALV grid and display if that were the case.

So...if at all possible, I recommend creating an ALV report instead of an SQ01 query.  If not, try putting just the LOOP...ENDLOOP in the START-OF-SELECTION or END-OF-SELECTION (before list).  You will need to change the variable names.

0 Kudos

Hi Eric, how are you doing?

Thanks again for the reply. I Didn't figured out how to solve it. I've tried to place the code, but I got the same abap error.

I'll try other ways. Any evolution i'll let you know. I'm just wondering why i'm not getting any result from T030, even that is not initial.

Thanks again.

0 Kudos

Hi,

Below are all the necessary definitions.

1. InfoSet  based on direct read of table SKB1

2. Additional field T030_KONTS

3. DATA SECTION

4. INITIALIZATION SECTION

5. Additional field CODE

6. Query based on InfoSet

Regards,

Boris.

0 Kudos

Hi Boris:

Thank You So Much!!!