Solved: Re: EQUAL condition on WHERE clause not working

Former Member · ‎2009 Jun 17

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

h_{kunnr h}name1 h_{name2 h}stras h_{adrnr h}ort01 h~regio

h_{pstlz i}vkbur i_{konda h}sortl h_{katr1 h}katr2 h~katr3

h_{katr4 h}katr5 h_{katr6 h}katr7 h_{katr8 h}katr9 h~katr10

i_{bzirk i}vkgrp i_{kdgrp i}klabc i_{konda i}kalks i~pltyp

k_{hityp k}hkunnr k_{datab k}datbi h~lzone

h_{ktokd h}loevm

INTO TABLE i_cust_mast " up to 50 rows

FROM kna1 AS h

JOIN knvv AS i ON i_{kunnr = h}kunnr

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

AND k_{kunnr = i}kunnr

AND k_{vkorg = i}vkorg

AND k_{vtweg = i}vtweg

AND k_{spart = i}spart

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 h_{kunnr h}parvw h_{kunn2 h}parza

INTO TABLE i_part_func

FROM knvp AS h

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

ENDLOOP.

Thanks!!!

Andy

Former Member · ‎2009 Jun 17

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...

Former Member · ‎2009 Jun 17

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

Former Member · ‎2009 Jun 17

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

h_{kunnr h}name1 h_{name2 h}stras h_{adrnr h}ort01 h~regio

h_{pstlz i}vkbur i_{konda h}sortl h_{katr1 h}katr2 h~katr3

h_{katr4 h}katr5 h_{katr6 h}katr7 h_{katr8 h}katr9 h~katr10

i_{bzirk i}vkgrp i_{kdgrp i}klabc i_{konda i}kalks i~pltyp

k_{hityp k}hkunnr k_{datab k}datbi h~lzone

h_{ktokd h}loevm

INTO TABLE i_cust_mast " up to 50 rows

FROM kna1 AS h

JOIN knvv AS i ON i_{kunnr = h}kunnr

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

AND k_{kunnr = i}kunnr

AND k_{vkorg = i}vkorg

AND k_{vtweg = i}vtweg

AND k_{spart = i}spart

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 h_{kunnr h}parvw h_{kunn2 h}parza

INTO TABLE i_part_func

FROM knvp AS h

WHERE h~kunnr = <x_cm>-kunnr.

ENDLOOP.

Former Member · ‎2009 Jun 17

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

former_member212002 · ‎2009 Jun 17

Hey Andy,

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

Regards

Abhinab

Former Member · ‎2009 Jun 17

Hi

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

Max

Former Member · ‎2009 Jun 17

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

Former Member · ‎2009 Jun 17

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.

Former Member · ‎2009 Jun 17

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

h_{kunnr h}name1 h_{name2 h}stras h_{adrnr h}ort01 h~regio

h_{pstlz i}vkbur i_{konda h}sortl h_{katr1 h}katr2 h~katr3

h_{katr4 h}katr5 h_{katr6 h}katr7 h_{katr8 h}katr9 h~katr10

i_{bzirk i}vkgrp i_{kdgrp i}klabc i_{konda i}kalks i~pltyp

k_{hityp k}hkunnr k_{datab k}datbi h~lzone

h_{ktokd h}loevm

INTO TABLE i_cust_mast " up to 50 rows

FROM kna1 AS h

JOIN knvv AS i ON i_{kunnr = h}kunnr

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

AND k_{kunnr = i}kunnr

AND k_{vkorg = i}vkorg

AND k_{vtweg = i}vtweg

AND k_{spart = i}spart

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 h_{kunnr h}parvw h_{kunn2 h}parza

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!!!

former_member212005 · ‎2009 Jun 17

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.

Former Member · ‎2009 Jun 17

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 · ‎2009 Jun 17

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 · ‎2009 Jun 17

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