ABAP Query basic, not getting full result

[Former Member]

Hi there.

I've got a requirement of creating a query, fetching all discounts/rebates and values for customers and material, together with pricing group of customer.

I've created a base of the KNVV-table, where the key customer/sales org is selected, linked with KNA1, to have something to start with (getting pricing group) in SQ01.

Now table A005 is a pool/cluster, and I have to fetch this.

In SQ02, I added a structure ZA005 with this code:

data: l_kunnr type kna1-kunnr,

       l_vkorg type knvv-vkorg.

clear l_kunnr.

clear l_vkorg.

select single kunnr vkorg from knvp

   into (l_kunnr, l_vkorg)

   where  kunnr = knvv-kunnr

     and  vkorg = knvv-vkorg.

   IF SY-SUBRC EQ 0.

select * into CORRESPONDING FIELDS OF za005

   from a005

   where kunnr = l_kunnr

     and vkorg = l_vkorg.

   endselect.

else. clear za005.

endif.

When I generate this infoset and run the query for a customer and sales org, I only get one result in the output, when I know theres about 10-15 materials and condition records for this key.

This is probably very basic for you guys, but I'm learning by web-searches and forums, and I can't seem to wrap my head around why this fails

Any tip or help would be greatly appreciated!

[Former Member]

Your second SELECT selects data iteratively (10-15 times) into your structure. At the end, you are left with just the last record selected.

Press F1 on SELECT and select the data into an internal table rather than a structure.

Rob

[Former Member]

Vergard,

Select is also like a loop. if you are expecting multiple entries, you need to pass into a workarea and append it to an internal table.

Example 1 :

Select * from <table> into <workarea> FROM table .

Append <workarea> into <internal table>.

endselect.

Example 2:

Select * from  <table> into corresponding fields of <internal table >.

Regards,

RK.

[Former Member]

Roberts, thank you for literally explaining this in a simple fashion. I've mostly used SQ02 to add additional fields with simple select functions, ie. to select count/sum etc., thus retrieving and working with the syntax of data declarations and whatnot is somewhat confusing to me

I do get the gist of how it works, but my knowledge stops at how I get the result from the internal table into my query result in SQ01.

I probably come across as lazy for asking you this, but when I search and read about what only I can assume is near-correct answer, my brain explodes in a syntax mayhem:

Would you in a similar fashion tell me what the next step is in your code examples?

Do you append/get/write <internal table> into corresponding fields of <my added table/structure> or something (which would be ZA005)?

[Former Member]

When you display info set there is an option called 'Extras'  where we can have your custom code ( code tab ).

Here once you retrieve your data into the <internal table>. then move the corresponding values to ZA005.

option 1 . <internal table>[] = ZA005[]. - same structure . you don't need if the structure as same as you can pass the values directly via Select statement.

option 2:

Loop at <internal table> into <wa>.

MOVE <wa> field to <structure> fields.

APPEND <structure> INTO ZA005.

Endloop.

put a break-point in the Code Section. and generate the program and execute and verify the data.

[Former Member]

Thank you for your time, Roberts

For my requirement, I should've used A005 as a stand-alone base table and simply fetch my other "nice-to-know" data by selects.

I appreciate you giving a good example tho, I will test this!

[Jelena]

1) Query is not a good tool to retrieve data from pool/cluster tables because it has limited options where code can be added. We have many queries, but in such cases we usually create ABAP reports that are more efficient.

2) You don't need to write any code for this. In SQ02 there is Extras button, then hit Create and you'll get an option to add additional table where you can enter A005 and then key fields. You already have keys in KNVV (why are you even reading KNVP here?). After that A005 will be added to the table list and you can use it just like any other table.

P.S. Use Google search, there are very many good blogs/documents on SCN on the query subject.

[Former Member]

I didn't know you could add a pool/cluster as an additional table in fact.

One problem here tho, it seems that all key fields needs to contain an argument, when in a "normal" join I would only link the VKORG and KUNNR fields:

From the top of my head I tried '%', '%*' - to see if a "wildcard" approach could be used, but no

Do you know how to bypass these blank fields in this setting?

[Jelena]

Vegard, yes, sorry, I forgot you need a full key to add table that way (just briefly looked at your code and assumed those two fields were the only key).

Actually reading your initial post more carefully, I believe you need to do the Infoset differently. Based on this:

I've got a requirement of creating a query, fetching all discounts/rebates and values for customers and material, together with pricing group of customer.

I'm guessing you are expecting to see as many lines in the query as there are matching A005 records. But if you based the query on KNVV/KNA1 join you will only see one line per KNVV (or whichever tables are in JOIN) record.

We cannot use Extras in the Infoset to add more lines. We can only use it to add more information to the same line. So your SELECT... ENDSELECT or even selecting into an internal table - none of this would help in the query. The code will still be executed once per JOIN record and only one line will be presented.

Think about the Infoset as of a huge SELECT ... ENDSELECT. Your "extra" code is executed inside it and only one line per SELECT loop iteration is displayed.

If you want to see multiple A005 records and just add some customer master data you need to re-do this using A005 as a base table. You won't be able to JOIN anything, but you can use Extras to add the fields from the customer master. (Actually, Infosets add some text fields by default, e.g. you don't need to join KNA1 just to get the customer name, it'll be available when you build the query.)

Although a custom ABAP report might be a better option here, depending on many factors.

[Former Member]

Jelena, doh..

Since A005 is not able to be part of a join, I gave up thinking I could use A005 at all. But starting off the querybuilder by using A005 as a table only, works perfectly! Now I'll just add whatever information I want by normal selects as I'm used to

But so I know for future references, theres no way at all of getting several line results based on a select-result?

[Jelena]

Vegard R wrote:

 But so I know for future references, theres no way at all of getting several line results based on a select-result?

To my knowledge - no. Actually query is just an ABAP report (it's generated by the system), I'd highly recommend looking at the ABAP code there. It really helps to understand how it works. You can see the report name somewhere in the menu in SQ01.

[Former Member]

Thank you for your valuable input, Jelena