topic Re: select query in Application Development and Automation Discussions

select query

Former Member — Thu, 13 Nov 2008 06:18:54 GMT

my actual query is

SELECT B_{WERKS A}VBELN B~MATNR

A_{FKDAT A}SPART A_{VTWEG A}ERDAT A~ERZET

B_{VGBEL B}AUBEL

A~FKART "Added by Arun pandey 08/05/2007

B_{NETWR B}FKIMG B~MWSBP

FROM VBRK AS A

JOIN VBRP AS B ON A_{VBELN = B}VBELN INTO TABLE IT_INV

WHERE A~FKART IN S_FKART

AND ( A_{SPART IN S_SPART AND A}SPART <> 'ST' )

AND A~FKDAT IN S_FKDAT

AND ( A_{VTWEG IN S_VTWEG AND A}VTWEG <> 'ST' )

AND A~FKSTO <> 'X'

AND A~KUNAG IN S_CUST

AND B~WERKS = S_WERKS.

when i rewrite like below

SELECT VBELN

FKDAT

SPART

VTWEG

ERDAT

ERZET

FKART FROM VBRK INTO TABLE IT_VBRK WHERE

FKART IN S_FKART

AND SPART IN S_SPART AND SPART <> 'ST'

AND FKDAT IN S_FKDAT

AND VTWEG IN S_VTWEG AND VTWEG <> 'ST'

AND FKSTO <> 'X'

AND KUNAG IN S_CUST.

SELECT VBELN MATNR WERKS VGBEL AUBEL NETWR FKIMG MWSBP FROM VBRP INTO

TABLE IT_VBRP FOR ALL ENTRIES IN IT_VBRK WHERE

VBELN = IT_VBRK-VBELN AND

WERKS = S_WERKS.

LOOP AT IT_VBRP INTO WA_VBRP.

READ TABLE IT_VBRK INTO WA_VBRK WITH KEY VBELN = WA_VBRP-VBELN.

IF SY-SUBRC = 0.

IT_INV-WERKS = WA_VBRP-WERKS.

IT_INV-VBELN = WA_VBRK-VBELN.

IT_INV-MATNR = WA_VBRP-MATNR.

IT_INV-FKDAT = WA_VBRK-FKDAT.

IT_INV-SPART = WA_VBRK-SPART.

IT_INV-VTWEG = WA_VBRK-VTWEG.

IT_INV-ERDAT = WA_VBRK-ERDAT.

IT_INV-ERZET = WA_VBRK-ERZET.

IT_INV-VGBEL = WA_VBRP-VGBEL.

IT_INV-AUBEL = WA_VBRP-AUBEL.

IT_INV-FKART = WA_VBRK-FKART.

IT_INV-NETWR = WA_VBRP-NETWR.

IT_INV-FKIMG = WA_VBRP-FKIMG.

IT_INV-MWSBP = WA_VBRP-MWSBP.

APPEND IT_INV.

ENDIF.

ENDLOOP.

it gives me different result.. in actual it suppose to give same.. i m getting less record in VBRP ..table

Re: select query

Former Member — Thu, 13 Nov 2008 06:33:17 GMT

May we know why you wanted to rewrite while Join is recommended to give more performance?

Regards

Karthik D

Re: select query

Former Member — Thu, 13 Nov 2008 06:38:08 GMT

Hi:

You are right and it would happen, Please try like this

SELECT A_{VBELN B}WERKS B~MATNR

A_{FKDAT A}SPART A_{VTWEG A}ERDAT A~ERZET

B_{VGBEL B}AUBEL

A~FKART "Added by Arun pandey 08/05/2007

B_{NETWR B}FKIMG B~MWSBP

FROM VBRK AS A

JOIN VBRP AS B ON A_{VBELN = B}VBELN INTO TABLE IT_INV

WHERE A~FKART IN S_FKART

AND ( A_{SPART IN S_SPART AND A}SPART 'ST' )

AND A~FKDAT IN S_FKDAT

AND ( A_{VTWEG IN S_VTWEG AND A}VTWEG 'ST' )

AND A~FKSTO 'X'

AND A~KUNAG IN S_CUST

AND B~WERKS = S_WERKS.

Another points try to maintain the field sequence as in table which effects its performance.

Regards

Shashi

Re: select query

Former Member — Thu, 13 Nov 2008 06:45:46 GMT

malay gal ,

your join condition contains some mistake.

See the following...


 SELECT   B~WERKS 
         A~VBELN 
         B~MATNR
         A~FKDAT 
         A~SPART
         A~VTWEG 
         A~ERDAT 
         A~ERZET
         B~VGBEL 
         B~AUBEL
         A~FKART "Added by Arun pandey 08/05/2007
         B~NETWR 
         B~FKIMG 
         B~MWSBP
  FROM  ( VBRK AS A
  JOIN   VBRP AS B ON A~VBELN = B~VBELN 
                  and B~WERKS = S_WERKS )    " Here is the mistake..
  INTO  TABLE IT_INV
 WHERE  A~FKART IN S_FKART
   AND  ( A~SPART IN S_SPART AND A~SPART 'ST' )
   AND  A~FKDAT IN S_FKDAT
   AND  ( A~VTWEG IN S_VTWEG AND A~VTWEG 'ST' )
   AND  A~FKSTO 'X'
   AND  A~KUNAG IN S_CUST.

Regards,

Mahi.