topic Re: select statement in Application Development and Automation Discussions

select statement

Former Member — Wed, 06 Sep 2006 20:30:11 GMT

hi to all,

help me in wrting trhe select statement

input is jobno revenue cost(both from same table and field)

Job Number Revenue Cost Difference Percentage

IE1111 5000 2500 2500 2500/4300*100

IE2222 3000 1200 1800 1800/4300*100

Total 8000 3700 4300

here job no is from bseg-xref1

revenue is from bseg-saknr

cost is from bseg-saknr

differnece is revenue-cost

thanks in advance

kiran kumar

Re: select statement

Former Member — Thu, 07 Sep 2006 09:43:38 GMT

I would be inclined to do a basic select to get the data from the table and then loop through the results to perform the calculations, something like this:


SELECT xref1 saknr shkzg
FROM bseg
INTO CORRESPONDING FIELDS OF TABLE itab_tmp.

* Make sure table is sorted by job number
SORT itab_tmp BY xref1.

LOOP AT itab_tmp INTO wa_itab_tmp.

* Using AT control so copy the work area
  wa_itab_1 = wa_itab_tmp.

* If the line is a debit
  If wa_itab_1-shkzg = 'S'. 

*   Record value as a cost and add to the total
    wa_itab_2-cost = wa_itab_1-saknr.
    total_cost = total_cost + wa_itab_1-saknr.

* Otherwise it is a credit
  ELSE.  

*   Record value as revenue and add to the total
    wa_itab_2-rev = wa_itab_1-saknr.
    total_rev = total_rev + wa_itab_1-saknr.
  ENDIF.

* At the end of each job number calculate the difference
* and percentage and append line to a new table
  AT END OF xref1.
    wa_itab_2-xref1 = wa_itab_1-xref1.
    wa_itab_2-diff = wa_itab_2-rev - wa_itab_2-cost.
    wa_itab_2-perc = wa_itab_2-diff / 4300 * 100.
    APPEND wa_itab_2 TO itab_result.

ENDLOOP.
* At end of loop calculate the total difference
total_diff = total_rev - total_cost.

I've not tested this but it should give you an idea of what to do, you'll obviously need to declare all the variables etc.

Hope it helps

Andy

Re: select statement

Former Member — Thu, 07 Sep 2006 09:46:31 GMT

SELECT xref1 saknr shkzg

FROM bseg

INTO CORRESPONDING FIELDS OF TABLE itab.

IF sy-subrc<>0.

endif.

*do the neccassary operations on itab.

Re: select statement

Former Member — Thu, 07 Sep 2006 09:58:55 GMT

SELECT xref1 shkzg sum(saknr)

FROM bseg

INTO TABLE itab.

group by xref1 shkzg.

sort itab.

Loop at itab.

if itab-shkzg = 'S'.

lrev = itab-saknr.

else.

lcost = itab-saknr.

endif.

at end of xref1.

ldiff = lrev - lcost.

lperc = ldiff * 100 / 4300.

write : / itab-xref1, lrev, lcost, ldiff, lperc.

endat.

endloop.

Re: select statement

Former Member — Thu, 07 Sep 2006 10:07:47 GMT

hi to all

loop at it_bseg2.

move it_bseg2-saknr1 to it_final-saknr1.

move it_bseg2-wrbtr to it_final-wrbtr1.

append it_final.

clear it_final.

here wrbtr is coming to herader line not body

what is command used to keep it in body

thanks in advance

kiran

Re: select statement

Former Member — Thu, 07 Sep 2006 10:12:26 GMT

Sorry can you be more specific about the problem it is in header line and body ???

Re: select statement

Former Member — Thu, 07 Sep 2006 10:18:41 GMT

hi it is in header line not in body how to insert it in body

Re: select statement

Former Member — Thu, 07 Sep 2006 10:20:47 GMT

Hi Kiran

I'm not entirely sure I understand your question. When you loop through an internal table with a header line then the contents of each row are copied into the header line. You can then make changes to the data but these will not be 'saved' back into the table unless Append/Modify the table. Your code seems OK to me, you loop through it_bseg2, copy the contents from one header line to anther and then append the second header line, which should add the contents to the table it_final.

Assuming it_final is declared with a header line then using the CLEAR command should only clear the contents of the header row for such tables so that should be fine too.

Could be more specific about what exaclt is happening and what you are trying to achieve.

Kind regards

Andy

Re: select statement

Former Member — Thu, 07 Sep 2006 10:21:46 GMT

hi it is in header line not in body how to insert it in body

Re: select statement

Former Member — Thu, 07 Sep 2006 10:39:34 GMT

Hi Kiran

So at the end of each iteration, the header line of it_final contains the data copied from the header line of it_bseg2. The Append it_final command is executed but the contents of the it_final header line are not added to the table itself. Presumably the clear statement then empties the header line. Is this correct?

Could you please post your table declarations? I think the code you have posted should work!

Kind regards

Andy