select statement problem

[aris_hidalgo]

Hello guys,

I am trying to do a select statement wherein I would like to fetch multiple records(ematn, elikz, erekz from table ekpo) and compare it to the values fetched by the internal table field t_ekpo-ebeln(where clause) and then they would be modified according to their value. So for example, in one of my IF statements, if t_ekpo2-ematn is initial I would move space to t_ekpo-elikz and 'NO' to t_ekpo2-erekz and would move those values to it_dtl-elikz and it_dtl-erekz that is to be displayed in report output. I have posted below my code. Help would greatly be appreciated. Thanks guys!

loop at t_ekpo.

select ematn elikz erekz

from ekpo

into corresponding fields of t_ekpo2

where ebeln EQ t_ekpo-ebeln.

append t_ekpo2.

refresh t_ekpo2.

if t_ekpo2-ematn is initial.

move space to t_ekpo2-elikz.

move 'NO' to t_ekpo2-erekz.

move t_ekpo2-elikz to it_dtl-elikz.

move t_ekpo2-erekz to it_dtl-erekz.

elseif not t_ekpo2-ematn is initial and

t_ekpo2-elikz is initial and

t_ekpo2-erekz is initial.

move 'NO' to t_ekpo-elikz.

move 'NO' to t_ekpo-erekz.

move t_ekpo2-elikz to it_dtl-elikz.

move t_ekpo2-erekz to it_dtl-erekz.

elseif not t_ekpo2-ematn is initial and

t_ekpo2-elikz = 'X' and

t_ekpo2-erekz is initial.

move 'YES' to t_ekpo2-elikz.

move 'NO' to t_ekpo2-erekz.

move t_ekpo2-elikz to it_dtl-elikz.

move t_ekpo2-erekz to it_dtl-erekz.

elseif not t_ekpo2-ematn is initial and

t_ekpo2-elikz = 'X' and

t_ekpo2-erekz = 'X'.

move 'YES' to t_ekpo2-elikz.

move 'YES' to t_ekpo2-erekz.

move t_ekpo2-elikz to it_dtl-elikz.

move t_ekpo2-erekz to it_dtl-erekz.

endif.

endselect.

endloop.

[former_member186741]

one problem is that elikz and erekz are char1 so you'll have to have them declared as longet than that in your data statements if you want 'YES' and 'NO' to be held.

how about:

select ematn elikz erekz

from ekpo

into corresponding fields of table t_ekpo2

for all entries in t_ekpo

where ebeln EQ t_ekpo-ebeln.

loop at t_ekpo2.

if t_ekpo2-ematn is initial.

move space to t_ekpo2-elikz.

move 'NO' to t_ekpo2-erekz.

else.

if t_ekpo2-elikz = 'X'.

t_ekpo2-elikz = 'YES'.

else.

t_ekpo2-elikz = 'NO'.

endif.

if t_ekpo2-erekz = 'X'.

t_ekpo2-erekz = 'YES'.

else.

t_ekpo2-erekz = 'NO'.

endif.

endif.

modify t_ekpo2.

move-corresponding t_ekpo2 to it_dtl.

append it_dtl.

endloop.

[aris_hidalgo]

Hello again guys,

Below is my modified code.My problem is that I cant insert additional records to the internal table it_dtl. it only gets one record from t-ekpo2-elikz and t_ekpo2-erekz so when the report output is shown, only one line item has a value in it's it_dtl-elikz and it_dtl-erekz and all of the remaining line item in the report output has no value. I think that it only overwrites the value instead of adding it in the it_dtl table everytime it loops.Help would really be appreciated. Thanks guys!

*AVH

select ebeln ematn elikz erekz

from ekpo

into corresponding fields of table t_ekpo2

for all entries in t_ekpo

where ebeln EQ t_ekpo-ebeln.

loop at t_ekpo2.

• read table it_dtl with key ebeln = t_ekpo2-ebeln.

• refresh t_ekpo2.

if t_ekpo2-ematn is initial.

move space to t_ekpo2-elikz.

move 'NO' to t_ekpo2-erekz.

move t_ekpo2-elikz to it_dtl-elikz.

move t_ekpo2-erekz to it_dtl-erekz.

append it_dtl.

elseif not t_ekpo2-ematn is initial and

t_ekpo2-elikz is initial and

t_ekpo2-erekz is initial.

move 'NO' to t_ekpo-elikz.

move 'NO' to t_ekpo-erekz.

move t_ekpo2-elikz to it_dtl-elikz.

move t_ekpo2-erekz to it_dtl-erekz.

append it_dtl.

elseif not t_ekpo2-ematn is initial and

t_ekpo2-elikz = 'X' and

t_ekpo2-erekz is initial.

move 'YES' to t_ekpo2-elikz.

move 'NO' to t_ekpo2-erekz.

move t_ekpo2-elikz to it_dtl-elikz.

move t_ekpo2-erekz to it_dtl-erekz.

append it_dtl.

elseif not t_ekpo2-ematn is initial and

t_ekpo2-elikz = 'X' and

t_ekpo2-erekz = 'X'.

move 'YES' to t_ekpo2-elikz.

move 'YES' to t_ekpo2-erekz.

move t_ekpo2-elikz to it_dtl-elikz.

move t_ekpo2-erekz to it_dtl-erekz.

append it_dtl.

endif.

endloop.

[former_member186741]

what is the structure of it_dtl? You are not moving anything apart from elikz and erekz from it_ekpo2 to it_dtl. I think you should have a move-corresponding.

Also, if you code it like my example you won't have duplicate code whcih can cause problems, for example the following looks wrong to me:

....

elseif not t_ekpo2-ematn is initial and

t_ekpo2-elikz is initial and

t_ekpo2-erekz is initial.

move 'NO' to t_ekpo-elikz.

move 'NO' to t_ekpo-erekz.

move t_ekpo2-elikz to it_dtl-elikz.

move t_ekpo2-erekz to it_dtl-erekz.

append it_dtl.

.... shouldn't it be referring to t_ekpo2 and NOT t_ekpo??

[aris_hidalgo]

Hello Neil,

I have posted below the structure of it_dtl. And sorry, it should be t_ekpo2 instead of t_ekpo.

DATA: BEGIN OF it_dtl OCCURS 0,

name1 LIKE lfa1-name1,

psphi LIKE prps-psphi,

ebeln LIKE ekpo-ebeln,

ebelp LIKE ekpo-ebelp,

posid LIKE prps-posid,

waers LIKE ekko-waers,

netwr LIKE ekpo-netwr,

dpamt LIKE ekpo-netwr,

gramt LIKE ekpo-netwr,

iramt LIKE ekpo-netwr,

tramt LIKE ekpo-netwr,

blamt LIKE ekpo-netwr,

*AVH - Insertion

elikz(3),

erekz(3).

*AVH - End of insertion.

DATA: END OF it_dtl.

[former_member186741]

Hi,

you are not moving any fields apart from elikz and erekz from t_ekpo2 to it_dtl. This explains why they are al empty. Look at my orininal code and put a 'move-corresponding' in. But even this will only move some of the fields. How are you going to populate the bulk of the IT_DTL?? i.e. most of the other fields

[aris_hidalgo]

Hello Neil,

I decided that instead of moving the records of t_ekpo2-elikz and t_ekpo2-erekz to it_dtl-elikz and it_dtl-erekz I instead used t_ekpo2-elikz and t_ekpo2-erekz. But here's the problem, only the last values of t_ekpo2-elikz and t_ekpo2-erekz are saved and is shown when I run the report. So for example the last values of both t_ekpo2-elikz and erekz is 'YES', all of the line items in the report output displays 'YES' instead of their corresponding values. I have posted again my code below. Help would really be appreciated.Thanks guys!

*AVH

select ebeln ematn elikz erekz

from ekpo

into corresponding fields of table t_ekpo2

for all entries in t_ekpo

where ebeln EQ t_ekpo-ebeln.

loop at t_ekpo2.

• read table it_dtl with key ebeln = t_ekpo2-ebeln binary search.

if t_ekpo2-ematn is initial.

move space to t_ekpo2-elikz.

move 'NO' to t_ekpo2-erekz.

append t_ekpo2.

elseif not t_ekpo2-ematn is initial and

t_ekpo2-elikz is initial and

t_ekpo2-erekz is initial.

move 'NO' to t_ekpo2-elikz.

move 'NO' to t_ekpo2-erekz.

append t_ekpo2.

elseif not t_ekpo2-ematn is initial and

t_ekpo2-elikz = 'X' and

t_ekpo2-erekz is initial.

move 'YES' to t_ekpo2-elikz.

move 'NO' to t_ekpo2-erekz.

append t_ekpo2.

elseif not t_ekpo2-ematn is initial and

t_ekpo2-elikz = 'X' and

t_ekpo2-erekz = 'X'.

move 'YES' to t_ekpo2-elikz.

move 'YES' to t_ekpo2-erekz.

append t_ekpo2.

endif.

endloop.

***Here is the output code in the report.As you can see, instead of using the itab it_dtl I used t_ekpo2-elikz and t_ekpo2-erekz directly.

FORM write_dtl.

DELETE it_dtl WHERE name1 IS initial.

LOOP AT it_dtl.

IF v_color = 3.

v_color = 2.

ELSE.

v_color = 3.

ENDIF.

FORMAT INTENSIFIED OFF COLOR = v_color.

WRITE: / sy-vline,

(35) it_dtl-name1,

(24) it_dtl-psphi,

(10) it_dtl-ebeln,

(05) it_dtl-waers,

(18) it_dtl-netwr,

(18) it_dtl-dpamt,

(18) it_dtl-gramt,

(18) it_dtl-iramt,

(18) it_dtl-tramt,

(18) it_dtl-blamt,

(18) t_ekpo2-elikz,

(18) t_ekpo2-erekz,

sy-vline.

FORMAT COLOR OFF.

ENDLOOP.

WRITE: / sy-uline(sy-linsz).

ENDFORM. " write_dtl

[Former Member]

Instead of appending at the end use MODIFY...

loop at t_ekpo2.
* read table it_dtl with key ebeln = t_ekpo2-ebeln binary search.
if t_ekpo2-ematn is initial.
move space to t_ekpo2-elikz.
move 'NO' to t_ekpo2-erekz.
<b>MODIFY t_ekpo2.</b>

elseif not t_ekpo2-ematn is initial and
t_ekpo2-elikz is initial and
t_ekpo2-erekz is initial.
move 'NO' to t_ekpo2-elikz.
move 'NO' to t_ekpo2-erekz.
<b>MODIFY t_ekpo2.</b>

elseif not t_ekpo2-ematn is initial and
t_ekpo2-elikz = 'X' and
t_ekpo2-erekz is initial.
move 'YES' to t_ekpo2-elikz.
move 'NO' to t_ekpo2-erekz.
<b>MODIFY t_ekpo2.</b>


elseif not t_ekpo2-ematn is initial and
t_ekpo2-elikz = 'X' and
t_ekpo2-erekz = 'X'.
move 'YES' to t_ekpo2-elikz.
move 'YES' to t_ekpo2-erekz.
<b>MODIFY t_ekpo2.</b>

endif.
endloop.

Ps: Reward points if helpful.

[former_member186741]

ok, you are using t_ekpo2 instead of t_dtl BUT...for this to work you then need to position on the correct t_ekpo2. Your write will always write out the current value of t_ekpo2-elikz and erekz so the values will never vary.

Put a read of t_ekpo2 in within your loop. Something like

read table t_ekpo2 with key ebeln = it_dtl-ebeln

(and maybe ematn).

[aris_hidalgo]

Hello Neil,

Many thanks man! I put in read table t_ekpo2 with key ebeln = it_dtl-ebeln in the form write_dtl.Thanks again!

[former_member186741]

no problem,

remember that if your tables are very large making the table t_ekpo2 a sorted one will improve access. i.e the read via ebeln will only be efficient if ebeln is the major key of t_ekpo2. This may not matter if you are dealing with small numbers of records.