Find nearest value

[Former Member]

Dear Gurus.

I have requirement to find the the nearest value.

SELECT SINGLE zzreels zzcases INTO (itab-zzreels , itab-zzcases)
      FROM lips
      WHERE vbelv = itab-vbeln
      AND   posnv = itab-posnr
      AND   werks eq werks.

When i execute the above statement it fetch the wrong details.

but when i execute this code

SELECT SINGLE zzreels zzcases INTO (itab-zzreels , itab-zzcases)
      FROM lips
      WHERE vbelv = itab-vbeln
      AND   posnv = itab-posnr
      AND   lfimg GE itab-kalab
      AND   werks eq werks.

in some records it fetch the correct result but fails to when the quantity is little less.

i need to re-write the statement where lfimg is nearest to itab-kalab .

please help me

regards

Saad Nisar

[Former Member]

hi,

the better option is, fetch the values in an internal table. Then sort them and pick up the closest value.

[Clemenss]

Hi Saad,

a SELECT SINGLE without fully specified primary key will always return any matching record, only by random the record you expect.

As the number of delivery items created from the same (order) item is limited, the select should be INTO CORRESPONDING FIELD OF TABLE, then use your own algorithm that will implement your personal definition of 'nearest' value.

Regards

Clemens

[Former Member]

Hi,

Try using UP TO 1 ROWS to get the nearest entry if getting the first record from table is your question.

Thanks,

Venkatesh.

Edited by: venkatesh333 on Aug 1, 2011 8:57 PM

[Clemenss]

Hi Venkatesh,

could you explain the difference between SELECT SINGLE and SELECT .. UP TO 1 ROWS except that ENDSELECT is required?

Regards

Clemens

[Former Member]

Hi Clemens,

As the question was to get the nearest value, i thought using "up to one rows" and an addition order by to lfimg ( descending or Ascending) may help.

Let me know if i have gone wrong.

Thanks,

Venkatesh.

[Former Member]

Hi,

Venkatesh is right, this is the best solution.

With select single you get the first entry that was found.

When you use up to 1 row combined with order by, the database seraches for all matching entries, orders them and only delivers the first (the one you need) entry.

Best regards,

Oliver

[Clemenss]

Hi Oliver and venkatesh,

Venkatesh's original proposal did not mention the order by.

Try using UP TO 1 ROWS to get the nearest entry if getting the first record from table is your question.

is without order by. It is a well known fact that select into table followed by an ABAP SORT is a lot faster than ORDER BY (which triggers SORT) on DB level.

That's what I suggested but I did not SORT because nearest value is still undefined.

Regards,

Clemens