data selection

[Former Member]

Please use more informative subject in future

Hi Friends,

I have one more problem. This is my select statement.

SELECT A_{PERNR A}SYEAR A_{SMONTH A}APF A_{VPF A}EPF

B_{TYEAR B}TMONTH B_{EMPLOYEEPF B}EMPLOYERPF

FROM ( ZUPWARDHAWOR AS A INNER JOIN ZTRANS_ENTRY AS B

ON A_{PERNR = B}PERNR )

INTO CORRESPONDING FIELDS OF BODY1

WHERE A~SYEAR BETWEEN SYEAR AND SYEAR

AND A~SMONTH BETWEEN '04' AND SMONTH

AND A~PERNR = SMF1-PERNR AND

B~PERNR = SMF1-PERNR

ORDER BY A_{SYEAR A}SMONTH ASCENDING .

APPEND BODY1.

COUNT = COUNT + 1.

ENDSELECT.

TMONTH and TYEAR is a field. It contains data once a time in year or may not be.

According this statement TDATE & TMONTH is to be retrieve data but every time it is repeated.

I need if the data is availbale in table it should be extract. but it should not be repeated in every month in loop.

I hope, u guys r getting my point.

Regards,

Swapnika

Edited by: Matt on Mar 6, 2009 10:02 AM

[Former Member]

hello instead of using in between why dont u use ranges for select condition

bcos of that conditions the data might be repeating instead declare ranges and use , u wont get duplicate records

regards

[Former Member]

hi,

use DISTINCT with the field name..

hope this help you

Regards

Ritesh J

[Former Member]

Hi Ritesh,

I hope u r getting my point.

SELECT A_{PERNR A}SYEAR A_{SMONTH A}APF A_{VPF A}EPF

B_{TYEAR B}TMONTH B_{EMPLOYEEPF B}EMPLOYERPF

FROM ( ZUPWARDHAWOR AS A INNER JOIN ZTRANS_ENTRY AS B

ON A_{PERNR = B}PERNR )

INTO CORRESPONDING FIELDS OF BODY1

WHERE A~SYEAR BETWEEN SYEAR AND SYEAR

AND A~SMONTH BETWEEN '04' AND SMONTH

AND A~PERNR = SMF1-PERNR AND

B~PERNR = SMF1-PERNR

ORDER BY A_{SYEAR A}SMONTH ASCENDING .

APPEND BODY1.

COUNT = COUNT + 1.

ENDSELECT.

TMONTH and TYEAR is a field. Im extracting data B_{EMPLOYEEPF B}EMPLOYERPF It contains data once a time in year or may not be.According this statement TDATE & TMONTH is to be retrieve data but every time it is repeated.I need if the data is availbale in table it should be extract. but it should not be repeated in every month in loop.

I hope, u guys r getting my point.

Regards,

Swapnika

[Former Member]

Hi sw,

If repetion is a problem then wat you can do is sort the table body1(internal table) by the two fields you specifed and then use the statement DELETE ADJACENT DUPLICATE ENTRIES.

Hope this will be Ok for you........

Good day

Varghese

[Former Member]

Could you please expain in details. what is happening now and what do you need.

or use clear body1 after you append the data.

like this,

append body1.

clear body1.

Edited by: kat k on Mar 6, 2009 8:30 AM

[Former Member]

Hi Kak K.,

SELECT A_{PERNR A}SYEAR A_{SMONTH A}APF A_{VPF A}EPF

B_{TYEAR B}TMONTH B_{EMPLOYEEPF B}EMPLOYERPF

FROM ( ZUPWARDHAWOR AS A INNER JOIN ZTRANS_ENTRY AS B

ON A_{PERNR = B}PERNR )

INTO CORRESPONDING FIELDS OF BODY1

WHERE A~SYEAR BETWEEN SYEAR AND SYEAR

AND A~SMONTH BETWEEN '04' AND SMONTH

AND A~PERNR = SMF1-PERNR AND

B~PERNR = SMF1-PERNR

ORDER BY A_{SYEAR A}SMONTH ASCENDING .

APPEND BODY1.

COUNT = COUNT + 1.

ENDSELECT.

TMONTH and TYEAR is a field. Im extracting data B_{EMPLOYEEPF B}EMPLOYERPF It contains data once a time in year or may not be.According this statement TDATE & TMONTH is to be retrieve data but every time it is repeated.I need if the data is availbale in table it should be extract. but it should not be repeated in every month in loop.

Regards,

Swapnika

[Former Member]

Hi Swapnika ,

Ur query may be encounter a performance issue so u can modify it as:-

RANGES: r_syear FOR ZUPWARDHAWOR-year.

r_syear-sign = 'I'.

r_syear-option = 'BT'.

r_syearlow = ' '. "value

r_syear-high = ' '. "value

APPEND r_syear.

CLEAR r_syear.

similarly make another range for month.

Now make 2 internal tables as of type A and A B both.

SELECT PERNR SYEAR SMONTH APF VPF EPF

FROM ZUPWARDHAWOR into itab1

where SYEAR in r_syear

and SMONTH in r_symonth.

Select TYEAR TMONTH EMPLOYEEPF EMPLOYERPF

FROM ZTRANS_ENTRY into itab2

for all entries in itab 1

where pernr = itab1-pernr.

Now

loop at itab2

read table itab1 with key pernr = itab2-pernr.

if sy-subrc = 0.

Modify itab2 index sy-tabix transporting values f itab1.

endif.

endloop.

Hope this helps....

Thanks & regards,

Ruchi Tiwari

[matt]

Please use more informative subject in future