SELECT not retrieving all rows

[Former Member]

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

1

2

3

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?

[Former Member]

hi

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^

[Former Member]

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.

[Former Member]

trkeynr

1

2

3

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.

[Former Member]

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

[Former Member]

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"?

[Former Member]

It's a summarization table from COPA.

Regards

[Former Member]

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.

[Former Member]

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

[Former Member]

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

[Former Member]

what are the contents in this table

t_tketrlg-ttabname

regards,

santhosh

[Former Member]

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.

[Former Member]

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

[Former Member]

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

[Former Member]

I'm pasting the Table entries:

TRKEYNR GJAHR PERDE VRGAR PALEDGER VVVOL_ME ...

0000634834429549 2007 003 F 02 SU ...

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

[Former Member]

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

1

2

3

loop at <b>itab1</b>.

read table <b>itab2</b> with key v1 = <b>itab1-v1</b>.

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 <b>itab2</b>.

read table <b>itab1</b> with key v1 = <b>itab2-v1</b>.

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