topic Re: dynamic sql in Application Development and Automation Discussions

dynamic sql

Former Member — Thu, 27 Mar 2008 10:41:13 GMT

Hi,

In the following dynamic sql query p_datapt is a parameter, so it showing the error as that field doesnot exist in table.

select PERNR p_datapt from (tabname) into corresponding fields of table <itab1> WHERE begda <= p_endda AND endda >= p_begda.

How do i handle this..?(that is how to place the field name which is entered by the user at runtime instead of p_datapt).

For Eg. If the user enters value for p_datapt as 'SUBTY' we need to select PERNR and SUBTY from the specified table.

Please help me in this.

Thanks in advance.

Regards,

Arunsri.

Re: dynamic sql

Former Member — Thu, 27 Mar 2008 11:48:16 GMT

Hi,

this should work:


data cols type string.

CONCATENATE 'PERNR' p_datapt into cols SEPARATED BY space.

select (cols) from (tabname) ...

Regards,

Thomas Langen

Re: dynamic sql

Former Member — Thu, 27 Mar 2008 12:38:16 GMT

Hi Arunsri,

you could also use an internal table to specify the fields. Here's what you can find in the SAP Help:

SELECT \[SINGLE \[FOR UPDATE\] | DISTINCT\] (itab)

Effect

Works like SELECT \[SINGLE \[FOR UPDATE\] | DISTINCT\] s1 ... sn, if the internal table itab contains the list s1 ... sn as ABAP source code, and works like SELECT \[SINGLE \[FOR UPDATE\] | DISTINCT\] *, if itab is empty. The internal table itab may only contain one field, which must have type C and not be longer than 72 characters. You must specify itab in parentheses. Do not include spaces between the parentheses and the table name.

Note

The same restrictions apply to this variant as to SELECT \[SINGLE \[FOR UPDATE\] | DISTINCT\] s1 ... sn.

Example

Example to display all Lufthansa routes:


DATA WA_SPFLI    TYPE SPFLI, 
     WA_FTAB(72) TYPE C, 
     FTAB        LIKE TABLE OF WA_FTAB. 

CLEAR FTAB. 
FTAB = 'CITYFROM'. APPEND WA_FTAB TO FTAB. 
FTAB = 'CITYTO'.   APPEND WA_FTAB TO FTAB. 
SELECT DISTINCT (FTAB) 
       INTO CORRESPONDING FIELDS OF WA_SPFLI 
       FROM SPFLI 
       WHERE 
         CARRID   = 'LH'. 
  WRITE: / WA_SPFLI-CITYFROM, WA_SPFLI-CITYTO. 
ENDSELECT.

I hope this helps. Best regards,

Alvaro