Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

none key field at where clause

Former Member
0 Kudos

hi,

i need to pull out data from table afru but none of the field at where clause is key field in table afru.

we not allowed to create indexes so would like to know how to optimise the fetch.

SELECT SUM( lmnga ) SUM( xmnga )

INTO (wa-lmnga, wa-xmnga)

FROM afru

WHERE aufnr EQ wa-aufnr AND

vornr EQ wa-vornr AND

werks EQ wa-werks and

stokz ne 'X' and

stzhl eq '0'.

thanks

9 REPLIES 9

Former Member
0 Kudos

try to include all primary key for data fetching

MANDT MANDT CLNT 3 0 Client

RUECK CO_RUECK NUMC 10 0 Completion confirmation number for the operation

RMZHL CO_RMZHL NUMC 8 0 Confirmation counter

• In a SELECT statement, only the fields (field-list) which are needed are selected in the order that they reside on the database, thus network load is considerably less. The number of fields can be restricted in two ways using a field list in the SELECT clause of the statement or by using a view defined in ABAP/4 Dictionary. The usage of view has the advantage of better reusability.

• SELECT SINGLE is used instead of SELECT-ENDSELECT loop when the entire key is available. SELECT SINGLE requires one communication with the database system, whereas SELECT-ENDSELECT needs two

• Always specify the conditions in the WHERE-clause instead of checking them with check-statements, the database system can then use an index (if possible) and the network load is considerably less. You should not check the conditions with the CHECK statement because the contents of the whole table must be read from the database files into DBMS cache and transferred over the network. If the conditions are specified in the where clause DBMS reads exactly the needed data.

• Complex code is not embedded within a SELECT / ENDSELECT statement.

• No complex WHERE clauses, since complex where clauses are poison for the statement optimizer in any database system.

• For all frequently used SELECT statements, try to use an index. You always use an index if you specify (a generic part of) the index fields concatenated with logical ANDs in the Select statement's WHERE clause

• When loading data into Internal table, INTO TABLE OR APPENDING TABLE is used instead of a SELECT/APPEND combination. It is always faster to use the INTO TABLE version of a Select statement than to use APPEND statements.

• Use a select list with aggregate functions instead of checking and computing, when trying to find the maximum, minimum, sum and average value or the count of a database column.

Rewards if useful........

Minal Nampalliwar

Former Member
0 Kudos

Eliana,

use in this way

SELECT SUM( lmnga ) SUM( xmnga )

INTO (wa-lmnga, wa-xmnga)

FROM afru

<b>upto 1 rows</b>

WHERE aufnr EQ wa-aufnr AND

vornr EQ wa-vornr AND

werks EQ wa-werks and

stokz ne 'X' and

stzhl eq '0'.

endselect.

0 Kudos

Srikanth,

You can fetch the data w.r.t a primary key in that table and the loop at the internal table and delete the data which are not corresponding with the workarea details as you had mentioned.

I mean fetch all data w.r.t rueck or rmzhl from afru and then loop at the internal table and delete the contents w.r.t the work areas details as per the requirement.

K.Kiran.

0 Kudos

kiran,

If the primary key is directly known, why will a person choose this type of indirect fetch. As the primary key is not known only, eliana is trying to fetch with the fields that are available to her.

0 Kudos

Hi,

Srikanth is correct. I donno why people don't read!!!!

Eliana you can try this


SELECT lmnga  
             xmnga 
             stokz
INTO TABLE afru
FROM afru
WHERE aufnr EQ wa-aufnr AND
vornr EQ wa-vornr AND
werks EQ wa-werks and
*stokz ne 'X' and
stzhl eq '0'.

Delete tab_afru where stokz eq 'X'.

Loop at tab_afru.
SUM.
Endloop.

This might help. The premise here is the 'NE' is performance intensive which it is since it's a rejection criteria rather than selection.

Regards

Nishant

0 Kudos

hi,

really appreciate the help the point given accordingly.

now i have i issue, as i only want to sum 2 quan data type field, the sum in the loop will sum up other quan data type field as well. how should i avoid that?

Loop at tab_afru.

SUM.

Endloop.

thanks

0 Kudos

Hi Eliana,

I understood from your Select query that there are only two fields.

Can you please put the actual code and I'll see what can be done ?

regards

Nishant

0 Kudos

hi,

loop at it_work into wa_work.

perform check_status using wa_work-aufnr wa_work-objnr1.

IF chk_del ne 'Y'.

SELECT SUM( lmnga ) SUM( xmnga )

INTO (wa_work-lmnga, wa_work-xmnga)

FROM afru

WHERE aufnr EQ wa_work-aufnr AND

vornr EQ wa_work-vornr AND

werks EQ wa_work-werks and

stokz ne 'X' and

stzhl eq '0'.

IF sy-subrc = 0.

it_work-lmnga = wa_work-lmnga.

it_work-xmnga = wa_work-xmnga.

it_work-f_del = ''.

ENDIF.

ELSE.

it_work-f_del = 'Y'.

ENDIF.

modify it_work transporting lmnga xmnga f_del.

clear wa_work.

endloop.

0 Kudos

Hey,

No need to worry,

Just create and internal table tab_afru with header line with fields : lmnga, xmnga and stokz.

Then use select as mentioned.

IF sy-subrc = 0.

it_work-lmnga = tab_afru-lmnga.

it_work-xmnga = tab_afru-xmnga.

it_work-f_del = ''.

ENDIF.

I think this should do it.

regards

Nishant