5 Replies Latest reply on Oct 14, 2019 2:51 AM by Manoj Lobo

    show the Max Insert date and the event date

    Doreen Hou

      dear all,

       

      below is my query and the result.

      but actually I only want to show 2nd line in the result which insert date is the latest.

      How do I adjust my SQL in order to get the expected result?

      really appreciate for the help.

       

       

      SELECT
      V_D_SHIPMENT.CONS_NO,
      decode(V_F_SHIPMENT_EVENTS.STATUS_CODE,'1200',V_F_SHIPMENT_EVENTS.EVENT_INSERT_DATE,NULL) Insert_Date_1200,
      decode(V_F_SHIPMENT_EVENTS.STATUS_CODE,'1200',V_F_SHIPMENT_EVENTS.EVENT_DATE,NULL) Latest_ETD_1,
      decode(V_F_SHIPMENT_EVENTS.STATUS_CODE,'8800',V_F_SHIPMENT_EVENTS.EVENT_INSERT_DATE,NULL) Insert_Date_8800,
      decode(V_F_SHIPMENT_EVENTS.STATUS_CODE,'8800',V_F_SHIPMENT_EVENTS.EVENT_DATE,NULL) Latest_ETA_1

      FROM
        V_F_SHIPMENT  V_D_SHIPMENT INNER JOIN V_F_SHIPMENT_EVENTS ON (V_D_SHIPMENT.CONS_ID=V_F_SHIPMENT_EVENTS.CONS_ID)

      WHERE
        V_D_SHIPMENT.CONS_NO = '20190905-S-00005'
      GROUP BY
      V_D_SHIPMENT.CONS_NO,
      decode(V_F_SHIPMENT_EVENTS.STATUS_CODE,'1200',V_F_SHIPMENT_EVENTS.EVENT_INSERT_DATE,NULL),
      decode(V_F_SHIPMENT_EVENTS.STATUS_CODE,'1200',V_F_SHIPMENT_EVENTS.EVENT_DATE,NULL),
      decode(V_F_SHIPMENT_EVENTS.STATUS_CODE,'8800',V_F_SHIPMENT_EVENTS.EVENT_INSERT_DATE,NULL),
      decode(V_F_SHIPMENT_EVENTS.STATUS_CODE,'8800',V_F_SHIPMENT_EVENTS.EVENT_DATE,NULL)

       

       

      Current Result


      Expected Result

      CONS_NOINSERT_DATE_1200LAST_ETD_1INSERT_DATE_8800LATEST_ETA_1
      120190905-S-0000510/09/1922/09/1910/09/1924/10/19