topic Re: Previous Record Using Select statement. in Application Development and Automation Discussions

Previous Record Using Select statement.

adeel_sarwar — Thu, 26 Feb 2009 15:28:11 GMT

Hi,

Is it possible using Select statement to retrieve the previous Record. For Example:-

I have two Tables Header and Item Tables

In the Header table "AAAA" I have 1 Record

MATNR

MaterialA

In the Item table "BBBB I have 2 Records

MATNR PRICE

MaterialA 20

MaterialA 30

Now using the select statement I want 1 output. The output should be as below just on 1 line

MATNR Current Price Previous Price

MaterialA 30 20

Hope that makes sense. I am ok with getting the current price as this is easy enough using the select * statement and it will bring the last record.

The Question is how can i obtain the Previous Price using the above as an Example?

Thanks

Adeel

Re: Previous Record Using Select statement.

Former Member — Thu, 26 Feb 2009 15:32:06 GMT

Hi,

no that won't work with one select statement.

You have to loop over both entries of the second table and fill in internal table in the form you want.

I hope that helps?

Bye

Frank

Re: Previous Record Using Select statement.

Former Member — Thu, 26 Feb 2009 15:33:15 GMT

Since you already have both records in the internal table, I don't think there's any reason to go back to the database.

Rob

Re: Previous Record Using Select statement.

adeel_sarwar — Thu, 26 Feb 2009 15:49:09 GMT

Hi,

Can some one give me an example code on how i can do this using loop?

thanks

Re: Previous Record Using Select statement.

tarangini_katta — Thu, 26 Feb 2009 15:59:52 GMT

Hi,

loop at it_item into wa_item.

read table it_header into wa_hedaer with key matnr = wa_item-matnr.

Populate the final internal table which you want to display.

endloop.

Thanks

Re: Previous Record Using Select statement.

adeel_sarwar — Thu, 26 Feb 2009 16:03:43 GMT

Hi,

I am ok with loops however i don't know how i could use the loop to obtain the information in the above example the -1 record.

That is the code i require

Thanks

Re: Previous Record Using Select statement.

tarangini_katta — Thu, 26 Feb 2009 16:20:34 GMT

Hi,

This may work for you.

you can do this thing in following way..

WORK_AREA AND WORK_AREA1 should have line type of t_table

data: w_num type i.

loop at T_TABLE INTO WORK_AREA.

if sy-tabix > 1.

w_num = sy-tabix.

w_num = w_num - 1 .

now read the table T_TABLE INTO WORKAREA2 WITH INDEX W_NUM.

endloop.

this code is worked for me.

This code answered to my question .

Thanks

Re: Previous Record Using Select statement.

adeel_sarwar — Fri, 27 Feb 2009 10:34:59 GMT

Hi,

Thanks for your reply, still having issues, might be easy if i paste the code. I have not added the "New Loop Code".

What I am trying to do is put all the data required in itab from table a005 and table konp.

Can you please help me add the correct code below. I am trying to get the previous condition price which is konp-kbetr.

Thanks

Adeel

DATA: l_count TYPE I.

DATA: BEGIN OF itab OCCURS 0,

amsg(100) TYPE C,

kunnr LIKE a005-kunnr,

matnr LIKE a005-matnr,

kbetr LIKE konp-kbetr.

DATA: END OF itab.

CLEAR:conc,

chnge.

SELECT kunnr matnr FROM a005 INTO TABLE itab

WHERE vkorg = a005-vkorg

AND matnr = a005-matnr

AND kunnr = a005-kunnr.

CONCATENATE a005-kunnr a005-matnr INTO itab-amsg.

MOVE itab-amsg TO conc.

SELECT kbetr FROM konp INTO TABLE itab

WHERE kschl = a005-kschl

AND knumh = a005-knumh.

SORT itab BY amsg.

LOOP AT itab.

ADD 1 to l_count.

AT END OF amsg.

MOVE l_count TO chnge.

CLEAR l_count.

ENDAT.

ENDLOOP.

IF as = 'X'.

CHECK chnge NE '1'.

ENDIF.

Re: Previous Record Using Select statement.

adeel_sarwar — Fri, 27 Feb 2009 11:25:47 GMT

Small issue

Re: Previous Record Using Select statement.

adeel_sarwar — Fri, 27 Feb 2009 11:30:38 GMT

Hi,

Thanks for all your help. It is working now however i have a small issue. Ok the program is date restricted. So if i run it without a date restriction it brings for example the following values :-

Material Price PreviousPRICE Date

MaterialA £1 £0 01/01/2009

MaterialA £5 £1 02/02/2009

MaterialA £10 £5 03/03/2009

When i run the report for 03/03/2009 it brings the following

Material Price PreviousPRICE Date

MaterialA £10 £0 03/03/2009

I expected the Previous Price to be £5

Thanks in advance.

Adeel

Loop Code

data: w_num type i.

LOOP AT itab.

IF sy-tabix > 1.

w_num = sy-tabix.

w_num = w_num - 1.

MOVE konp-kbetr TO old_price.

ENDIF.

ENDLOOP.

pprice = old_price.

Re: Previous Record Using Select statement.

tarangini_katta — Fri, 27 Feb 2009 12:20:26 GMT

Hi Adeel,

IF u get the data based on the particular date u r sying it is bringing only one record to u r intrenal table.

if that is the case do one thing then i think sy-tabix will not be grater than 1.

so the value will not be moved.

if u want to get the old value in date condition laso.

LOOP AT itab.

IF sy-tabix > 1.

w_num = sy-tabix.

w_num = w_num - 1.

MOVE konp-kbetr TO old_price.

V_temp_price = old_price.

EXPORT old_price from konp-kbetrTO MEMORY ID 'MID'.

ENDIF.

impoRT old_price to V_TEMP_PRICE FROM MEMORY ID 'MID'.

ENDLOOP

pprice = V_TEMP_PRICE.

Hope this may help you.

Thanks

Re: Previous Record Using Select statement.

santosh_kumarm — Fri, 27 Feb 2009 13:14:40 GMT

Hi Adeel Sarwar ,

Create a internal table with 3 columns.

below are the data for 2 internal tables

Internal table 1

MATNR

MaterialA

internal tabl 2

MATNR PRICE

MaterialA 20

MaterialA 30

internal table 3

MATNR , a 20.

loop internal table 1

internal table- fields = internal table1-MATNR.

loop internal table 2 where field = internal table1-field.

internal table-field = A

internal table-field = 30.

endlop.

append internal table 3.

endloop

by this way you can have the record in a single line in internal table 3.

Hope it shall be useful

Regard

Santosh

Re: Previous Record Using Select statement.

adeel_sarwar — Fri, 27 Feb 2009 13:24:15 GMT

Hi Tarangini

Tried that same result. I copied your code word to word.

Any other ideas?

thanks

Re: Previous Record Using Select statement.

tarangini_katta — Fri, 27 Feb 2009 14:11:26 GMT

Hi adeel,

I think memory id is refrehing after every exceution.

Try to write Export statement above if condition.

First run with out date condition and see the MID in debug.

Then run with date condition and MID in debug if it refreshes it wont for you.

IF it is same value then it will work for you.

check this once.

IF you dont get it please let me know.

Thanks

Re: Previous Record Using Select statement.

adeel_sarwar — Mon, 02 Mar 2009 09:07:55 GMT

Hi,

Tried it still not working. I am right in saying i don't have to define 'MID'.

Code I am using.

DATA: w_num TYPE I,

old_price LIKE konp-kbetr,

v_temp_price LIKE konp-kbetr.

LOOP AT itab.

EXPORT old_price FROM konp-kbetr TO MEMORY ID 'MID'.

IF sy-tabix > 1.

w_num = sy-tabix.

w_num = w_num - 1.

MOVE konp-kbetr TO old_price.

v_temp_price = old_price.

ENDIF.

IMPORT old_price TO v_temp_price FROM MEMORY ID 'MID'.

ENDLOOP.

pprice = v_temp_price.

Re: Previous Record Using Select statement.

tarangini_katta — Mon, 02 Mar 2009 09:37:35 GMT

Hi Adeel,

With out defining MID.The export parameter does not hold the value.

I think u need to declare the MID.

Thanks

Re: Previous Record Using Select statement.

dev_parbutteea — Mon, 02 Mar 2009 09:40:29 GMT

Hi,

please use the following logic:

DATA: w_num TYPE I,

old_price LIKE konp-kbetr,

v_temp_price LIKE konp-kbetr.

field-SYMBOLS: <fs_tab> TYPE type_of_ur_table.

data: wa_previous type type_of_ur_table.

----> sort itab by matnr date.

LOOP AT itab assigning <fs_tab>.

IF sy-tabix > 1.

w_num = sy-tabix.

w_num = w_num - 1.

read table itab into wa_previous " read previous record

index w_num.

if sy-subrc = 0.

if wa_previous-matnr = <fs_tab>-matnr. " if previous record matnr are equal

<fs_tab>-old_price = wa_previous -new_price.

endif.

ENDLOOP.

Edited by: Dev Parbutteea on Mar 2, 2009 10:48 AM

Re: Previous Record Using Select statement.

Former Member — Mon, 02 Mar 2009 09:44:07 GMT

Hi,

If you have already records in the internal table then why you want fetch data from DB. You can directly print that as per your requirement. Still you have any quiries please let me know.

Regards

Md.MahaboobKhan

Re: Previous Record Using Select statement.

adeel_sarwar — Mon, 02 Mar 2009 10:06:22 GMT

Hi,

Thankyou everyone for all your help. Still not working. I am pasting the full code you might spot something. Like mentioned before it works perfect if i don't restrict the dates. it only fails on restriction of dates. If some one can contact me via contact details, i could possibly do a webex where you could have a look.

Thanks

Adeel

TABLES rv12l.

SELECTION-SCREEN BEGIN OF BLOCK Date

WITH FRAME TITLE text-100.

SELECT-OPTIONS Date FOR RV12L-DATINT

DEFAULT SY-DATLO TO '99991231'

NO-EXTENSION

MODIF ID IA.

DATA DATLOW LIKE A005-DATAB.

DATA DATHIGH LIKE A005-DATBI.

DATLOW = DATE-LOW.

DATHIGH = DATE-HIGH.

IF DATLOW IS INITIAL.

DATLOW = '00010101'.

IF DATHIGH IS INITIAL.

DATHIGH = '99991231'.

ENDIF.

SELECTION-SCREEN: END OF BLOCK Date.

CHECK ( ( A005-DATAB >= DATLOW AND A005-DATBI <= DATHIGH ) OR

( A005-DATAB >= DATLOW AND A005-DATBI >= DATLOW ) ).

DATA: l_count TYPE I,

w_num TYPE I,

zkbetr LIKE konp-kbetr,

old_price LIKE konp-kbetr,

mid(60) type c,

v_temp_price LIKE konp-kbetr.

DATA: BEGIN OF itab OCCURS 0,

amsg(100) TYPE C,

kunnr LIKE a005-kunnr,

matnr LIKE a005-matnr,

knumh LIKE a005-knumh,

kbetr LIKE konp-kbetr.

DATA: END OF itab.

CLEAR:conc,

chnge,

pprice,

v_temp_price,

old_price.

SELECT kunnr matnr knumh FROM a005 INTO TABLE itab

WHERE vkorg = a005-vkorg

AND matnr = a005-matnr

AND kunnr = a005-kunnr.

CONCATENATE a005-kunnr a005-matnr INTO itab-amsg.

MOVE itab-amsg TO conc.

SELECT kbetr FROM konp INTO zkbetr

WHERE knumh = a005-knumh

AND kopos = '1'.

ENDSELECT.

MOVE zkbetr TO itab-kbetr.

SORT itab BY amsg.

LOOP AT itab.

ADD 1 to l_count.

AT END OF amsg.

MOVE l_count TO chnge.

CLEAR l_count.

ENDAT.

ENDLOOP.

LOOP AT itab.

EXPORT old_price FROM konp-kbetr TO MEMORY ID MID.

IF sy-tabix > 1.

w_num = sy-tabix.

w_num = w_num - 1.

MOVE konp-kbetr TO old_price.

v_temp_price = old_price.

ENDIF.

IMPORT old_price TO v_temp_price FROM MEMORY ID MID.

ENDLOOP.

pprice = v_temp_price.

IF as = 'X'.

CHECK chnge NE '1'.

ENDIF.

Re: Previous Record Using Select statement.

dev_parbutteea — Mon, 02 Mar 2009 10:37:05 GMT

> SELECT kunnr matnr knumh FROM a005 INTO TABLE itab

> WHERE vkorg = a005-vkorg

> AND matnr = a005-matnr

> AND kunnr = a005-kunnr.

I dont understand where you are getting the A005 values

> CONCATENATE a005-kunnr a005-matnr INTO itab-amsg.

> MOVE itab-amsg TO conc.

what r u doing with the conc value?

> LOOP AT itab.

> EXPORT old_price FROM konp-kbetr TO MEMORY ID MID.

> IF sy-tabix > 1.

> w_num = sy-tabix.

> w_num = w_num - 1.

> MOVE konp-kbetr TO old_price.

> v_temp_price = old_price.

> ENDIF.

> IMPORT old_price TO v_temp_price FROM MEMORY ID MID.

> ENDLOOP.

where are you populating the konp-kbetr value ?

Regards.