topic Re: data selection in Application Development and Automation Discussions

data selection

Former Member — Fri, 06 Mar 2009 07:07:15 GMT

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

Re: data selection

Former Member — Fri, 06 Mar 2009 07:17:05 GMT

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

Re: data selection

Former Member — Fri, 06 Mar 2009 07:25:27 GMT

hi,

use DISTINCT with the field name..

hope this help you

Regards

Ritesh J

Re: data selection

Former Member — Fri, 06 Mar 2009 07:27:46 GMT

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

Re: data selection

Former Member — Fri, 06 Mar 2009 07:30:08 GMT

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

Re: data selection

Former Member — Fri, 06 Mar 2009 07:32:56 GMT

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

Re: data selection

Former Member — Fri, 06 Mar 2009 07:38:01 GMT

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.

Regards,

Swapnika

Re: data selection

Former Member — Fri, 06 Mar 2009 07:39:51 GMT

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

Re: data selection

matt — Fri, 06 Mar 2009 09:02:45 GMT

Please use more informative subject in future