You can use this to test different values. This should give you an idea of
what you need to do.
DECLARE
last_name_prefix VARCHAR2( 300 ) := 'Ab';
first_name_prefix VARCHAR2( 300 ) := 'G';
first_name VARCHAR2( 300 );
last_name VARCHAR2( 300 );
BEGIN
FOR cur1
IN ( SELECT *
FROM (SELECT 'Adam-salami' lname, 'Fred' fname FROM DUAL
UNION ALL
SELECT 'Adam' lname, 'George' fname FROM DUAL
UNION ALL
SELECT 'Adam' lname, 'Geolab' fname FROM DUAL
UNION ALL
SELECT 'Ableman' lname, 'Geolab' fname FROM DUAL) mytab
WHERE fname >= (CASE WHEN first_name_prefix IS NULL THEN ' '
ELSE first_name_prefix END)
AND lname >= (CASE WHEN last_name_prefix IS NULL THEN ' ' ELSE
last_name_prefix END) )
LOOP
DBMS_OUTPUT.put_line( ' last=' || cur1.lname ||'first=' || cur1.fname
);
END LOOP;
END;
On Wed, Jul 28, 2010 at 11:31 AM, John H <ho...@chesterfield.gov> wrote:
> 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-Hidequotedtext
> > >> -
> >
> > >> > > > - 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
>
--
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
