Select Group By problem.

Former Member — Wed, 11 Jul 2007 00:13:52 GMT

Hi;

1) QUESTION.

I have the next select and i get 2 lines of the same document number.

this example is an extract of all the data i get in each line of the select:

A_{VBELN B}FKIMG B_{KZWI1 E}VERPR C_{PERNR C}KUNNR

0090000023 2.000 1586.20 608.10 00000000 0010000287

0090000023 2.000 1586.20 608.10 00000015

All the field are equal the only diference are C_{PERNR C}KUNNR, because of them is that i have 2 lines instead of one.

This is the actual SELECT:

DATA: BEGIN OF itab OCCURS 0,

sociedad LIKE vbrk-bukrs,

centro LIKE vbrp-werks,

provee LIKE mara-extwg,

material LIKE vbrp-matnr,

descri LIKE vbrp-arktx,

unidad LIKE vbrp-vrkme,

codigo LIKE vbrk-kunag,

codcli LIKE kna1-kunnr,

nomcli LIKE kna1-name1,

direc LIKE kna1-ort01,

docum LIKE vbrk-vbeln,

cantidad LIKE vbrp-fkimg,

monto LIKE vbrp-kzwi1,

costo LIKE mbew-verpr,

vendedor LIKE vbpa-pernr,

destina LIKE vbpa-kunnr,

zona LIKE vbrp-bzirk_auft,

szona LIKE vbrp-kdgrp_auft,

pais LIKE vbrp-lland_auft,

depto LIKE vbrp-regio_auft,

orgvtas LIKE vbrp-vkorg_auft,

canal LIKE vbrp-vtweg_auft,

sector LIKE vbrp-spart,

oficina LIKE vbrp-vkbur,

tipodoc LIKE vbrk-vbtyp,

fecha LIKE vbrk-fkdat,

catego LIKE mara-matkl,

matprov LIKE eina-idnlf.

DATA: END OF itab.

SELECT a_{bukrs b}werks d_{extwg b}matnr b_{arktx b}vrkme a~kunag

g_{kunnr g}name1 g_{ort01 a}vbeln b_{fkimg b}kzwi1 e_{verpr c}pernr

c_{kunnr b}bzirk_auft b_{kdgrp_auft b}lland_auft b~regio_auft

b_{vkorg_auft b}vtweg_auft b_{spart b}vkbur a_{vbtyp a}fkdat d_{matkl f}idnlf

INTO TABLE itab FROM

( ( ( ( ( ( vbrk AS a

INNER JOIN vbrp AS b ON a_{vbeln = b}vbeln )

INNER JOIN vbpa AS c ON b_{vbeln = c}vbeln )

INNER JOIN mara AS d ON b_{matnr = d}matnr )

INNER JOIN mbew AS e ON b_{matnr = e}matnr )

INNER JOIN eina AS f ON b_{matnr = f}matnr )

INNER JOIN kna1 AS g ON a_{kunag = g}kunnr )

WHERE a~bukrs = psocie AND

a~erdat IN pfecha AND

b~werks = pcentro AND

e~bwkey = pcentro AND

e~vmvpr = 'VE' AND

c~parvw NOT IN ('AG','RE','RG').

SORT itab BY material docum.

What i want is to GROUP this data to get one single line for each document number.

i tryied this but it doesn´t work:

SELECT a_{bukrs b}werks d_{extwg b}matnr b_{arktx b}vrkme a~kunag

g_{kunnr g}name1 g_{ort01 a}vbeln b_{fkimg b}kzwi1 e_{verpr c}pernr

c_{kunnr b}bzirk_auft b_{kdgrp_auft b}lland_auft b~regio_auft

b_{vkorg_auft b}vtweg_auft b_{spart b}vkbur a_{vbtyp a}fkdat d_{matkl f}idnlf

INTO TABLE itab FROM

( ( ( ( ( ( vbrk AS a

INNER JOIN vbrp AS b ON a_{vbeln = b}vbeln )

INNER JOIN vbpa AS c ON b_{vbeln = c}vbeln )

INNER JOIN mara AS d ON b_{matnr = d}matnr )

INNER JOIN mbew AS e ON b_{matnr = e}matnr )

INNER JOIN eina AS f ON b_{matnr = f}matnr )

INNER JOIN kna1 AS g ON a_{kunag = g}kunnr )

WHERE a~bukrs = psocie AND

a~erdat IN pfecha AND

b~werks = pcentro AND

e~bwkey = pcentro AND

e~vmvpr = 'VE' AND

c~parvw NOT IN ('AG','RE','RG') GROUP BY

a_{bukrs b}werks d_{extwg b}matnr b_{arktx b}vrkme a~kunag

g_{kunnr g}name1 g_{ort01 a}vbeln b_{fkimg b}kzwi1 e_{verpr c}pernr

c_{kunnr b}bzirk_auft b_{kdgrp_auft b}lland_auft b~regio_auft

b_{vkorg_auft b}vtweg_auft b_{spart b}vkbur a_{vbtyp a}fkdat d_{matkl f}idnlf.

It doesn't group the lines in one.

2) QUESTION

Why i can´t do a SELECT over an Internal Table as the one i declared in the QUESTION # 1...?

Thaks on advance.

David Fúnez
Tegucigalpa, Honduras
Corp. Mandofer

Re: Select Group By problem.

Former Member — Wed, 11 Jul 2007 00:20:57 GMT

Hi David,

1. Try to reduce the tables in the JOIN so that the code is more maintainable.

Include all the key fields from all the tables in the select.

2. You can't do SELECT on internal table, instead you can use READ or LOOP on the internal table.

Regards,

Atish

Re: Select Group By problem.

Former Member — Wed, 11 Jul 2007 02:21:30 GMT

"It doesn't group the lines in one" ???

You are expecting one line for each document number knowing that you are selecting MATNR from VBRP???

An invoice can (and usually) will have multiple line items. How are expecting to condense multiple, unique line items (by MATNR) into one line?

Please clarify your business requirement(s).

Re: Select Group By problem.

Former Member — Wed, 11 Jul 2007 02:44:34 GMT

Here is one simple suggestion :

I will not write any select query with more than 3 tables join,even if you want to write,take 3 tables as one join and remain would be for all entries .

as you mentioned group by

here i am writing group by in one of my program :

select matnr sum( vsolm ) from zrfltap into table i_tsold

where matnr in so_matnr

and vdatu in so_vdatu

and werks = p_werks

group by matnr.

sort i_tsold by matnr.

I hope you got it.

Thanks

Seshu

topic Select Group By problem. in Application Development and Automation Discussions

Select Group By problem.

Re: Select Group By problem.

Re: Select Group By problem.

Re: Select Group By problem.