topic Re: SELECT not retrieving all rows in Application Development and Automation Discussions

SELECT not retrieving all rows

Former Member — Mon, 06 Nov 2006 09:55:53 GMT

Hello, I'm checking a bug in a program and I see a problem with this select:

select (w_tfieldlist)

from (t_tketrlg-ttabname)

into corresponding fields of table t_ttabname

for all entries in t_ktabname

where trkeynr eq t_ktabname-trkeynr

AND GJAHR EQ P_YPER+0(4)

AND PERDE EQ P_YPER+4(3)

AND VRGAR IN S_VRGAR

and paledger eq w_paledger.

The selected table has something like this:

trkeynr A

1 1000

2 2000

2 3000

3 4000

The t_ktabname has something like this

trkeynr

When I checked the results I see this

1 1000

2 2000

3 4000

As you can see, the select is skipping one of the records, anyone knows why this happens?

Re: SELECT not retrieving all rows

Former Member — Mon, 06 Nov 2006 10:02:24 GMT

good

check out wheather your pointer is countion from first record or second record and after countion what the header value,

debug it and give the clear itab statement at a appropriate place, it llwork.

thanks

mrutyun^

Re: SELECT not retrieving all rows

Former Member — Mon, 06 Nov 2006 10:04:41 GMT

Hi Scott,

Not sure abt ur query, but in general this issue arises whenever 'for all entries' is used and not all key fields are selected from the database table. My suggestion is to retrive all key fields in the select query. May be this can solve the issue.

Regards,

Santosh.

Re: SELECT not retrieving all rows

Former Member — Mon, 06 Nov 2006 10:09:14 GMT

by the result do u mean in the internal table or do u mean the output u are getting?

if it is the output. check u are looping the correct table

santhosh

Re: SELECT not retrieving all rows

Former Member — Mon, 06 Nov 2006 10:09:39 GMT

trkeynr

have only three value for all entries first it check value regarding 1 and fetch the value and than regarding 2 and fetch the value but in table there is two value for 2 but in this query it fetch only one.

better way used join statement to fetch the value from tables.

Re: SELECT not retrieving all rows

Former Member — Mon, 06 Nov 2006 10:11:53 GMT

I'm debugging the program and I checked the internal table that only has one entry instead of two entries

Re: SELECT not retrieving all rows

Former Member — Mon, 06 Nov 2006 10:13:03 GMT

hey kishan it would fetch all the values with 2 coz he is not using that trkeynr table in the select stment, not only one rite..coz it is for all entries i dont see any problems there there is a problem in some loop.

regards,

santhosh

Re: SELECT not retrieving all rows

Former Member — Mon, 06 Nov 2006 10:15:42 GMT

what are the contents in this table

t_tketrlg-ttabname

regards,

santhosh

Re: SELECT not retrieving all rows

Former Member — Mon, 06 Nov 2006 10:16:34 GMT

Kaluvala, the strange thing is that I'm checking the internal table from the debugger and it only retrieved one of the two records it should have retrieved.

How do you do a join with an internal table without using the "all entries"?

Re: SELECT not retrieving all rows

Former Member — Mon, 06 Nov 2006 10:19:27 GMT

It's a summarization table from COPA.

Regards

Re: SELECT not retrieving all rows

Former Member — Mon, 06 Nov 2006 10:29:23 GMT

select t_tketrlg~w_tfieldlist ktabname~trkeynr from from table t_tketrlg innerjoin ktabname 
on t_tketrlg~trkeynr = ktabname~trkeynr into corresponding fields of table t_ttabname 
where  t_tketrlg~GJAHR EQ P_YPER+0(4) 
AND t_tketrlg~PERDE EQ P_YPER+4(3) 
AND t_tketrlg~VRGAR IN S_VRGAR 
and t_tketrlg~paledger eq w_paledger.

Re: SELECT not retrieving all rows

Former Member — Mon, 06 Nov 2006 10:31:10 GMT

Hello.

You should select column "A" too but your selected table has no primary key.

If your selected table has a primary key for unique each line you will not found this problem.

Re: SELECT not retrieving all rows

Former Member — Mon, 06 Nov 2006 10:37:08 GMT

hey scott,

can u confirm 2 things..your table has all the key u specified in the where condition

and for the record u are specifing the keys are all the same and are not any differnt.

santhosh

Re: SELECT not retrieving all rows

Former Member — Mon, 06 Nov 2006 10:44:41 GMT

Please check the below points ..they can be the cause -->

1. Internal table t_ttabname definition..is it a sorter/hashed table or a standard table ?

2. What about the other fields in the WHERE condition are they matching ?

AND GJAHR EQ P_YPER+0(4)

AND PERDE EQ P_YPER+4(3)

AND VRGAR IN S_VRGAR

and paledger eq w_paledger.

Regards

Anurag

Re: SELECT not retrieving all rows

Former Member — Mon, 06 Nov 2006 10:49:54 GMT

Hi,

if you shud get tthe output like this

1 1000

2 2000

2 3000

3 4000

you have to reverse the tables

i am not sure about the select query using for all entries but

if u do like the below

itab1 has

1 1000

2 2000

2 3000

3 4000

itab2 has

loop at itab1.

read table itab2 with key v1 = itab1-v1.

it_fin-v1 = itab1-v1.

it_fin-v2 = itab1-v2.

append it_fin.

endloop.

in this u r reading data from itab2 checking data wrt itab1, so both records (with 2) r coming

if u do like this

loop at itab2.

read table itab1 with key v1 = itab2-v1.

it_fin-v1 = itab1-v1.

it_fin-v2 = itab1-v2.

append it_fin.

endloop.

here u will get only one record.

try this in debug mode u may get some idea

regards,

Sowjanya

Re: SELECT not retrieving all rows

Former Member — Mon, 06 Nov 2006 10:55:16 GMT

I'm pasting the Table entries:

TRKEYNR GJAHR PERDE VRGAR PALEDGER VVVOL_ME ...

0000634834429549 2007 003 F 02 SU ...

The PK of the table includes all the fields that are used in the WHERE clause.

The definition of the Int Table is standard:

data : begin of t_ttabname occurs 0 ,

bukrs like t001-bukrs ,

prctr like cepc-prctr ,

TRKEYNR TYPE NUM16 , 'I checked and is the same tipe as the one in the table.

...

end of t_ttabname