topic Re: dynamic query in Application Development and Automation Discussions

dynamic query

Former Member — Fri, 27 Nov 2009 06:10:13 GMT

Hi,

i have five fields on selection screen. i may give input to any of the combinations.

Need to retrieve the data from standard table depending on the input given.

i have tried using where by calling it dynamically but it is gng to dump showing error in the and condition of where clause.

pls help me.

thanks in advance

Re: dynamic query

Former Member — Fri, 27 Nov 2009 06:11:39 GMT

Post the code with what you have tried. That would be helpful to suggest better

Re: dynamic query

former_member194669 — Fri, 27 Nov 2009 06:14:35 GMT

If you have 5 selection inputs

say

s_input1

s_input2

s_input3

s_input4

s_input5

are Input fields in selection screen

then

select * from mara where

matnr in S_INPUT1

matkx in S_INPUT2

matkx1 in S_INPUT3

....

...

I think this solve your issue

Re: dynamic query

Former Member — Fri, 27 Nov 2009 06:16:45 GMT

this is the code :

i have declared where as itab.

if not i_field1 is initial.

concatenate 'field1 = ' ' ' ' i_field1' ' ' ' into where.

endif.

if not i_field2 is initial.

concatenate 'and field1 = ' ' ' ' i_field1' ' ' ' into where.

endif.

like this for five fields.

select * from mara where (where).

Re: dynamic query

Former Member — Fri, 27 Nov 2009 06:20:01 GMT

What exactly u r trying to do? I dont really understand ur code. Never seen one like this before.

U cannot use Where as an internal table variable as it is an SAP reserved keyword.

Edited by: Navita Gujral on Nov 27, 2009 7:20 AM

Re: dynamic query

Former Member — Fri, 27 Nov 2009 06:20:25 GMT

i dont know to which fields the data will be passed.

suppose i pass data to fields 1 & 2 then i need to retrieve depending on only 1 & 2.

if we use the logic said by you it will take spaces for remaining fields which i dont want

Re: dynamic query

kesavadas_thekkillath — Fri, 27 Nov 2009 06:23:19 GMT

If the question is not posted clearly , you wont get the solution exactly .

Re: dynamic query

awin_prabhu — Fri, 27 Nov 2009 06:23:44 GMT

Hi,

U might have forget to give space between fields while populating where clause dynamically.

Check for spaces and braces in the value of where condition in debugging.

Ex:


Data: l_v_where TYPE string.
      CONCATENATE 'KLAH~KLART = L_V_CLASSTY' 'AND'
      'KLAH~CLASS = L_V_CLASSNO' INTO l_v_where SEPARATED BY space.  " Space between fields

      SELECT cabn~atnam FROM cabn
        INNER JOIN ksml ON ksml~imerk = cabn~atinn
        INNER JOIN klah ON klah~clint = ksml~clint
        INTO TABLE l_t_tab
         WHERE (l_v_where).      " Check for spaces between fields here in debugging

Thanks

Re: dynamic query

former_member194669 — Fri, 27 Nov 2009 06:29:10 GMT

Then you may need to Check fm FVD_SELECT_OPTIONS_2_WHERE or FREE_SELECTIONS_RANGE_2_WHERE

then will generate dynmic where clause statements

a®

Re: dynamic query

Former Member — Fri, 27 Nov 2009 06:34:55 GMT

hi,

this is the way i need.

But in my requirement i have to check for each & every field whether it is initial or not.

if not initial

concatenate 'and field1=' into where1.

append where1 to where.

endif.

in my query it is showing error at And in the where clause.

pls help

Re: dynamic query

Former Member — Fri, 27 Nov 2009 06:50:46 GMT

Hi,

You can first check if all the select options are initial or not,

then in your select query you can check the select options

with the IN option in your select query.

Hope it helps

Regards

Mansi

Re: dynamic query

Former Member — Fri, 27 Nov 2009 06:53:25 GMT

Hi,

Just a suggestion,

Try this


if not initial 
concatenate ' and field1=' into where1.  "Give a space before AND
append where1 to where.
endif.

Re: dynamic query

Former Member — Fri, 27 Nov 2009 06:59:32 GMT

Phew !!

Here you go. I did it with LIKP and the fields vbeln, ernam and erdat. Change it according to your requirement

here text-001 is the text symbol for quotes ( ' )


tables : likp.
parameters: vbeln type likp-vbeln,
            ernam type likp-ernam,
            erdat type likp-erdat.

data: var1 type string,
      var2 type string,
      var3 type string.

data: var4 type string,
      var5 type string,
      var6 type string.

data: where(100) type c.
data: where1 like table of where.

if not vbeln is initial.
concatenate text-001 vbeln text-001 into var1.
var4 = 'VBELN'.
endif.

if not ernam is initial.
concatenate text-001 ernam text-001 into var2.
var5 = 'ERNAM'.
endif.


if not erdat is initial.
concatenate text-001 erdat text-001 into var3.
var6 = 'ERDAT'.
endif.


if not vbeln is initial.
concatenate 'VBELN =' var1 into where separated by space.
endif.

if not ernam is initial.
if vbeln is initial.
concatenate where 'ERNAM =' var2 into where separated by space.
else.
concatenate where 'AND ERNAM =' var2 into where separated by space.
endif.
endif.

if not erdat is initial.
if vbeln is initial and ernam is initial.
concatenate where 'ERDAT =' var3 into where separated by space.
else.
concatenate where 'AND ERDAT =' var3 into where separated by space.
endif.
endif.

append where to where1.

select single *
from likp
where (where1).

write: likp-vbeln, likp-ernam, likp-erdat.

Vikranth

Re: dynamic query

Former Member — Fri, 27 Nov 2009 07:10:28 GMT

may i know wht have u used for text-001.

it seems it will for my requirement.

thnx in advance

Re: dynamic query

Former Member — Fri, 27 Nov 2009 07:12:14 GMT

I have just used ' (apostrophe ) for text symbol 001. You can replace text-001 with ```` to get the quotes

Re: dynamic query

Former Member — Fri, 27 Nov 2009 11:05:57 GMT

hi vikrant,

solved the issue.

thank u soo much.

if u dont mind may i have ur personal id.

Re: dynamic query

Former Member — Fri, 04 Dec 2009 06:47:36 GMT

hi,

now while activating it is giving error as table as already been dec when i remove tables st, it is giving syntax error as target area must be dec explicitly with an into clause or by tables st.

so when i add tables st it is giving error while activating.

pls help me

Re: dynamic query

Former Member — Fri, 04 Dec 2009 07:16:47 GMT

Hello,

Post the code with what you have tried.

Vikranth

Re: dynamic query

Former Member — Fri, 04 Dec 2009 07:20:33 GMT

got it

thank u

Re: dynamic query

Former Member — Mon, 07 Dec 2009 10:49:24 GMT

hi,

when i did the same in ides its working fine while in our system it is showing error at activation time as table is already dec if i comment tables st it will give error as target area needs to be dec, if i activate it by selecting anyways and run the prog it is going to dump and saying error at where cond and saying invalid value 'class' is used in internal table.

pls help me