topic Re: dynamic select in Application Development and Automation Discussions

dynamic select

Former Member — Tue, 13 Jun 2006 22:00:10 GMT

Hi,

i need to select entires from a table based on year which i determine dynamically.

for eg w_year = '2006'.

I'm looking for something like this

select * from dbtable into .....where date like w_year%.

can someone help me out .

Thanks,

venu.

Re: dynamic select

Former Member — Tue, 13 Jun 2006 22:03:36 GMT

Hi Venu,

I really dont see need of dynamic select.

Once you have the value of year, you can directly put it in the 'where' conddition of select statement.

Cheers,

Vikram

Pls reward for helpful replies!!

Re: dynamic select

Former Member — Tue, 13 Jun 2006 22:05:08 GMT

hi Venu,

Why do you want to select the year dynamically??? once the year gets populated to the field(w_year) then use the field(w_year) in the select statement.

i.e,

w_year = 2006.

select * from dbtable into .....where <b>date = w_year</b>.

Regards,

Santosh

Re: dynamic select

Former Member — Tue, 13 Jun 2006 22:05:43 GMT

You build dynamic select if you derive the table or where conditions at run time. I dont see any need for the synamic select here.

Re: dynamic select

Former Member — Tue, 13 Jun 2006 22:10:18 GMT

Hi,

appreciate your response. the year field in the table contains yyyymmdd but i have yyyy (in this ex 2006 = w_year) thats the reason i want to use ..where year like W_year% this will be called multiple times based on w_year.

hope i'm clear. your help will be rewarded

Thanks,

venu.

Re: dynamic select

Former Member — Tue, 13 Jun 2006 22:13:36 GMT

in that case do this way

w_year = field(yyyymmddformat) + 0(4).

now use this variable in the select.

i.e,

select * from dbtable into .....where date = w_year.

Regards,

Santosh

Re: dynamic select

Former Member — Tue, 13 Jun 2006 22:15:42 GMT

Hi Venu,

Declare internal table itab to hold data from dbtable.

You can try like this :

*Code starts

select * into itab from dbtable.

loop at itab.

if itab-year CP w_year.

continue.

else.

delete itab.

endif.

endloop.

*Code ends

finally itab has all the relevant records from dbtable.

Cheers,

Vikram

Pls reward for helpful replies!!

Re: dynamic select

Former Member — Tue, 13 Jun 2006 22:18:05 GMT

This is what you need.


DATA: BEGIN OF where_clause OCCURS 0,
        clause(72).
DATA: END OF where_clause.

CONCATENATE w_year
            '%'
       INTO where_clause-clause.
CONDENSE where_clause-clause NO-GAPS.

CONCATENATE 'DATE LIKE'
            where_clause-clause
       INTO where_clause-clause SEPARATED BY SPACE.

SELECT * FROM dbtable WHERE (where_clause).

Message was edited by: Srinivas Adavi

Re: dynamic select

Puneet_Gupta — Tue, 13 Jun 2006 23:45:50 GMT

Hi Venu,

Declare 2 temporary date variables.

l_date1 and l_date2.

data: l_Date1 like sy-datum,

l_date2 like sy-datum.

l_date1(4) = w_year.

l_date1+4(4) = '0101'.

l_date2(4) = w_year.

l_date2+4(4) = '1231'.

select * from dbtable into .....where date GE l_date1 and

date le l_date2.

This should solve your problem.

Let me know if you need more inputs.

thanks

Puneet