topic Re: another problem about select..sum.. in Application Development and Automation Discussions

another problem about select..sum..

Former Member — Thu, 09 Oct 2008 06:19:34 GMT

HI,

I'm back, and there is another select ..sum problem.

the following code:

SELECT aufnr SUM( wrbtr ) AS jfje FROM bsis
 into CORRESPONDING FIELDS OF TABLE it_syye
 WHERE bukrs = '1500'
 AND   aufnr = it_num-aufnr
 AND   budat >= '20040131'
 AND   budat <= q_date2
 AND   hkont = '0041010013'
 AND   shkzg = 'S'
 GROUP by aufnr.

SELECT aufnr SUM( wrbtr ) AS dfje FROM bsis
 into  CORRESPONDING FIELDS OF TABLE it_syye1
 WHERE bukrs = '1500'
 AND   aufnr = it_num-aufnr
 AND   budat >= '20040131'
 AND   budat <= q_date2
 AND   hkont = '0041010013'
 AND   shkzg = 'H'
 GROUP by aufnr.

I want to select the credit amount and debit amount from the table BSIS,but using the select sum , it looks like the field SHKZG doesn't work.

It select all the amount no matter what the SHKZG is 'S' or 'H'.

Could anyone explain that ,and give a good solution?

Thank you very much.

Re: another problem about select..sum..

JozsefSzikszai — Thu, 09 Oct 2008 06:26:20 GMT

strange... as a workaround can you try to replace SHKZG with BSCHL (posting key)

shkzg = 'S' ==> bschl = '40'

shkzg = 'H' ==> bschl = '50'

Re: another problem about select..sum..

Former Member — Thu, 09 Oct 2008 06:32:18 GMT

Hi Bill,

You wanna say that u give any of the 3 condition i.e.

SHKZG = 'H'.

SHKZG = 'S'.

SHKZG = ' '.

Its gives the same result???

Regards,

Surinder

Re: another problem about select..sum..

Former Member — Thu, 09 Oct 2008 06:44:20 GMT

2 conditions

shkzg = 'S'

shkzg = 'H'

the result is same.

Re: another problem about select..sum..

Former Member — Thu, 09 Oct 2008 06:45:12 GMT

Hi, eric

I use the BSCHL instead of SHKZG ,but the result is same.

Re: another problem about select..sum..

Former Member — Thu, 09 Oct 2008 06:46:17 GMT

Try the third case and check the result.

And tell the output for all three cases.

Regards,

Surinder

Re: another problem about select..sum..

JozsefSzikszai — Thu, 09 Oct 2008 06:49:27 GMT


> Try the third case and check the result.
> 
> And tell the output for all three cases.
> 
> Regards,
> Surinder

this does not make any sense... shkzg is either S or H, but can NEVER be empty...

Re: another problem about select..sum..

Former Member — Thu, 09 Oct 2008 07:05:34 GMT

Hi Eric,

That is what i wanted to know that for condition SHKZG = ' ' is it given the sum as zero or sumthing else.

Regards,

Surinder

Re: another problem about select..sum..

Former Member — Thu, 09 Oct 2008 07:10:40 GMT

OK.

the result is: SHKZG

condition 'S' and 'H' is same.

condition ' ' is null.

Re: another problem about select..sum..

Former Member — Thu, 09 Oct 2008 07:17:55 GMT

Bill,

There might be the case the value of debit and credit are same so it is showing the same output. You can try the below code

SELECT aufnr SUM( wrbtr ) AS jfje FROM bsis

into CORRESPONDING FIELDS OF TABLE it_syye

WHERE bukrs = '1500'

AND aufnr = it_num-aufnr

AND budat >= '20040131'

AND budat <= q_date2

AND hkont = '0041010013'

AND ( shkzg = 'S' or shkzg = 'H' )

GROUP by aufnr.

If the result now generated is different then that solves your query.

Regards,

Surinder

Re: another problem about select..sum..

Former Member — Fri, 21 Aug 2009 01:13:30 GMT

Hi Bill Gu,

I think you cannot use " INTO CORRESPODING FIELDS" with SELECT SUM ( ) statement.

Thanks & regards,

Sunny.

Re: another problem about select..sum..

Former Member — Fri, 21 Aug 2009 01:58:23 GMT

Hi Bill,

The answer is very simple... both amounts must be the same alway, if you get any difference you have to check. That's a financial concept, Debit less Credit must be zero. Remember that table BSIS have G/L open items, if you check any document on BSIS all of them have the same amount in credit and debit.

I hope is helps.

Dimas B. Salazar P.

ABAP Consultant

Caracas - Venezuela