topic Re: problem of using formula in select join statement in Application Development and Automation Discussions

problem of using formula in select join statement

Former Member — Sun, 10 Dec 2006 02:59:08 GMT

I would like to use following statement but I can not.(do you have an example of an statement where this wuld work):

SELECT

lips_{prodh sum(lips}lfimg * lips_{umvkz / lips}umvkn) sum(lips_{volum) lips}vtweg likp~wadat_ist

INTO CORRESPONDING

FIELDS OF TABLE itab FROM

lips

JOIN likp

ON lips_{vbeln = likp}vbeln

WHERE lips~vkorg = '0200'.

Re: problem of using formula in select join statement

RichHeilman — Sun, 10 Dec 2006 03:11:41 GMT

Unfortunately, you can not do such calculations inside of a SELECT statement. You must first retrieve the records into an internal table, then LOOP the internal table and do the calculation.

Regards,

RIch Heilman

Re: problem of using formula in select join statement

Former Member — Sun, 10 Dec 2006 04:39:01 GMT

Hi Tina,

If you want to avoid LOOP you can use the following codes:

DATA: BEGIN OF itab OCCURS 0,

prodh LIKE lips-prodh,

lfimg LIKE lips-lfimg,

umvkz LIKE lips-umvkz,

umvkn LIKE lips-umvkn,

volum LIKE lips-volum,

vtweg LIKE lips-vtweg,

wadat_ist LIKE likp-wadat_ist,

END OF itab.

SELECT lips_{prodh lips}lfimg lips_{umvkz lips}umvkn lips_{volum lips}vtweg

likp~wadat_ist

INTO CORRESPONDING FIELDS OF itab

FROM lips

JOIN likp

ON lips_{vbeln = likp}vbeln

WHERE vkorg = '0200'.

itab-lfimg = itab-lfimg * itab-umvkz / itab-umvkn.

CLEAR : itab-umvkz, itab-umvkn.

COLLECT itab.

ENDSELECT.

Rgds,

Re: problem of using formula in select join statement

Former Member — Sun, 10 Dec 2006 04:58:26 GMT

Hi ,

types: BEGIN OF s_itab ,

prodh LIKE lips-prodh,

lfimg LIKE lips-lfimg,

umvkz LIKE lips-umvkz,

umvkn LIKE lips-umvkn,

volum LIKE lips-volum,

vtweg LIKE lips-vtweg,

wadat_ist LIKE likp-wadat_ist,

END OF s_itab.

DATA : temp type p decimal 2 ,

temp1 type p decimal 2 ,

temp2 type p decimal 2 ,

itab type table of s_itab ,

wt_tab type s_itab .

SELECT lips~prodh

lips~lfimg

lips~umvkz

lips~umvkn

lips~volum

lips~vtweg

likp~wadat_ist

INTO CORRESPONDING FIELDS OF TABLE itab

FROM ( lips INNER JOIN likp ON lips_{vbeln = likp}vbeln )

WHERE lips~vkorg = '0200'.

loop at itab inro wt_tab .

temp = ( wt_tab-lfimg * wt_tab-umvkz ) / lips~umvkn .

temp1 = temp1 + temp .

temp2 = temp2 + wt_tab .

endloop.

Hope it helped you .

Message was edited by:

Lakshminarayanan rohini