Select Query Problem

[Former Member]

Hi All,

I am having following select queries in the code one after the other as shown below, but when I am executing the code, the second query is bringing just one row i.e the last row where as there are 3 rows which satisfies the criteria.

Please suggest what can be the reason.

SELECT MAX( hierno ) FROM zexc_rec INTO l_hierno

WHERE lifnr = lt_ct1-lifnr AND

docno = lt_ct1-docno AND

doctyp in ('CT1','CT1C').

if sy-subrc eq 0.

SELECT * FROM zexc_rec INTO CORRESPONDING FIELDS

OF TABLE it_chng WHERE docno = lt_ct1-docno

AND lifnr = lt_ct1-lifnr AND

doctyp in ('CT1','CT1C') AND

hierno = l_hierno.

endif.

Thanks

Message was edited by:

Rahul Ghan

[Former Member]

u have to sort key field for read perticular record.

if number of record is fixed than u can use index to read record from this table to another work area.

[Former Member]

hi

are u sure that you have 3 rows in the db table..it seems to me there is nothing wrong in the second select and it should return as many rows in the table into the int. table..i doubt there is only one record for that particular hierno

[Former Member]

Hi Rahul

Change

SELECT * FROM zexc_rec INTO CORRESPONDING FIELDS

OF TABLE it_chng WHERE docno = lt_ct1-docno

to

SELECT * FROM zexc_rec APPENDING CORRESPONDING FIELDS

OF TABLE it_chng WHERE docno = lt_ct1-docno

Regards

Gareth

[Former Member]

Hi Gareth,

Its not working, i tried changing the select query as said by u.

But if I am commenting the first query, then it is working fine and bringing all the records.

Please suggest

[Former Member]

Hi Rahul,

Hmm. So in this first query you are getting the highest value of l_hier, but in the second you are getting all values for that lifnr and docno.

I suppose the question is do you need the first query, if you can sort the second query for the hierachy value.

Regards

Gareth

[Former Member]

HI All,

I found out why it is not selecting all the rows, actully there is a gap at the beggining of the field for two rows but for one gap is not there , thats why its not selecting all the rows,

Its like ' 2' in the database.

can anyone please tell how to ignore this blank space in select query.

Thanks.

[Former Member]

Hi Rahul,

to avoid that try this...

concatenate '%' l_heirno into l_heirno

in the 2nd select statement use

<b>hierno like l_hierno.</b>

<b>concatenate '%' l_heirno into l_heirno.</b>

SELECT * FROM zexc_rec INTO CORRESPONDING FIELDS

OF TABLE it_chng WHERE docno = lt_ct1-docno

AND lifnr = lt_ct1-lifnr AND

doctyp in ('CT1','CT1C') AND

<b>hierno like l_hierno.</b>

Message was edited by:

Chandrasekhar Jagarlamudi

null

[Former Member]

Hi Chandrasekhar,

Thnaks a lot ,

its working now.

[Former Member]

Hi Rahul,

<b>use APPENDING CORRESPONDING FIELDS OF TABLE

SELECT * FROM zexc_rec APPENDING CORRESPONDING FIELDS

OF TABLE it_chng WHERE docno = lt_ct1-docno.</b>

Reward if useful.

[Former Member]

Hi,

The queries look correct to me.

Try clearing the workarea I_hierno.

or

try changing the second query like:

SELECT * FROM zexc_rec INTO CORRESPONDING FIELDS

OF TABLE it_chng WHERE docno = lt_ct1-docno

AND lifnr = lt_ct1-lifnr AND

doctyp in ('CT1','CT1C') .

delete it_chng where hierno ne I_hierno.

Just try.

Regards

Subramanian

[jayanthi_jayaraman]

Hi,

I guess lt_ctl is an internal table with work area.So it is judt taking the work area record.

So you should use for all entries if you want to select for every record.

SELECT * FROM zexc_rec INTO CORRESPONDING FIELDS

OF TABLE it_chng <b>for all entries in lt_ctl</b>

WHERE docno = lt_ct1-docno

AND lifnr = lt_ct1-lifnr AND

doctyp in ('CT1','CT1C') AND

hierno = l_hierno.

[Former Member]

I guess in the first select you are trying to get the maximum hierno and

in the second select you are selecting all the records for that hierno .

2 selects looks good but looks strange why you are not getting the data.

change the order of docnr and lifnr in WHERE condition and try ur luck

SELECT * FROM zexc_rec INTO CORRESPONDING FIELDS

OF TABLE it_chng WHERE <b>lifnr = lt_ct1-lifnr AND

docno = lt_ct1-docno AND</b>doctyp in ('CT1','CT1C') AND

hierno = l_hierno.

[sreeramkumar_madisetty]

Hi

First sort the selection by the required fields .

Check in the debugging mode how many records are comming.

You can use Binqry search addition also for Performance sake.

Regards,

kumar