1 Reply Latest reply: Oct 21, 2014 3:18 PM by Carl Von Stetten RSS

    Using NVL in Query of Query resulting in error

    alecken Community Member
    alecken Oct 21, 2014 2:46 PM

      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,