Skip to main content
A
alecken
Known Participant
October 21, 2014
Answered

Using NVL in Query of Query resulting in error

  • October 21, 2014
  • 1 reply
  • 1696 views

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,

This topic has been closed for replies.
Correct answer Carl Von Stetten

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.

1 reply

Carl Von Stetten
Carl Von StettenCorrect answer
Legend
October 21, 2014

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.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices