topic Re: modification in the query in Application Development and Automation Discussions

modification in the query

Former Member — Wed, 06 Jul 2005 14:16:53 GMT

hi Rich,

I need a small modification in first half of the query. In addition to getting the data based on the custoemr id also need to get the data between a date range and i.e -from start date - first day of the month and enddate last day of the month.

both these are not the fields in the table.

as of now I am getting a lot of records for this customer number but want the records between a certain date range

I hope you got it... How to insert that part in the query.

select * into corresponding fields of table ijha

from jhak

inner join jhaga

on jhak_{avm_nr = jhaga}avm_nr

where jhaga~inserent = p_custid.

thanks

santhosh

Re: modification in the query

RichHeilman — Wed, 06 Jul 2005 14:25:57 GMT

How do you want to filter out these records, does the jhak or Jhaga have any date fields that you can work with. Again, these tables do not exists in my system.

If there is a date field in one of the those tables that you can work with, then its real easy.

Create a range.



ranges: r_datum for sy-datum.

r_datum-sign   = 'I'.
r_datum-option = 'BT'.
r_datum-low    = '20050701'.
r_datum-high   = '20050731'.
append r_datum.

Now in your where clause....



where jhaga~inserent = p_custid
  and SOME_DATE_FIELD in r_datum.

Regards,

Rich Heilman

Re: modification in the query

Former Member — Wed, 06 Jul 2005 14:26:15 GMT

WHERE ARE THE DATE FIELDS?? FROM AND TO DATE?? TABLE A OR TABLE B??

Re: modification in the query

eddy_declercq — Wed, 06 Jul 2005 14:28:53 GMT

Hi,

If I understand it well, you need something like this:

data: startdate type dats, enddate type dats.

initialize you data here.

select * into corresponding fields of table ijha

from jhak

inner join jhaga

on jhak_{avm_nr = jhaga}avm_nr

where jhaga~inserent = p_custid

and jhak~date between startdate and enddate

and jhaga~date between startdate and enddate.

Eddy

Re: modification in the query

Former Member — Wed, 06 Jul 2005 14:44:28 GMT

there is a date field (planned billing date) -fakdatum in jhaga table which can be any date in the middle of the month like '07/05/02005' from this can I generate the startdate and enddate for that month just not for july month.

what I am try to achieve here is run the records on a monthly basis from startof the month to the end of the month so that I can grab the records of that month

thanks

santhosh

Re: modification in the query

RichHeilman — Wed, 06 Jul 2005 14:51:50 GMT

You need to know that range before the select statement.



data: sdate type sy-datum.
data: edate type sy-datum.

ranges: r_datum for sy-datum.

* First day of month will always be 01. 
sdate+0(6) = sy-datum+0(6).
sdate+6(2) = '01'. 

* Get the end of the month date
call function 'RP_LAST_DAY_OF_MONTHS'
  exporting
    day_in                  = sdate
 IMPORTING
   LAST_DAY_OF_MONTH        = edate.

* Set up your range with the start and end dates
r_datum-sign   = 'I'.
r_datum-option = 'BT'.
r_datum-low    = sdate.
r_datum-high   = edate.
append r_datum.
 
* Now do your select with this WHERE clause.

where jhaga~inserent = p_custid
  and jhaga~fakdatum in r_datum.

Regards,

Rich Heilman

Re: modification in the query

Former Member — Wed, 06 Jul 2005 15:26:13 GMT

Thanks Rich, It really helped a lot. I will need your help further too. because i am working an a complex query to get the data from the condition types I will get back.

I have awarded 6 points because my question is not fully answered i am working on joining th puzzle is small pieces.

santhosh

Re: modification in the query

Former Member — Wed, 06 Jul 2005 16:27:42 GMT

continuing with it...

Hi Rich,

The code goes like this...

select * into corresponding fields of table ijha

from jhak

inner join jhaga

on jhak_{avm_nr = jhaga}avm_nr

where jhaga~regulierer = p_custid and

jhaga~fakdatum in r_datum.

I having four records in ijha table

select kposn kschl krech kawrt kbetr kwert

from konv

into table cdata

for all entries in ijha

where konv~knumv = ijha-knumv.

It is fetching the records of various condition types from konv for each record from ijha table.

loop at cdata into wac.

case wac-kschl.

when 'ZRAT'.