3 Replies Latest reply: Apr 15, 2014 2:36 AM by Rajesh S R RSS

    while loop data order with Sequence id

      Hi ,

       

      we are trying to add sequence to below raw table using Master table. Sequence number is required

      Table: ( Below table having details of each Leg  Anode [i.e. Start] & Znode [i.e End] details )

      ID,LegID,Anode,Znode

      S1,MSB2,N2,N3

      S1,MSB1,N1,N2

      S1,MSB4,N4,N5

      S1,MSB3,N3,N4

      S2,MSB2,N2,N3

      S2,MSB5,N3,N6

       

      MasterTable: ( This Master table having details of start & End details )

      ID, StartNode, EndNode

      S1,N1,N5

      S2,N2,N6

       

      we required data with sequence ID in below format

      Output:

      ID,Cktid, SequenceID

      S1,MSB1,1

      S1,MSB2,2

      S1,MSB3,3

      S1,MSB4,4

      S2,MSB2,1

      S2,MSB5,2

       

      Can any one help,  Thank's in advance.

       

      regards

      Rajesh

        • Re: while loop data order with Sequence id
          anbu cheliyan

          You can get sequence number using sql

           

          select ID,LegID, row_number() over(partition by ID order by LegID) from Table

            • Re: while loop data order with Sequence id
              Massimo Grossi

              1.png

               

              source:

              load * inline [

              ID,LegID,Anode,Znode

              S1,MSB2,N2,N3

              S1,MSB1,N1,N2

              S1,MSB4,N4,N5

              S1,MSB3,N3,N4

              S2,MSB2,N2,N3

              S2,MSB5,N3,N6

              ];

               

              table:

              load

              AutoNumber(ID & '-' & LegID, ID) as Sequence,

              *

              Resident source

              order by ID, LegID;

               

              DROP Table source;

                • Re: while loop data order with Sequence id

                  Thanks for update...

                  LegId is not in sequence always .

                   

                    Our logic is need to get the sequence number  using ANode -> Z Node traversing.

                          From Master table select the ID,Start Node  & Logic  in source table is

                              1. Select  row using ID ( Value#S1) & Anode ( Value#N1)

                                                  For above selection need to return Sequence as "1"

                                                   i.e.  ID LegID Sequence

                                                          S1 MSB1 1

                              2. For Second sequence Legid , Business Logic:  Znode of previous sequence will be Anode in current sequence. In Below Case MSB3  will come as Second Sequence.

                                                           S1 MSB3 2

                                                           S1 MSB5 3

                                                           S1 MSB2 4

                   

                   

                  MasterTable: ( This Master table having details of start & End details )

                  ID, StartNode, EndNode

                  S1,N1,N5

                  S2,N2,N6

                   

                  Below is the example ,

                   

                  source:                                      

                  ID,LegID,Anode,Znode

                  S1,MSB3,N2,N3

                  S1,MSB1,N1,N2

                  S1,MSB2,N4,N5

                  S1,MSB5,N3,N4

                  S2,MSB101,N2,N3

                  S2,MSB6,N3,N6

                   

                  And final out put will be

                  ID,LegID,Anode,Znode , Sequence

                  S1,MSB1,N1,N2,1

                  S1,MSB3,N2,N3,2

                  S1,MSB5,N3,N4,3

                  S1,MSB2,N4,N5,4

                  S2,MSB101,N2,N3,1

                  S2,MSB6,N3,N6,2

                   

                  regards

                  Rajesh