topic SQL error in the database when accessing a table in Application Development and Automation Discussions

SQL error in the database when accessing a table

Former Member — Fri, 01 Apr 2011 07:00:28 GMT

Hi All,

i'm getting error 'SQL error in the database when accessing a table' when process below sql for no's of entries

data :qfrom type string , count type i.

"qfrom = 'DD01L INNER JOIN DD02L ON DD02L_{AS4LOCAL = DD01L}AS4LOCAL AND DD02L_{AS4VERS = DD01L}AS4VERS".

select count(*) into count from (qfrom) .

write: count.

but for other sql like below one it's working fine...

*qfrom = 'mara INNER JOIN mard ON mard_{matnr = mara}matnr AND mard_{pstat = mara}pstat'.

select count(*) into count from (qfrom) .

write: count.

Runtime Errors DBIF_RSQL_SQL_ERROR

Except. CX_SY_OPEN_SQL_DB

Regards,

Anuj jain

Re: SQL error in the database when accessing a table

SuhaSaha — Fri, 01 Apr 2011 08:27:48 GMT

Hello Anuj,

This is because the COUNT(*) is returning a value which is longer than the INT4 data type it is expecting. If you restrict the selection by introducing a WHERE clause, then you won't get the dump:

DATA: dyn_from    TYPE string,
      dyn_where   TYPE string,
      gx_sql_err  TYPE REF TO cx_sy_open_sql_error,
      gv_text     TYPE string.

dyn_from  = `DD01L INNER JOIN DD02L ON DD02L~AS4LOCAL = DD01L~AS4LOCAL AND DD02L~AS4VERS = DD01L~AS4VERS`.
dyn_where = `DOMNAME LIKE 'Z%'`.

TRY .

    SELECT COUNT(*) FROM (dyn_from) WHERE (dyn_where).
    WRITE: / sy-dbcnt NO-GROUPING.
  CATCH:  cx_sy_open_sql_db             INTO gx_sql_err,
          cx_sy_dynamic_osql_semantics  INTO gx_sql_err,
          cx_sy_dynamic_osql_syntax     INTO gx_sql_err.
ENDTRY.

IF gx_sql_err IS BOUND.
  gv_text = gx_sql_err->get_text( ).
  WRITE: / gv_text.
ENDIF.

BR,

Suhas

Re: SQL error in the database when accessing a table

Former Member — Fri, 01 Apr 2011 09:16:51 GMT

That's ok Suhas...but can i get this value which is more than int4 length in any method without defined any where like

data: count type string.

SELECT COUNT(*) into count FROM (dyn_from) WHERE (dyn_where).

or we can not get any return value whose length is more than int4 becoz it's dependent on sy-dbcnt whose data type is int4.

Regards ,

Anuj jain

Re: SQL error in the database when accessing a table

SuhaSaha — Fri, 01 Apr 2011 09:32:39 GMT

Hello Anuj,

A quick F1 on COUNT(*) will tell you that it expects a variable of type INT4.

BR,

Suhas

Re: SQL error in the database when accessing a table

Sandeep_Panghal — Fri, 01 Apr 2011 09:36:37 GMT

You can try with type DEC .

Re: SQL error in the database when accessing a table

Former Member — Fri, 01 Apr 2011 10:06:29 GMT

Thanks Suhas....

Solved.