Dynamic Native Sql

[Former Member]

hi, i have seen many examples of Dynamic Open SQL.

Is there a way to do dynamic Native SQL ?

i tried below, but it does not work.

data: begin of myitab occurs 0,

age type i,

end of myitab.

data: str type string.

str = 'age = 25' .

exec sql PERFORMING RUN_INSERT.

select age into :myitab

from class

where (:str)

endexec.

form run_insert.

append myitab.

endform.

[Former Member]

Hi,

Please try like this.

data: begin of myitab occurs 0,

age type i,

end of myitab.

<b>data: wa_age type i.</b>

data: str type string.

str = 'age = 25' .

exec sql PERFORMING RUN_INSERT.

<b> select age into :wa_age</b>

from class

where (:str)

endexec.

form run_insert.

<b> move wa_age to myitab.</b>

append myitab.

endform.

Hope this will help.

Regards,

Ferry Lianto

Please reward points if helpful.

[RichHeilman]

Maybe you can write the native sql statement dynamically in a FORM routine.

report zrich_0001
       no standard page heading.


perform gen_exe_native_sql.


*---------------------------------------------------------------------*
*       FORM gen_exe_native_Sql                                       *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
form gen_exe_native_sql.


  types: t_source(72).

  data: routine(32) value 'TEMP_ROUTINE',
        program(8),
        message(128),
        line type i.

  data: isource type table of t_source,
        xsource type t_source.

*  data: begin of myitab occurs 0,
*        age type i,
*        end of myitab.

  data: str type string.

* Dynamic where clause.
  str = 'age = 25'.


* Begin building the FORM inside the new report
  xsource = 'REPORT ZTEMP_REPORT.'.
  insert xsource  into isource index 1.


<b>  xsource = 'data: begin of myitab occurs 0,'.
  append xsource to isource.
  xsource = 'age type i,'.
  append xsource to isource.
  xsource = ' end of myitab.'.
  append xsource to isource.


  xsource = 'FORM & .'.
  replace '&' with routine into xsource.
  append xsource  to isource.

* Build the Native SQL statement

  xsource = 'exec sql PERFORMING RUN_INSERT.'.
  append xsource to isource.

  xsource = 'select age into :myitab from class'.
  append xsource to isource.

  concatenate 'where :' str  into xsource.
  append xsource to isource.

  xsource = 'endexec.'.
  append xsource to isource.</b>

* End the FORM
  xsource = 'ENDFORM.'.
  append xsource to isource.


* Build the RUN_INSERT form.
  xsource = 'form run_insert.'.
  append xsource to isource.
  xsource = 'append myitab.'.
  append xsource to isource.
  xsource = 'ENDFORM.'.
  append xsource to isource.

  generate subroutine pool isource name program
                           message message
                           line line.
  if sy-subrc = 0.
    perform (routine) in program (program).
  else.
    write:/ message.
  endif.

endform.

Regards,

Rich Heilman

[Former Member]

hi Rich,

your solution look promising.

The native SQL is able to run without error.

but, how do i pass the values of itab in the "ZTEMP_REPORT" back to my main program zrich_0001?

[RichHeilman]

I have modified a bit here. We will now pass the internal table back thru the TABLES parameters.

Please be aware that I have renamed the myitab to xitab in the generated FORM routine.

report zrich_0001
       no standard page heading.


data: begin of myitab occurs 0,
      age type i,
      end of myitab.


perform gen_exe_native_sql.

loop at myitab.
  write:/ myitab-age.
endloop.


*---------------------------------------------------------------------*
*       FORM gen_exe_native_Sql                                       *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
form gen_exe_native_sql.


  types: t_source(72).

  data: routine(32) value 'TEMP_ROUTINE',
        program(8),
        message(128),
        line type i.

  data: isource type table of t_source,
        xsource type t_source.

  data: str type string.

* Dynamic where clause.
  str = 'age = 25'.


* Begin building the FORM inside the new report
  xsource = 'REPORT ZTEMP_REPORT.'.
  insert xsource  into isource index 1.


<b>  xsource = 'data: begin of xitab occurs 0,'.
  append xsource to isource.
  xsource = 'age type i,'.
  append xsource to isource.
  xsource = ' end of xitab.'.
  append xsource to isource.</b>


  xsource = 'FORM <b>& tables p_itab.'.</b>
  replace '&' with routine into xsource.
  append xsource  to isource.

* Build the Native SQL statement

  xsource = 'exec sql PERFORMING RUN_INSERT.'.
  append xsource to isource.

  xsource = 'select age into<b> :xitab</b> from class'.
  append xsource to isource.

  concatenate 'where :' str  into xsource.
  append xsource to isource.

  xsource = 'endexec.'.
  append xsource to isource.

* Example code.
*  xsource = 'do 10 times.'.
*  append xsource to isource.
*  xsource = 'xitab-age = sy-index. append xitab.'.
*  append xsource to isource.
*  xsource = 'enddo.'.
*  append xsource to isource.

<b>  xsource = 'p_itab[] = xitab[].'.
  append xsource to isource.</b>

* End the FORM
  xsource = 'ENDFORM.'.
  append xsource to isource.


* Build the RUN_INSERT form.
  xsource = 'form run_insert.'.
  append xsource to isource.
  xsource = 'append xitab.'.
  append xsource to isource.
  xsource = 'ENDFORM.'.
  append xsource to isource.

  generate subroutine pool isource name program
                           message message
                           line line.
  if sy-subrc = 0.
    perform (routine) in program (program)<b> tables myitab.</b>
  else.
    write:/ message.
  endif.

endform.

Please award points for helpful answers and mark as solved when solved completely. Thanks.

Regards,

Rich Heilman

[Former Member]

hi Rich,

Thank you very much, you solution solved my problem.

my problem arise when my "where condition" in the native sql have to be dynamically generated at run-time.

Is using dynamically generating SUBROUTINE, the only solution? Bec it seems cumbersome.

Can't we just dynamically generate the sql statement and run it in main program zrich_0001 ?

[RichHeilman]

I know, it is cumbersome. I don't work with native SQL at all, so I don't know about dynamic where clause in native SQL, of course open SQL does support dynamic WHERE clauses which is very nice.

In this case, a dynamically generated subroutine is the only way to go. You can not dynamically generate the SQL statement in the main program, it just doesn't work that way, this is why you must generate a new program at runtime.

Regards,

Rich Heilman