LIKE in a SELECT statement

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

Yes

[ingo_barschow]

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).

[Former Member]

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

[Former Member]

Hi,

I do agree with the solution provided by Ingo Barschow .

[ingo_barschow]

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.

[Former Member]

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

[former_member217544]

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

[Former Member]

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

[ingo_barschow]

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

[Former Member]

Thank you very much Ingo.

The problem is solved.

Cheers!

Kami