Tom,
I am pretty sure that Query of queries using short circuit evaluation.
This should allow you to test for NULL before actually performing the
LIKE evaluation:
WHERE
....
AND
(
A IS NOT NULL
AND
A LIKE '%foo%'
)
If A IS NULL then the first part of the AND statement should fail and
the rest should not be fired. The LIKE clause should only every be
evaluated if the first part is also true (A IS NOT NULL).
......................
Ben Nadel
Certified Advanced ColdFusion MX7 Developer
www.bennadel.com
Need ColdFusion Help?
www.bennadel.com/ask-ben/
-----Original Message-----
From: Tom King [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 07, 2006 8:25 AM
To: CF-Talk
Subject: RE: Head Banging Access LIKE error in QoQ
Agh!
that's it in a nutshell..
:(
Oh for a solution....*sigh*
T
-----Original message-----
From: "Ben Nadel" [EMAIL PROTECTED]
Date: Tue, 7 Nov 2006 13:21:22 +0000
To: CF-Talk [email protected]
Subject: RE: Head Banging Access LIKE error in QoQ
> Tom,
>
> I think that is definitely the case. Check out this blog post:
>
> http://www.bennadel.com/index.cfm?dax=blog:144.view
>
> I have found that ColdFusion query of queries throws errors on NULL
> fields when using LIKE, UPPER(), and LOWER().
>
>
> ......................
> Ben Nadel
> Certified Advanced ColdFusion MX7 Developer www.bennadel.com
>
> Need ColdFusion Help?
> www.bennadel.com/ask-ben/
>
> -----Original Message-----
> From: Tom King [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, November 07, 2006 8:07 AM
> To: CF-Talk
> Subject: RE: Head Banging Access LIKE error in QoQ
>
> That's closer I think: I DO have empty fields interspersed with
> populated fields in the column..
> Just need to get the syntax right now..
>
> T
>
> -----Original message-----
> From: "Katz, Dov B \(IT\)" [EMAIL PROTECTED]
> Date: Tue, 7 Nov 2006 12:44:19 +0000
> To: CF-Talk [email protected]
> Subject: RE: Head Banging Access LIKE error in QoQ
>
> > Just a guess:
> >
> > You might have an empty phototag value somewhere
> >
> > Try
> >
> > WHERE ("" & phototag) LIKE "%#trim(variables.thistag)#%'
> >
> > Or, try ""+phototag (not sure of syntax)
> >
> > Does this fix the issue?
> >
> > Dov
> >
> >
> > -----Original Message-----
> > From: Tom King [mailto:[EMAIL PROTECTED]
> > Sent: Tuesday, November 07, 2006 7:30 AM
> > To: CF-Talk
> > Subject: Head Banging Access LIKE error in QoQ
> >
> > OK, This have been driving me nuts...
> >
> > request.photolist exists, and has phototag etc fields.
> > variables.thistag is also fine:
> > BUT, this code:
> >
> > <cfquery dbtype="query" name="GetTaggedPhotos">
> > SELECT * from request.photolist WHERE phototag LIKE
> > '%#trim(variables.thistag)#%';
> > </cfquery>
> >
> > Throws this very unhelpful 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
> >
> > Using this works, but obvious doesn't return the same recordset, as
> > it's looking for an exact match:
> > <cfquery dbtype="query" name="GetTaggedPhotos">
> > SELECT * from request.photolist WHERE phototag =
> > '#variables.thistag#';
> > </cfquery>
> >
> > Help???????
> >
> > Ta
> >
> >
> >
> >
> >
> >
> >
> >
> >
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:259423
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
