title: Using nested case statements somewhat like a loop?
body: <pre><code>TASKS         DATE
  NOD 1 OUT     2/5/10
  NOD 1 IN      NULL 
  NOD 2 OUT     2/10/10
  NOD 2 IN      2/11/10
  NOD 3 OUT     2/15/10
  NOD 3 IN      NULL
  </code></pre>
  
  <p>What I need to do is find Find the greatest NOD, then if the NOD has an IN date, place 'NO NOD' in the row, but if the greatest NOD has been sent OUT but not received back IN, calculate the number of days since it was sent out.  I am attempting to nest the case statements, but what is happening is that it is seeing the NOD 2 OUT then IN and placing the 'No NOD' text.  I can't figure out how to get it to find the greatest NOD.  Here is what I have so far (I am newer to SQL, so I apologize if anything is way off and I know my formatting is bad, but I can see what goes with what better this way.):</p>
  
  <pre><code>CASE 
               WHEN COUNT(DISTINCT DATE_NOD_TBL.COMPLETED_DATE) = '0' THEN 'No NOD Out'
               WHEN  MAX(DATE_NOD_TBL_2.COMPLETED_DATE) IS NOT NULL AND MAX(DATE_NOD_TBL_5.COMPLETED_DATE) IS NOT NULL 
               THEN    (CASE 
                        WHEN MAX(DATE_NOD_TBL_3.COMPLETED_DATE) IS NOT NULL AND MAX(DATE_NOD_TBL_6.COMPLETED_DATE) IS NOT NULL 
                        THEN    (CASE
                                 WHEN MAX(DATE_NOD_TBL_4.COMPLETED_DATE) IS NOT NULL AND MAX(DATE_NOD_TBL_7.COMPLETED_DATE) IS NOT NULL 
                                 THEN 'No NOD Out_3'
                                 ELSE TO_CHAR(TRUNC(SYSDATE - (MAX(DATE_NOD_TBL.COMPLETED_DATE)))) 
                                 END)
                        ELSE TO_CHAR(TRUNC(SYSDATE - (MAX(DATE_NOD_TBL.COMPLETED_DATE))))
                        END) 
               ELSE TO_CHAR(TRUNC(SYSDATE - (MAX(DATE_NOD_TBL.COMPLETED_DATE))))
               END   AS "Days Since Last NOD", 
  </code></pre>
  
  <p>I have aliased the tables so that I touch the table once per each task, as such: </p>
  
  <pre><code>DATE_NOD_TBL contains all OUT tasks
  DATE_NOD_TBL_2 is NOD 1 OUT
  DATE_NOD_TBL_3 is NOD 2 OUT
  DATE_NOD_TBL_4 is NOD 3 OUT
  DATE_NOD_TBL_5 is NOD 1 IN
  DATE_NOD_TBL_6 is NOD 2 IN
  DATE_NOD_TBL_7 is NOD 3 IN
  </code></pre>
  
  <p>I am using 10g Oracle SQL.  </p>
  
  <p>Entire Query:</p>
  
  <pre><code>SELECT DSKMTB_ACTIVITY_TYPE.ACTIVITY_TYPE_LABEL       AS "Project Type",
    MAX(DATE_ISSUED_TBL_3.ASSIGNED_STAFF_ID)                  AS "Project Manager",
    AGENCY_INTEREST_ALT.ALTERNATE_AI_ID                          AS "Farm ID #",
    MTB_PARISH_COUNTY.PARISH_OR_COUNTY_DESC             AS "County",
    MAX(DISTINCT AGENCY_INTEREST.MASTER_AI_NAME)        AS "Operation Name",
    MAX(DATE_RECEIVED_TBL.COMPLETED_DATE)                   AS "Date Received",
    MAX(DATE_NOD_TBL.COMPLETED_DATE)                            AS "NOD Date",
    COUNT(DISTINCT DATE_NOD_TBL.COMPLETED_DATE)         AS "# of NOD's",
  
  --//days since last NOD
   CASE
      WHEN GREATEST(MAX(DATE_NOD_TBL_2.COMPLETED_DATE),
                    MAX(DATE_NOD_TBL_3.COMPLETED_DATE), 
                    MAX(DATE_NOD_TBL_4.COMPLETED_DATE)) &lt;
           GREATEST(MAX(DATE_NOD_TBL_5.COMPLETED_DATE), 
                    MAX(DATE_NOD_TBL_6.COMPLETED_DATE), 
                    MAX(DATE_NOD_TBL_7.COMPLETED_DATE)) 
           THEN TO_CHAR(TRUNC(SYSDATE - (MAX(DATE_NOD_TBL.COMPLETED_DATE))))
      ELSE 'No NOD Out'
  END AS "Days Since Last NOD",
  
  
    --//net time
    CASE WHEN 
      COUNT(DISTINCT DATE_NOD_TBL.COMPLETED_DATE) = '0' 
        THEN TRUNC(MAX(SYSDATE - DATE_RECEIVED_TBL.COMPLETED_DATE))
      ELSE
    TRUNC(MAX((SYSDATE - DATE_RECEIVED_TBL.COMPLETED_DATE) - 
    --//issued NOD #1/NOD #1 Information Received
    NVL(((CASE WHEN DATE_NOD_TBL_5.STATUS_CODE IS NULL THEN SYSDATE ELSE DATE_NOD_TBL_5.COMPLETED_DATE END) - DATE_NOD_TBL_2.COMPLETED_DATE),'0') -
    --//issued NOD #2/NOD #2 Information Received
    NVL(((CASE WHEN DATE_NOD_TBL_6.STATUS_CODE IS NULL THEN SYSDATE ELSE DATE_NOD_TBL_6.COMPLETED_DATE END) - DATE_NOD_TBL_3.COMPLETED_DATE),'0') -  
    --//issued NOD #3/NOD #3 Information Received
    NVL(((CASE WHEN DATE_NOD_TBL_7.STATUS_CODE IS NULL THEN SYSDATE ELSE DATE_NOD_TBL_7.COMPLETED_DATE END) - DATE_NOD_TBL_4.COMPLETED_DATE),'0')   
    )) END
    AS "Net Time",
  
  
    TRUNC(MAX(SysDate - DATE_RECEIVED_TBL.COMPLETED_DATE))  AS "Gross Time",
    MAX(DATE_SITEASSESSMENT_TBL.COMPLETED_DATE)             AS "Site Visit"
  FROM DSK_CENTRAL_FILE
  INNER JOIN ACTIVITY_TASK_LIST DATE_ISSUED_TBL
  ON DATE_ISSUED_TBL.MASTER_AI_ID       = DSK_CENTRAL_FILE.MASTER_AI_ID
  AND DATE_ISSUED_TBL.INT_DOC_ID        = DSK_CENTRAL_FILE.INT_DOC_ID
  AND DATE_ISSUED_TBL.REFERENCE_TASK_ID = '171'
  AND DATE_ISSUED_TBL.STATUS_CODE      IS NULL
  INNER JOIN ACTIVITY_TASK_LIST DATE_ISSUED_TBL_2
  ON DATE_ISSUED_TBL_2.MASTER_AI_ID       = DSK_CENTRAL_FILE.MASTER_AI_ID
  AND DATE_ISSUED_TBL_2.INT_DOC_ID        = DSK_CENTRAL_FILE.INT_DOC_ID
  AND DATE_ISSUED_TBL_2.REFERENCE_TASK_ID = '163'
  AND DATE_ISSUED_TBL_2.STATUS_CODE      IS NULL
  INNER JOIN ACTIVITY_TASK_LIST DATE_ISSUED_TBL_3
  ON DATE_ISSUED_TBL_3.MASTER_AI_ID       = DSK_CENTRAL_FILE.MASTER_AI_ID
  AND DATE_ISSUED_TBL_3.INT_DOC_ID        = DSK_CENTRAL_FILE.INT_DOC_ID
  AND DATE_ISSUED_TBL_3.REFERENCE_TASK_ID = '262'
  INNER JOIN ACTIVITY_TASK_LIST DATE_RECEIVED_TBL
  ON DATE_RECEIVED_TBL.MASTER_AI_ID       = DSK_CENTRAL_FILE.MASTER_AI_ID
  AND DATE_RECEIVED_TBL.INT_DOC_ID        = DSK_CENTRAL_FILE.INT_DOC_ID
  AND DATE_RECEIVED_TBL.INT_DOC_ID        = DATE_ISSUED_TBL.INT_DOC_ID
  AND DATE_RECEIVED_TBL.MASTER_AI_ID      = DATE_ISSUED_TBL.MASTER_AI_ID
  AND DATE_RECEIVED_TBL.REFERENCE_TASK_ID = '571'
  AND DATE_RECEIVED_TBL.STATUS_CODE       = '001'
  AND DATE_RECEIVED_TBL.COMPLETED_DATE   IS NOT NULL
  LEFT OUTER JOIN ACTIVITY_TASK_LIST DATE_NOD_TBL
  ON DATE_NOD_TBL.MASTER_AI_ID        = DSK_CENTRAL_FILE.MASTER_AI_ID
  AND DATE_NOD_TBL.INT_DOC_ID         = DSK_CENTRAL_FILE.INT_DOC_ID
  AND DATE_NOD_TBL.INT_DOC_ID         = DATE_ISSUED_TBL.INT_DOC_ID
  AND DATE_NOD_TBL.MASTER_AI_ID       = DATE_ISSUED_TBL.MASTER_AI_ID
  AND DATE_NOD_TBL.REFERENCE_TASK_ID IN ( '393','394','395')
  LEFT OUTER JOIN ACTIVITY_TASK_LIST DATE_NOD_TBL_8
  ON DATE_NOD_TBL_8.MASTER_AI_ID        = DSK_CENTRAL_FILE.MASTER_AI_ID
  AND DATE_NOD_TBL_8.INT_DOC_ID         = DSK_CENTRAL_FILE.INT_DOC_ID
  AND DATE_NOD_TBL_8.INT_DOC_ID         = DATE_ISSUED_TBL.INT_DOC_ID
  AND DATE_NOD_TBL_8.MASTER_AI_ID       = DATE_ISSUED_TBL.MASTER_AI_ID
  AND DATE_NOD_TBL_8.REFERENCE_TASK_ID  IN('602','603','608')
  LEFT OUTER JOIN ACTIVITY_TASK_LIST DATE_NOD_TBL_2
  ON DATE_NOD_TBL_2.MASTER_AI_ID        = DSK_CENTRAL_FILE.MASTER_AI_ID
  AND DATE_NOD_TBL_2.INT_DOC_ID         = DSK_CENTRAL_FILE.INT_DOC_ID
  AND DATE_NOD_TBL_2.INT_DOC_ID         = DATE_ISSUED_TBL.INT_DOC_ID
  AND DATE_NOD_TBL_2.MASTER_AI_ID       = DATE_ISSUED_TBL.MASTER_AI_ID
  AND DATE_NOD_TBL_2.REFERENCE_TASK_ID = '393'
  LEFT OUTER JOIN ACTIVITY_TASK_LIST DATE_NOD_TBL_3
  ON DATE_NOD_TBL_3.MASTER_AI_ID        = DSK_CENTRAL_FILE.MASTER_AI_ID
  AND DATE_NOD_TBL_3.INT_DOC_ID         = DSK_CENTRAL_FILE.INT_DOC_ID
  AND DATE_NOD_TBL_3.INT_DOC_ID         = DATE_ISSUED_TBL.INT_DOC_ID
  AND DATE_NOD_TBL_3.MASTER_AI_ID       = DATE_ISSUED_TBL.MASTER_AI_ID
  AND DATE_NOD_TBL_3.REFERENCE_TASK_ID = '394'
  LEFT OUTER JOIN ACTIVITY_TASK_LIST DATE_NOD_TBL_4
  ON DATE_NOD_TBL_4.MASTER_AI_ID        = DSK_CENTRAL_FILE.MASTER_AI_ID
  AND DATE_NOD_TBL_4.INT_DOC_ID         = DSK_CENTRAL_FILE.INT_DOC_ID
  AND DATE_NOD_TBL_4.INT_DOC_ID         = DATE_ISSUED_TBL.INT_DOC_ID
  AND DATE_NOD_TBL_4.MASTER_AI_ID       = DATE_ISSUED_TBL.MASTER_AI_ID
  AND DATE_NOD_TBL_4.REFERENCE_TASK_ID = '395'
  LEFT OUTER JOIN ACTIVITY_TASK_LIST DATE_NOD_TBL_5
  ON DATE_NOD_TBL_5.MASTER_AI_ID        = DSK_CENTRAL_FILE.MASTER_AI_ID
  AND DATE_NOD_TBL_5.INT_DOC_ID         = DSK_CENTRAL_FILE.INT_DOC_ID
  AND DATE_NOD_TBL_5.INT_DOC_ID         = DATE_ISSUED_TBL.INT_DOC_ID
  AND DATE_NOD_TBL_5.MASTER_AI_ID       = DATE_ISSUED_TBL.MASTER_AI_ID
  AND DATE_NOD_TBL_5.REFERENCE_TASK_ID = '603'
  LEFT OUTER JOIN ACTIVITY_TASK_LIST DATE_NOD_TBL_6
  ON DATE_NOD_TBL_6.MASTER_AI_ID        = DSK_CENTRAL_FILE.MASTER_AI_ID
  AND DATE_NOD_TBL_6.INT_DOC_ID         = DSK_CENTRAL_FILE.INT_DOC_ID
  AND DATE_NOD_TBL_6.INT_DOC_ID         = DATE_ISSUED_TBL.INT_DOC_ID
  AND DATE_NOD_TBL_6.MASTER_AI_ID       = DATE_ISSUED_TBL.MASTER_AI_ID
  AND DATE_NOD_TBL_6.REFERENCE_TASK_ID = '602'
  LEFT OUTER JOIN ACTIVITY_TASK_LIST DATE_NOD_TBL_7
  ON DATE_NOD_TBL_7.MASTER_AI_ID        = DSK_CENTRAL_FILE.MASTER_AI_ID
  AND DATE_NOD_TBL_7.INT_DOC_ID         = DSK_CENTRAL_FILE.INT_DOC_ID
  AND DATE_NOD_TBL_7.INT_DOC_ID         = DATE_ISSUED_TBL.INT_DOC_ID
  AND DATE_NOD_TBL_7.MASTER_AI_ID       = DATE_ISSUED_TBL.MASTER_AI_ID
  AND DATE_NOD_TBL_7.REFERENCE_TASK_ID = '608'
  LEFT OUTER JOIN ACTIVITY_TASK_LIST DATE_SITEASSESSMENT_TBL
  ON DATE_SITEASSESSMENT_TBL.MASTER_AI_ID       = DSK_CENTRAL_FILE.MASTER_AI_ID
  AND DATE_SITEASSESSMENT_TBL.INT_DOC_ID        = DSK_CENTRAL_FILE.INT_DOC_ID
  AND DATE_SITEASSESSMENT_TBL.INT_DOC_ID        = DATE_ISSUED_TBL.INT_DOC_ID
  AND DATE_SITEASSESSMENT_TBL.MASTER_AI_ID      = DATE_ISSUED_TBL.MASTER_AI_ID
  AND DATE_SITEASSESSMENT_TBL.REFERENCE_TASK_ID = '201'
  INNER JOIN DSKMTB_ACTIVITY_TYPE
  ON DSKMTB_ACTIVITY_TYPE.ACTIVITY_CATEGORY_CODE  = DSK_CENTRAL_FILE.ACTIVITY_CATEGORY_CODE
  AND DSKMTB_ACTIVITY_TYPE.ACTIVITY_CLASS_CODE    = DSK_CENTRAL_FILE.ACTIVITY_CLASS_CODE
  AND DSKMTB_ACTIVITY_TYPE.ACTIVITY_TYPE_CODE     = DSK_CENTRAL_FILE.ACTIVITY_TYPE_CODE
  AND DSKMTB_ACTIVITY_TYPE.PROGRAM_CODE           = DSK_CENTRAL_FILE.PROGRAM_CODE
  AND DSKMTB_ACTIVITY_TYPE.PROGRAM_CODE           = 'CF'
  AND DSKMTB_ACTIVITY_TYPE.ACTIVITY_CATEGORY_CODE = 'ATH'
  LEFT OUTER JOIN AGENCY_INTEREST_ALT
  ON AGENCY_INTEREST_ALT.MASTER_AI_ID   = DSK_CENTRAL_FILE.MASTER_AI_ID
  AND AGENCY_INTEREST_ALT.END_DATE     IS NULL
  AND AGENCY_INTEREST_ALT.USER_GROUP_ID = 'CFO'
  INNER JOIN AGENCY_INTEREST
  ON AGENCY_INTEREST.MASTER_AI_ID = AGENCY_INTEREST_ALT.MASTER_AI_ID
  AND AGENCY_INTEREST.INT_DOC_ID  = AGENCY_INTEREST_ALT.INT_DOC_ID
  INNER JOIN SUBJ_ITEM_LOC_GOV_WITHIN
  ON SUBJ_ITEM_LOC_GOV_WITHIN.MASTER_AI_ID = DSK_CENTRAL_FILE.MASTER_AI_ID
  AND SUBJ_ITEM_LOC_GOV_WITHIN.INT_DOC_ID  = AGENCY_INTEREST_ALT.INT_DOC_ID
  INNER JOIN MTB_PARISH_COUNTY
  ON MTB_PARISH_COUNTY.PARISH_OR_COUNTY_CODE = SUBJ_ITEM_LOC_GOV_WITHIN.PARISH_OR_COUNTY_CODE
  WHERE DSK_CENTRAL_FILE.PROGRAM_CODE          = 'CF'
  AND DSK_CENTRAL_FILE.INT_DOC_ID             != 0
  AND AGENCY_INTEREST_ALT.INT_DOC_ID           = 0
  GROUP BY DSKMTB_ACTIVITY_TYPE.ACTIVITY_TYPE_LABEL,
    AGENCY_INTEREST_ALT.ALTERNATE_AI_ID,
    MTB_PARISH_COUNTY.PARISH_OR_COUNTY_DESC
  ORDER BY DSKMTB_ACTIVITY_TYPE.ACTIVITY_TYPE_LABEL,
  MAX(DATE_RECEIVED_TBL.COMPLETED_DATE)
  </code></pre>