mike mike - 5 months ago 8
SQL Question

Is there a way to order the WHERE conditions that my JPQL query tries?

I have a query

SELECT CAST(p.propertyId AS long) FROM Property p
WHERE p.propertyId IS NOT NULL
AND p.propertyId NOT LIKE '%-%'
AND p.propertyId NOT LIKE '%+%'


That works beautifully because the WHERE conditions successfully filter out all of the non-numeric values for propertyId. But if I try to use the cast as a condition

SELECT p FROM Property p
WHERE p.propertyId IS NOT NULL
AND p.propertyId NOT LIKE '%-%'
AND p.propertyId NOT LIKE '%+%'
AND CAST(p.propertyId AS long) BETWEEN 999999 AND 10000000


it fails with the error
java.sql.SQLException: ORA-01722: invalid number
.

Is there a way to force the last condition to only check of the first three pass?

Answer

If your 1st request filters out all non numeric values you can select from its result:

SELECT p FROM
(
SELECT p1 FROM Property p1
 WHERE p1.propertyId IS NOT NULL
   AND p1.propertyId NOT LIKE '%-%'
   AND p1.propertyId NOT LIKE '%+%'
)
where CAST(p.propertyId AS long) BETWEEN 999999 AND 10000000