topic Re: SELECT with calculations in Application Development and Automation Discussions

SELECT with calculations

former_member804611 — Wed, 02 Jun 2010 08:04:46 GMT

good day

I am new to ABAP, How to I use calculation in SELECT statement, the following gives me errors

SELECT sf~carrid sf~connid sf~fldate sf~price sf~seatsocc ( sf~seatsmax - sf~seatsocc ) AS seatsavl 
INTO CORRESPONDING FIELDS OF TABLE itab_flight
FROM ( sflight AS sf LEFT JOIN scarr AS sc ON sf~carrid = sc~carrid )
WHERE sf~carrid = carrier.

I want the value of

seatsaval

to be the difference of

seatsmax

and

seatsocc

thanx in advance

Re: SELECT with calculations

Former Member — Wed, 02 Jun 2010 08:12:03 GMT

hi,

write your select statement like this.

SELECT sf~carrid sf~connid sf~fldate sf~price sf~seatsocc sf~seatsmax - sf~seatsocc 
INTO CORRESPONDING FIELDS OF TABLE itab_flight
FROM ( sflight AS sf LEFT JOIN scarr AS sc ON sf~carrid = sc~carrid )
WHERE sf~carrid = carrier.

loop at itab_flight.
itab_flight-seatsavl = itab_flight-seatsmax - itab_flight-seatsocc.
modify itab_flight.
clear itab_flight.
endllop.

this will do.

cheers,

Sany

Re: SELECT with calculations

MarcinPciak — Wed, 02 Jun 2010 08:23:27 GMT

These are only [aggregate functions|http://help.sap.com/abapdocu_70/en/ABENAGGREGATE_SHORTREF.htm] you can use within select statment. So calculate it either within select statement itself or later within loop


SELECT sf~carrid sf~connid sf~fldate sf~price sf~seatsocc sf~seatsmax sf~seatsocc
INTO CORRESPONDING FIELDS wa_flight  "use work area
FROM ( sflight AS sf LEFT JOIN scarr AS sc ON sf~carrid = sc~carrid )
WHERE sf~carrid = carrier.

"and calculate it yourself
wa_flight-seatsaval = wa_flight-seatsmax  - wa_flight-seatsocc.

append wa_flight to itab_flight.
ENDSELECT.

Regards

Marcin

Re: SELECT with calculations

Former Member — Wed, 02 Jun 2010 10:07:00 GMT

Can you check with ABAP Documentation , there you can find the possibilities that can be done on a table.

SELECT - aggregate

Syntax

... { MAX( [DISTINCT] col )

| MIN( [DISTINCT] col )

| AVG( [DISTINCT] col )

| SUM( [DISTINCT] col )

| COUNT( DISTINCT col )

| COUNT( * )

| count(*) } ... .

Effect

As many of the specified column labels as you like can be listed in the SELECT command as arguments of the above aggregate expression. In aggregate expressions, a single value is calculated from the values of multiple rows in a column as follows (note that the addition DISTINCT excludes double values from the calculation):

MAX( [DISTINCT] col ) Determines the maximum value of the value in the column col in the resulting set or in the current group.

MIN( [DISTINCT] col ) Determines the minimum value of the content of the column col in the resulting set or in the current group.

AVG( [DISTINCT] col ) Determines the average value of the content of the column col in the resulting set or in the current group. The data type of the column has to be numerical.

SUM( [DISTINCT] col ) Determines the sum of the content of the column col in the resulting set or in the current group. The data type of the column has to be numerical.

COUNT( DISTINCT col ) Determines the number of different values in the column col in the resulting set or in the current group.

COUNT( * ) (or count(*)) Determines the number of rows in the resulting set or in the current group. No column label is specified in this case.

If you are using aggregate expressions, all column labels that are not listed as an argument of an aggregate function are listed after the addition GROUP BY. The aggregate functions evaluate the content of the groups defined by GROUP BY in the database system and transfer the result to the combined rows of the resulting set.

The data type of aggregate expressions with the function MAX, MIN or SUM is the data type of the corresponding column in the ABAP Dictionary. Aggregate expressions with the function AVG have the data type FLTP, and those with COUNT have the data type INT4. The corresponding data object after INTO or APPENDING has to be selected accordingly.

Best Regards.

Re: SELECT with calculations

Former Member — Wed, 09 Jul 2014 12:53:22 GMT

"and calculate it yourself

wa_flight-seatsaval = wa_flight-seatsmax - wa_flight-seatsocc.

Produces syntax error dataobject wa_flight does not have a component seatsaval.

Please explain this to resolve

Thank you