Query of Queries and LIKE operator

[DannoParker]

I am a long time user of query of queries. I changed companies recently, and
now I can't get the like operator to work with query of queries. Is this a
server setting, or am I overlooking the obvious? If I remove the like operator,
the page compiles and runs just fine. Many thanks for any help!!

<CFQUERY NAME="thisQuery" DATASOURCE="#Request.appDSN#">
SELECT thisField
FROM myTable
</CFQUERY>

<CFQUERY NAME="QoQthisQuery" DBTYPE="query">
SELECT thisField
FROM thisQuery
WHERE thisField LIKE 'a%'
</CFQUERY>

Error:

The system has attempted to use an undefined value, which usually indicates a
programming error, either in your code or some system code.

Null Pointers are another name for undefined values.

java.lang.NullPointerException

[Purple Haze]

What Database are you using? My suggestion any time you are having trouble
writing a query, is to use the query builder in the database to write the
query, that way you can refer to its documentation on syntax etc. It also tends
to give you more verbose errors on what it doesn't like.

[DannoParker]

I'm using an Oracle 9i database. The query returns from the database fine, it just breaks when I try to put a like operator in the query of queries.

[Kronin555]

Query of Queries have nothing to do with what database you're using. They run
exclusively in ColdFusion and don't talk to the database, so syntax is
exclusive to what ColdFusion both supports and expects.

As far as I know, ColdFusion doesn't support the LIKE command in Query of
Queries. The SQL that is supported is a relatively-limited subset of what you
can do when talking directly to your database.

[DannoParker]

I found a workaround! If I add a WHERE clause to the parent query (WHERE
thisField = thisField), the query of query works fine with the LIKE operator.
Very strange........I wonder what the nullPointerException means.....the Java
compiler must be getting confused.....just one of those things I guess.......

Thanks for the comments Purple Haze and Kronin555!!

<CFQUERY NAME="thisQuery" DATASOURCE="#Request.appDSN#">
SELECT thisField
FROM myTable
WHERE thisField = thisField
</CFQUERY>

<CFQUERY NAME="QoQthisQuery" DBTYPE="query">
SELECT thisField
FROM thisQuery
WHERE thisField LIKE 'a%'
</CFQUERY>

[Chugglethwaite]

Hi Danno,

I suspect that in the original query there was a NULL value for thisField.
When it came to the QofQ it wouldn't be able to compare the NULL value using
LIKE - hence the Java error that was thrown (NullPointerException).

Adding the line "WHERE thisField = thisField" would effectively eliminate the
NULL values. Another option would be to use Oracle's equivalent of the ISNULL()
function to replace any NULL values with a given value (say a blank string).

Cheers
Andy

[DannoParker]

Andy,

Thanks so much for the reply. That was exactly the problem. If I wrap my
fields with Oracle's version of ISNULL(), which in this example would be
NVL(thisField,'N/A'), everything works perfectly. Oh, for anyone else who
stumbles across this post, don't set it to an empty string. i.e.
NVL(thisField,''). Oracle simply returns it as null again....arghh....

Thanks again for the help!

[DStanten]

By the way, another solution to this would be: WHERE thisField is not null
and thisField LIKE 'a%'.
This is bug 48243, documented here:

[url]http://www.macromedia.com/support/coldfusion/releasenotes/mx/mx61_known_problems[/url]
..html