Community
Application Development and Automation Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Read only

Dynamic select query with dynamic where condition

Former Member

‎2010 Aug 12 7:34 AM

45,699

Hi all,

I want to use the dynamic select query with dynamic where condition. For that I used the below code but I am getting dump when using this code.

Please advice, if there is any other way to achieve this requirement.

Thanks,

Sanket Sethi

Code***************

PARAMETERS: p_tabnam      TYPE tabname,
            p_selfl1      TYPE edpline,
            p_value       TYPE edpline,
            p_where1      TYPE edpline .

DATA: lt_where    TYPE TABLE OF edpline,
      lt_sel_list TYPE TABLE OF edpline,
      l_wa_name   TYPE string,
      ls_where    TYPE edpline,
      l_having    TYPE string,
      dref        TYPE REF TO data,
      itab_type   TYPE REF TO cl_abap_tabledescr,
      struct_type TYPE REF TO cl_abap_structdescr,
      elem_type   TYPE REF TO cl_abap_elemdescr,
      comp_tab    TYPE cl_abap_structdescr=>component_table,
      comp_fld    TYPE cl_abap_structdescr=>component.

TYPES: f_count TYPE i.

FIELD-SYMBOLS : <lt_outtab> TYPE ANY TABLE,
*                <ls_outtab> TYPE ANY,
                <l_fld> TYPE ANY.

struct_type ?= cl_abap_typedescr=>describe_by_name( p_tabnam ).
elem_type   ?= cl_abap_elemdescr=>describe_by_name( 'F_COUNT' ).
comp_tab = struct_type->get_components( ).


comp_fld-name = 'F_COUNT'.
comp_fld-type = elem_type.
APPEND comp_fld TO comp_tab.

struct_type = cl_abap_structdescr=>create( comp_tab ).
itab_type   = cl_abap_tabledescr=>create( struct_type ).

l_wa_name = 'l_WA'.
CREATE DATA dref TYPE HANDLE itab_type.
ASSIGN dref->* TO <lt_outtab>.
*CREATE DATA dref TYPE HANDLE struct_type.
*ASSIGN dref->* TO <ls_outtab>.

* Creation of the selection fields
APPEND p_selfl1 TO lt_sel_list.
APPEND 'COUNT(*) AS F_COUNT' TO lt_sel_list.

** Creation of the "where" clause
*CONCATENATE p_selfl1 '= '' p_value ''.'
*            INTO ls_where
*            SEPARATED BY space.
*APPEND ls_where TO lt_where.

* Creation of the "where" clause
APPEND p_where1 TO lt_where.

* Creation of the "having" clause
l_having = 'count(*) >= 1'.

* THE dynamic select
SELECT          (lt_sel_list)
       FROM     (p_tabnam)
       INTO CORRESPONDING FIELDS OF TABLE <lt_outtab>.
*       WHERE    (lt_where).

Hi all,

I want to use the dynamic select query with dynamic where condition. For that I used the below code but I am getting dump when using this code.

Please advice, if there is any other way to achieve this requirement.

Thanks,

Sanket Sethi

Code***************

PARAMETERS: p_tabnam      TYPE tabname,
            p_selfl1      TYPE edpline,
            p_value       TYPE edpline,
            p_where1      TYPE edpline .

DATA: lt_where    TYPE TABLE OF edpline,
      lt_sel_list TYPE TABLE OF edpline,
      l_wa_name   TYPE string,
      ls_where    TYPE edpline,
      l_having    TYPE string,
      dref        TYPE REF TO data,
      itab_type   TYPE REF TO cl_abap_tabledescr,
      struct_type TYPE REF TO cl_abap_structdescr,
      elem_type   TYPE REF TO cl_abap_elemdescr,
      comp_tab    TYPE cl_abap_structdescr=>component_table,
      comp_fld    TYPE cl_abap_structdescr=>component.

TYPES: f_count TYPE i.

FIELD-SYMBOLS : <lt_outtab> TYPE ANY TABLE,
*                <ls_outtab> TYPE ANY,
                <l_fld> TYPE ANY.

struct_type ?= cl_abap_typedescr=>describe_by_name( p_tabnam ).
elem_type   ?= cl_abap_elemdescr=>describe_by_name( 'F_COUNT' ).
comp_tab = struct_type->get_components( ).


comp_fld-name = 'F_COUNT'.
comp_fld-type = elem_type.
APPEND comp_fld TO comp_tab.

struct_type = cl_abap_structdescr=>create( comp_tab ).
itab_type   = cl_abap_tabledescr=>create( struct_type ).

l_wa_name = 'l_WA'.
CREATE DATA dref TYPE HANDLE itab_type.
ASSIGN dref->* TO <lt_outtab>.
*CREATE DATA dref TYPE HANDLE struct_type.
*ASSIGN dref->* TO <ls_outtab>.

* Creation of the selection fields
APPEND p_selfl1 TO lt_sel_list.
APPEND 'COUNT(*) AS F_COUNT' TO lt_sel_list.

** Creation of the "where" clause
*CONCATENATE p_selfl1 '= '' p_value ''.'
*            INTO ls_where
*            SEPARATED BY space.
*APPEND ls_where TO lt_where.

* Creation of the "where" clause
APPEND p_where1 TO lt_where.

* Creation of the "having" clause
l_having = 'count(*) >= 1'.

* THE dynamic select
SELECT          (lt_sel_list)
       FROM     (p_tabnam)
       INTO CORRESPONDING FIELDS OF TABLE <lt_outtab>.
*       WHERE    (lt_where).

Reply
11 REPLIES 11
Read only

former_member222860
Active Contributor

‎2010 Aug 12 7:50 AM

0 Likes
15,058

Hi,

Instead of using CONCATENATE for a Where-Clause in the Select statement, use REPLACE with your field-name and value.

Here's the logic:

lt_where = '& = ''&'' '.
REPLACE '&' WITH field_name INTO lt_where.
REPLACE '&' WITH field_value INTO lt_where.

SELECT (lt_sel_list) INTO CORRESPONDING FIELDS OF TABLE <lt_outtab>
FROM (p_tabnam)
WHERE (lt_where).

thanks\

Mahesh

Read only

Former Member
In response to former_member222860

‎2010 Aug 12 8:05 AM

0 Likes
15,058

Hi Mahesh,

Thanks for the reply. Actually my Where condition is not working properly. I want to use the below code for where condition

EQUNR = '12345'.

But when I use this value in lt_where, it gives me a dump. Do you have any idea on how to use the dynamic where condition.

Thanks,

Sanket sethi

Read only

former_member222860
Active Contributor
In response to former_member222860

‎2010 Aug 12 8:11 AM

0 Likes
15,058

Hi Sanket,

The above given logic of mine works for you, put the code in the If condition and try-

just like below:

IF NOT P_EBELN IS INITIAL.

lt_where = '& = ''&'' '.
REPLACE '&' WITH p_ebeln INTO lt_where.
REPLACE '&' WITH field_value INTO lt_where.

SELECT (lt_sel_list) INTO CORRESPONDING FIELDS OF TABLE <lt_outtab>
FROM (p_tabnam)
WHERE (lt_where).

ENDIF.

thanks\

Mahesh

Read only

Former Member

‎2010 Aug 12 8:09 AM

0 Likes
15,058

i think your dynamic Where table is empty....

what does the error analysis of the dump say??

regards,

Priyank

Read only

Former Member
In response to Former Member

‎2010 Aug 12 8:14 AM

0 Likes
15,058

Hi Priyank,

I have the value (1 Entry) in LT_WHERE table as EQUNR = '12345'. , but the short dump appears with the following text "Runtime error, short dump could not be written."

Read only

Former Member
In response to Former Member

‎2010 Aug 12 8:17 AM

0 Likes
15,058

hi Sanket,

Can you please share the values of your selection screen?

regards,

Priyank

Read only

Former Member
In response to Former Member

‎2010 Aug 12 8:23 AM

0 Likes
15,058

two observations here...

1) your target table will have the structure of the table you specify on the selection screen...it is not likely to have the field where you will put the count(*). remove that field from the selection list...and check point (2) below...it should be fine

2) i changed your concatenate as follows....

concatenate p_selfl1 ' = ' '''' p_value '''' into ls_where .

Please try....and think about the first one.

Read only

Former Member
In response to Former Member

‎2010 Aug 12 8:24 AM

0 Likes
15,058

I have the Table name (say EQUI), Field name (Say EQUNR) & value (say 12345) on selection screen.

I have pass the value to the LT_where as EQUNR = '12345'. but it gives the dump for

The current ABAP program has tried to execute an Open SQL stateme

which contains a dynamic condition. The part of the condition spe

at runtime is incorrectly parenthesized.

Read only

Former Member
In response to Former Member

‎2010 Aug 12 8:29 AM

0 Likes
15,058

check my previous reply...it should work for you...

Read only

Former Member
In response to Former Member

‎2010 Aug 12 8:31 AM

0 Likes
15,058

Hi priyank,

I have already changed the code as you suggested. But the issue is I have concatenate the period after the value

(E.g. Equnr = '000000000010000000'.)

It throughs the dump . Anyways thanks for all of you for the valuable suggestions.

Read only

medas144
Explorer

‎2020 May 16 4:22 PM

0 Likes
15,058 
*CONCATENATE p_selfl1 '= '' p_value ''.'

Remove this dot '.'