topic Re: Problem with SELECT UP TO 1 ROWS STATEMENT in Application Development and Automation Discussions

Problem with SELECT UP TO 1 ROWS STATEMENT

Former Member — Mon, 01 Feb 2010 07:42:54 GMT

Hi All,

I am facing a problem with the select query where in SELECT UP TO 1 ROWS is used.There are 2 records in the table abc which

are satisfting WHERE caluse conditions of query, but query is sometimes returning right record and some times wrong record.

What could be the reason behind this random behaviour of query?Please help.

SELECT vkorg

vtweg

spart

kschl

kunnr

matnr

datab

zgocoahnid

zzacseq

aufnr

INTO TABLE t_abc

FROM abc UP TO 1 ROWS

FOR ALL ENTRIES IN t_xyx

WHERE vkorg EQ t_wa-vkorg

AND vtweg EQ t_wa-vtweg

AND spart EQ zt_wa-spart

AND kschl EQ t_xyz-kschl.

Re: Problem with SELECT UP TO 1 ROWS STATEMENT

Former Member — Mon, 01 Feb 2010 07:45:08 GMT

In Select UP TO 1 ROW statement sort or order by the select query to get the correct result.

Thanks

Khushboo

Re: Problem with SELECT UP TO 1 ROWS STATEMENT

former_member404244 — Mon, 01 Feb 2010 07:47:15 GMT

Hi,

Select upto 1 record always gets one record ryt.. Moreover what is there in the table , it always get the first record only.

Regards,

Nagaraj

Re: Problem with SELECT UP TO 1 ROWS STATEMENT

Former Member — Mon, 01 Feb 2010 07:47:22 GMT

Hi,

Please go though the below thread....

Regards

Arbind

Re: Problem with SELECT UP TO 1 ROWS STATEMENT

SuhaSaha — Mon, 01 Feb 2010 08:19:38 GMT

Hello ,

Also if you check this [WIKI|https://wiki.sdn.sap.com/wiki/display/ABAP/ABAPPerformanceand+Tuning#ABAPPerformanceandTuning-WhatisthedifferencebetweenSELECTSINGLEandSELECT...UPTO1ROWS%3F]:

SELECT SINGLE and SELECT UP TO n ROWS return the first matching row/rows for the given condition. It may not be unique, if there are more matching rows for the given condition.

This is the reason why your SELECT UP TO stmt is not returning the same record.

BR,

Suhas

Edited by: Suhas Saha on Feb 1, 2010 1:50 PM

Re: Problem with SELECT UP TO 1 ROWS STATEMENT

Former Member — Mon, 01 Feb 2010 09:40:26 GMT

Select up to 1 row will fetch all records satisfying the condition and keep it in a buffer. From there, the first record is fetched. It will try to find the most suitable index.

Try using select single giving all the primary keys in the where condition.

Re: Problem with SELECT UP TO 1 ROWS STATEMENT

rainer_hbenthal — Mon, 01 Feb 2010 12:00:00 GMT

In a table in a relational database no first nor last record exists. There is no sequence guaranteed except you specify an order by clause.

Re: Problem with SELECT UP TO 1 ROWS STATEMENT

Former Member — Mon, 01 Feb 2010 12:09:04 GMT

Hi,

If the fields in the table are in primary key then write select single

or create Secondary index for the fields whic dont have primary key.

Regards

Azeez

Re: Problem with SELECT UP TO 1 ROWS STATEMENT

Former Member — Tue, 02 Feb 2010 06:10:13 GMT

HI,

it is better to u delete ADJACENT DUPLICATES FROM itab after select query..