topic help on sql in Application Development and Automation Discussions

help on sql

Former Member — Tue, 17 Jan 2006 01:53:36 GMT

Can anyone help me why this SQL isn't working..PSMNG and WEMNG always have zeros even though the database tables so have values

Structure of T_ORDER

DATA: BEGIN OF T_ORDER OCCURS 0,

WERKS LIKE AUFK-WERKS,

MATNR LIKE AFPO-MATNR,

PSMNG LIKE AFPO-PSMNG,

WEMNG LIKE AFPO-WEMNG,

ACTSCRAP LIKE MARC-AUSSS,

DIFF LIKE MARC-AUSSS,

END OF T_ORDER.

LOOP AT T_ACTIVE.

SELECT AUFK_{WERKS AFPO}MATNR AVG( AFPO~PSMNG )

AVG( AFPO~WEMNG )

APPENDING CORRESPONDING FIELDS OF TABLE T_ORDER

FROM AUFK INNER JOIN AFPO

ON AUFK_{AUFNR EQ AFPO}AUFNR

WHERE AUFK~WERKS EQ T_ACTIVE-PLWRK

AND AUFK~IDAT2 LT SY-DATUM

AND AUFK~IDAT2 GE W-MDATE

AND AFPO~MATNR EQ T_ACTIVE-MATNR

GROUP BY AUFK_{WERKS AFPO}MATNR.

ENDLOOP.

Former Member — Tue, 17 Jan 2006 02:24:53 GMT

I am guessing that you need to take out the word CORRESPONDING. Just use APPENDING FIELDS OF TABLE.

I do not think the target fields have the same names when you use SQL aggregate functions.

former_member186741 — Tue, 17 Jan 2006 02:36:02 GMT

try,

SELECT AUFK_{WERKS AFPO}MATNR AVG( AFPO~PSMNG ) AS PSMNG

AVG( AFPO~WEMNG ) AS WEMNG

Former Member — Wed, 18 Jan 2006 23:49:22 GMT

Thank you so much..that did the trick..

Cheers

Rani