Select Statement Doubt

[Former Member]

Moderator message: Please use meaningful subject in future

Hi,

I've a requirement that I need to get a record from EREV table for a Purchase Requisition no. based on Latest Version No. (REVNO. ) at a single shot.

I hav used the following code but i dint get desired values.

Select EDOKN

FGNAM

FGDAT

REVNO

from EREV into ls_erev

where edokn eq p_edokn and

revno eq (select Max(revno) from erev)).

Any body pl help me.

-Rajiv

Edited by: Vaddepati on Dec 3, 2008 11:01 AM

Edited by: Matt on Dec 3, 2008 10:03 AM

[Former Member]

Please send ur code

[Former Member]

Better do this

take all versions into one internal table and sort in descending.

the READ TABLE ITAB index 1.

so u will get higher version into work area .

now use that in ur select query.

Regards,

Ajay

[Former Member]

hi,

i think you want to get the highest of the version ...

if its so just sort it in order so that you can arrange it in order ..........

or if you want to get latest,,,

you can use read table...

[Former Member]

Hi,

Check the below code.

Select EDOKN
FGNAM
FGDAT
max( REVNO ) 
from EREV into ls_erev
where edokn eq p_edokn.

Regards

Kumar M

[Former Member]

Hi,

if you want single record you can use below statement but i think performance wise not good.

Select EDOKN

FGNAM

FGDAT

REVNO

from EREV into ls_erev upto 1 rows order by revno descending

where edokn eq p_edokn

endselect.

other wise select the all records into one internal table.

sort internal table by version in descending order.

delete all other versions using delete adjacent duplicates statement.

Regards,

Suresh.

[Former Member]

hi,

if you want to select record into a work area

:

select * from table_name into wa.
endselect.

if you want to select records into a internal table:

select * from table_name into table internal_table_name." key word ---table--- should not be omitted

so according to your code, ls_erev is a work area

you have to enclosing it with

endselect

Edited by: David,Liu on Dec 3, 2008 9:42 AM

[Former Member]

Hi veddapati,

Your sequence of using the fields in Select statement is wrong...thats why, you are not getting the required output..

You should select revno in the select statement before FGNAM as it is present in the table..

Please modify your select statement like this..... ::

Select EDOKN

REVNO

FGNAM

FGDAT

from EREV into ls_erev

where edokn eq p_edokn and

revno eq (select Max(revno) from erev)).

Hope it will solve your problem

Regards,,

Nikita

Edited by: Nikita Jain on Dec 3, 2008 2:29 PM