topic Re: ABAP SQL: Multiplication in Select-Statement in Application Development and Automation Discussions

ABAP SQL: Multiplication in Select-Statement

Former Member — Fri, 16 May 2014 10:04:15 GMT

Hi everybody,

in native SQL it should be possible to use a statement like:

SELECT preisstueck, (anzahl*preisstueck) as gesamtpreis FROM kaufteile

in ABAP I tried:

SELECT profile

(SUM( val2300 ) AS val2300 ) + ( SUM( val2315 ) AS val2315 ) + ( SUM( val2330 ) AS val2330 ) + (SUM( val2345 ) AS val2345 )

But I get an Error: Unknown row: (

Could anybody pls help?

Thanks

Regards

Mario

Re: ABAP SQL: Multiplication in Select-Statement

Former Member — Fri, 16 May 2014 10:10:22 GMT

Hi Mario,

you can't use + or * operators in ABAP SQL.

regards,

Edgar

Re: ABAP SQL: Multiplication in Select-Statement

Former Member — Fri, 16 May 2014 10:24:24 GMT

Hi edgar,

can I use native SQL instead of ABAP-SQL?

Thx, regards

Mario

Re: ABAP SQL: Multiplication in Select-Statement

Former Member — Fri, 16 May 2014 10:41:15 GMT

I suppose you can, but the SQL syntax is SGBD dependent so I can't say what it is.

It must be something like:

DATA: BEGIN OF ls_data,

menge1 LIKE mseg-menge,

menge2 LIKE mseg-menge,

END OF ls_data.

*first row only

EXEC SQL.

select top 1 val1 + val2 as menge1, val1 * val2 as menge2 into :ls_data from ztable.

ENDEXEC.

*all rows

EXEC SQL.

select sum( val1 + val2 ) as menge1, sum( val1 * val2 ) as menge2 into :ls_data from ztable.

ENDEXEC.

regards,

Edgar

Re: ABAP SQL: Multiplication in Select-Statement

Former Member — Fri, 16 May 2014 10:44:32 GMT

Hi Mario,

You can use

execute sql.

.......

" your native SQL query here

.........

endexec.

Give a try once and revert.

Re: ABAP SQL: Multiplication in Select-Statement

gaurab_banerji — Fri, 16 May 2014 10:45:46 GMT

I would never recommend using native SQL. Please try to use OpenSQL so that other abapers can easily understand the code (in future). Suppose in future the customer decides to migrate from Oracle to DB2 or HANA then the sql has to be changed too.

but if you still insist in using it here is a sample code. Please note that it works only for transparent tables.


REPORT demo_native_sql.
DATA: BEGIN OF wa,
        connid   TYPE spfli-connid,
        cityfrom TYPE spfli-cityfrom,
        cityto   TYPE spfli-cityto,
      END OF wa.
DATA c1 TYPE spfli-carrid VALUE 'LH'.
EXEC SQL PERFORMING loop_output.
  SELECT connid, cityfrom, cityto
  INTO   :wa
  FROM   spfli
  WHERE  carrid = :c1
ENDEXEC.
FORM loop_output.
  WRITE: / wa-connid, wa-cityfrom, wa-cityto.
ENDFORM.

Please note:

Native SQL statements bypass the R/3 database interface. There is no table logging, and no synchronization with the database buffer on the application server. For this reason, you should, wherever possible, use Open SQL to change database tables declared in the ABAP Dictionary. In particular, tables declared in the ABAP Dictionary that contain long columns with the types LCHR or LRAW should only be addressed using Open SQL, since the columns contain extra, database-specific length information for the column. Native SQL does not take this information into account, and may therefore produce incorrect results. Furthermore, Native SQL does not support automatic client handling. Instead, you must treat client fields like any other.

See native SQL syntax for

Oracle:Native SQL for Oracle (SAP Library - ABAP Programming (BC-ABA))

Informix: Native SQL for Informix (SAP Library - ABAP Programming (BC-ABA))

DB2: Native SQL for DB2 Common Server (SAP Library - ABAP Programming (BC-ABA))