3 Replies Latest reply: Feb 7, 2013 10:52 AM by Christian Erlinger RSS

    Search Three Columns @ once

    Moazam Shareef
      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
          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
            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
              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