Using NVL in Query of Query resulting in error

[alecken]

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:

<cfquery name="GetC2" datasource="#Trim(application.OracDSN)#">

     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,

[Carl Von Stetten · Accepted solution]

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.