topic Re: SQL doubt in Application Development and Automation Discussions

SQL doubt

Former Member — Mon, 10 Dec 2007 11:58:57 GMT

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....

Re: SQL doubt

former_member386202 — Mon, 10 Dec 2007 12:05:46 GMT

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

Re: SQL doubt

Former Member — Mon, 10 Dec 2007 12:08:39 GMT

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????

Re: SQL doubt

Former Member — Mon, 10 Dec 2007 12:10:44 GMT

Hi,

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

Wht u have done is correct.. go head.

Regards,

Niyaz

Re: SQL doubt

Former Member — Mon, 10 Dec 2007 12:12:53 GMT

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

Re: SQL doubt

Former Member — Mon, 10 Dec 2007 12:14:09 GMT

Hi,

You can use the collect statement.

select fields from table into wa

where condition.

collect wa into itab.

endselect.

Thanks,

Sriram Ponna.

Re: SQL doubt

franciscojavier_carrillom — Mon, 10 Dec 2007 12:16:35 GMT

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.

Re: SQL doubt

Former Member — Mon, 10 Dec 2007 12:21:36 GMT

Thank you anindita

problem solved...