topic Re: select two diff fields and storing them in two variable in a same query ? in Application Development and Automation Discussions

select two diff fields and storing them in two variable in a same query ?

Former Member — Mon, 20 Nov 2006 06:21:57 GMT

Hello,

i have a join query and i am selecting fields from different table(one field is the material number and second is bsad-wrbtr, on which i am doing the SUM). Now i cant store material number in a variable, cause i will be getting many material numbers(against 'vbeln') and sum( bsad-wrbtr ) will be stored collectively in a variable.

Shud i be using an internal table to store both of them ? , cause i have to display all the material numbers retrieved . If yes, the how ? what will be the query like ?

My current select/join query is,


  select  sum( bd~wrbtr ) into l_cramt
    from ( ( bsis as bs
              inner join bsad as bd on bs~bukrs = bd~bukrs and
                                       bs~belnr = bd~augbl and
                                       bs~bldat = bd~augdt )

              inner join vbrp as vb on vb~vbeln = bd~belnr )

    where bs~blart = 'DZ' and bs~hkont = p_hkont.

Waiting for your replies, thank..

Shehryar Dahar

Re: select two diff fields and storing them in two variable in a same query ?

Former Member — Mon, 20 Nov 2006 06:25:36 GMT

u have to use internal table to store the material and sum cause u have to display in the output....try to use internal table and pass on the data to it...

Re: select two diff fields and storing them in two variable in a same query ?

Former Member — Mon, 20 Nov 2006 06:28:11 GMT

Hi,

Do this way. Use collect statement.

Define internal table which stores 2 fields

material number

wrbtr value

Now define

select bd~matnr

bd~wrbtr into <internal table>

from ( ( bsis as bs

inner join bsad as bd on bs_{bukrs = bd}bukrs and

bs_{belnr = bd}augbl and

bs_{bldat = bd}augdt )

inner join vbrp as vb on vb_{vbeln = bd}belnr )

where bs_{blart = 'DZ' and bs}hkont = p_hkont.

COLLECT <internal table>

ENDSELECT.

This will sum up value for each same material.

*REWARD all helpful answers

Re: select two diff fields and storing them in two variable in a same query ?

Former Member — Mon, 20 Nov 2006 06:33:30 GMT

Use group by clause in the select stmt. It gives sum for the particular field

My current select/join query is,

select material sum( bd~wrbtr ) into l_cramt

from ( ( bsis as bs

inner join bsad as bd on bs_{bukrs = bd}bukrs and

bs_{belnr = bd}augbl and

bs_{bldat = bd}augdt )

inner join vbrp as vb on vb_{vbeln = bd}belnr )

where bs_{blart = 'DZ' and bs}hkont = p_hkont

group by material.

Re: select two diff fields and storing them in two variable in a same query ?

Former Member — Mon, 20 Nov 2006 06:39:13 GMT

Hello,

Ramesh, please show me what the query will be ?

Palak, is it bd_{matnr or vb}matnr ?

Vijay, your query is giving a dump. Says 'few fields in the INTO clause' .

Thanks,

Shehryar Dahar

Re: select two diff fields and storing them in two variable in a same query ?

Former Member — Mon, 20 Nov 2006 06:41:15 GMT

You should extract the data into internal table containing 2 fields of Matnr and wrbrt

Re: select two diff fields and storing them in two variable in a same query ?

Former Member — Mon, 20 Nov 2006 06:50:47 GMT

Vijay,

the internal table is showing the sum grouped by material wise. I want to display each material and the SUM.

Thanks,

Shehryar Dahar

Re: select two diff fields and storing them in two variable in a same query ?

Former Member — Mon, 20 Nov 2006 06:56:09 GMT

I don't think u can extract the material and the sum within the same query.

One solution might be again apply sum for the all the entries in the internal table, by this u will the materials as well as its sum.

Re: select two diff fields and storing them in two variable in a same query ?

Former Member — Mon, 20 Nov 2006 07:19:27 GMT

Hello,

Lemme check and get back to you.

Thank,

Shehryar Dahar

Re: select two diff fields and storing them in two variable in a same query ?

Former Member — Mon, 20 Nov 2006 07:32:49 GMT

Hi Shehryar

You cannot select and sum the values using a single query coz you query will return multiple materials.

This should be done in two steps,

1) select your required data into the internal tables

2) Then use SUM or Collect to get the required sum for the materials and display that.

This would be a better approach,

wa_temp.

loop at itab.

wa_temp-material = itab-material.

sum.

at end of material.

wa_temp-wrbtr = itab-wrbtr.

append wa_temp into itab2.

endat.

endloop.

Note: You do not have material number in your selection. It is required if you want to display the sum for materials.

Hope this solves your problem.

Regards

Kathirvel

Re: select two diff fields and storing them in two variable in a same query ?

Former Member — Mon, 20 Nov 2006 07:52:46 GMT

Hi,

If you want to have a list of materials and sum for each of them try this:

types: begin of t_list,

matnr like vbrp-matnr,

wrbtr like bsad-wrbtr,

end of t_list.

data: i_list type table of t_list.

...

select vb_{matnr sum( bd}wrbtr ) into table i_list

from ( ( bsis as bs

inner join bsad as bd on bs_{bukrs = bd}bukrs and

bs_{belnr = bd}augbl and

bs_{bldat = bd}augdt )

inner join vbrp as vb on vb_{vbeln = bd}belnr )

where bs_{blart = 'DZ' and bs}hkont = p_hkont

group by vb~matnr.

If you want to have a sum for a given material try this:

select vb_{matnr sum( bd}wrbtr ) into (v_matnr, v_wrbtr )

from ( ( bsis as bs

inner join bsad as bd on bs_{bukrs = bd}bukrs and

bs_{belnr = bd}augbl and

bs_{bldat = bd}augdt )

inner join vbrp as vb on vb_{vbeln = bd}belnr )

where bs_{blart = 'DZ' and bs}hkont = p_hkont

and vb~matnr = p_matnr.