topic Re: select query in Application Development and Automation Discussions

select query

Former Member — Tue, 11 Mar 2008 07:50:00 GMT

hi all ,

I wrote code like this ,

When ever I am reading the table ilist1 , max1 = ilist1-zzmax_cpi value I am getting value 0.In the ilist1 showing ALL THE VALUES.

in the loop i am getting sy-subrc = 8.

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.

Re: select query

prabhu_s2 — Tue, 11 Mar 2008 07:54:12 GMT

when use binary search first ensure the internal table is sorted. else binary search will not work. the rest cannot be determined unless i can look into the internal table in debuggin mode on ur sys (which is not feasible)

Re: select query

Former Member — Tue, 11 Mar 2008 08:01:21 GMT

Check the below SELECT statement of yours.


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 .

Try replacing it with


SELECT * INTO table wveda FROM veda
for all entries in ilist1
WHERE vbeln = ilist1-vbeln
"Comment the below line as wvbap-posnr does not have any value.
*AND vposn = wvbap-posnr
AND vkuegru = ''
AND venddat IN dates .

Hope this helps.

Thanks,

Balajji

Re: select query

Former Member — Tue, 11 Mar 2008 08:04:13 GMT

Hi,

Try to check sy-subrc after the read table and then move the value to max1.

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

if sy-subrc = 0.

clear max1.

max1 = ilist1-zzmax_cpi.

if max1 is not initial.

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

Hope it will solve ur problem

Thanks.

Re: select query

Former Member — Tue, 11 Mar 2008 08:09:37 GMT

Hi,

this select query of yours is wrong,

For all entries is on table ilist1 then in the where clause you cannot have another internal table field

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 .

correct this and see if it works then,

reward and close the thread if answered,

Taher