1 Reply Latest reply on Nov 8, 2013 9:32 AM by Jim Smith

    get datetime in 1st row of another column

    sindhujaTK

      Hi Everyone,

       

      Please anyone help me...

       

      Below is the query and output.

       

      Query:

      SELECT

        WMUMF.BIZDOCTYPEDEF.TYPENAME,

        Table__52.BD_PARTNERORDERNUMBER,

        Table__67.BD_TRACKINGNUMBER,

        WMUMF.BIZDOCATTRIBUTE.STRINGVALUE,

        TO_DATE(TO_CHAR(WMUMF.BIZDOC.DOCTIMESTAMP,'MM/DD/YYYY HH24:MI:SS'),'MM/DD/YYYY HH24:MI:SS')

      FROM

        WMUMF.PARTNER  SENDER,

        WMUMF.PARTNER  RECEIVER,

        WMUMF.BIZDOCTYPEDEF,

        WMUMF.BIZDOC,

        (

        SELECT

            WMUMF.BIZDOCARRAYATTRIBUTE.DOCID,

            WMUMF.BIZDOCARRAYATTRIBUTE.STRINGVALUE BD_PartnerOrderNumber

            FROM

            WMUMF.BIZDOCATTRIBUTEDEF  BIZDOCATTRIBUTEDEF_A1,

            WMUMF.BIZDOCARRAYATTRIBUTE

            WHERE

            WMUMF.BIZDOCARRAYATTRIBUTE.ATTRIBUTEID=BIZDOCATTRIBUTEDEF_A1.ATTRIBUTEID AND

            Bizdocattributedef_A1.Attributename  In  ( 'BD_PartnerOrderNumber'  )

        )  Table__52,

        (

        SELECT

      WMUMF.BIZDOCARRAYATTRIBUTE.DOCID,

      WMUMF.BIZDOCARRAYATTRIBUTE.STRINGVALUE BD_TrackingNumber

      FROM

      WMUMF.BIZDOCATTRIBUTEDEF BIZDOCATTRIBUTEDEF_A1,

      WMUMF.BIZDOCARRAYATTRIBUTE

      WHERE

      WMUMF.BIZDOCARRAYATTRIBUTE.ATTRIBUTEID=BIZDOCATTRIBUTEDEF_A1.ATTRIBUTEID AND

      Bizdocattributedef_A1.Attributename In ( 'BD_TrackingNumber' )

        )  Table__67,

        WMUMF.BIZDOCATTRIBUTEDEF,

        (

        SELECT

            WMUMF.BIZDOCARRAYATTRIBUTE.DOCID,

            WMUMF.BIZDOCARRAYATTRIBUTE.STRINGVALUE BD_SiteID

            FROM

            WMUMF.BIZDOCATTRIBUTEDEF  BIZDOCATTRIBUTEDEF_A1,

            WMUMF.BIZDOCARRAYATTRIBUTE

            WHERE

            WMUMF.BIZDOCARRAYATTRIBUTE.ATTRIBUTEID=BIZDOCATTRIBUTEDEF_A1.ATTRIBUTEID AND

            Bizdocattributedef_A1.Attributename  In  ( 'BD_SiteID'  )

        )  Table__44

      WHERE

        ( WMUMF.BIZDOC.DOCTYPEID=WMUMF.BIZDOCTYPEDEF.TYPEID  )

        AND  ( WMUMF.BIZDOC.RECEIVERID=RECEIVER.PARTNERID  )

        AND  ( SENDER.PARTNERID=WMUMF.BIZDOC.SENDERID  )

        AND  ( WMUMF.BIZDOC.DOCID=WMUMF.BIZDOCATTRIBUTE.DOCID  )

        AND  ( WMUMF.BIZDOCATTRIBUTEDEF.ATTRIBUTEID=WMUMF.BIZDOCATTRIBUTE.ATTRIBUTEID  )

        AND  ( Table__44.DOCID(+)=WMUMF.BIZDOC.DOCID  )

        AND  ( WMUMF.BIZDOC.DOCID=Table__46.DOCID(+)  )

        AND  ( WMUMF.BIZDOC.DOCID=Table__52.DOCID(+)  )

        AND  ( WMUMF.BIZDOC.DOCID=Table__67.DOCID(+)  )

        AND

        (

         WMUMF.BIZDOCTYPEDEF.TYPENAME  IN  ( 'doc_Xml_HoldReleaseNotification_CustomXML_Cisco','doc_Xml_PIP_3A9CancellationRequest_V_11_01'  )

         AND

         SENDER.CORPORATIONNAME  =  'CISCO LSS (E2Open)'

         AND

         RECEIVER.CORPORATIONNAME  =  'FLEXTRONICS'

         AND

         WMUMF.BIZDOCATTRIBUTEDEF.ATTRIBUTENAME  =  'ApplicationMessageType'

         AND

         WMUMF.BIZDOCATTRIBUTE.STRINGVALUE  IN  ( 'H','R','3A9V11.01.00'  )

         AND

         Table__44.BD_SITEID  =  '027'

       

       

        )

        AND

         WMUMF.BIZDOC.DOCTIMESTAMP >= TO_DATE('06/15/2013','MM/DD/YYYY')

         AND WMUMF.BIZDOC.DOCTIMESTAMP <= SYSDATE+1

       

       

      Output:

       

       

      PartnerOrderNumber

      DateTime

      StringValueTracking NumberDocument
      PO11 amH123DOC
      PO12 amH246DOC
      PO13 amR125DOC
      PO14 amH678DOC
      PO25 amH890DOC
      PO26 amR098DOC
      PO27 amH001DOC
      PO28 amR870DOC
      PO39 amH008DOC
      PO310 amH065DOC
      PO311 amR593DOC
      PO312 pmH636DOC
      PO31 pmH978DOC
      PO32 pmR675DOC

       

       

       

       

      But I need like below...

       

       

      PartnerOrderNumberH DateTimeR DateTimeTracking NumberDocument
      PO11 am3 am123DOC
      PO12 am246DOC
      PO24 am6 am678DOC
      PO25 am890DOC
      PO27 am8 am001DOC
      PO39 am11 am008DOC
      PO310 am065DOC
      PO312 pm2 pm636DOC
      PO31 pm978DOC

       

       

      My aim is to get H datetime in one column and R datetime in another column but only in 1st row of H datetime. PO3 has H H R H H R so each R datetime should be in 1st of H datetime.

       

      Thanks in Advance,

      Sindhu