QofQ and SQL 5.0

[Tyran]

I have just had a conversation with my hosting company about one of my apps
suddenly throwing up errors when I perform a SQL similar to the following:

<cfquery name="cmacsRS_contactLeadDetails" dbtype="query">
SELECT contact_id FROM cmacsRS_allContactLeads
WHERE contact_company LIKE '%#variables.distinctCompanyName#%'
</cfquery>

If I do this as a normal query it works fine, but it now falls over even
though the page has been up and runniing for over a year. Has anyone else
experienced this? Is there a work round in the Admin or SQL 5.0 itself?

My datasets are quite large so I was using this method to speed things up.

Cheers!

[CF_Oracle]

What the error message?
By the way inquery of query this string you are filtering for is case sencitive.

[Tyran]

I didn't realise about the case sensitivity, cheers.

It's coming back with a null pointer error which is very strange! Convert the same SQL to standard and it works fine.

[Dan Bracuk]

What's the value of your variable?

If you hard code that value into your Q of Q, does it still crash?

[Tyran]

That was my first thought that the session was somehow the cause, but I substituted 'A%' and it still came back with the same error.

[Adam Cameron]

> That was my first thought that the session was somehow the cause, but I substituted 'A%' and it still came back with the same error.

I suspect you have some null data in cmacsRS_allContactLeads (and back when
the QoQ worked, you did NOT have null data in it).

QoQ gets very confused very quickly when there are nulls in the query it's
querying; they don't even have to be in columns you're referencing in the
SQL, but I'd check contact_company and contact_id first.

--
Adam

[Tyran]

I've checked those fields already but haven't come acroos that QofQ can fal over if there is ANY null in the database. Thanks, I'll investigate that one and get back to everyone.

:beer;

[Adam Cameron]

> QofQ can fal over if there is ANY null in the database.

Just to clarify, not in the *database*, in the query you're performing the
QoQ on (ie: only the data CF can see in the <cfquery dbtype="query">
statement).

--
Adam