| index

View V_CURRENT_ENQUIRY_STATUS

Columns

Name | Type | Optional | Default | Comments
ENQU_ID | NUMBER |  |  |
EVENT_DATE | DATE |  |  |
ENST_CODE | VARCHAR2(80) |  |  |

SQL

    CREATE OR REPLACE VIEW V_CURRENT_ENQUIRY_STATUS AS
    SELECT enqu_id
          ,event_date
          ,enst_code
      FROM (SELECT enqu_id
                  ,event_date
                  ,enst_code
                  ,decode(enst_code
                         ,'INP'
                         ,1
                         ,'SUBMITTED'
                         ,2
                         ,'QUOTED'
                         ,3) AS this_event
                  ,MAX(decode(enst_code
                             ,'INP'
                             ,1
                             ,'SUBMITTED'
                             ,2
                             ,'QUOTED'
                             ,3)) over(PARTITION BY enqu_id) AS max_event
              FROM enquiry_events)
     WHERE this_event = max_event;