topic Re: Problem Using 'LIKE' in Select statement. in Application Development and Automation Discussions

Problem Using 'LIKE' in Select statement.

Former Member — Thu, 17 May 2007 14:43:07 GMT

Hi Guys,

Presently Iam using the select query on VBAK and VBAP in this way.

SELECT a~vbeln "Sales order number

a~erdat "Creation date

a~ernam "Name of person who created

a~lifsk "Delivery block

a~vkorg "Sales Org

a~spart "Division

b~posnr "Item number

b~matnr "Material number

b~werks "Plant

b~arktx "Short Text for SO Item

b~netwr "Document currency

b~zz_catalog_id "Catalog ID for Variant configurator

FROM vbak AS a

INNER JOIN vbap AS b

ON a~vbeln = b~vbeln

INTO TABLE i_vbak_vbap

WHERE a~vbeln IN s_vbeln
AND a~vkorg IN s_vkorg
AND a~lifsk NE c_c

WHERE a~erdat IN s_erdat

and a~spart in s_spart

AND b~matnr like s_matnr-low.

Here Iam using the “ Like” to fetch the materials if user in the selection screen enters L* or X then I would be getting materials starting with L or the materials with X as part of the material.I have taken care of the conversions of * to %.

Now the User wants multiple selection of materials where he gives L* and V* and Z*,so that he wants me to fetch all the materials that start with L,V and Z.

I have tried the same statement in various forms but could not succeed ,

Some of the forms I tried …AND ( b~matnr like v_matnr1

and b~matnr like v_matnr2 )

But not able to fetch the records of all the materials which start with L , V and Z. I guess may be Join is making the problem.

I request you guys to give a solution to this in the same SQL statement .

Note: I have done all the conversions of * to %..

Thanks,

Venkat.

Re: Problem Using 'LIKE' in Select statement.

ferry_lianto — Thu, 17 May 2007 14:47:51 GMT

Hi,

You can build ranges for material with wild card and use in the select statement.


ranges r_matnr for mara-matnr.

r_matnr-sign = 'I'.
r_matnr-option = 'CP'.
r_matnr-low = 'L*'.
append r_matnr.

r_matnr-sign = 'I'.
r_matnr-option = 'CP'.
r_matnr-low = 'V*'.
append r_matnr.

r_matnr-sign = 'I'.
r_matnr-option = 'CP'.
r_matnr-low = 'Z*'.
append r_matnr.

SELECT a~vbeln "Sales order number
a~erdat "Creation date
a~ernam "Name of person who created
a~lifsk "Delivery block
a~vkorg "Sales Org
a~spart "Division
b~posnr "Item number
b~matnr "Material number
b~werks "Plant
b~arktx "Short Text for SO Item
b~netwr "Document currency
b~zz_catalog_id "Catalog ID for Variant configurator
FROM vbak AS a
INNER JOIN vbap AS b
ON a~vbeln = b~vbeln
INTO TABLE i_vbak_vbap
* WHERE a~vbeln IN s_vbeln
* AND a~vkorg IN s_vkorg
* AND a~lifsk NE c_c
WHERE a~erdat IN s_erdat
and a~spart in s_spart
AND b~matnr in r_matnr.                         *<--- Change here

Regards,

Ferry Lianto

Re: Problem Using 'LIKE' in Select statement.

Former Member — Thu, 17 May 2007 14:49:56 GMT

SELECT a~vbeln "Sales order number

a~erdat "Creation date

a~ernam "Name of person who created

a~lifsk "Delivery block

a~vkorg "Sales Org

a~spart "Division

b~posnr "Item number

b~matnr "Material number

b~werks "Plant

b~arktx "Short Text for SO Item

b~netwr "Document currency

b~zz_catalog_id "Catalog ID for Variant configurator

FROM vbak AS a

INNER JOIN vbap AS b

ON a_{vbeln = b}vbeln

INTO TABLE i_vbak_vbap

WHERE a~vbeln IN s_vbeln
AND a~vkorg IN s_vkorg
AND a~lifsk NE c_c

WHERE a~erdat IN s_erdat

and a~spart in s_spart

AND <b>b~matnr = s_matnr-low.</b>

Re: Problem Using 'LIKE' in Select statement.

Former Member — Thu, 17 May 2007 14:57:45 GMT

HI,

no need for LIKE statement.... u can use IN for select options.

If u given L* as input in select options then u can use IN as usual.

SELECT a~vbeln "Sales order number

a~erdat "Creation date

a~ernam "Name of person who created

a~lifsk "Delivery block

a~vkorg "Sales Org

a~spart "Division

b~posnr "Item number

b~matnr "Material number

b~werks "Plant

b~arktx "Short Text for SO Item

b~netwr "Document currency

b~zz_catalog_id "Catalog ID for Variant configurator

FROM vbak AS a

INNER JOIN vbap AS b

ON a_{vbeln = b}vbeln

INTO TABLE i_vbak_vbap

WHERE a~vbeln IN s_vbeln
AND a~vkorg IN s_vkorg
AND a~lifsk NE c_c

WHERE a~erdat IN s_erdat

and a~spart in s_spart

AND <b>b_{matnr IN s_matnr. or b}matnr = s_matnr-low</b>.

Regards

SAB

Re: Problem Using 'LIKE' in Select statement.

Former Member — Thu, 17 May 2007 14:58:42 GMT

Guys, I tried all this stuff, like placing them in ranges and then using ' in'

"b~matnr in s_matnr" with all the conversions using%, but i could not succeed...try a sample Program and send me if it is succssfull...Thanks, Venkat...

Re: Problem Using 'LIKE' in Select statement.

Former Member — Thu, 17 May 2007 15:04:12 GMT

HI,

u dont need to use any conversions... dont convert * to % ... only for LIKE need to convert.

selection screen:

s_matnr: L*

select statement as my previous post.

Regards

SAB

Re: Problem Using 'LIKE' in Select statement.

ferry_lianto — Thu, 17 May 2007 15:07:47 GMT

Hi,

It works fine for me ... please try this simple program to pull material starting with A* and B*.


REPORT ZZFL_TEST.

TABLES: MARA.
                                                                        
DATA: BEGIN OF T_MATNR OCCURS 0,
        MATNR LIKE MARA-MATNR.
DATA: END OF T_MATNR.
                                                                        
RANGES: R_MATNR FOR MARA-MATNR.
                                                                        
R_MATNR-SIGN = 'I'.
R_MATNR-OPTION = 'CP'.
R_MATNR-LOW = 'A*'.
APPEND R_MATNR.
                                                                        
R_MATNR-SIGN = 'I'.
R_MATNR-OPTION = 'CP'.
R_MATNR-LOW = 'B*'.
APPEND R_MATNR.
                                                                        
SELECT MATNR
INTO TABLE T_MATNR
FROM MARA
WHERE MATNR IN R_MATNR.
                                                                        
LOOP AT T_MATNR.
  WRITE: / T_MATNR-MATNR.
ENDLOOP.

Regards,

Ferry Lianto

Re: Problem Using 'LIKE' in Select statement.

Former Member — Thu, 17 May 2007 15:17:56 GMT

Guys and Ferry,

I have tried out this option but could not succeed, I feel that "Join" in the SQL is making the problem there. If you could try out the same thing on join SQL and if it went successfulll , Please post me the necessary code.

THANKS.

Re: Problem Using 'LIKE' in Select statement.

Former Member — Thu, 17 May 2007 15:33:16 GMT

HI Venkat,

i tried this code...

its working fine... but it will not work when u give s_matnr-low or like statements.

tables: mara, makt.

data: begin of itab occurs 0,

matnr like mara-matnr,

maktx like makt-maktx,

end of itab.

select-options: s_matnr for mara-matnr. " in ur case L*

select mara_{matnr makt}maktx into corresponding fields of table itab

from mara inner join makt on mara_{matnr = makt}matnr where mara~matnr in

s_matnr.

loop at itab.

write: / itab-matnr, itab-maktx.

endloop.

Regards

SAB

Re: Problem Using 'LIKE' in Select statement.

ferry_lianto — Thu, 17 May 2007 15:46:55 GMT

Hi,

It works for me ... and check this sample code.


REPORT ZZFL_TEST.
                                                                        
TABLES: VBAK, VBAP.
                                                                        
DATA: BEGIN OF T_VBAP OCCURS 0,
        VBELN LIKE VBAK-VBELN,
        POSNR LIKE VBAP-POSNR,
        MATNR LIKE VBAP-MATNR.
DATA: END OF T_VBAP.
                                                                        
RANGES: R_MATNR FOR VBAP-MATNR.
                                                                        
R_MATNR-SIGN = 'I'.
R_MATNR-OPTION = 'CP'.
R_MATNR-LOW = 'E*'.
APPEND R_MATNR.
                                                                        
R_MATNR-SIGN = 'I'.
R_MATNR-OPTION = 'CP'.
R_MATNR-LOW = 'A*'.
APPEND R_MATNR.


SELECT A~VBELN B~POSNR B~MATNR
INTO TABLE T_VBAP
FROM VBAK AS A
INNER JOIN VBAP AS B
ON A~VBELN = B~VBELN
WHERE MATNR IN R_MATNR.
                                                                        
LOOP AT T_VBAP.
  WRITE: / T_VBAP-VBELN,
           T_VBAP-POSNR,
           T_VBAP-MATNR.
ENDLOOP.

Regards,

Ferry Lianto

Re: Problem Using 'LIKE' in Select statement.

Former Member — Tue, 12 Feb 2008 09:26:41 GMT

Hi,

I am also facing the same problem. I have written a BAPI to get the items according to the search criteria. Please advice, how can i pass my text to the select statement. At present i am passing the description like PIPE% from the front end.

SELECT * FROM TABLE WHERE DESCRIPTION LIKE DESCRIPTION.

I don't want to pass the descriptionl like PIPE% and this should be taken care in the back end.

Please help!

Kind Regards,

Rao (rao.busi@yahoo.com)