Select Query with Date comparison

[Former Member]

Hi all,

I need to select data from Infotypes based on AEDTM.

I have an internal table which has all the records from IT0000.

I am looping into this internal table and then select data fom It0001 and IT0002 depending on the AEDTM of it0000.

loop at it_0000 into wa_0000

select * from Pa0001 into it_0001

where pernr = wa_0000-pernr

and aedtm LE wa_0000-aedtm.

endloop.

All records from IT0001 are not getting fetched.

Only records for condition aedtm = wa_0000-aedtm are getting fetched not for 'less than'.

Please let me know how this can be achieved or any pointers in this case.

Thanks,

Saher

[kesavadas_thekkillath]

jus check this.

data:startdate type sy-datum value '19000101'.

select * from table where budat between startdate and wa_0000-aedtm.

also check should there be any conversion exit to be applied

Edited by: Keshu Thekkillam on Aug 31, 2009 12:14 PM

[Former Member]

selects inside loop is not a good practice.

so please avoid that.

and your below code will give only the last matching record as its in loop.

so use following

select * from Pa0001 into table it_0001
               for all entries in IT_0000
               where pernr = IT_0000-pernr
                 and aedtm LE IT_0000-aedtm.

[Former Member]

Hi,

Its a very bad programming practice to use select statement within a loop as this would affect the performance badly. Instead use FOR ALL ENTRIES

Try using like this.

select * 
from Pa0001 
into corresponding fields of it_0001
for all entries in it_0000
where pernr = it_0000-pernr
and aedtm <= it_0000-aedtm.

Regards,

Vikranth

[Former Member]

hi,

Thank you for replying.

My requirement is actually as follows:

For every action in IT0000 i.e for every record in IT0000,

I need to fecth the record from IT0001 and It0002 which have aedtm same as It0000-aedtm or aedtm < IT0000-aedtm.

All selection for every action will be appended in final internal table and a seperate file would be generated for each Action in IT0000, with corresponding records fron IT0001 and IT0002.

I had created the loop for this.

but your suggestion gives all the records at one shot. This is different from my requirement.

Any pointers in this regard would be helful.

Thanks,

Saher

[Former Member]

Hi,

Am still not clear with your logic for populating IT0001and It0002 and the what the final internal table would hold. Can you explain a bit more?

Regards,

Vikranth

[Former Member]

saher,

I still stick with my code.

after getting all records with using FOR ALL ENTRIES.

after fetching these entries also you can make the files.

[Former Member]

Hi,

My actual problem is that, the date comparison in the select statement is not working

'aedtm <= it_0000-aedtm'. Its fetching values only for '=' not for '<'.

[Former Member]

Hi,

Try using 'GE' rather than '<='.

May be this will work.

Thanks and Regards

Krunal

[Former Member]

Hi,

'LE' or '<=' none of them work.

[Former Member]

Hi Saher,

I checked in my system and its working fine. Check the date format for it_0000-aedtm. It must be in the form 'YYYYMMDD'. Also check in PA0001 in the database level if there are records satisfying 'aedtm < it_0000-aedtm'. I dont think there is anything wrong with 'aedtm <= it_0000-aedtm' synatically and logically.

Regards,

Vikranth

[Former Member]

Hi,

Is it because, the row fron It0001 is already fetched into the internal table for aedtm = it0000-aedtm.

So in the next iteration of the loop, the row from It0001 satisfying the condition aedtm < it0000-aedtm, is the same as the one that was fetched in the last iteration, so may be it is not getting fetched?

[Former Member]

Hi,

I dont think there will be two iterations one for '=' and one for '<' . The result of the select query you wrote depends on the values in it0000. Just post the values of it0000 and it0001which you get while debugging and the select query on it0000. It will be clear then

Regards,

Vikranth

[Former Member]

Hi,

It_0000 has 3 records with AEDTM as '20090611', '20090706', '20090806'.

IT0001 has 2 records with aedtm as '20090611' and '20090806'.

Loop at it_0000.

select * from Pa0001 into it_0001

where pernr = it_0000-pernr

and aedtm <= it_0000-aedtm.

endloop.

In the 1st iteration of the loop it_0000-aedtm = '20090611', the 1st record of PA0001 with aedtm = '20090611' will be fecthed.

in the 2nd iteration of the loop it_0000-aedtm = '20090706', the 1st record of PA0001 should be selected again and appended to it_0001 as

PA0001-aedtm < it_0000-aedtm. But this not happening.

[Former Member]

Hi,

As per your logic,

Loop at it_0000.
select * from Pa0001 into it_0001
where pernr = it_0000-pernr
and aedtm <= it_0000-aedtm.
endloop.

Here the internal table it_0001 will not be appened automatically each time you run through the loop.

At the first loop for it_0000-aedtm = '20090611', the corresponding fields will be fetched.

Wnen the second loop starts for it_0000-aedtm = '20090706', the internal table fields will be replaced and it wont be appended.

Therefore after the whole loop only fields corresponding to it_0000-aedtm <= '20090806' will be present in it_0001.

Also this logic is not advisable.

As suggested use this

select * from Pa0001 into it_0001
for all entries in it_0000
where pernr = it_0000-pernr
and aedtm <= it_0000-aedtm.

which will give the correct values.

Regards,

Vikranth

[Former Member]

Hi,

use as given below

select * from Pa0001 into table it_0001

where pernr = wa_0000-pernr

and aedtm LE wa_0000-aedtm.

i hopw this will work.. and also put <= instead of LE

thanks

Ashu

[kesavadas_thekkillath]

 and also put <= instead of LE

What difference does this make ?

[kesavadas_thekkillath]

search in scn for some function modules which gets data of pa0001 and try with those