topic Re: Dynamic SQL select parameters in Application Development and Automation Discussions

Dynamic SQL select parameters

Former Member — Tue, 10 Jan 2006 16:27:36 GMT

Until runtime I don't know the table name and column-name on which I have to do a select.

Here is my SQL:

SELECT single <b>w_sum_col_name </b> FROM (w_tbl_name)

INTO ZOH_AUDIT-ZOH_NET1

WHERE REQUID = ZOH_AUDIT-REQUID.

My SQL fails when I have SUM(any_column) in the string w_sum_col_name. However it works if its a just a column name of the table. Failure is when the w_sum_col_name string has an aggregate function.

Error is ..SAPSQL_ILLEGAL_AGGREGATE" CX_SY_DYNAMIC_OSQL_SYNTAX

Any alternatives for my requirement is appreciated.

Re: Dynamic SQL select parameters

Former Member — Tue, 10 Jan 2006 16:31:50 GMT

Why are you aggregating when you have a single record?

Regards,

Srikanth

Re: Dynamic SQL select parameters

Former Member — Tue, 10 Jan 2006 16:39:28 GMT

Sorry, that single in SQL is a typo: Here is the actual one.

SELECT w_sum_col_name FROM (w_tbl_name)

INTO ZOH_AUDIT-ZOH_NET1

WHERE REQUID = ZOH_AUDIT-REQUID.

Re: Dynamic SQL select parameters

Former Member — Tue, 10 Jan 2006 16:44:07 GMT

Try this, I have never tried but I guess should work

SELECT (w_sum_col_name) FROM (w_tbl_name)

INTO ZOH_AUDIT-ZOH_NET1

WHERE REQUID = ZOH_AUDIT-REQUID.

Re: Dynamic SQL select parameters

Former Member — Tue, 10 Jan 2006 16:46:51 GMT

unless I wrap the dynamic strings in () they never work.

I already have them in ().

Thanks for answering.

Gopi

Re: Dynamic SQL select parameters

Former Member — Tue, 10 Jan 2006 18:16:32 GMT

Try this code.


REPORT zreport NO STANDARD PAGE HEADING MESSAGE-ID zfi
                                        LINE-COUNT 65
                                        LINE-SIZE 120.

PARAMETERS: p_matnr LIKE mara-matnr.

DATA: mytable      LIKE dd02l-tabname,
      fields(50)   TYPE c OCCURS 0 WITH HEADER LINE,
      labst        LIKE mard-labst.

TABLES: mard.

DEFINE select_statement.

  select (&1) into &2
              from (&3)
             where matnr = p_matnr.
  endselect.

END-OF-DEFINITION.

fields = 'SUM( LABST )'.
APPEND fields.

mytable = 'MARD'.

select_statement fields[] labst 'MARD'.

WRITE:/ labst.

Re: Dynamic SQL select parameters

Former Member — Tue, 10 Jan 2006 23:25:28 GMT

I resolved this by having string = ( sum(col_name) ) instead of string = (sum(col_name)).

Thank U for all the replies

GOpi

Re: Dynamic SQL select parameters

former_member186741 — Tue, 10 Jan 2006 23:37:17 GMT

Hi Gopi - well done, now please close this thread - you can mark as 'solved it myself'.

Regards

Neil