0

I am trying to match the records having MAX(WORKITEM_ID) but some PROCESS_INSTANCE_ID are not coming though they are present in WORKITEM table.

You can see their is records present in WORKITEM table for PROCESS_INSTANCE_ID=73641

select WI.WORKITEM_ID as WORKITEMID,
       PI.PROCESS_INSTANCE_ID PIID,WS.WS_LABEL as WSNAME

from  WORKITEM WI,PROCESSINSTANCE PI,TASKASSIGNMENT_V4TA,
       SDI_WIDGET_WORKSTEP WS

where WS.WS_NAME = WI.WORKSTEP_NAME and 
      TA.PROCESS_INSTANCE_ID = PI.PROCESS_INSTANCE_ID and 
      PI.status in ('PI_ACTIVATED','PI_COMPLETED')and 
      WI.PROCESS_INSTANCE_ID = PI.PROCESS_INSTANCE_ID
      and WI.status in ('I_ASSIGNED','I_AVAILABLE')
      and WI.WORKITEM_ID = 
(select MAX(WORKITEM_ID)from (select WITEM.WORKITEM_ID from WORKITEM WITEM
 where **WITEM.PROCESS_INSTANCE_ID = WI.PROCESS_INSTANCE_ID**) as WORKITEMID)
  • 2
    Can you supply some sample data for each of these tables? Any answer you get here will also be rewriting that old-school join syntax... just don't use it. – FilamentUnities May 29 '15 at 16:19
1

You didn't state which version of SQL Server you are using, but I'll assume it is 2005 or later, and therefore, will suggest using a CTE . And, as Skagalak mentioned, I've rewritten the joins.

I wasn't sure whether you wanted the max value from the whole WORKITEM table or if you wanted to group it by PROCESS_INSTANCE_ID. The example below groups the MAX(WORKITEM_ID). If you don't want that, just remove the GROUP BY clause from the CTE.

CAVEAT: I've checked for syntax, but since I couldn't test against a data set I give no guarantees that this will give the desired results. It is merely an example of how you might want to structure the query.

;WITH cte AS (SELECT MAX(WORKITEM_ID) AS max_workitem_id, PROCESS_INSTANCE_ID 
                  FROM WORKITEM
                  GROUP BY PROCESS_INSTANCE_ID
                 )
    SELECT WI.WORKITEM_ID AS WORKITEMID,
           PI.PROCESS_INSTANCE_ID PIID,WS.WS_LABEL AS WSNAME
    FROM WORKITEM AS WI
        INNER JOIN PROCESSINSTANCE AS [PI] ON WI.PROCESS_INSTANCE_ID = [PI].PROCESS_INSTANCE_ID
        INNER JOIN TASKASSIGNMENT_V4 AS TA ON TA.PROCESS_INSTANCE_ID = [PI].PROCESS_INSTANCE_ID
        INNER JOIN SDI_WIDGET_WORKSTEP AS WS ON WS.WS_NAME = WI.WORKSTEP_NAME
        INNER JOIN cte ON WI.WORKITEM_ID = cte.max_workitem_id AND cte.PROCESS_INSTANCE_ID = WI.PROCESS_INSTANCE_ID
    WHERE [PI].status IN ('PI_ACTIVATED','PI_COMPLETED')    
          AND WI.status IN ('I_ASSIGNED','I_AVAILABLE')
| improve this answer | |
  • In this particular case a CTE is no more than a syntactic sugar. You could easily use a normal subselect instead of it and thereby avoid relying on the OP's using a 2005+ version. Just saying. – Andriy M Jun 26 '15 at 13:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.