topic Re: Selecting char % in a column using WHERE Statement. in Application Development and Automation Discussions

Selecting char % in a column using WHERE Statement.

Former Member — Fri, 10 Sep 2010 19:41:16 GMT

Hi Expert.

Im selecting records in RESB table using Open SQL where all entries in AUFNR column that contains a character ' % '.

eg. AUFNR

My code right now is using SELECT... ENDSELECT and take so long due to RESB data and I have no idea how can i use simple SELECT in WHERE statement since % stands for any character string, including a blank string.

My Code: (it is working but took so long)

select rsnum rspos rsart bdart rssta

xloek xwaok kzear xfehl matnr

werks lgort prvbe charg plpla

sobkz bdter bdmng meins shkzg

fmeng aufnr

from resb

into i_resb01

where xloek = space.

if i_resb01-aufnr ca '%'.

append i_resb01.

clear i_resb01.

endif.

endselect.

Thank you

Re: Selecting char % in a column using WHERE Statement.

Former Member — Fri, 10 Sep 2010 19:52:08 GMT

Hi,

Dont use select end select, this will decrease the performance of the program..

Instead select all the record in one single shot in a table and later read it based on your requrement.

Regards

Satish Boguda

Re: Selecting char % in a column using WHERE Statement.

prasenjit_sharma — Fri, 10 Sep 2010 19:58:39 GMT

Hi,

I do not think any entries in table RESB would have a '%' in AUFNR field. You can check it in SE16. Now if it does then use the folllowing code:

select rsnum rspos rsart bdart rssta 
xloek xwaok kzear xfehl matnr 
werks lgort prvbe charg plpla 
sobkz bdter bdmng meins shkzg 
fmeng aufnr 
from resb 
into table i_resb01 
where xloek = space. 

loop at i_resb01.
if i_resb01-aufnr na '%'. 
 delete i_resb01.
endif. 
endloop.

Re: Selecting char % in a column using WHERE Statement.

Former Member — Fri, 10 Sep 2010 20:09:30 GMT

Thanks for the quick reply. In our case we have an entry in RESB-AUFNR with this character % and wanted to get them.

I do not want to use LOOP or SELECT.. ENDSELECT because RESB is gigantic and take so long to run. All i want is if there is a way to use a single SELECT statement


SELECT rsnum rspos rsart bdart rssta   
              xloek xwaok kzear xfehl matnr   
              werks lgort prvbe charg plpla   
              sobkz bdter bdmng meins shkzg   
             fmeng aufnr                     
  FROM  resb                            
    INTO TABLE i_resb01                  
 WHERE xloek    = space                
     AND aufnr LIKE '%' .

Unfortunately the SELECT statement above doesn't work because SAP consider the % as keyword

['%' - stands for any character string, including a blank string]

Re: Selecting char % in a column using WHERE Statement.

Former Member — Fri, 10 Sep 2010 20:28:24 GMT

Hello neljoti!

Yes you can filter a field that has a character '%'. But you must use the ESCAPE keyword.

Try this :

SELECT rsnum rspos rsart bdart rssta   
              xloek xwaok kzear xfehl matnr   
              werks lgort prvbe charg plpla   
              sobkz bdter bdmng meins shkzg   
             fmeng aufnr                     
  FROM  resb                            
    INTO TABLE i_resb01                  
 WHERE xloek    = space                
     AND aufnr LIKE '%#%%' ESCAPE '#'.

Best Regards.

Claudio (Aparatey)

Re: Selecting char % in a column using WHERE Statement.

Former Member — Fri, 10 Sep 2010 20:52:23 GMT

Interesting - I never heard of using an escape character in a SELECT before.

However, the problem with this SELECT is that no key field is being used. So I doubt if there is any direct way to speed it up.

Alternatively, is this odd order number in table AUFK?

Rob

Re: Selecting char % in a column using WHERE Statement.

Former Member — Fri, 10 Sep 2010 21:26:28 GMT

Hello Claudio, Your SQL works and never heard about that ESCAPE before

Rob, no i didn't check the AUFK table.

THANKS A BUNCH!