topic Re: Optimize SQL Statement in Application Development and Automation Discussions

Optimize SQL Statement

Former Member — Thu, 25 Jan 2007 11:38:05 GMT

Hi All

I have writen a SQL statement.

However, the result has duplicate value, may I know how should i optimize the code below to avoild duplicate? Or maybe a better way to do the query?

SELECT K_{KUNNR K}VKORG K_{VTWEG B}BZIRK

INTO CORRESPONDING FIELDS OF TABLE IT_REP

FROM VBPA AS V

INNER JOIN VBKD AS B ON ( B_{VBELN = V}VBELN

AND B_{POSNR = V}POSNR )

INNER JOIN VBAK AS K ON K_{VBELN = B}VBELN

WHERE K~KUNNR IN S_REP

AND V~PARVW IN ('ZD','ZS','ZX').

Result I get :

300027 100 10 NBA

300027 100 10 NBA (DUPLICATE)

300005 187 10 NAE

Thanks a lot.

Re: Optimize SQL Statement

Former Member — Thu, 25 Jan 2007 11:40:11 GMT

use <b>delete adjacent duplicates</b> statement to avod duplicte entries ..


DELETE ADJACENT DUPLICATES FROM IT_REP Comparing kunnr vkorg.

Re: Optimize SQL Statement

Former Member — Thu, 25 Jan 2007 11:42:02 GMT

You could do a SELECT DISTINCT.

What you need to do is run an SE16 on the tables and see which table has the multiple entries. Then adjust your code accordingly to use the correct join conditions.

Extract the partner funtion into your table, this is probably causing the duplicate entries.

Message was edited by:

Martin Shinks

Re: Optimize SQL Statement

Former Member — Thu, 25 Jan 2007 11:42:54 GMT

Hi Hui ,

What i would suggest is keep the code as it is and once you have selected the data , use the command

DELETE ADJACENT DUPLICATES FROM IT_REP.

Regards

Arun

Assign points if reply is helpful

Re: Optimize SQL Statement

Former Member — Thu, 25 Jan 2007 11:48:40 GMT

Problem solved. Thanks everyone.