Native SQL with a 'WITH <expression>

[former_member125661]

I am trying to get a system related info from the Database server and I know that this query works when executed by itself. Now, I am trying to execute this in my program. But i am unable to capture the output in a variable lv_pagesteal which I have declared as a char10. I am not sure if I am putting the 'into :lv_pagesteal' at the right place in the code.. I am getting a DBIF_DSQL2_SQL_ERROR . I copied the same code into ST04, native sql command line without the 'into :lv_pagesteal'. This works fine and I get the % Steals..

I am trying to capture % Page Steals information from the Database server.

data : lv_pagesteal(10).

exec sql.


with dbsnap as
(
select
float(pool_drty_pg_steal_clns) as pg_steal,
pool_drty_pg_steal_clns +  pool_drty_pg_thrsh_clns + pool_lsn_gap_clns  as total_clns
from
table(snap_get_db_v97(CAST (NULL AS VARCHAR(128)),-2))
)
select dec((pg_steal / nullif(total_clns,0))*100,5,2) as "% Steals"
into :lv_pagesteal
from dbsnap


endexec.

[Former Member]

1) What database is this?

2) There should be a return code with the SQL error. What was that?

Rob

[former_member125661]

This is a DB2.

Database Interface Information

C 1 *** ERROR in prepareStatement[dbdb6.c, 15184] CON = 0 (BEGIN) [dbsldb6trc.c#1675]

C 1 &+     dsql_db6_exec( SQLExtendedPrepare 😞 [IBM][CLI Driver][DB2/AIX64] SQL0104N  An unexpect

C 1 &+     owing "ls" from dbsnap into".  Expected tokens may include:  "END-OF-STATEMENT".  SQLST

C 1 &+

C 1 &+     with dbsnap as ( select float ( pool_drty_pg_steal_clns ) as pg_steal, pool_drty_pg_ste

C 1 &+     h_clns + pool_lsn_gap_clns as total_clns from table ( snap_get_db_v97 ( CAST ( NULL AS

C 1 &+      select dec ( ( pg_steal / nullif ( total_clns, 0 ) )*100, 5, 2 ) as "% Steals" from db

C 1 &+       cursor type=NO_HOLD, isolation=UR, cc_release=YES, optlevel=5, degree=1, op_type=18,

C 1 &+       ABAP location info: 'SAPLZKPI', 971

C 1 &+

C 1 &+     ABAP location info 'SAPLZKPI', 971

C 1 &+     SAP user 'XXXXX', transaction code 'SE37'

C 1 &+

C 1 *** ERROR in prepareStatement[dbdb6.c, 15184] (END) [dbsldb6trc.c#1675]

B E ***LOG BY2=> sql error -104 performing EXE [dbds#672]

B E ***LOG BY0=> SQL0104N  An unexpected token "?" was found following "ls" from dbsnap into".  Ex

Category               Installation Errors

Runtime Errors         DBIF_DSQL2_SQL_ERROR

Except.                CX_SY_NATIVE_SQL_ERROR

Date and Time          01/31/2016 02:51:56

Name     Val.

SY-SUBRC   1

SY-INDEX    1

SY-TABIX     1

SY-DBCNT   1

SY-FDPOS  255

SY-LSIND    0

SY-PAGNO  0

SY-LINNO    1

SY-COLNO  1

SY-PFKEY

SY-UCOMM STRT

[retired_member]

Hello Shareen,

Answer:

EXEC SQL does not support an INTO clause with WITH.

There's a small parser in the DBI that tries to pick the ABAP specific INTO clause and that supports this only for the "classic" SQL statement SELECT.

Workaround:

Use ADBC.

That's the recommended way anyhow.

Here is my example:

I will add this behavior to the documentation of EXEC SQL immediately

Best

Horst

PS: If you want to stay with EXEC SQL, you might also try to do something without INTO clause: first a OPEN CURSOR ..., then a FETCH.

EXEC SQL.

   open dbcur for

   with my_scarr as

  ( select carrid, carrname from scarr )

  select carrid, carrname  from my_scarr

ENDEXEC.

EXEC SQL.

  FETCH NEXT dbcur into :wa

ENDEXEC.