Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

EQUAL condition on WHERE clause not working

Former Member
0 Kudos
186

Hi,

Can someone please tell me why the following WHERE clause (marked with ***** below) is not finding any EQUAL conditions?

I know that table i_cust_mast is populated with several thousand rows and that there ARE matching rows in KNVP.

Here is the code as I have it:

TYPES:

BEGIN OF t_cust_mast,

kunnr TYPE kunnr,

name1 TYPE name1_gp,

name2 TYPE name2_gp,

stras TYPE stras_gp,

adrnr TYPE adrnr,

ort01 TYPE ort01_gp,

regio TYPE regio,

pstlz TYPE pstlz,

vkbur TYPE vkbur,

konda TYPE konda,

sortl TYPE sortl,

katr1 TYPE katr1,

katr2 TYPE katr2,

katr3 TYPE katr3,

katr4 TYPE katr4,

katr5 TYPE katr5,

katr6 TYPE katr6,

katr7 TYPE katr7,

katr8 TYPE katr8,

katr9 TYPE katr9,

katr10 TYPE katr10,

bzirk TYPE bzirk,

vkgrp TYPE vkgrp,

kdgrp TYPE kdgrp,

klabc TYPE klabc,

kondaa TYPE konda,

kalks TYPE kalks,

pltyp TYPE pltyp,

hityp TYPE hityp_kh,

hkunnr TYPE hkunnr_kh,

datab TYPE datab,

datbi TYPE datbi,

lzone TYPE lzone,

  • parvw TYPE parvw,

kunn2 TYPE kunn2,

kunn2a TYPE kunn2,

kunn2b TYPE kunn2,

kunn2c TYPE kunn2,

kunn2d TYPE kunn2,

kunn2e TYPE kunn2,

kunn2f TYPE kunn2,

kunn2g TYPE kunn2,

parza TYPE parza,

ktokd TYPE ktokd,

loevm TYPE loevm_x,

END OF t_cust_mast .

TYPES:

BEGIN OF t_part_func,

kunnr TYPE kunnr,

parvw type parvw,

kunn2 TYPE kunn2,

parza type parza,

END OF t_part_func .

TYPES:

t_t_cust_mast TYPE STANDARD TABLE OF t_cust_mast,

t_t_part_func TYPE STANDARD TABLE OF t_part_func.

DATA: i_cust_mast TYPE t_t_cust_mast,

i_part_func TYPE t_t_part_func.

CONSTANTS: c_type_a TYPE msgty VALUE 'A'.

FIELD-SYMBOLS: <x_cm> LIKE LINE OF i_cust_mast.

  • Get Customer Master Data

SELECT

hkunnr hname1 hname2 hstras hadrnr hort01 h~regio

hpstlz ivkbur ikonda hsortl hkatr1 hkatr2 h~katr3

hkatr4 hkatr5 hkatr6 hkatr7 hkatr8 hkatr9 h~katr10

ibzirk ivkgrp ikdgrp iklabc ikonda ikalks i~pltyp

khityp khkunnr kdatab kdatbi h~lzone

hktokd hloevm

INTO TABLE i_cust_mast " up to 50 rows

FROM kna1 AS h

JOIN knvv AS i ON ikunnr = hkunnr

JOIN knvh AS k ON hityp = c_type_a " 'A'

AND kkunnr = ikunnr

AND kvkorg = ivkorg

AND kvtweg = ivtweg

AND kspart = ispart

AND k~datab <= sy-datum

AND k~datbi >= sy-datum.

IF sy-subrc = 0.

SORT i_cust_mast[] BY kunnr.

ENDIF.

  • Get Partner Function Data for each Customer Master Record

LOOP AT i_cust_mast ASSIGNING <x_cm>.

SELECT hkunnr hparvw hkunn2 hparza

INTO TABLE i_part_func

FROM knvp AS h

WHERE h~kunnr = <x_cm>-kunnr. ***** WHERE clause not finding any matches *****

ENDLOOP.

Thanks!!!

Andy

1 ACCEPTED SOLUTION

Former Member
0 Kudos
138

Hi,

Please do not write select statemnet inside the loop ... It is a performance issue... better use 'FOR ALL ENTRIES' .



LOOP AT i_cust_mast ASSIGNING <x_cm>.<------ Remove this loop

SELECT kunnr parvw kunn2 parza
INTO TABLE i_part_func
FROM knvp 
for all entries in i_cust_mast      <------- Use for all entries
WHERE kunnr = i_cust_mast-kunnr.

ENDLOOP.

This will solve your problem...

12 REPLIES 12

Former Member
0 Kudos
138

WHOA...let me try to clean this up...sorry!

0 Kudos
138

TYPES:

BEGIN OF t_cust_mast,

kunnr TYPE kunnr,

name1 TYPE name1_gp,

name2 TYPE name2_gp,

stras TYPE stras_gp,

adrnr TYPE adrnr,

ort01 TYPE ort01_gp,

regio TYPE regio,

pstlz TYPE pstlz,

vkbur TYPE vkbur,

konda TYPE konda,

sortl TYPE sortl,

katr1 TYPE katr1,

katr2 TYPE katr2,

katr3 TYPE katr3,

katr4 TYPE katr4,

katr5 TYPE katr5,

katr6 TYPE katr6,

katr7 TYPE katr7,

katr8 TYPE katr8,

katr9 TYPE katr9,

katr10 TYPE katr10,

bzirk TYPE bzirk,

vkgrp TYPE vkgrp,

kdgrp TYPE kdgrp,

klabc TYPE klabc,

kondaa TYPE konda,

kalks TYPE kalks,

pltyp TYPE pltyp,

hityp TYPE hityp_kh,

hkunnr TYPE hkunnr_kh,

datab TYPE datab,

datbi TYPE datbi,

lzone TYPE lzone,

  • parvw TYPE parvw,

kunn2 TYPE kunn2,

kunn2a TYPE kunn2,

kunn2b TYPE kunn2,

kunn2c TYPE kunn2,

kunn2d TYPE kunn2,

kunn2e TYPE kunn2,

kunn2f TYPE kunn2,

kunn2g TYPE kunn2,

parza TYPE parza,

ktokd TYPE ktokd,

loevm TYPE loevm_x,

END OF t_cust_mast .

TYPES:

BEGIN OF t_part_func,

kunnr TYPE kunnr,

parvw type parvw,

kunn2 TYPE kunn2,

parza type parza,

END OF t_part_func .

TYPES:

t_t_cust_mast TYPE STANDARD TABLE OF t_cust_mast,

t_t_part_func TYPE STANDARD TABLE OF t_part_func.

DATA: i_cust_mast TYPE t_t_cust_mast,

i_part_func TYPE t_t_part_func.

CONSTANTS: c_type_a TYPE msgty VALUE 'A'.

FIELD-SYMBOLS: <x_cm> LIKE LINE OF i_cust_mast.

  • Get Customer Master Data

SELECT

hkunnr hname1 hname2 hstras hadrnr hort01 h~regio

hpstlz ivkbur ikonda hsortl hkatr1 hkatr2 h~katr3

hkatr4 hkatr5 hkatr6 hkatr7 hkatr8 hkatr9 h~katr10

ibzirk ivkgrp ikdgrp iklabc ikonda ikalks i~pltyp

khityp khkunnr kdatab kdatbi h~lzone

hktokd hloevm

INTO TABLE i_cust_mast " up to 50 rows

FROM kna1 AS h

JOIN knvv AS i ON ikunnr = hkunnr

JOIN knvh AS k ON hityp = c_type_a " 'A'

AND kkunnr = ikunnr

AND kvkorg = ivkorg

AND kvtweg = ivtweg

AND kspart = ispart

AND k~datab <= sy-datum

AND k~datbi >= sy-datum.

IF sy-subrc = 0.

SORT i_cust_mast[] BY kunnr.

ENDIF.

  • Get Partner Function Data for each Customer Master Record

LOOP AT i_cust_mast ASSIGNING <x_cm>.

SELECT hkunnr hparvw hkunn2 hparza

INTO TABLE i_part_func

FROM knvp AS h

WHERE h~kunnr = <x_cm>-kunnr.

ENDLOOP.

0 Kudos
138

Hmmmm.....still trying, obviously...

0 Kudos
138

Hey Andy,

Can you please format the code into lines and segments, so that we can have a broad look...

Regards

Abhinab

0 Kudos
138

Hi

There a limit for the lines of abap code can be posted.

Max

0 Kudos
138

Sorry...I'll split this into two posts...thanks!

0 Kudos
138

TYPES:

BEGIN OF t_cust_mast,

kunnr TYPE kunnr,

name1 TYPE name1_gp,

name2 TYPE name2_gp,

stras TYPE stras_gp,

adrnr TYPE adrnr,

ort01 TYPE ort01_gp,

regio TYPE regio,

pstlz TYPE pstlz,

vkbur TYPE vkbur,

konda TYPE konda,

sortl TYPE sortl,

katr1 TYPE katr1,

katr2 TYPE katr2,

katr3 TYPE katr3,

katr4 TYPE katr4,

katr5 TYPE katr5,

katr6 TYPE katr6,

katr7 TYPE katr7,

katr8 TYPE katr8,

katr9 TYPE katr9,

katr10 TYPE katr10,

bzirk TYPE bzirk,

vkgrp TYPE vkgrp,

kdgrp TYPE kdgrp,

klabc TYPE klabc,

kondaa TYPE konda,

kalks TYPE kalks,

pltyp TYPE pltyp,

hityp TYPE hityp_kh,

hkunnr TYPE hkunnr_kh,

datab TYPE datab,

datbi TYPE datbi,

lzone TYPE lzone,

  • parvw TYPE parvw,

kunn2 TYPE kunn2,

kunn2a TYPE kunn2,

kunn2b TYPE kunn2,

kunn2c TYPE kunn2,

kunn2d TYPE kunn2,

kunn2e TYPE kunn2,

kunn2f TYPE kunn2,

kunn2g TYPE kunn2,

parza TYPE parza,

ktokd TYPE ktokd,

loevm TYPE loevm_x,

END OF t_cust_mast .

TYPES:

BEGIN OF t_part_func,

kunnr TYPE kunnr,

parvw type parvw,

kunn2 TYPE kunn2,

parza type parza,

END OF t_part_func .

TYPES:

t_t_cust_mast TYPE STANDARD TABLE OF t_cust_mast,

t_t_part_func TYPE STANDARD TABLE OF t_part_func.

DATA: i_cust_mast TYPE t_t_cust_mast,

i_part_func TYPE t_t_part_func.

0 Kudos
138

METHOD get_data.

CONSTANTS: c_type_a TYPE msgty VALUE 'A'.

FIELD-SYMBOLS: <x_cm> LIKE LINE OF i_cust_mast.

  • Get Customer Master Data

SELECT

hkunnr hname1 hname2 hstras hadrnr hort01 h~regio

hpstlz ivkbur ikonda hsortl hkatr1 hkatr2 h~katr3

hkatr4 hkatr5 hkatr6 hkatr7 hkatr8 hkatr9 h~katr10

ibzirk ivkgrp ikdgrp iklabc ikonda ikalks i~pltyp

khityp khkunnr kdatab kdatbi h~lzone

hktokd hloevm

INTO TABLE i_cust_mast " up to 50 rows

FROM kna1 AS h

JOIN knvv AS i ON ikunnr = hkunnr

JOIN knvh AS k ON hityp = c_type_a " 'A'

AND kkunnr = ikunnr

AND kvkorg = ivkorg

AND kvtweg = ivtweg

AND kspart = ispart

AND k~datab <= sy-datum

AND k~datbi >= sy-datum.

IF sy-subrc = 0.

SORT i_cust_mast[] BY kunnr.

ENDIF.

  • Get Partner Function Data for each Customer Master Record

LOOP AT i_cust_mast ASSIGNING <x_cm>.

SELECT hkunnr hparvw hkunn2 hparza

INTO TABLE i_part_func

FROM knvp AS h

WHERE h~kunnr = <x_cm>-kunnr. ***** WHERE clause not finding any matches HERE *****

ENDLOOP.

IF sy-subrc = 0.

SORT i_part_func[] BY kunnr.

ENDIF.

ENDMETHOD.

Thanks!!!

0 Kudos
138

Andrew...i think you need to use APPENDING TABLE...



* Get Partner Function Data for each Customer Master Record
LOOP AT i_cust_mast ASSIGNING <x_cm>.

SELECT h~kunnr h~parvw h~kunn2 h~parza
APPENDING TABLE i_part_func
FROM knvp AS h
WHERE h~kunnr = <x_cm>-kunnr. ***** WHERE clause not finding any matches HERE *****

ENDLOOP.

Also...you can avoid the select inside the loop by using FOR ALL ENTRIES addition..



SELECT h~kunnr h~parvw h~kunn2 h~parza
INTO TABLE i_part_func
FROM knvp
FOR ALL ENTRIES in i_cust_mast
WHERE kunnr = i_cust_mast-kunnr.

0 Kudos
138

Sneha and Vin...both of your suggestions worked perfectly. I guess I have to use Sneha's answer since he answered first, but thank you both very much for the help (and information).

Former Member
0 Kudos
139

Hi,

Please do not write select statemnet inside the loop ... It is a performance issue... better use 'FOR ALL ENTRIES' .



LOOP AT i_cust_mast ASSIGNING <x_cm>.<------ Remove this loop

SELECT kunnr parvw kunn2 parza
INTO TABLE i_part_func
FROM knvp 
for all entries in i_cust_mast      <------- Use for all entries
WHERE kunnr = i_cust_mast-kunnr.

ENDLOOP.

This will solve your problem...

viquar_iqbal
Active Contributor
0 Kudos
138

HI Andrew,

Please try this query instead

SELECT kunnr 
             parvw 
             kunn2 
             parza
INTO TABLE i_part_func
         FROM knvp 
       WHERE kunnr = <x_cm>-kunnr.

Thanks

Viquar Iqbal