topic Re: Select - SUM - inner join in Application Development and Automation Discussions

Select - SUM - inner join

Former Member — Fri, 31 Jul 2009 05:56:42 GMT

Hi,

select sum( a~dmbtr )

into o_debit

from bsik as A inner join FAGLFLEXA as B

on a_{bukrs = b}RBUKRS

and a_{gjahr = b}RYEAR

and a_{belnr = b}docnr

where a~bukrs = x_bukrs

and a~lifnr = p_lifnr

and a~shkzg = 'S'

and a~budat in s_budat

and a~hkont gt '0000000000'

and a~hkont le '9999999999'

and b~prctr = p_prctr.

Above query is not giving result in o_debit though data is available in both the table. Please guide me, if I have written something wrong.

Re: Select - SUM - inner join

kesavadas_thekkillath — Fri, 31 Jul 2009 06:05:33 GMT

Check your where clause values whether it matches the table values.. i thin something may be wrong with the values in where clause

Re: Select - SUM - inner join

Former Member — Fri, 31 Jul 2009 06:07:25 GMT

Where caluse values are correct. My problem is with Select Statement. Without inner join it is providing me the value. Can I use SUM() functionality with inner join.

Re: Select - SUM - inner join

Former Member — Sat, 01 Aug 2009 05:31:22 GMT

Please help...

Re: Select - SUM - inner join

faisalatsap — Sat, 01 Aug 2009 05:38:14 GMT

Hi, Raj.

Test the following Sample It is working fine for me hope will work for you too, I did some little change.

TYPES: BEGIN OF ty_test,
  dmbtr LIKE bsik-dmbtr,
  END OF ty_test.
DATA: it_test TYPE STANDARD TABLE OF ty_test WITH HEADER LINE.

SELECT SUM( a~dmbtr ) as dmbtr
INTO CORRESPONDING FIELDS OF TABLE it_test
FROM bsik AS a INNER JOIN faglflexa AS b ON ( a~bukrs = b~rbukrs AND a~gjahr = b~ryear AND a~belnr = b~docnr )
WHERE a~bukrs = x_bukrs
AND a~lifnr = p_lifnr
AND a~shkzg = 'S'
AND a~budat IN s_budat
AND a~hkont GT '0000000000'
AND a~hkont LE '9999999999'
AND b~prctr = p_prctr.

Please Reply if still any Issue,

Best Regards,

Faisal

Re: Select - SUM - inner join

Former Member — Sat, 01 Aug 2009 06:25:44 GMT

zero value in it_test.

Re: Select - SUM - inner join

faisalatsap — Sat, 01 Aug 2009 06:54:42 GMT

Hi, Raj

Please Remove the Where Conditions and try again i think data is not available according to your conditions.

Only use the following line of Code with out where conditions.

TYPES: BEGIN OF ty_test,
  dmbtr LIKE bsik-dmbtr,
  END OF ty_test.
DATA: it_test TYPE STANDARD TABLE OF ty_test WITH HEADER LINE.
 
SELECT SUM( a~dmbtr ) as dmbtr
INTO CORRESPONDING FIELDS OF TABLE it_test
FROM bsik AS a INNER JOIN faglflexa AS b ON ( a~bukrs = b~rbukrs AND a~gjahr = b~ryear AND a~belnr = b~docnr ).

Please Reply Back if any problem,

Best Regards,

Faisal

Re: Select - SUM - inner join

Former Member — Sat, 01 Aug 2009 06:54:44 GMT

Thanks Faisel, The query is working perfectly. Thanks for helping me.