This discussion is archived
2 Replies Latest reply: Sep 18, 2012 5:56 AM by k.alexandrov@group-vista.ru RSS

SQL condition not working

882128 Newbie
Currently Being Moderated
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.

Legend

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