[RESOLVED] Select not returning expected rows

Development (ABAP Development WorkBench, ABAP/4 programming)

Moderators: Snowy, thx4allthefish, YuriT, Gothmog

Post Reply
Posts: 70
Joined: Wed Jan 02, 2008 10:27 am
Location: North Norfolk, UK

[RESOLVED] Select not returning expected rows

Post by AndyW » Wed Jan 19, 2011 10:02 am

Hi All

I wonder if anyone can spot anything wrong with the following select statement or at least give me a clue on how I might work out why its not working!!

Code: Select all

SELECT vakpa~vbeln vakpa~auart vbap~matnr vbap~zmeng
  FROM vakpa INNER JOIN vbap ON vakpa~vbeln = vbap~vbeln
             INNER JOIN vbkd ON vbkd~vbeln = vakpa~vbeln
                            AND vbkd~posnr = '000000'
  INTO TABLE lt_history
 WHERE vakpa~kunde = i_parent_employer
   AND vakpa~parvw = 'AG'
   AND ( vakpa~auart = lt_t066-lookup_doc_type OR
         vakpa~auart = 'ZDR' )   
   AND vakpa~vbeln <> i_current_doc
   AND vbap~matnr = lt_t066-lookup_para
   AND vbap~abgru IN lt_abgru[]  
   AND vbkd~prsdt = l_grant_yr.
I think it's fairly obvious what data the variables contained but am happy to explain more if necessary.

It should return three rows of data; one from one sales order and two items from another. However, it is only returning two rows, one from each sales order. The sales order with two items to be returned has four items on it in total but only two should be selected. Each of these items have pretty much the same data as far as this select is concerned i.e. same material code and rejection reason (different billing dates but I don't see how that's relevant). I can see nothing wrong when debugging it i.e. all the variables appear to be populated correctly. I've run the SQL trace and that also looks ok and is indicating three records are being returned but within the program only two are showing. I've also tried replicating the above in an SQL utility we have and that also returns the rows correctly.

The fact that the trace is indicating three rows are being returned makes me think the SQL and variables are ok but I don't see how a row can then disappear between being returned from the database and appearing within the program. :?

I'm sure its something simple/obvious but I can't figure it out! I'd be glad to hear any ideas or suggestions...

Last edited by AndyW on Wed Jan 19, 2011 11:13 am, edited 1 time in total.

Posts: 1955
Joined: Wed Sep 12, 2007 4:46 am
Location: Probably not home

Re: Select not returning expected rows

Post by Gothmog » Wed Jan 19, 2011 10:35 am

For all entries implicitly deletes duplicates - could it be the reason ?

Edit to quote the help :
With duplicated rows in the resulting set, the addition FOR ALL ENTRIES has the same effect as if addition DISTINCT were specified in the definition of the selection quantity. Unlike DISTINCT, the rows are not deleted from the database system but are deleted on the application server from the resulting set.
This seems to match the behaviour you're describing.
68 74 74 70 3a 2f 2f 74 69 6e 79 75 72 6c 2e 63 6f 6d 2f 62 64 6f 37 6d 77 67

Posts: 70
Joined: Wed Jan 02, 2008 10:27 am
Location: North Norfolk, UK

Re: Select not returning expected rows

Post by AndyW » Wed Jan 19, 2011 11:13 am

Gothmog, you are a star!

That's exactly what it is - I've changed the value of the quantity field so they're different and now it works. I just need to add POSNR to the select to fix it properly. I had no idea FOR ALL ENTRIES did that and it didn't even occur to me to check the help (I normally do, honest!) - just goes to show, I thought I knew all about FOR ALL ENTRIES but clearly didn't!

Well, you've taught me something new and solved a major headache for me - take the rest of the day off :wink:



Post Reply