topic Re: problem in select statement in Application Development and Automation Discussions

problem in select statement

Former Member — Wed, 06 Dec 2006 10:15:33 GMT

Dear Gurus,

I am using a select statement wherein I donot mention DISTINCT but still the result is Distinct where as I need non-distinct values.

The statement is

SELECT KNUMV KBETR KWERT

FROM KONV

INTO TABLE WS_IT_KONV

FOR ALL ENTRIES IN WS_IT_VBRK

WHERE KNUMV = WS_IT_VBRK-KNUMV AND KSCHL = 'ZST1'.

Only distinct values of KWERT are picked up where if all the values are taken then that gives the desired output.

I collected data from KONV using SE16 by giving in KUMNV all values mentioned in WS_IT_VBRK , say 461 to 462 and KSCHL ='ZST1'.

Pls correct me if I have understood the query wrong.

Regards,

Shweta Soni.

Re: problem in select statement

Former Member — Wed, 06 Dec 2006 10:19:31 GMT

Hi,

1 . using FOR ALL ENTRIES might have restricted the data

2 . The data itself might be like that

Re: problem in select statement

Former Member — Wed, 06 Dec 2006 10:19:53 GMT

Hi Shewta ,

The problem is that when you use <b>for all entries</b> , <u>duplicate entries are deleted.</u>

So what i would suggest is select all the primary keys of the table KONV(KNUMV,KPOSN,STUNR,ZAEHK) into your internal table along with the already mentioned fields.

This should serve the purpose.

Thanks

Arun

Re: problem in select statement

Former Member — Wed, 06 Dec 2006 10:21:47 GMT

Hi,

Whenever You use "for all entries" option in your select statement, then it is mandatory to have all the key fields in the select list. then the result of the select statement will be correct. use below select statement.

SELECT KNUMV KPOSN STUNR ZAEHK KBETR KWERT

FROM KONV

INTO TABLE WS_IT_KONV

FOR ALL ENTRIES IN WS_IT_VBRK

WHERE KNUMV = WS_IT_VBRK-KNUMV AND KSCHL = 'ZST1'.

Now you will get desired output .

thanks,

sksingh

Re: problem in select statement

Former Member — Wed, 06 Dec 2006 10:24:21 GMT

Sort internal table and use for all entries.

sort ws_it_vbrk by knumv.

SELECT KNUMV KBETR KWERT

FROM KONV

INTO TABLE WS_IT_KONV

FOR ALL ENTRIES IN WS_IT_VBRK

WHERE KNUMV = WS_IT_VBRK-KNUMV AND KSCHL = 'ZST1'.

Re: problem in select statement

jayanthi_jayaraman — Wed, 06 Dec 2006 10:27:14 GMT

Hi,

For me the coding seems to be correct.

Just check anywhere in the program,'delete adjacent duplicates from ws_it_konv comparing kwert' is used.

Otherwise,try giving the values hard coded and observe whether the result is achieved.Then you can easily trace the problem.

for eg..

ws_it_vbrk-knumv = 'ABCD'.(here use from se16 table contents)

append ws_it_vbrk.

SELECT KNUMV KBETR KWERT

FROM KONV

INTO TABLE WS_IT_KONV

FOR ALL ENTRIES IN WS_IT_VBRK

WHERE KNUMV = WS_IT_VBRK-KNUMV AND KSCHL = 'ZST1'.

Then check in SE16 table contents,whether you will be getting the same value.

Re: problem in select statement

Former Member — Wed, 06 Dec 2006 10:34:32 GMT

Hi,

While using all entries you have to use all the keys of konv table

to get all records else select query returns distinct

values by l deleting duplicate entries .

change your logic as below

SELECT KNUMV KPOSN

STUNR

ZAEHK

KBETR KWERT

FROM KONV

INTO TABLE WS_IT_KONV

FOR ALL ENTRIES IN WS_IT_VBRK

WHERE KNUMV = WS_IT_VBRK-KNUMV AND KSCHL = 'ZST1'.

REgards

amole

Re: problem in select statement

Former Member — Wed, 06 Dec 2006 11:57:12 GMT

I see no error in your code .

Just see se16 and find out if there are any redundant entries in the table .

if there are any then try giving hard coded values in select statement of ws_it_vbrk and see if the select is selecting any redundant values if it is selecting then your for all entries should work fine .