Need help with Dynamic Select statement

[Former Member]

Hi guys,

I have a unique requirement in the CRM conversions.

In table CABN there is a field "ATNAM' which is Attribute/Charactristic Name and for every characteristic their will be a check table which is the field 'ATPRT'.

My requirement is to first fetch the Check table name of an characteristic and then get the primary field of this check table and then validate the characteristic value available with the primary field.

I have already written the code to get the check table name and then using FM COM_GET_KEY_FIELDS_FOR_TABLE have fetch the primary field names in an field symbol table as i am not sure how many fields will the primary field of the check table have for different characteristic's.

Can any one suggest me how can i write a dynamic select statement to capture the required field name of the check table.

SAMPLE CODE:

READ TABLE it_atson into wa_atson WITH KEY atnam = wa_partner-atname .

IF sy-subrc EQ 0.

lv_atprt = wa_atson-atprt.

• Get key field from the database table

FIELD-SYMBOLS: <Fs_atprt> TYPE ANY TABLE.

CALL FUNCTION 'COM_GET_KEY_FIELDS_FROM_TABLE'

EXPORTING

iv_table_name = lv_atprt

• IV_CLIENT_NEEDED = ' '

IMPORTING

ET_KEY_FIELDS = <Fs_atprt>

EXCEPTIONS

NO_INPUT = 1

OTHERS = 2

.

IF sy-subrc <> 0.

• MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

• WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

if lv_atprt IS NOT INITIAL.

SELECT *

from (lv_atprt)

INTO table lt_atvalue

where field = <fs_atprt>.

[Former Member]

Maybe this example can help you:

PARAMETERS: p_cityfr TYPE spfli-cityfrom, 
            p_cityto TYPE spfli-cityto. 

DATA: BEGIN OF wa, 
         fldate TYPE sflight-fldate, 
         carrname TYPE scarr-carrname, 
         connid   TYPE spfli-connid, 
       END OF wa. 

DATA itab LIKE SORTED TABLE OF wa 
               WITH UNIQUE KEY fldate carrname connid. 
DATA: column_syntax TYPE string, 
      dbtab_syntax TYPE string. 

column_syntax = `c~carrname p~connid f~fldate`. 

dbtab_syntax = `( ( scarr AS c ` 
  & ` INNER JOIN spfli AS p ON p~carrid  = c~carrid` 
  & ` AND p~cityfrom = p_cityfr` 
  & ` AND p~cityto   = p_cityto )` 
  & ` INNER JOIN sflight AS f ON f~carrid = p~carrid ` 
  & ` AND f~connid = p~connid )`. 

SELECT (column_syntax) 
       FROM (dbtab_syntax) 
       INTO CORRESPONDING FIELDS OF TABLE itab. 

LOOP AT itab INTO wa. 
  WRITE: / wa-fldate, wa-carrname, wa-connid. 
ENDLOOP.

And this one:

PARAMETERS: column TYPE c LENGTH 8, 
            value  TYPE c LENGTH 30. 

DATA spfli_wa TYPE spfli. 

DATA cond_syntax TYPE string. 

CONCATENATE column '= value' 
            INTO cond_syntax SEPARATED BY space. 

TRY. 
    SELECT SINGLE * 
           FROM spfli 
           INTO spfli_wa 
           WHERE (cond_syntax). 
  CATCH cx_sy_dynamic_osql_error. 
    MESSAGE `Wrong WHERE condition!` TYPE 'I'. 
ENDTRY.

As variables column_syntax dbtab_syntax and cond_syntax are string you can build em as you want (following the select rules):

Notice that when buidling cond_syntax and db_syntax they can't be syntax checked and will give you dumps in execution if not made properly.

[kesavadas_thekkillath]

Use table DD03l, there will be field keyflag to identify the key fields ( marked as X )

data:r_tab type range of TABNAME.

data:wa_r like line of r_rab.

select fieldname into table it_fieldname from dd03l where tabname = lv_atprt and keyflag = 'X'.

loop at it_fieldname into wa.

wa_r-option = 'EQ'.

wa_r_sign = 'I'.

wa_r-low = wa-fieldname.

append wa_r to r_tab.

endloop

if lv_atprt IS NOT INITIAL.

SELECT *

from (lv_atprt)

INTO table lt_atvalue

where field in r_tab "range table

endif.