topic Re: How to write SQL statement in Application Development and Automation Discussions

How to write SQL statement

Former Member — Fri, 30 Jan 2009 16:55:15 GMT

Dear Sir,

We have a following scenario :

We have an Internal Table populated having Fields : OBJNR , WBS

We have to select all Records from table COEP which have OBJNR field value as per the records in Internal Table . The selected Records will have some Fields from table COEP and WBS Field from Internal Table .

Kindly guide us as what statement we need to write so that program execution is efficient .

With Thanks and Rgds

Sonia

Re: How to write SQL statement

Former Member — Fri, 30 Jan 2009 17:22:06 GMT

COEP does have a secondary index on OBJNR/KSTAR/GJAHR/PERIO/PAROB1. Check index 2 for COEP in SE11. So your SELECT query on COEP by OBJNR will be efficient as it is the first key of the secondary index.

Now coming to your question. You need to use the OBJNRs in your internal table to read COEP. So if the OBJNRs are repeated in your table, you need to append them in a separate table containing only OBJNRs, sort them and delete adjacent duplicates. If there are no duplicate OBJNRs in your table then you need not worry.

You then do a

SELECT * FROM COEP INTO <internal table with the requried fields, itab2>
FOR ALL ENTRIES IN <internal table of unique OBJNR, say itab1>
WHERE OBJNR = <itab1>-OBJNR.

Once done

LOOP AT itab2.
  READ TABLE itab1 WITH KEY OBJNR = itab2-objnr BINARY SEARCH. "Assuming itab2 is sorted by OBJRN!
  itab2-WBS = itab1-WBS.
  MODIFY itab2
ENDLOOP.

Re: How to write SQL statement

Former Member — Fri, 30 Jan 2009 17:24:43 GMT

I hope thi codes helps you...

SELECT OBJNR WBS... FIELD1 FIELD2 FIELD3 etc...

INTO CORRESPONDING FIELDS OF TABLE I_TAB_OUT

FOR ALL ENTRIES IN I_TAB_IN

FROM COEP

WHERE COEP-OBJNR EQ I_TAB_IN-OBJNR

Regards

Re: How to write SQL statement

Former Member — Fri, 30 Jan 2009 17:29:39 GMT


> COEP does have a secondary index on OBJNR/KSTAR/GJAHR/PERIO/PAROB1. Check index 2 for COEP in SE11. So your SELECT query on COEP by OBJNR will be efficient as it is the first key of the secondary index.

Unfortunately, this index is generally not created in the database and so won't help.

A better solution is to add LEDNR (which only has a value of 0) to the SELECT and use index COEP~1.

Rob