topic Re: Select Statement Doubt in Application Development and Automation Discussions

Select Statement Doubt

Former Member — Wed, 03 Dec 2008 05:30:59 GMT

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

Re: Select Statement Doubt

Former Member — Wed, 03 Dec 2008 05:36:37 GMT

Please send ur code

Re: Select Statement Doubt

Former Member — Wed, 03 Dec 2008 05:38:43 GMT

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

Re: Select Statement Doubt

Former Member — Wed, 03 Dec 2008 05:41:57 GMT

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...

Re: Select Statement Doubt

Former Member — Wed, 03 Dec 2008 05:59:05 GMT

Hi,

Check the below code.


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

Regards

Kumar M

Re: Select Statement Doubt

Former Member — Wed, 03 Dec 2008 06:01:33 GMT

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.

Re: Select Statement Doubt

Former Member — Wed, 03 Dec 2008 08:41:45 GMT

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

Re: Select Statement Doubt

Former Member — Wed, 03 Dec 2008 08:50:49 GMT

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