SQL doubt

[Former Member]

Hi all,

SELECT plaf~matnr plaf~gsmng INTO CORRESPONDING FIELDS OF TABLE it_plan FROM plaf
    INNER JOIN mara ON
    plaf~matnr EQ mara~matnr
    WHERE plaf~matnr IN s_matnr AND
          plaf~plwrk EQ p_werks AND
          mara~mtart EQ 'FERT' AND
          plaf~umskz EQ 'X' AND
          plaf~paart EQ 'LA' AND
          plaf~pedtr IN s_date.
"In this query i want to get  total quantity (gsmng)  of particular material with in date range...
for example : 
now I'm getting like this..

MATNR           GSMNG    
947604015       10.00        "13.12.2007
947604015       20.00        "16.12.2007
947604016       45.00        "11.12.2007
947604015       15.00        "18.12.2007

but I want like this...

947604015       30.00        
947604016       60.00        

please give me some suggetions....

[Former Member]

Hi,

You can use the collect statement.

select fields from table into wa

where condition.

collect wa into itab.

endselect.

Thanks,

Sriram Ponna.

[former_member386202]

Hi,

create one more intaernal table type it_plan n do like this

Loop at it_plan.

collect it_plan to it_plan1.

Endloop.

Regards,

Prashant

[Former Member]

Hi Prashant,

For time being I'm doing same thing what you've suggested... but while getting data from DB table itself can't we do something????

[Former Member]

Hi,

Sorry.. u have any other options. But it needs one more internal table.

Wht u have done is correct.. go head.

Regards,

Niyaz

[Former Member]

You can try with the following code:

SELECT plaf_{matnr sum(plaf}gsmng) as gsmng

INTO CORRESPONDING FIELDS OF TABLE it_plan FROM plaf

INNER JOIN mara ON

plaf_{matnr EQ mara}matnr

WHERE plaf~matnr IN s_matnr AND

plaf~plwrk EQ p_werks AND

mara~mtart EQ 'FERT' AND

plaf~umskz EQ 'X' AND

plaf~paart EQ 'LA' AND

plaf~pedtr IN s_date

group by plaf~matnr.

This will help you to collect the figures per matnr from the ddatabase at one shot.

Regards,

Anindita

[Former Member]

Thank you anindita

problem solved...

[Former Member]

Hi,

You can use the collect statement.

select fields from table into wa

where condition.

collect wa into itab.

endselect.

Thanks,

Sriram Ponna.

[franciscojavier_carrillom]

I think that:

SELECT plaf_{matnr SUM( plaf}gsmng )

INTO CORRESPONDING FIELDS OF TABLE it_plan

FROM plaf INNER JOIN mara ON plaf_{matnr EQ mara}matnr

WHERE plaf~matnr IN s_matnr AND

plaf~plwrk EQ p_werks AND

mara~mtart EQ 'FERT' AND

plaf~umskz EQ 'X' AND

plaf~paart EQ 'LA' AND

plaf~pedtr IN s_date

GROUP BY plaf~matnr.

Luck.