2 Replies Latest reply: Sep 18, 2012 7:56 AM by k.alexandrov@group-vista.ru RSS

    SQL condition not working

    882128
      Hi,

      The following query works as expected while running from Oracle database.
      the same query is on a query zone of type F1-DE-QUERY but doesnt work the same way.
      i know the problem is in using the replace(PHONE,')','') LIKE .... statement only.
      is there any restriction on using the sql functions from query zone.
      please help.


      SELECT DISTINCT A.PER_ID as person, EMAILID as email, PHONE as phone
      FROM
      CI_PER P
      JOIN CI_ACCT_PER A ON P.PER_ID=A.PER_ID
      LEFT OUTER JOIN CI_PER_PHONE PH ON PH.PER_ID = P.PER_ID
      WHERE
      (replace(PHONE,' ','') LIKE '%' || :F1 || '%' or
      replace(PHONE,'(','') LIKE '%' || :F1 || '%' or
      replace(PHONE,')','') LIKE '%' || :F1 || '%' or
      replace(PHONE,'-','') LIKE '%' || :F1 || '%'
      )
      AND
      LENGTH(:F1) > 4;

      Thanks.