This discussion is archived
3 Replies Latest reply: Feb 7, 2013 8:52 AM by Christian Erlinger RSS

Search Three Columns @ once

Moazam Shareef Explorer
Currently Being Moderated
Hi Guyz,

I'm creating a tubular form based on the below table. having different columns i put one non database item called DIS_SRCH single item. i want to search the item based on DIS_SRCH item
First Name Varchar2(30)
Second Name Varchar2(30)
Third Name Varchar2(30)
SET_BLOCK_PROPERTY('MN_EMP', DEFAULT_WHERE, 'UPPER(FIRST_NAME)LIKE UPPER(:CTRL.DIS_SRCH)||''%''');
i can search with the first name but how can search with the second and third name if not found in first?

im using forms 6i database 9i.
  • 1. Re: Search Three Columns @ once
    Christian Erlinger Guru
    Currently Being Moderated
    SQL 101: How do I add another optional condition to my query? Ever heard of OR ?
    select [...]
    from tab
    where col1 like 'TEXT%'
    or col2 like 'TEXT%'
    or [...]
    cheers
  • 2. Re: Search Three Columns @ once
    Moazam Shareef Explorer
    Currently Being Moderated
    Thx christian, i forget the OR clause in oracle ;)

    the below query in Sql working fine
    SELECT EMPID,NATIONALITY,COMPANY FROM MN_EMPLOYEE
    WHERE NAME1_ENG LIKE '&name1%' or name2_eng
    like '&NAME2%' OR NAME3_ENG LIKE '&NAME3%'
    but when i use in forms on WBP TRIGGER its querying all records anything wrong with my below code? non error while compiling...
    GO_ITEM('MN_EMPLOYEE.NAME1_ENG');
    SET_BLOCK_PROPERTY('MN_EMPLOYEE', DEFAULT_WHERE, 'NAME1_ENG LIKE (:CTRL.DIS_SRCH||''%''OR NAME2_ENG LIKE (:CTRL.DIS_SRCH||''%''''');
    EXECUTE_QUERY;
    GO_ITEM('ctrl.DIS_SRCH');
  • 3. Re: Search Three Columns @ once
    Christian Erlinger Guru
    Currently Being Moderated
    That where clause isn't correct:
    SET_BLOCK_PROPERTY('MN_EMPLOYEE', DEFAULT_WHERE, 'NAME1_ENG LIKE (:CTRL.DIS_SRCH||''%''OR NAME2_ENG LIKE (:CTRL.DIS_SRCH||''%''''');
    You open 2 brackets but never close them. So IMHO this should look like
    SET_BLOCK_PROPERTY('MN_EMPLOYEE', DEFAULT_WHERE, 'NAME1_ENG LIKE :CTRL.DIS_SRCH||''%'' OR NAME2_ENG LIKE :CTRL.DIS_SRCH||''%''');
    cheers

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points