Michael,
I tried your solution and it works great as long as you don't have a
first name or initial.
This is a warrant browse, the user can enter a last name and a first
name, or part of a last name and or part of a first name. Once the
user enters data the browse should continue to browse through the
names. It does this by doing a new query based on the last name on the
bottom of the screen. The sql I attached works great with the
exception of the one situation where I have a 'Adam' and an 'Adam-
salmi'.
On Jul 27, 4:42 pm, Michael Moore <michaeljmo...@gmail.com> wrote:
> The problem is that you are concat lname and fname .
> consider this.
>
> SELECT *
> FROM (SELECT 'Adam-salami' lname ,'Fred' fname FROM DUAL
> UNION ALL
> SELECT 'Adam' lname ,'George' fname FROM DUAL) mytab
> WHERE lname||fname >= 'Adam-salami'||'';
>
> the comparisons turn out to be
>
> Adam-salamiFred >= Adam-salami
> AdamGeorge > = Adam-salami
>
> both comparisons are TRUE
>
> Can you just make your where clause to be ..
> LAST_NAME >= TRIM(:WS-BEGIN-KEY02)
> and :WS-BEGIN-KEY02 should only be last name too.
>
> If this does not fit your requirement, then you will need to have two
> separate cobol variables, one for first name and one for last name
> and then do:
>
> AND ( LAST_NAME >= TRIM(:WS-LAST_NAME)
> or (LAST_NAME = TRIM(:WS-BEGIN-KEY02) and FIRST_NAME >=
> TRIM(:WS-FIRST_NAME)
> )
> )
>
> Mike
>
> On Tue, Jul 27, 2010 at 1:20 PM, Michael Moore <michaeljmo...@gmail.com>wrote:
>
>
>
> > I realize now that I have misunderstood the problem. Please disregard
> > previous posts.
>
> > On Tue, Jul 27, 2010 at 1:09 PM, John H <ho...@chesterfield.gov> wrote:
>
> >> Rich,
>
> >> I gave that a try and it gives me more than just Adam, it takes me
> >> back to the beggining of the A's.
>
> >> On Jul 27, 2:45 pm, Richard Pascual <richg...@gmail.com> wrote:
> >> > Hi John,
>
> >> > In that case, here's something that I have found useful in my
> >> applications
> >> > that I have programmed which can do partial searches. Instead of >=, as
> >> in:
>
> >> > AND LAST_NAME || FIRST_NAME >= TRIM(:WS-BEGIN-KEY02)
>
> >> > try:
>
> >> > AND INSTR(LAST_NAME || FIRST_NAME, TRIM(:WS-BEGIN-KEY02)) > 0
>
> >> > The INSTR function checks the presence of the second parameter in the
> >> first
> >> > parameter. If the second parameter turns up a partial or full match, the
> >> > result of the INSTR function returns a non-zero value (i.e., the exact
> >> > position in parameter 1 that parameter 2 appears)
>
> >> > For example, some quick tests:
>
> >> > SELECT INSTR('TEST','ES') from DUAL
> >> > will return the value "2" because the string "ES" appears beginning in
> >> the
> >> > second position of the search string "TEST".
>
> >> > SELECT INSTR('TEST','ABC') from DUAL
> >> > will return the value "0" because the string "ABC" is nowhere in the
> >> search
> >> > field value "TEST".
>
> >> > One issue you will encounter involves mixed cases. I usually solve that
> >> by
> >> > casting all strings in either all upper or all lower case... which may
> >> be a
> >> > performance issue depending on how large your database is. If you try my
> >> > suggestion, try it without converting the case to all UPPER or LOWER
> >> case
> >> > first.
>
> >> > Rich
>
> >> > On Tue, Jul 27, 2010 at 11:34 AM, John H <ho...@chesterfield.gov>
> >> wrote:
> >> > > Rich,
>
> >> > > This is a browse and I would like for it to show the "Adam-salmi" and
> >> > > anything greater incase they need to browse farther. Sometimes they
> >> > > don't type in a full name.
>
> >> > > On Jul 27, 2:14 pm, Richard Pascual <richg...@gmail.com> wrote:
> >> > > > Hi John,
> >> > > > I am curious of your clause in the "where" criteria that says:
>
> >> > > > AND LAST_NAME || FIRST_NAME >= TRIM(:WS-BEGIN-KEY02)
>
> >> > > > The ">=" operator will include results that are not also exact
> >> matches,
> >> > > such
> >> > > > as in your hyphenated example: "Adam-salmi" returning "Adam"
>
> >> > > > If you want an exact match, replace the ">=" operator with "="
>
> >> > > > There are a number of ways to facilitate partial matches (such as
> >> using
> >> > > > "instr" or "like" operators) but it depends on the needs of your
> >> > > application
> >> > > > which uses this query.
>
> >> > > > Rich
>
> >> > > > rgpasc...@berkeley.edu
> >> > > > Rich Pascual
> >> > > > Database Programmer, IT Systems Management
> >> > > > Advancement Operations
> >> > > > U.C. Berkeley University Relations
> >> > > > Phone: (510) 643-7652
>
> >> > > > On Tue, Jul 27, 2010 at 10:46 AM, John H <ho...@chesterfield.gov>
> >> wrote:
> >> > > > > I am currently trying to write a select statement that will return
> >> > > > > name information. We have a situtation where we have last names
> >> with
> >> > > > > hypens in them and when the exact name is entered "Adam-salmi" it
> >> > > > > returns last names that are "Adam" also. I am new to Oracle and
> >> any
> >> > > > > help would be greatly appreciated. I will attach the sql
> >> statement.
>
> >> > > > > SELECT LAST_NAME,
> >> > > > > FIRST_NAME,
> >> > > > > MIDDLE_NAME,
> >> > > > > SUFFIX,
> >> > > > > TO_CHAR(BIRTH_DATE,'YYYY/MM/DD'),
> >> > > > > SSN_OR_EIN,
> >> > > > > WARRANT_DEPARTMENT,
> >> > > > > WARRANT_KEY,
> >> > > > > PERSON_SEX,
> >> > > > > RACE_CODE,
> >> > > > > LAST_NAME || FIRST_NAME
> >> > > > > FROM PR00100T.WARRANT A,
> >> > > > > PR00100T.MASTER_NAME B,
> >> > > > > PR00100T.PERSON C
> >> > > > > WHERE A.WANTED_PERSON_KEY = B.PERSON_KEY
> >> > > > > AND A.WANTED_PERSON_KEY = C.PERSON_KEY
> >> > > > > AND A.WANTED_NAME_KEY = B.NAME_KEY
> >> > > > > AND A.WARR_STATUS_CODE = '01'
> >> > > > > AND LAST_NAME || FIRST_NAME >= TRIM(:WS-BEGIN-KEY02)
> >> > > > > ORDER BY B.LAST_NAME ASC,
> >> > > > > B.FIRST_NAME ASC,
> >> > > > > B.MIDDLE_NAME ASC,
> >> > > > > A.WARRANT_KEY ASC;
>
> >> > > > > --
> >> > > > > You received this message because you are subscribed to the Google
> >> > > > > Groups "Oracle PL/SQL" group.
> >> > > > > To post to this group, send email to
> >> Oracle-PLSQL@googlegroups.com
> >> > > > > To unsubscribe from this group, send email to
> >> > > > > oracle-plsql-unsubscr...@googlegroups.com
> >> > > > > For more options, visit this group at
> >> > > > >http://groups.google.com/group/Oracle-PLSQL?hl=en-Hidequoted text
> >> -
>
> >> > > > - Show quoted text -
>
> >> > > --
> >> > > You received this message because you are subscribed to the Google
> >> > > Groups "Oracle PL/SQL" group.
> >> > > To post to this group, send email to Oracle-PLSQL@googlegroups.com
> >> > > To unsubscribe from this group, send email to
> >> > > oracle-plsql-unsubscr...@googlegroups.com
> >> > > For more options, visit this group at
> >> > >http://groups.google.com/group/Oracle-PLSQL?hl=en-Hide quoted text -
>
> >> > - Show quoted text -
>
> >> --
> >> You received this message because you are subscribed to the Google
> >> Groups "Oracle PL/SQL" group.
> >> To post to this group, send email to Oracle-PLSQL@googlegroups.com
> >> To unsubscribe from this group, send email to
> >> oracle-plsql-unsubscr...@googlegroups.com
> >> For more options, visit this group at
> >>http://groups.google.com/group/Oracle-PLSQL?hl=en- Hide quoted text -
>
> - Show quoted text -
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
