topic Re: LIKE in a SELECT statement in Application Development and Automation Discussions

LIKE in a SELECT statement

Former Member — Wed, 30 Jul 2008 12:25:28 GMT

Hi mates,

I have a statement

SELECT xxx INTO CORRESPONDING FIELDS OF TABLE i_mtnr

WHERE marc~matnr LIKE input_par-matnum

AND mccode~mktcde LIKE input_par-mc

Both fields "input_par-matnum" and "input_par-mc" are import parameters of an RFC-Function module and could be passed by the caller as "*" or even e.g. "?47" for mc or matnum. Now the question is how to edit the statement that the SELECT delivers result even the input parameter would partly passed.

Thanks

Kami

Re: LIKE in a SELECT statement

Former Member — Wed, 30 Jul 2008 12:28:58 GMT

hi we will use the like in the where statement for the partly giving values by this....

like '00%' or like 'ER%'.

it will fectch the data which are starting with 00 or ER

Re: LIKE in a SELECT statement

Former Member — Wed, 30 Jul 2008 12:40:54 GMT

but my input parameter are dynamic. It means if I don't find any technics I have to concatenate % separately to the input parameters and I have still the problem with the search criteria "?"

Thanks

Re: LIKE in a SELECT statement

Former Member — Wed, 30 Jul 2008 12:42:00 GMT

Yes

Re: LIKE in a SELECT statement

ingo_barschow — Wed, 30 Jul 2008 13:33:21 GMT

DATA:
  lc_qry TYPE c LENGTH 128,
  lc_tmp TYPE c LENGTH 128.
*
TRANSLATE input_par-matnum USING '*%'.
TRANSLATE input_par-mc     USING '*%'.
*
IF  input_par-matnum CA '%'
 OR input_par-matnum CA '?'.
    lc_tmp ='MARC~MATNR LIKE INPUT_PAR-MATNUM'.
ELSE.
    lc_tmp = 'MARC~MATNR  EQ INPUT_PAR-MATNUM'.
ENDIF.
*
IF  input_par-mc CA '%'
 OR input_par-mc CA '?'.
    lc_qry = 'AND MCCODE~MKTCDE LIKE INPUT_PAR-MC.'.
ELSE.
    lc_qry = 'AND MCCODE~MKTCDE   EQ INPUT_PAR-MC.'.
ENDIF.
*
CONCATENATE lc_tmp lc_qry INTO lc_qry SEPARATED BY space.
*
SELECT xxx FROM yyy INTO CORRESPONDING FIELDS OF TABLE i_mtnr 
  WHERE (lc_qry).

Re: LIKE in a SELECT statement

Former Member — Wed, 30 Jul 2008 14:54:24 GMT

Hi,

I do agree with the solution provided by Ingo Barschow .

Re: LIKE in a SELECT statement

Former Member — Thu, 31 Jul 2008 08:25:01 GMT

Hi Ingo,

first thank you verrrry much for your support, it help me lot ! However I still have problems if the entry contains "?". As long as I know the "?" stands for only one character and if I put e.g. " *2? " your logic turns this in to " %2? " which is correct from the SQL point of view but the value in the data base " 0021 or 0028" would be found in the result. Do you have any idea

Cheers

Kami

Re: LIKE in a SELECT statement

ingo_barschow — Thu, 31 Jul 2008 11:25:30 GMT

Hi kami,

I'm not sure for understanding right. You wrote if searching with eg '%2?' the SQL-statement returns '0021' and '0028', so where is the problem? Or did you mean it wouldn't return '0021' and '0028'?

Regards.

Re: LIKE in a SELECT statement

Former Member — Thu, 31 Jul 2008 13:38:21 GMT

Hi Ingo,

unfortunately it won't return any value ( "0021" and "0028" included ) and I am wondering why, since the SELECT should return at least these two values but as I said sy-subrc = 4

Look forward for your comments.

Gruss

Kami

Re: LIKE in a SELECT statement

former_member217544 — Thu, 31 Jul 2008 14:17:46 GMT

Hi Kami,

It is correct that it should not return any of the values 0021 or 0028.

When the input is '%2' then it will return only the records that will end with number 2. ( like 002, 0012, 0022, 0032 etc.,)

However it willl return the values 0021 and 0028 if the input provided is %2% .

And if the expected inout is like %25 then use this syntax

TRANSLATE v_input USING '%**%'.

In the where clause of select statement use LIKE

select ......

where field1 LIKE v_input.

Hope this will clear the doubt.

Regards,

Swarna Munukoti.

Edited by: Swarna Munukoti on Jul 31, 2008 4:20 PM

Re: LIKE in a SELECT statement

Former Member — Thu, 31 Jul 2008 14:34:56 GMT

Hi Swarna,

But my issue is the case if the user enters '%2?' (see my recent replay to Ingo) with hope to get 0021 and 0028 since ? stands for only one and any character.

Regards

Edited by: kami on Jul 31, 2008 4:35 PM

Re: LIKE in a SELECT statement

ingo_barschow — Fri, 01 Aug 2008 10:46:46 GMT

Hi kami,

sorry for any inconvenience. I've made a mistake, depending on several SQL-standards (Oracle/DB2).

You also have to replace the '?' with an underscore '_' and it should work.

The code must be:

DATA:
  lc_qry TYPE c LENGTH 128,
  lc_tmp TYPE c LENGTH 128.
*
TRANSLATE input_par-matnum USING '*%'.
TRANSLATE input_par-matnum USING '?_'.
TRANSLATE input_par-mc     USING '*%'.
TRANSLATE input_par-mc     USING '?_'.
*
IF  input_par-matnum CA '%'
 OR input_par-matnum CA '_'.
    lc_tmp ='MARC~MATNR LIKE INPUT_PAR-MATNUM'.
ELSE.
    lc_tmp = 'MARC~MATNR  EQ INPUT_PAR-MATNUM'.
ENDIF.
*
IF  input_par-mc CA '%'
 OR input_par-mc CA '_'.
    lc_qry = 'AND MCCODE~MKTCDE LIKE INPUT_PAR-MC.'.
ELSE.
    lc_qry = 'AND MCCODE~MKTCDE   EQ INPUT_PAR-MC.'.
ENDIF.
*
CONCATENATE lc_tmp lc_qry INTO lc_qry SEPARATED BY space.
*
SELECT xxx FROM yyy INTO CORRESPONDING FIELDS OF TABLE i_mtnr 
  WHERE (lc_qry).

Further you have to check the fields are not INITIAL and they must be in CHAR or STRING format.

Regards and excuse the long time for answer, but I have to work

Edited by: Ingo Barschow on Aug 1, 2008 12:47 PM

Re: LIKE in a SELECT statement

Former Member — Mon, 04 Aug 2008 09:39:02 GMT

Thank you very much Ingo.

The problem is solved.

Cheers!

Kami