topic Re: SQL Question. in Application Development and Automation Discussions

SQL Question.

Former Member — Thu, 10 Dec 2009 13:50:26 GMT

Hi everybody, the idea of the following query is to get the customers that some of their fields match partially or totally with an input text pattern (w_text_pattern).

CONCATENATE '%' im_pattern '%' INTO w_text_pattern.

SELECT * INTO TABLE customer_list

FROM kna1

WHERE kunnr LIKE w_text_pattern OR

name1 LIKE w_text_pattern OR

name2 LIKE w_text_pattern OR

ort01 LIKE w_text_pattern OR

pstlz LIKE w_text_pattern OR

regio LIKE w_text_pattern OR

stras LIKE w_text_pattern OR

telf1 LIKE w_text_pattern OR

telfx LIKE w_text_pattern.

The query is working fine, but the LIKE word has a limitation where w_text_pattern can not have more than 6 characters. The question is:

Is there another way to do that avoiding the restrction of the LIKE word?

Thanks and Regards.

Re: SQL Question.

guilherme_frisoni — Thu, 10 Dec 2009 14:11:01 GMT

Hi,

try to create ranges:


RANGES: lr_kunnr for kna1-kunnr,
        ls_kunnr LIKE LINE OF lr_kunnr.

CONCATENATE '%' im_pattern '%' INTO w_text_pattern.

ls_kunnr-sign = 'I'.
ls_kunnr-option = 'CP'.
ls_kunnr-low = w_text_pattern.
append ls_kunnr to lr_kunnr.

SELECT * INTO TABLE customer_list
FROM kna1
WHERE kunnr IN lr_kunnr and
  ...

The only problem is to create one range for each field...

Regards,

Frisoni

Re: SQL Question.

Former Member — Thu, 10 Dec 2009 14:27:36 GMT

Hi Fernando Franzolini,

There is no such limit of 6 Characters .. Please have a look on below example

Try declaring w_text_pattern of more length and assign more then 6 characters in it and check out...

REPORT ZILESH_TEST_LIKE.
PARAMETERS: P_NAME(40) TYPE C .

DATA L_SRCH_STR(60) TYPE C .
DATA L_ENAME LIKE P0001-ENAME .

CONCATENATE '%' P_NAME '%' INTO L_SRCH_STR .

SELECT ENAME FROM PA0001
       INTO L_ENAME
       WHERE ENAME LIKE L_SRCH_STR .

  WRITE:/ L_ENAME .

ENDSELECT .

Hope it will solve your problem..

Thanks & Regards

ilesh 24x7

ilesh Nandaniya

Re: SQL Question.

Former Member — Thu, 10 Dec 2009 20:48:31 GMT

Hi thanks for your answer, but if I assign something with more than 6 characters the system shows me the following dump:

Short text

ABAP/4 Open SQL statement with WHERE ... LIKE and pattern too long.

Error analysis

An exception occurred that is explained in detail below.

The exception, which is assigned to class 'CX_SY_DYNAMIC_OSQL_SEMANTICS', was

not caught in

procedure "Y_FA_GET_CUSTOMER_LIST" "(FUNCTION)", nor was it propagated by a

RAISING clause.

Since the caller of the procedure could not have anticipated that the

exception would occur, the current program is terminated.

The reason for the exception is:

The current ABAP program attempted to execute an Open SQL statement in

which the WHERE condition contains a LIKE operator.

The pattern belonging to a LIKE operator should be (with the exception

of closing blanks) no more than 6 characters long. This is a minimum

of 256 and twice as long as the database field (3) to which the

condition

applies.

In this particular case, the pattern contains

"%ASDDDEEEER%"

more than the maximum permitted 6 valid characters.

Re: SQL Question.

Former Member — Thu, 10 Dec 2009 22:34:07 GMT

Instead of using LIKE, why not just use a range table??

Rob

Re: SQL Question.

Clemenss — Thu, 10 Dec 2009 23:05:25 GMT

Hi Fernando,

you use the pattern to check against a couple of fields. If you check pattern %ASDDDEEEER% it will match all text fields containing string ASDDDEEEER.

Then, for me, it is given that the field that is checked ha a minimum length of 10 characters. I'm not on the system now, but regio field is probably shorter. Thats why it dumps.

Just think abaout what you are coding.

I would use ranges anyway, but then it is ASDDDEEEER not %ASDDDEEEER% as some wannabe recommended.

Regards,

Clemens

Re: SQL Question.

Former Member — Fri, 11 Dec 2009 15:03:08 GMT

Thanks, your anwser was the solution, but I had to use '*' instead '%' in the concatenate statement. Thanks again.