topic Re: select queries in Application Development and Automation Discussions

select queries

Former Member — Tue, 11 Mar 2008 05:47:58 GMT

Hi all,

I wrote code like this.

Here I am getting the values,but when ever ia m reading the table in a loop max1 = ilist1-zzmax_cpi. value is always getting same value.

please give me the solution

SELECT *

INTO TABLE wknvv FROM knvv

WHERE vkorg IN sales_or

AND vtweg IN dist_cha

AND spart IN div

AND vkbur IN sales_of .

SELECT vbeln auart kunnr zzmax_cpi INTO table ilist1 FROM vbak

for all entries in wknvv

WHERE

vbtyp = 'G'

AND kunnr = wknvv-kunnr

AND auart IN contr_ty.

SELECT vbeln posnr abgru ZZASSET_TAG INTO table wvbap FROM vbap

for all entries in ilist1

WHERE vbeln = ilist1-vbeln

AND abgru = ''.

SELECT * INTO table wveda FROM veda

for all entries in ilist1

WHERE vbeln = ilist1-vbeln

AND vposn = wvbap-posnr

AND vkuegru = ''

AND venddat IN dates .

LOOP AT wknvv .

read table ilist1 with key kunnr = wknvv-kunnr BINARY SEARCH .

max1 = ilist1-zzmax_cpi.

if sy-subrc = 0 and max1 is not initial.

read table wvbap with key vbeln = ilist1-vbeln BINARY SEARCH .

if sy-subrc = 0.

read table wvbap with key vbeln = ilist1-vbeln BINARY SEARCH .

endif.

IF sy-subrc = 0.

MOVE-CORRESPONDING wvbap TO wlist .

MOVE-CORRESPONDING wveda TO wlist .

MOVE-CORRESPONDING wknvv TO wlist .

APPEND wlist.

ENDIF .

endif.

ENDLOOP.

Re: select queries

Former Member — Tue, 11 Mar 2008 05:54:23 GMT

After statement

APPEND wlist.

inside loop you should add

clear: wknvv.

regards

Aditya

Re: select queries

Former Member — Tue, 11 Mar 2008 05:57:24 GMT

Hi Rakesh,

You are using BINARY search in read statements. For this internal tables must be sorted.

Sort you internal tables before the loop.

Plz revert if need more clarifications.

Reward if helpful.

Regards,

Mandeep

Re: select queries

vinod_vemuru2 — Tue, 11 Mar 2008 05:58:49 GMT

Hi,

Few mistakes i have identified in this code.

1. Before using for all entries please check the driver table is initial or not.

2. BINARY SEARCH WILL not work if u dont sort the internal tables.

Check below modified code.

SELECT *

INTO TABLE wknvv FROM knvv

WHERE vkorg IN sales_or

AND vtweg IN dist_cha

AND spart IN div

AND vkbur IN sales_of .

IF NOT wknvv[] IS INITIAL.

SELECT vbeln auart kunnr zzmax_cpi INTO table ilist1 FROM vbak

for all entries in wknvv

WHERE

vbtyp = 'G'

AND kunnr = wknvv-kunnr

AND auart IN contr_ty.

IF NOT ilist1[] IS INITIAL.

SELECT vbeln posnr abgru ZZASSET_TAG INTO table wvbap FROM vbap

for all entries in ilist1

WHERE vbeln = ilist1-vbeln

AND abgru = ''.

SELECT * INTO table wveda FROM veda

for all entries in ilist1

WHERE vbeln = ilist1-vbeln

AND vposn = wvbap-posnr

AND vkuegru = ''

AND venddat IN dates .

ENDIF.

SORT: ilist1 BY kunnr,

wvbap BY vbeln.*

LOOP AT wknvv .

read table ilist1 with key kunnr = wknvv-kunnr BINARY SEARCH .

max1 = ilist1-zzmax_cpi.

if sy-subrc = 0 and max1 is not initial.

read table wvbap with key vbeln = ilist1-vbeln BINARY SEARCH .

if sy-subrc = 0.

read table wvbap with key vbeln = ilist1-vbeln BINARY SEARCH .

endif.

IF sy-subrc = 0.

MOVE-CORRESPONDING wvbap TO wlist .

MOVE-CORRESPONDING wveda TO wlist .

MOVE-CORRESPONDING wknvv TO wlist .

APPEND wlist.

ENDIF .

endif.

ENDLOOP.

Hope this will solve ur problem.

Thanks,

Vinod.

Edited by: Vinod Kumar Vemuru on Mar 11, 2008 11:32 AM

Re: select queries

Former Member — Tue, 11 Mar 2008 06:04:41 GMT

thans for reply ,

again i am getting the same problem.

Re: select queries

Former Member — Tue, 11 Mar 2008 06:22:33 GMT

Hi,

read table ilist1 with key kunnr = wknvv-kunnr BINARY SEARCH .

in this statement, you are not using the key field of VBAK ie VBELN, that is why you are gettin same entries.

ILIST1 is gettin populated based on VBAK, in which VBELN is the key field, but you are reading based on kunnr, so you will get duplicate entries as present in the internal table ILIST1.

Reward if helpful.

Regards.

Re: select queries

Former Member — Tue, 11 Mar 2008 06:24:27 GMT

HI akshay,

can you tell me how to do that.