topic Re: Select Query Problem in Application Development and Automation Discussions

Select Query Problem

Former Member — Tue, 13 Feb 2007 03:56:05 GMT

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

Re: Select Query Problem

Former Member — Tue, 13 Feb 2007 04:03:25 GMT

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.

Re: Select Query Problem

Former Member — Tue, 13 Feb 2007 04:04:30 GMT

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

Re: Select Query Problem

Former Member — Tue, 13 Feb 2007 04:04:36 GMT

Hi Rahul

Change

SELECT * FROM zexc_rec INTO CORRESPONDING FIELDS

OF TABLE it_chng WHERE docno = lt_ct1-docno

SELECT * FROM zexc_rec APPENDING CORRESPONDING FIELDS

OF TABLE it_chng WHERE docno = lt_ct1-docno

Regards

Gareth

Re: Select Query Problem

Former Member — Tue, 13 Feb 2007 04:11:02 GMT

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

Re: Select Query Problem

Former Member — Tue, 13 Feb 2007 04:13:15 GMT

Hi Rahul,

use APPENDING CORRESPONDING FIELDS OF TABLE

SELECT * FROM zexc_rec APPENDING CORRESPONDING FIELDS

OF TABLE it_chng WHERE docno = lt_ct1-docno.

Reward if useful.

Re: Select Query Problem

Former Member — Tue, 13 Feb 2007 04:15:57 GMT

Hi,

The queries look correct to me.

Try clearing the workarea I_hierno.

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

Re: Select Query Problem

Former Member — Tue, 13 Feb 2007 04:16:00 GMT

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

Re: Select Query Problem

jayanthi_jayaraman — Tue, 13 Feb 2007 04:19:25 GMT

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 for all entries in lt_ctl

WHERE docno = lt_ct1-docno

AND lifnr = lt_ct1-lifnr AND

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

hierno = l_hierno.

Re: Select Query Problem

Former Member — Tue, 13 Feb 2007 04:28:12 GMT

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.

Re: Select Query Problem

Former Member — Tue, 13 Feb 2007 04:30:03 GMT

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 lifnr = lt_ct1-lifnr AND

docno = lt_ct1-docno ANDdoctyp in ('CT1','CT1C') AND

hierno = l_hierno.

Re: Select Query Problem

Former Member — Tue, 13 Feb 2007 04:34:00 GMT

Hi Rahul,

to avoid that try this...

concatenate '%' l_heirno into l_heirno

in the 2nd select statement use

hierno like l_hierno.

concatenate '%' l_heirno into l_heirno.

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

Message was edited by:

Chandrasekhar Jagarlamudi

null

Re: Select Query Problem

Former Member — Tue, 13 Feb 2007 04:38:31 GMT

Hi Chandrasekhar,

Thnaks a lot ,

its working now.

Re: Select Query Problem

sreeramkumar_madisetty — Tue, 13 Feb 2007 04:43:34 GMT

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