Using NVL in EJB-QL or WL-QL - Weblogic

This is a discussion on Using NVL in EJB-QL or WL-QL - Weblogic ; Hello, I need to create an EJB-QL or WL-QL query similar to this SQL query: SELECT * FROM thisTable WHERE NVL(thisColumn, 'null') = NVL(?1, 'null') This is because a search of: SELECT * FROM thisTable WHERE thisColumn = ?1 where ...

+ Reply to Thread
Results 1 to 2 of 2

Thread: Using NVL in EJB-QL or WL-QL

  1. Using NVL in EJB-QL or WL-QL

    Hello,

    I need to create an EJB-QL or WL-QL query similar to this SQL query:

    SELECT *
    FROM thisTable
    WHERE NVL(thisColumn, 'null') = NVL(?1, 'null')

    This is because a search of:

    SELECT *
    FROM thisTable
    WHERE thisColumn = ?1

    where ?1 = NULL does not return any results (NULL can never equal anything). Nor does the same query if the database column equals null and the passed in string is "". This is a very common problem in SQL that is resolved by using NVL.

    Any suggestions?

  2. RE: Using NVL in EJB-QL or WL-QL

    OK, I've got a workaround, but man, does this look ugly. We should really think about putting a NVL or DECODE into EJB-QL:

    SELECT OBJECT(o)
    FROM ThisEB AS o
    WHERE (o.thisColumn = ?1 OR ((?1 = '' OR ?1 IS NULL) AND o.thisColumn IS NULL))

    I figured I'd post the result out there in case someone else came across the same problem.

    Thanks,
    Leo

+ Reply to Thread