ABAP Standards for a SQL statement

[former_member202077]

Hi,

I have a Native SQL statement in my ABAP user exit, pls. edit into a Open SQL,

SELECT v1~tknum v1~tsnum
      INTO TABLE t_vtts
      FROM vtts AS v1
      FOR ALL ENTRIES IN t_shipment_del
      WHERE v1~tknum = t_shipment_del-tknum AND
            v1~vstel = rv_shppt          AND
            v1~kunnz = rv_shipto         AND
            v1~tsrfo = c_one             AND
            EXISTS ( SELECT addrnumber
                       FROM adrc CLIENT SPECIFIED
                       WHERE client     =  v1~mandt AND
                             addrnumber =  v1~adrnz AND
                             date_from  <= sy-datum AND
                             date_to    >= sy-datum AND
                             post_code1 =  rv_post_code1 ).

Thank you

[Former Member]

Small correction: This is not a native SQL statement, this is clearly an [Open SQL|http://help.sap.com/abapdocu_70/en/ABENOPENSQL.htm] statement. You can recognize native SQL statements as they have to be wrapped into a [EXEC SQL - ENDEXEC|http://help.sap.com/abapdocu_70/en/ABENNATIVESQL.htm]. Don't know of any database that has a FOR ALL ENTRIES condition...

So essentially you have an Open SQL query with a [subquery|http://help.sap.com/abapdocu_70/en/ABENWHERE_LOGEXP_SUBQUERY.htm].

What exactly are you trying to accomplish? Do you want to optimize the query, because performance is poor? Then you should provide some background what you're trying to accomplish. Or simply try yourself some alternatives, like rephrasing it as a [join|http://help.sap.com/abapdocu_70/en/ABAPSELECT_JOIN.htm].

Cheers, harald

[former_member202077]

Thank you for recollecting my memory that, its a open SQL, bcoz, Native needs EXEC-ENDEXC, am Sorry for the mistake.

Its SELECT existing in one of ABAP user-exit here. Yes, ur guess is correct that, the performence is very poor, so, am looking to hv a simple/suggested SQLs, with wht i want to replace this SUB Query SQL.

Actually, i tried, but, did not get success!!

Vielen Danka!

[Former Member]

Hhmm, for proper performance analysis we'd need some further details. Looking at your query I doubt that the following alternative helps, but you can give it a try, because subqueries are not always optimal:

  SELECT tknum tsnum INTO TABLE t_vtts
         FROM vtts INNER JOIN adrc ON
              vtts~adrnz = adrc~addrnumber
         FOR ALL entries IN t_shipment_del
         WHERE tknum      = t_shipment_del-tknum AND
               vstel      = rv_shppt             AND
               kunnz      = rv_shipto            AND
               tsrfo      = c_one                AND
               post_code1 = rv_post_code1.

You might need to add the condition on the address dates, but in any system I've checked so far, the ADRC-FROM date was always 00010101 and there was only one entry per address number (so if required, add it back).

For performance tuning requests you should try to provide some background information, like:

<ol>

<li>How many entries are you your table T_SHIPMENT_DEL</li>

<li>How many entries match your conditions on VTTS</li>

<li>How many entries get dropped because of the condition on postal code</li>

<li>Do a SQL trace and provide information about the execution plan chosen by the database</li>

</ol>

Also, you might want to consider moving your question to the forum.

Cheers, harald