0

So I'm working on implementing an auto-complete search bar and integrating it with an RPG and Assembly system using DB2. I have tried these two techniques to search for distinct IDNumbs from a file, where the input requires at least 3 numbers before the search takes place. I have tried the following:

 Regular expression example:  ^[0-9]*$

SQL

SELECT DISTINCT [IDNUM] FROM FILE WHERE [iNPUT] > 3;

Unfortunately neither technique works accurately. I'd love it if someone had some advice on how to make it more robust. An RPG solution is welcomed as well but an SQL one would be great.

Michael Petch
  • 46,082
  • 8
  • 107
  • 198
  • 1
    I don't see how your question is actually about assembly language. You should give more details about what your example solutions are supposed to do, how they're used and what happens instead. You should also probably mention what SQL database you're using. – Ross Ridge May 10 '16 at 21:46
  • Auto-complete? As you are typing? And 'search bar'? For what window? And is `Assembly system` meaning some kind of BOM process? – user2338816 May 16 '16 at 04:44

1 Answers1

1

You could check if your search string is a number in RPG this way:

MONITOR
    EVAL FIELD = %INT(SEARCH_STR)
ON-ERROR
    // error: not a number
ENDMON

where SEARCH_STR is your search string and FIELD is defined as a number.

Regarding the query, if the server is DB2 this should work:

SELECT DISTINCT(IDNUM) 
FROM FILE
WHERE CHARACTER_LENGTH(SEARCH_STR) >= 3 AND 
      IDNUM LIKE '%SEARCH_STR%'

Change SEARCH_STR with your search string.

But I'd suggest to check the length of the search string via code instead of via SQL since it is trivial to do.

Loris Securo
  • 7,538
  • 2
  • 17
  • 28
  • This is great, thanks you gave me a great place to start from, now I noticed what I was doing wrong!. You are truly the best –  May 10 '16 at 23:52