Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-5335

"No value was available" in ROW_NUMBER while inserting in foreign temporary table

    XMLWordPrintable

    Details

      Description

      while executing the following query the following error occurs:

      insert into rep149_variables (patientid,variableid,scoretime,varvalue)
      select patientid, code, entertime, 1 as varvalue
      from (
      	select cr.code, ci.patientid, ci.entertime, row_number() over(partition by ci.patientid, ci.codeid order by ci.entertime) as rang
      	from izisprod.S_CodeSystemRef csr
      	join izisprod.S_CodeGroupRef cgr on cgr.CodeSysID = csr.CodeSysID
      	join izisprod.S_CodeRef cr on cr.GroupID = cgr.GroupID and cr.code not in ('HK_GEEN','MZ_GEEN','zMG_fout')
      	join iziswh.P_CodedItem ci on ci.CodeID = cr.CodeID and ci.ItemStatus = 0
      	join rep149_cohort c on c.patientid = ci.patientid
      	where csr.CodeSysID in (16,33)
      ) v
      where v.rang = 1;
      

      2018-05-02 14:03:56,206 ERROR [org.teiid.PROCESSOR] (Worker4_QueryProcessorQueue142) MPLUURNpwXk3 TEIID30019 Unexpected exception for request MPLUURNpwXk3.36: org.teiid.core.TeiidComponentException: TEIID30328 Unable to evaluate ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime): No value was available
      at org.teiid.query.util.CommandContext.getFromContext(CommandContext.java:519)
      at org.teiid.query.eval.Evaluator.internalEvaluate(Evaluator.java:732)
      at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:707)
      at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:331)
      at org.teiid.query.eval.Evaluator.internalEvaluateTVL(Evaluator.java:185)
      at org.teiid.query.eval.Evaluator.evaluateTVL(Evaluator.java:174)
      at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:168)
      at org.teiid.query.processor.relational.SelectNode.nextBatchDirect(SelectNode.java:106)
      at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
      at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
      at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
      at org.teiid.query.processor.relational.SourceState.getTupleBuffer(SourceState.java:244)
      at org.teiid.query.processor.relational.EnhancedSortMergeJoinStrategy.loadLeft(EnhancedSortMergeJoinStrategy.java:227)
      at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:228)
      at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:200)
      at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
      at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
      at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
      at org.teiid.query.processor.relational.SourceState.getTupleBuffer(SourceState.java:244)
      at org.teiid.query.processor.relational.EnhancedSortMergeJoinStrategy.loadLeft(EnhancedSortMergeJoinStrategy.java:227)
      at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:228)
      at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:200)
      at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
      at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146)
      at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
      at org.teiid.query.processor.relational.ProjectIntoNode.nextBatchDirect(ProjectIntoNode.java:140)
      at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
      at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:141)
      at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:148)
      at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:111)
      at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
      at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
      at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:480)
      at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:350)
      at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:47)
      at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:276)
      at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:277)
      at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
      at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
      at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
      at java.lang.Thread.run(Thread.java:745)

      When I only execute the query itself then the results are shown without any error.

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                gadeyne.bram Bram Gadeyne
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: