Re: Using NVL in Query of Query resulting in error

forums_noreply@adobe.com — Tue, 21 Oct 2014 22:18:27 GMT

NVL is an Oracle function, and is not available in ColdFusion Query of Query. If you are trying to check for null values, then use IS NULL or IS NOT NULL. So

WHERE NVL(Rel2_2,' ') <> ' '

AND NVL(p_ln2,' ') = ' '

AND NVL(p_fn2,' ') = ' '

becomes

WHERE Rel2_2 IS NOT NULL

AND p_ln2 IS NULL

AND p_fn2 IS NULL

-Carl V.

Using NVL in Query of Query resulting in error

forums_noreply@adobe.com — Tue, 21 Oct 2014 21:46:43 GMT

I'm still using CF8 and Oracle 11G back-end.

When I use NVL in the query of query I got error....Can't I use NVL to check on null value? Please help

Here is my codes:

SELECT CamID2, rel2_2,p_ln2,p_fn2,ins,l_year

FROM prt_temp

WHERE Ins = 'CC'

AND l_year = '1481'

	AND NVL(Child_LN2,' ') <> ' '
	AND NVL(Child_FN2,' ') <> ' '

</cfquery>

	<cfif GetC2.Recordcount NEQ 0>
	<cfquery name="CheckRel2C2" dbtype="QUERY">
	SELECT CamID2, rel2_2
	FROM GetC2
	WHERE NVL(Rel2_2,' ') <> ' '
	AND NVL(p_ln2,' ') = ' '
	AND NVL(p_fn2,' ') = ' '
	AND Ins = 'CC'
	AND l_year = '1481'
	</cfquery>

</cfif>

The error:

Error Executing Database Query.

Query Of Queries syntax error.

Encountered "NVL ( Rel2_2 ,. Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition,

Adobe Community: Message List - Using NVL in Query of Query resulting in error

Re: Using NVL in Query of Query resulting in error

Using NVL in Query of Query resulting in error