Dynamic Select statements (Joins)

Former Member — Wed, 01 Jun 2005 15:52:27 GMT

Hi friends the following is the Source code in my FUNCTION MODULE and this i have few parameters in my report

MARKET

MEMBER

EFFECTIVE DATE

STATUS

COUNTRY

ADMISSION TYPE

I need the output from Different Select options (PARAMETER OPTIONS) from the User point of view..this is Just one market's (XETRA) selections .. I have 13 markets... Kindly anyone help me with the similar output I need a dynamic selection ...

SAMPLE or EXAMPLE CODE appreciated...

CASE iv_market.

WHEN lc_xtra_market.

lv_table = 'ZTGP_MEMBER_XTRA'.

IF iv_member_id IS INITIAL AND iv_country IS INITIAL AND iv_adm_type IS INITIAL.

SELECT ztgp_member_xtra~xetra_id

ztgp_member_xtra~admission

ztgp_member_xtra~partner

ztgp_member_xtra~status

ztgp_member_xtra~adm_type

ztgp_member_xtra~object_id

ztgp_member_xtra~addrnumber

ztgp_member_xtra~beg_dat

adrc~country

INTO (lt_member-member_id, lt_member-admission, lt_member-partner, lt_member-status, lt_member-adm_type,

lt_member-object_id, lt_member-addrnumber, lt_member-beg_dat,lt_member-country)

FROM ztgp_member_xtra JOIN adrc ON ztgp_member_xtra_{addrnumber = adrc}addrnumber WHERE ztgp_member_xtra_{status EQ iv_status AND ztgp_member_xtra}beg_dat LE iv_date_effective AND

ztgp_member_xtra~end_dat GE iv_date_effective.

APPEND lt_member.

ENDSELECT.

ELSEIF NOT iv_member_id IS INITIAL AND iv_country IS INITIAL AND iv_adm_type IS INITIAL.

SELECT ztgp_member_xtra~xetra_id

ztgp_member_xtra~admission

ztgp_member_xtra~partner

ztgp_member_xtra~status

ztgp_member_xtra~adm_type

ztgp_member_xtra~object_id

ztgp_member_xtra~addrnumber

ztgp_member_xtra~beg_dat

adrc~country

INTO (lt_member-member_id, lt_member-admission, lt_member-partner, lt_member-status, lt_member-adm_type,

lt_member-object_id, lt_member-addrnumber, lt_member-beg_dat,lt_member-country)

FROM ztgp_member_xtra JOIN adrc ON ztgp_member_xtra_{addrnumber = adrc}addrnumber

WHERE ztgp_member_xtra~xetra_id EQ iv_member_id AND

ztgp_member_xtra~status EQ iv_status AND

ztgp_member_xtra~beg_dat LE iv_date_effective AND

ztgp_member_xtra~end_dat GE iv_date_effective.

APPEND lt_member.

ENDSELECT.

ELSEIF NOT iv_member_id IS INITIAL AND NOT iv_country IS INITIAL AND iv_adm_type IS INITIAL.

SELECT ztgp_member_xtra~xetra_id

ztgp_member_xtra~admission

ztgp_member_xtra~partner

ztgp_member_xtra~status

ztgp_member_xtra~adm_type

ztgp_member_xtra~object_id

ztgp_member_xtra~addrnumber

ztgp_member_xtra~beg_dat

adrc~country

INTO (lt_member-member_id, lt_member-admission, lt_member-partner, lt_member-status, lt_member-adm_type,

lt_member-object_id, lt_member-addrnumber, lt_member-beg_dat,lt_member-country)

FROM ztgp_member_xtra JOIN adrc ON ztgp_member_xtra_{addrnumber = adrc}addrnumber

WHERE ztgp_member_xtra~xetra_id EQ iv_member_id AND

adrc~country EQ iv_country AND

ztgp_member_xtra~status EQ iv_status AND

ztgp_member_xtra~beg_dat LE iv_date_effective AND

ztgp_member_xtra~end_dat GE iv_date_effective.

APPEND lt_member.

ENDSELECT.

ELSEIF iv_member_id IS INITIAL AND NOT iv_country IS INITIAL AND iv_adm_type IS INITIAL.

SELECT ztgp_member_xtra~xetra_id

ztgp_member_xtra~admission

ztgp_member_xtra~partner

ztgp_member_xtra~status

ztgp_member_xtra~adm_type

ztgp_member_xtra~object_id

ztgp_member_xtra~addrnumber

ztgp_member_xtra~beg_dat

adrc~country

INTO (lt_member-member_id, lt_member-admission, lt_member-partner, lt_member-status, lt_member-adm_type,

lt_member-object_id, lt_member-addrnumber, lt_member-beg_dat,lt_member-country)

FROM ztgp_member_xtra JOIN adrc ON ztgp_member_xtra_{addrnumber = adrc}addrnumber

WHERE adrc~country EQ iv_country AND

ztgp_member_xtra~status EQ iv_status AND

ztgp_member_xtra~beg_dat LE iv_date_effective AND

ztgp_member_xtra~end_dat GE iv_date_effective.

APPEND lt_member.

ENDSELECT.

ELSEIF iv_member_id IS INITIAL AND iv_country IS INITIAL AND NOT iv_adm_type IS INITIAL.

SELECT ztgp_member_xtra~xetra_id

ztgp_member_xtra~admission

ztgp_member_xtra~partner

ztgp_member_xtra~status

ztgp_member_xtra~adm_type

ztgp_member_xtra~object_id

ztgp_member_xtra~addrnumber

ztgp_member_xtra~beg_dat

adrc~country

INTO (lt_member-member_id, lt_member-admission, lt_member-partner, lt_member-status, lt_member-adm_type,

lt_member-object_id, lt_member-addrnumber, lt_member-beg_dat,lt_member-country)

FROM ztgp_member_xtra JOIN adrc ON ztgp_member_xtra_{addrnumber = adrc}addrnumber

WHERE ztgp_member_xtra~adm_type EQ iv_adm_type AND

ztgp_member_xtra~status EQ iv_status AND

ztgp_member_xtra~beg_dat LE iv_date_effective AND

ztgp_member_xtra~end_dat GE iv_date_effective.

APPEND lt_member.

ENDSELECT.

ENDIF.

WHEN lc_repo_market.

WHEN lc_eurex_market.

When lc_fwb_market.

and so on... 13 markets..

ENDIF.

IF sy-subrc NE 0.

ENDIF.

WHEN OTHERS.

EXIT.

ENDCASE.

Re: Dynamic Select statements (Joins)

Former Member — Wed, 01 Jun 2005 17:35:27 GMT

In your case your internal tables seems to have constant and same structure so it can be defined statically.

lv_table & lv_where_cond are variables.

SELECT F1 F2 FROM (lv_table)

INTO CORRESPONDING FIELDS OF TABLE itab

WHERE (lv_where_cond).

topic Re: Dynamic Select statements (Joins) in Application Development and Automation Discussions

Dynamic Select statements (Joins)

Re: Dynamic Select statements (Joins)