1 Reply Latest reply on Mar 14, 2013 9:19 PM by shawkins

    org.teiid.core.TeiidComponentException: TEIID30328 Unable to evaluate u.scopeid: No value was available

    nsabina

      One of our queries is throwing this error on the following Select Node:

       

      SelectNode(60) output=[u.usermask, RTRIM(u.usermask)] (u.scopeid IN (SELECT SN5.NEXTREC FROM (SELECT SN5.* FROM (SELECT * FROM chorus_time.timetable WHERE period = 30000) AS chorus_refresh_expression, (SELECT * FROM SECURITY.SCPNEXT) AS SN5) AS SN5 WHERE (SN5.SYSID = ?) AND (SN5.SCOPEID IN (SELECT SN4.NEXTREC FROM (SELECT SN4.* FROM (SELECT * FROM chorus_time.timetable WHERE period = 30000) AS chorus_refresh_expression, (SELECT * FROM SECURITY.SCPNEXT) AS SN4) AS SN4 WHERE (SN4.SYSID = ?) AND (SN4.SCOPEID IN (SELECT SN3.NEXTREC FROM (SELECT SN3.* FROM (SELECT * FROM chorus_time.timetable WHERE period = 30000) AS chorus_refresh_expression, (SELECT * FROM SECURITY.SCPNEXT) AS SN3) AS SN3 WHERE (SN3.SYSID = ?) AND (SN3.SCOPEID IN (SELECT SN2.NEXTREC FROM (SELECT SN2.* FROM (SELECT * FROM chorus_time.timetable WHERE period = 30000) AS chorus_refresh_expression, (SELECT * FROM SECURITY.SCPNEXT) AS SN2) AS SN2 WHERE (SN2.SYSID = ?) AND (SN2.SCOPEID = ?))))))))) OR (u.scopeid IN (SELECT SN4.NEXTREC FROM (SELECT SN4.* FROM (SELECT * FROM chorus_time.timetable WHERE period = 30000) AS chorus_refresh_expression, (SELECT * FROM SECURITY.SCPNEXT) AS SN4) AS SN4 WHERE (SN4.SYSID = ?) AND (SN4.SCOPEID IN (SELECT SN3.NEXTREC FROM (SELECT SN3.* FROM (SELECT * FROM chorus_time.timetable WHERE period = 30000) AS chorus_refresh_expression, (SELECT * FROM SECURITY.SCPNEXT) AS SN3) AS SN3 WHERE (SN3.SYSID = ?) AND (SN3.SCOPEID IN (SELECT SN2.NEXTREC FROM (SELECT SN2.* FROM (SELECT * FROM chorus_time.timetable WHERE period = 30000) AS chorus_refresh_expression, (SELECT * FROM SECURITY.SCPNEXT) AS SN2) AS SN2 WHERE (SN2.SYSID = ?) AND (SN2.SCOPEID = ?))))))) OR (u.scopeid IN (SELECT SN3.NEXTREC FROM (SELECT SN3.* FROM (SELECT * FROM chorus_time.timetable WHERE period = 30000) AS chorus_refresh_expression, (SELECT * FROM SECURITY.SCPNEXT) AS SN3) AS SN3 WHERE (SN3.SYSID = ?) AND (SN3.SCOPEID IN (SELECT SN2.NEXTREC FROM (SELECT SN2.* FROM (SELECT * FROM chorus_time.timetable WHERE period = 30000) AS chorus_refresh_expression, (SELECT * FROM SECURITY.SCPNEXT) AS SN2) AS SN2 WHERE (SN2.SYSID = ?) AND (SN2.SCOPEID = ?))))) OR (u.scopeid IN (SELECT SN2.NEXTREC FROM (SELECT SN2.* FROM (SELECT * FROM chorus_time.timetable WHERE period = 30000) AS chorus_refresh_expression, (SELECT * FROM SECURITY.SCPNEXT) AS SN2) AS SN2 WHERE (SN2.SYSID = ?) AND (SN2.SCOPEID = ?))) OR (u.scopeid = ?)

       

      Complete query is rather complicated however there is only one view involved - SECURITY.SCPNEXT and u.scopeid is one of the columns of the actual table).

       

      Following alter view is executed at startup:

       

      alter view SECURITY.SCPNEXT as SELECT t.sysid, t.scopeid, t.nextrec FROM ( SELECT n.sysid, n.scopeid, n.nextrec FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT n ) AS t

       

      I see following values are passed to org.teiid.query.util.CommandContext.getFromContext(CommandContext.java:427) where exception is thrown.

       

      variableMap =

      {$param/pos0=DE29, $param/pos1=DE29, $param/pos2=DE29, $param/pos3=DE29, $param/pos4=DE29, $param/pos5=CFLDIV, $param/pos6=DE29, $param/pos7=DE29, $param/pos8=DE29, $param/pos9=CFLDIV, $param/pos10=DE29, $param/pos11=DE29, $param/pos12=CFLDIV, $param/pos13=DE29, $param/pos14=CFLDIV, $param/pos15=CFLDIV}

       

      variable = u.scopeid

      since variable is not in the context, exception is thrown:

       

      throw new TeiidComponentException(QueryPlugin.Event.TEIID30328, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30328, expression, QueryPlugin.Util.getString("Evaluator.no_value"))); //$NON-NLS-1$

       

      What can possibly cause the error? View definition ?