Search program

[Former Member]

Hello All,

I have written a new ALV GRID program to search the vendor by name from PAYR table. It can search based on the title or name1 or name2 or name3 or name4 fields of the table. But it is not retrieving some entries. For example if I give the search “VICTOR” it is missing the entry with “VICTOR WILLIAMS”. But it retrieved an entry with

“VICTOR COMPEAN”. I am not sure why it is behaving like this. It displays the final list in ALV GRID FORMAT.

My piece of code:

DATA:

i_src like table of wa_src,

l_str1(20) type C.

PARAMETERS: l_string(20) type C.

START-OF-SELECTION.

concatenate '%' l_string '%' into l_str1.

Select * from PAYR INTO table I_src where

ZANRE LIKE l_str1 OR

ZNME1 LIKE l_str1 OR

ZNME2 LIKE l_str1 OR

ZNME3 LIKE l_str1 OR

ZNME4 LIKE l_str1.

call screen '0100'.

Thanks for the great help,

Sobhan.

[RichHeilman · Accepted solution]

Or you might be able to do this better with a range.

DATA:
i_src like table of wa_src,
l_str1(20) type C.

<b>ranges: r_str for l_str1.</b>

PARAMETERS: l_string(20) type C

START-OF-SELECTION.

<b>concatenate l_string '*' into l_str1.
r_str-sign = 'I'.
r_str-option = 'PT'.
r_str-low  = l_str1.
appen r_str.

concatenate '*' l_string '*' into l_str1.
r_str-sign = 'I'.
r_str-option = 'PT'.
r_str-low  = l_str1.
appen r_str.</b>

Select * from PAYR INTO table I_src where
ZANRE LIKE r_str OR
ZNME1 LIKE r_str OR
ZNME2 LIKE r_str OR
ZNME3 LIKE r_str OR
ZNME4 LIKE r_str.

call screen '0100'.

Regards,

Rich Heilman

[Former Member · Accepted solution]

Hello All,

Thank you very much for the solutions.

I tried Rich’s second solution. The problem is, the query is not retreiving any rows. Becaz I think it is not liking the *. The sane thing also tried with %, still it is not retrieving any rows.

I tried ROB's solution also. But it is not getting compiled.

The following compilation error message is coming:

"I_SRC" cannot be a table, a reference, a string, or contain any of these objects.”

I think ROB is correct. The problem is with case sensitive. It is retrieving with “VICTOR COMPEAN” and it is not retrieving with “Victor Williams”.

Means “Victor Williams” has first letter upper case and and then the rest are lower case. So if this situation can be handled then the problem will be resolved.

I don’t know how to handle this situation. Can you help?

Thanksv for the great help,

Sobhan.

[Former Member · Accepted solution]

No - you have to put into a work area and then append it line by line to your internal table.

Rob

[Former Member · Accepted solution]

Hi Sobhan,

One way is to check in the where clause....

check the conditions '%VICTOR% or '%Victor%'. But this would make no sense because if other letters are in different case, then the select would fail. Checking in the where clause could be a laborious job.

The best way could be fetch the records with condition

'V%' or 'v%'. This would fetch the records with starting with 'V' or 'v'. Now as Nagarani suggested, you can you compare the strings and collect the required data in an internal table. But before comparing make sure that the case of both the fields are same.

Regards,

Vara

[Former Member · Accepted solution]

To get more than one record, you have to use cursor processing. From F1 for 'EXEC SQL':

Cursor Processing with Table AVERI_CLNT: 



DATA: F1(3), F2(3), F3(3). 
F3 = ' 1 '. 
EXEC SQL. 
  OPEN C FOR 
    SELECT CLIENT, ARG1 FROM AVERI_CLNT 
        WHERE ARG2 = :F3 
ENDEXEC. 
DO. 
  EXEC SQL. 
    FETCH NEXT C INTO :F1, :F2 
  ENDEXEC. 
  IF SY-SUBRC <> 0. 
    EXIT. 
  ENDIF. 
  WRITE: / F1, F2. 
ENDDO. 
EXEC SQL. 
  CLOSE C 
ENDEXEC. 

Instead of the 'write', you would append your table.

Rob

[Former Member · Accepted solution]

The following works quite well:

REPORT zrobtest NO STANDARD PAGE HEADING LINE-SIZE 255.

TABLES payr.

DATA:
wa_src LIKE payr,
i_src LIKE TABLE OF payr,
l_str1(20) TYPE c.


PARAMETERS: l_string(20) TYPE c.

START-OF-SELECTION.
  CONCATENATE '%' l_string '%' INTO l_str1.

  EXEC SQL.
    OPEN C FOR
    SELECT *
    FROM  PAYR
    WHERE UPPER(ZANRE)  LIKE  :L_STR1
    OR    UPPER(ZNME1)  LIKE  :L_STR1
    OR    UPPER(ZNME2)  LIKE  :L_STR1
    OR    UPPER(ZNME3)  LIKE  :L_STR1
    OR    UPPER(ZNME4)  LIKE  :L_STR1
  ENDEXEC.

  DO.
    EXEC SQL.
      FETCH NEXT C INTO :wa_SRC
    ENDEXEC.
    IF sy-subrc <> 0.
      EXIT.
    ENDIF.
    APPEND wa_src TO i_src.
  ENDDO.

  EXEC SQL.
    CLOSE C
  ENDEXEC.

  LOOP AT i_src INTO wa_src.
    WRITE: /001 wa_src-zanre,
                wa_src-znme1,
                wa_src-znme2,
                wa_src-znme3,
                wa_src-znme4.
  ENDLOOP.

The use of native SQL is dependent on your database system. This should work in Oracle and DB2.

Rob

[Former Member · Accepted solution]

Hi All,

Good Morning. Thanks for the solutions.

I tried Rob soluiton. The program is terminated abnormally. The following is the eror message.

SQL ERROR "-206" occured when executing EXEC SQL.

what happened?

The error occured in the current database connection "DEFAULT".

The followoing is the code.

DATA:

i_src like table of wa_src,

g_sum,

g_det,

g_nocode,

g_lines type i,

ok_code type sy-ucomm,

ok_tmp like ok_code,

l_str1(20) type C,

l_str2(20) type C,

l_str3(20) type C.

PARAMETERS: l_string(20) type C.

START-OF-SELECTION.

CONCATENATE '%' l_string '%' into l_str1.

EXEC SQL .

OPEN C FOR Select * INTO :wa_src from PAYR where

UPPER(ZANRE) LIKE l_string OR

UPPER(ZNME1) LIKE l_string OR

UPPER(ZNME2) LIKE l_string OR

UPPER(ZNME3) LIKE l_string OR

UPPER(ZNME4) LIKE l_string

ENDEXEC.

DO .

EXEC SQL.

FETCH NEXT C INTO :wa_src

ENDEXEC.

IF sy-subrc <> 0.

EXIT.

ENDIF.

APPEND WA_SRC TO I_SRC.

ENDDO.

EXEC SQL.

CLOSE C

ENDEXEC.

call screen '0100'.

Thanks,

Sobhan.

[RichHeilman · Accepted solution]

Not to highjack your post, Rob, but Sobhan, your problem is because of the where clause, you need to put colons in front of the field L_STR1.

          WHERE UPPER(ZANRE)  LIKE  :L_STR1
          OR    UPPER(ZNME1)  LIKE  :L_STR1
          OR    UPPER(ZNME2)  LIKE  :L_STR1
          OR    UPPER(ZNME3)  LIKE  :L_STR1
          OR    UPPER(ZNME4)  LIKE  :L_STR1

And, yes Rob, it does work quite good. Nice job.

Regards,

Rich HEilman

[Former Member · Accepted solution]

I think it's saying that it can't find table PAYR. The trouble with native SQL is that the sytnax may be different for different databases. The code I gave you works on DB2 and probably Oracle. What database are you using?

Rob

[Former Member · Accepted solution]

Rob,

Thanks for the answer. We use just SAP db on IBM AS/400.

Thanks,

Sobhan.

[RichHeilman · Accepted solution]

Same here, when I ran Rob's program without the ":" in front of the paramter fields in the where clause, I got the same error as you. Put the ":" there, it should work.

Regards,

Rich HEilman

[Former Member · Accepted solution]

That's why we try to avoid native SQL. You have to know which DB you're using. SAP programs that do this test sy-dbnam (I think that's the one) and come up with different statements for each.

So you need to adjust it for AS/400.

Rob

Message was edited by: Rob Burbank

[RichHeilman · Accepted solution]

Rob, your programs runs great on my system. We are using an AS/400 with DB2.

Am I totally wrong about needed the ":" in front of the parameter in the where clause?

Regards,

Rich Heilman

Message was edited by: Rich Heilman

[Former Member · Accepted solution]

Hi Rob and Rich,

It Worked. By putting :l_str1, it worked.

It is really great help. Thanks a lot.

Thanks,

Sobhan.

[Former Member · Accepted solution]

I think there's a minor misunderstanding. I think my original post includes the ':', but was dropped by the original poster. So in this case my original code should work in AS/400, DB2 and probably Oracle.

It definitely worked as posted in our system.

Rob

[RichHeilman · Accepted solution]

No points for that post?

Regards,

Rich Heilman

[Former Member · Accepted solution]

Rich,

I tried to share points between you and Rob. But I dont know how. So I first selected radio button for problem solved for Rob and then I clicked the radio button helpful answer for you since problem solved radio button was deselected.

Please let me know how I can do that so that I will reward the points correctly.

Thanks,

Sobhan.

[RichHeilman · Accepted solution]

You can only give the 10 points to one, in this case, Rob should get the 10 points. There should still be some stars next to the other posts. Award points (6 or 2) to the post that helped you. THanks.

Kind Regards,

Rich Heilman

[Former Member · Accepted solution]

Rich,

Thanks for the reply. I rewarded 6 points to you. You should get it now.

Thanks,

Sobhan.

[Former Member · Accepted solution]

Sobhan - glad to help. It was an interesting problem and a learning experience. I hadn't worked with cursors before.

But remember that in the production system, this is going to take time to run.

Rob

[Former Member · Accepted solution]

Thanks Rob for your great idea. It is very really interesting problem. It really surprises me to have such a limitations with OPEN SQL.

Once again thanks to you all,

Sobhan.

[Former Member]

Well all of those fields are case sensitive. You could try selecting base on '%Victor%' or '%VICTOR%' or '%victor%'. Just trying '%ictor%' would be best. But since this is a parameter, you could also use native SQL to translate to upper case during the select. For DB2 or Oracle, something like:

EXEC SQL.
  SELECT *
  INTO :I_SRC
  FROM  PAYR
  WHERE UPPER(ZANRE)  LIKE  :L_STR1
  OR    UPPER(ZNME1)  LIKE  :L_STR1
  OR    UPPER(ZNME2)  LIKE  :L_STR1
  OR    UPPER(ZNME3)  LIKE  :L_STR1
  OR    UPPER(ZNME4)  LIKE  :L_STR1
ENDEXEC.

I wasn't able to test this, so if you want to use it, please test thoroughly.

If you want to continue to test the way you are, you'll have to:

PARAMETERS: l_string(20) type C lower case.

Since PAYR can be large and these aren't key fields, performance will be an issue.

Rob

Message was edited by: Rob Burbank

[Former Member]

Sobhan,

Try to give VICT* in the search criteria. Sometimes there can be a typo in 'O' & 0 (Zero).

Cheers,

Bipin

[Former Member]

Hi

Try to split your search:

Select * from PAYR INTO table I_src

where ZANRE LIKE l_str1.

Select * from PAYR appending table I_src

where ZNME1 LIKE l_str1.

Select * from PAYR appending table I_src

where ZNME2 LIKE l_str1.

Select * from PAYR appending table I_src

where ZNME3 LIKE l_str1.

Select * from PAYR appending table I_src

where ZNME4 LIKE l_str1.

SORT I_src BY ZBUKR HBKID HKTID RZAWE CHECT.

DELETE ADJACENT DUPLICATES FROM I_SRC.

and then observe if it's retrieving all entries.

Max

[RichHeilman]

Not sure if this will help or not. Make sure that there are no-gaps between the concatenated values.

DATA:
i_src like table of wa_src,
l_str1(20) type C.


PARAMETERS: l_string(20) type C.

START-OF-SELECTION.
concatenate '%' l_string '%' into l_str1.
<b>condense l_str1 no-gaps.</b>

Select * from PAYR INTO table I_src where
ZANRE LIKE l_str1 OR
ZNME1 LIKE l_str1 OR
ZNME2 LIKE l_str1 OR
ZNME3 LIKE l_str1 OR
ZNME4 LIKE l_str1.

call screen '0100'.

Regards,

Rich Heilman

[Former Member]

What you have to do is retrive all the data from PAYR based on the condition and then compare with the string operation contains any 'CA'.

data: v_zanre type payr-zanre.

clear v_zanre.

Select zanre from PAYR INTO v_zanre

where <condition>

if sy-subrc = 0.

if v_znare ca l_str1.

endif.

endif.