topic Re: query problem in Application Development and Automation Discussions

query problem

Former Member — Fri, 28 Sep 2007 06:47:00 GMT

Hi experts

I have done the following queries, but after executing second query,

the order of data in first query got changed, why its like that, i dont understand its behaviour, can anyone clarify me pls.

for example it_zlist have data like (1,2,3)

but after executiing second query it_first has data like (2,3,1),

can u tell me, about this changes ?

SELECT matnr arktx KWMENG VRKME FROM vbap

INTO corresponding fields of table it_zlist

WHERE vbeln = zctlk-vbeln.

if sy-subrc = 0.

select matnr test zsno ztnam from zmaster

into corresponding fields of table it_first

for all entries in it_zlist

where matnr = it_zlist-matnr.

endif.

Thanks in advance.

Regards

Rajaram

Re: query problem

Former Member — Fri, 28 Sep 2007 06:49:36 GMT

hi raj

can u send me the structure of both iternal table?

and

send me the ZMASTER table fileds too.?

if possible...

thhanks,

baskaran.

Message was edited by:

baskaran nagamanickam

Re: query problem

Former Member — Fri, 28 Sep 2007 06:50:06 GMT

hi raja... it may happen because one of the field of your internal table... but you should use SORT statement after every select query.. it'll help u out...

pls reward points if helpful...

Re: query problem

Former Member — Fri, 28 Sep 2007 06:50:48 GMT

Hi Rajaram,

Can you explain a bit

for example <b>it_zlist</b> have data like (1,2,3)

but after executiing second query <b>it_first</b> has data like (2,3,1),

can u tell me, about this changes ?

this part.

Regards,

Atish

Re: query problem

Former Member — Fri, 28 Sep 2007 06:51:00 GMT

Hi Raja Ram,

Check what is the primary key value in Vbap, the data whick u are getting in the Internal table ( Ist Query) is sorting against the primery key value .

give point if helpfull.

Re: query problem

former_member404244 — Fri, 28 Sep 2007 06:51:31 GMT

Hi,

try like this..use sort commnad and cehck

SELECT matnr arktx KWMENG VRKME FROM vbap

INTO corresponding fields of table it_zlist

WHERE vbeln = zctlk-vbeln.

if sy-subrc = 0.

sort it_zlist by matnr.

select matnr test zsno ztnam from zmaster

into corresponding fields of table it_first

for all entries in it_zlist

where matnr = it_zlist-matnr.

endif.

reward points if u find useful..

Regards,

nagaraj

Re: query problem

Former Member — Fri, 28 Sep 2007 06:54:16 GMT

This is my internal table declarations yar.

DATA:BEGIN OF it_first OCCURS 20,

MATNR like zmaster-MATNR,

TEST like zmaster-TEST,

ZTNAM like zmaster-ZTNAM,

arktx like zctlp-arktx,

KWMENG like zctlp-KWMENG,

VRKME like zctlp-VRKME,

END OF it_first.

DATA:BEGIN OF it_zlist OCCURS 20,

INCLUDE STRUCTURE zlist.

DATA:END OF it_zlist.

STRUCTURE ZLIST :

CTLNO : CHAR

TEST : CHAR

ZTNAM : CHAR

MATNR : CHAR

VBELN : CHAR

ZOPTION : CHAR

ZSNO : NUMC

ARKTX : CHAR

KWMENG : QUAN

VRKME : UNIT

ZEXPRE : CHAR

ZNUMB1 : CHAR

Re: query problem

Former Member — Fri, 28 Sep 2007 06:56:37 GMT

No, i dont want to sort by matnr, i want the data as it is in it_zlist (same order).

Re: query problem

former_member404244 — Fri, 28 Sep 2007 07:02:57 GMT

Hi,

try to decalre like this.. and chek

DATA:BEGIN OF it_first OCCURS 20,

TEST like zmaster-TEST,

ZTNAM like zmaster-ZTNAM,

MATNR like zmaster-MATNR,

arktx like zctlp-arktx,

KWMENG like zctlp-KWMENG,

VRKME like zctlp-VRKME,

END OF it_first.

Regards,

Nagaraj

Re: query problem

Former Member — Fri, 28 Sep 2007 07:03:00 GMT

After first query(it_zlist) i have data in it_zlist is

MATNR

1----

2----

3----

But After executing second query(it_first) it becomes (reading some other data)

MATNR

2----

3----

1----

but i want the it_first data as what exist in it_zlist.

My requirement

1----

2----

3----

Re: query problem

Former Member — Fri, 28 Sep 2007 11:09:45 GMT

Hi,

The order of the rows returned by each SELECT depends on the set-up of database tables and the database management system (e.g. Oracle).

SELECTs for ABAP are converted into SELECTs for Oracle (or whatever) and then used to query Oracle.

In the absence of 'order by', Oracle will return the rows in whatever order it sees fit. The order will depend on the data retrieval plan that Oracle decides on, which itself will depend on factors like database statistics and the existence of indexes.

So the row-orders for the two SELECTs are not necessarily related, assuming that ABAP does the second ABAP SELECT as a single Oracle SELECT.

A SELECT cannot have both 'for all entries in' and 'order by', but you could of course SORT after the second SELECT.

John