topic Re: select single problem... in Application Development and Automation Discussions

select single problem...

Former Member — Wed, 28 Jan 2009 10:18:19 GMT

hi all,

i have to take first value of table BSIS according to condition. i m using select single statement in table BSIS, but problem is that i m getting the value only in header by which i m not able to apply loop at itab. can anyone please help me?

code.

SELECT single bukrs hkont gjahr monat shkzg prctr FROM bsis INTO itab

WHERE hkont IN hkont

AND bukrs IN bukrs

AND prctr IN prctr

AND gjahr IN gjahr

AND monat IN monat.

here sy-subrc is 0. but value is only in header.

regards saurabh.

Re: select single problem...

JozsefSzikszai — Wed, 28 Jan 2009 10:19:52 GMT

you have to APPEND the internal table:

APPEND itab.

Re: select single problem...

former_member404244 — Wed, 28 Jan 2009 10:21:24 GMT

Hi ,

declare a work area for this instead of table and try appending to internal table.

SELECT single bukrs hkont gjahr monat shkzg prctr FROM bsis INTO wa_itab

WHERE hkont IN hkont

AND bukrs IN bukrs

AND prctr IN prctr

AND gjahr IN gjahr

AND monat IN monat.

if sy-subrc eq 0.

append wa_itab to itab.

endif.

P.S->the wrokarea should have all the fields that u mentioned in ur quey in sequence.

Regards,

Nagaraj

Re: select single problem...

Former Member — Wed, 28 Jan 2009 10:24:47 GMT

dear Eric Cartman ,

Append is working only when i have single value in select-options. but when i m passing more than single value then its only appending first one. suppose i given 5 values of HKONT in select-options then i need 5 rows in itab.

please suggest me accordingle.

regards saurabh.

Re: select single problem...

Former Member — Wed, 28 Jan 2009 10:25:24 GMT

hi,

either declare itab with occurs 0 or create separate work area and append as shown above.

Re: select single problem...

JozsefSzikszai — Wed, 28 Jan 2009 10:26:55 GMT

yes, because you coded: SELECT SINGLE => this means only one record will be selected, even if the conditions would allow more records.

change the SELECT to:

SELECT bukrs hkont gjahr monat shkzg prctr FROM bsis INTO TABLE itab...

Now you don't need APPEND, all lines will be selected directly to the internal table

Re: select single problem...

GauthamV — Wed, 28 Jan 2009 10:27:39 GMT

How can you expect 5 records with select single ?

Re: select single problem...

Former Member — Wed, 28 Jan 2009 10:31:17 GMT

hi all thanx for reply,

actually if i paas 5 diffrent values of HKONT in select-options then i need only 5 values in itab. when i use select bukrs hkont..... then its taking number of records.

so plz tell me accordingly.

regards saurabh.

Re: select single problem...

SuhaSaha — Wed, 28 Jan 2009 10:33:32 GMT

Hello Saurabh,

SELECT SINGLE is used to get a unique record based on the key fields. In your select stmt you are using multiple values in your WHERE clause.

As pointed by Gautham, you want to select single record but your stmt will return multiple recs.

BR,

Suhas

Edit:

As per your post, plz remove the SELECT SINGLE and use SELECT into ITAB.

Then SORT your table by HKONT & then DELETE ADJACENT DUPLICATES COMPARING hkont.

Edit:

Edited by: Suhas Saha on Jan 28, 2009 4:04 PM

Edited by: Vijay Babu Dudla on Jan 28, 2009 7:40 AM

Re: select single problem...

JozsefSzikszai — Wed, 28 Jan 2009 10:34:11 GMT

then I would do this, but hope this is only for practice (at least I don't see the business relevance):

LOOP AT hkont.
SELECT SINGLE bukrs hkont gjahr monat shkzg prctr FROM bsis INTO itab
WHERE hkont EQ hkont-low
AND bukrs IN bukrs
AND prctr IN prctr
AND gjahr IN gjahr
AND monat IN monat.
IF sy-subrc EQ 0.
APPEND itab.
ENDIF.
ENDLOOP.

Again: SELECT in LOOP - don't use it in real programs, as I told I hope, this is only for practice.

Re: select single problem...

Former Member — Wed, 28 Jan 2009 10:35:08 GMT

Hi,

Use this code


SELECT bukrs hkont gjahr monat shkzg prctr FROM bsis INTO table itab
WHERE hkont IN hkont
AND bukrs IN bukrs
AND prctr IN prctr
AND gjahr IN gjahr
AND monat IN monat.

regards,

Advait

Re: select single problem...

murat_kaya — Wed, 28 Jan 2009 10:37:35 GMT

hi,

if you may get more than 1 record, then why use select single, especially when you are using "IN" operator? this query returns missing data even if you put single value to a non-key field, it gets the first suitable row and returns it back even if there are other rows that fits the selection criteria? create an internal table and change your query like

SELECT bukrs hkont gjahr monat shkzg prctr FROM bsis

INTO TABLE itab

WHERE hkont IN hkont

AND bukrs IN bukrs

AND prctr IN prctr

AND gjahr IN gjahr

AND monat IN monat.

then you can loop at itab and process data as you like.

regards,

Murat Kaya

Re: select single problem...

SuhaSaha — Wed, 28 Jan 2009 10:38:02 GMT

Hello Eric,

Better still if you try to SELECT into ITAB.

Then SORT your table by HKONT & then DELETE ADJACENT DUPLICATES COMPARING hkont.

I also dont see any business relevance )

Suhas

Re: select single problem...

GauthamV — Wed, 28 Jan 2009 10:38:31 GMT

You may have many number of records for that specific 5 HKONT values.

so in what way you want to restrict them ?



Total Posts:  287  
Total Questions:  160 (89 unresolved)

Plz be specific in question to expect better replies.

Re: select single problem...

Former Member — Wed, 28 Jan 2009 10:52:32 GMT

hello kaya and eric,

when i m using this query then its taking too much time while i m giving only 3 values of HKONT in select-options. and after all its picking 14 same values from table BSIS. can u please tell me that how would i improve the performance of this query?

regards saurabh.

Re: select single problem...

SuhaSaha — Wed, 28 Jan 2009 10:55:21 GMT

Hello,

This is bound to happen if you do a SELECT inside a LOOP.


Eric wrote:
"then I would do this, but hope this is only for practice (at least I don't see the business relevance)"

Suhas

Re: select single problem...

JozsefSzikszai — Wed, 28 Jan 2009 10:55:32 GMT

try the idea from Suhas Saha, that might be faster.

Re: select single problem...

murat_kaya — Wed, 28 Jan 2009 12:33:55 GMT

Hi Saurabh,

i don't know what your program does but if you process the data you retrieve from table bsis more than once, it may be useful to create your internal table identical to table bsis;

data: itab like bsis occurs 0 with header line.

then get all the data from table bsis to your internal table at once.

select * from bsis

into table itab.

then process itab with your selection criteria as you wish.

avoid using the statement ...into corresponding fields of table (i assume you already are not using this)

well i don't know what else you can do..

regards;

Murat Kaya

Re: select single problem...

Former Member — Wed, 28 Jan 2009 13:04:02 GMT

change the code

SELECT single bukrs hkont gjahr monat shkzg prctr FROM bsis INTO table itab

WHERE hkont IN hkont

AND bukrs IN bukrs

AND prctr IN prctr

AND gjahr IN gjahr

AND monat IN monat.