dynamic query

[Former Member]

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

[Former Member]

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

[Former Member]

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).

[Former Member]

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

[former_member194669]

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

[Former Member]

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

[kesavadas_thekkillath]

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

[former_member194669]

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®

[awin_prabhu]

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

[Former Member]

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

[Former Member]

Hi,

Just a suggestion,

Try this

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

[Former Member]

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

[Former Member]

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

it seems it will for my requirement.

thnx in advance

[Former Member]

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

[Former Member]

hi vikrant,

solved the issue.

thank u soo much.

if u dont mind may i have ur personal id.

[Former Member]

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

[Former Member]

Hello,

Post the code with what you have tried.

Vikranth

[Former Member]

got it

thank u

[Former Member]

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

[Former Member]

hi,

can't we use dynamic cond for delete st.

i wnt to delete from itab on cond of a field value.

when i specify the selection screen field directly it is taking space for the field and deleting rec from itab, where it shd not delete bcz i havent specified the value for the selection screen field.

pls help

[Former Member]

Hello,

As per forum rules, you are allowed to ask only one question per thread.

Vikranth

[Former Member]

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

[Former Member]

follow up

[Former Member]

thanks