3 Replies Latest reply: Apr 4, 2014 4:04 AM by Madhu.149 RSS

    Query with "where" condition piped.

    fabietto771

      Hi every one,

      i've a doubt about a query with have a where condition with "||" pipe.

       

      SELECT val1 , val2 FROM mytab.table A WHERE
                   A.x || A.y LIKE :B1 || '%' AND ROWNUM < 2;

       

      Can someone explain this query.

       

      thank you very much.

        • 1. Re: Query with "where" condition piped.
          Hoek

          When concatenated the values of a.x and a.y will be considered as one column value instead of two.

          • 2. Re: Query with "where" condition piped.
            KarK

            Hi,

             

            See the below:

             


            SCOTT@krman>WITH t(col1,col2) AS
            (
            SELECT 'aaa','bbb' FROM DUAL UNION ALL
            SELECT 'aaa','bbb' FROM DUAL UNION ALL
            SELECT 'aaa','bbb' FROM DUAL UNION ALL
            SELECT 'aaabbb','bbb' FROM DUAL
            )
            SELECT * FROM t WHERE t.col1||t.col2 LIKE &1 ;  -- here t.col1||t.col2 is formed as a single string  'aaabbb'

             

            Enter value for 1: 'aaabbb'
            old   8: SELECT * FROM t WHERE t.col1||t.col2 LIKE &1
            new   8: SELECT * FROM t WHERE t.col1||t.col2 LIKE 'aaabbb'

             

            OUTPUT:

             

            COL1   COL
            ------ ---
            aaa    bbb
            aaa    bbb
            aaa    bbb

            • 3. Re: Query with "where" condition piped.
              Madhu.149

              Hi,

               

              Your are just comparing the concatenated values with the concatenated User input values.

               

              Try the below example. you will understand

               

              Schema: HR

              Table: EMPLOYEES

               

              select FIRST_NAME, LAST_NAME from EMPLOYEES
              where first_name||last_name like :b||'%' ;
              

               

              Check with the below values for the bind variable

               

              John

              JohnC

              JohnS

               

               

              Regards,

              Madhu