Using NVL in Query of Query resulting in error

Community Beginner ,
Oct 21, 2014 Oct 21, 2014

Copy link to clipboard

Copied

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,

TOPICS
Getting started

Views

1.2K

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct Answer

Guide , Oct 21, 2014 Oct 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.

Likes

Translate

Translate
Guide ,
Oct 21, 2014 Oct 21, 2014

Copy link to clipboard

Copied

LATEST

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines