SQL numbering based on a pattern

if you don't have starting values in a numbering column already, how do you get the sequences started?

The numbering column is blank and I need a select statement to get numbering based on two other columns.

yes, but you have only explained what you want to happen for the 2nd occurrence of a node_level.

Looks like you want to add 100 each time a node_level repeats
you haven't explained how you are getting your starting values though.

why is node_level 1  1000  and node_level 2  1100  the first time?

Node_leve 1 default to 1000.  
Then the first time node_level =2 the value will be 1100...  
Then the first time node_level =3 the value will be 1X10   X is the numbering for previous node_level =2.  
Then the first time node_level =4 the value will be 1XY1   X is the numbering for previous node_level =2.  

Thanks.

So, effectively 1,2,3,4 are hardcoded for starting points

Since 1 and 2 are fixed and 3 and 4 derived from them then they aren't really dynamic either

Where does tree_seq com into play?

If I think I understand isn't your original sample data wrong?

Shouldn't
2                        5                     1200
be
2                        5                     1210

and
3                        6                      1210
be
3                        6                      1220

Try this:


Select

(Select cast(count(*) as char(1)) from YOURTABLE T2
   where T2.Tree_Seq <= T1.Tree_Seq
       and T2.node_level = T1.node_level
       and T1.node_level = 1)
||
(Select cast(count(*) as char(1)) from YOURTABLE T2
   where T2.Tree_Seq <= T1.Tree_Seq
       and T2.node_level = T1.node_level
       and T1.node_level = 2)
(Select cast(count(*) as char(1)) from YOURTABLE T2
   where T2.Tree_Seq <= T1.Tree_Seq
       and T2.node_level = T1.node_level
       and T1.node_level = 3)
||
(Select cast(count(*) as char(1)) from YOURTABLE T2
   where T2.Tree_Seq <= T1.Tree_Seq
       and T2.node_level = T1.node_level
       and T1.node_level = 4)
From YOURTABLE T1

Try again:
Select
(Select cast(count(*) as char(1)) from YOURTABLE T2
   where T2.Tree_Seq <= T1.Tree_Seq
       and T2.node_level = T1.node_level
       and T1.node_level = 1)
||
(Select cast(count(*) as char(1)) from YOURTABLE T2
   where T2.Tree_Seq <= T1.Tree_Seq
       and T2.node_level = T1.node_level
       and T1.node_level = 2)
||
(Select cast(count(*) as char(1)) from YOURTABLE T2
   where T2.Tree_Seq <= T1.Tree_Seq
       and T2.node_level = T1.node_level
       and T1.node_level = 3)
||
(Select cast(count(*) as char(1)) from YOURTABLE T2
   where T2.Tree_Seq <= T1.Tree_Seq
       and T2.node_level = T1.node_level
       and T1.node_level = 4)
From YOURTABLE T1

Sample data is correct.  2                        5                     1200   because level =2 will always have 1X00.

since the first numberings are fixed you can embed them at the lowest level instead of deriving them

SELECT node_level,
       tree_seq,
       first_numbering + (100 * rn)numbering
FROM (SELECT node_level,
             tree_seq,
             CASE
                 WHEN node_level = 1 THEN 1000
                 WHEN node_level = 2 THEN 1100
                 WHEN node_level = 3 THEN 1110
                 WHEN node_level = 4 THEN 1111
                 ELSE 0
             END
                 first_numbering,
             ROW_NUMBER() OVER (PARTITION BY node_level ORDER BY tree_seq) - 1 rn
      FROM yourtable)
ORDER BY tree_seq

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:

dqmq:  That is not the pattern.  if node_level =3 then the pattern will be 1XX0.  


SET DEFINE OFF;
Insert into T
   (NODE_LEVEL, TREE_SEQ)
 Values
   (4, 4);
Insert into T
   (NODE_LEVEL, TREE_SEQ)
 Values
   (2, 5);
Insert into T
   (NODE_LEVEL, TREE_SEQ)
 Values
   (3, 6);
Insert into T
   (NODE_LEVEL, TREE_SEQ)
 Values
   (1, 1);
Insert into T
   (NODE_LEVEL, TREE_SEQ)
 Values
   (2, 2);
Insert into T
   (NODE_LEVEL, TREE_SEQ)
 Values
   (3, 3);
COMMIT;

sdstuber:  Your solution looks good so far.  Let me test more data...

I'm not sure what you're trying to say by 1X10 and 1XY1

your starting points appear to be constants

ok, I must have guessed your intent correctly then.  :)

X can be 1, 2, 3,....  Yes, we are talking about numbers.  

sdstuber:   Here is data.  Node_level =4 tree_seq should be 1311 but your result is 1211.  


SET DEFINE OFF;
Insert into T
   (NODE_LEVEL, TREE_SEQ)
 Values
   (4, 4);
Insert into T
   (NODE_LEVEL, TREE_SEQ)
 Values
   (2, 5);
Insert into T
   (NODE_LEVEL, TREE_SEQ)
 Values
   (3, 6);
Insert into T
   (NODE_LEVEL, TREE_SEQ)
 Values
   (2, 7);
Insert into T
   (NODE_LEVEL, TREE_SEQ)
 Values
   (3, 8);
Insert into T
   (NODE_LEVEL, TREE_SEQ)
 Values
   (4, 9);
Insert into T
   (NODE_LEVEL, TREE_SEQ)
 Values
   (1, 1);
Insert into T
   (NODE_LEVEL, TREE_SEQ)
 Values
   (2, 2);
Insert into T
   (NODE_LEVEL, TREE_SEQ)
 Values
   (3, 3);
COMMIT;

why would the second level 4 be 1311?

there is only one prior level 4 before it

please explain how you want your data to increment

1311 because its level 2 (seq =7) is 1300 and its level 3 (seq =8)  is 1310.  

I don't understand

please explain the entire set of sequencing rules.

how do seq7 and seq8  relate to it?

The whole data set is like a parent child relationship, if you look at this way.  Implied,  seq7 is actually the parent of seq8 and seq8 is the parent of seq9.  My initial post on spec is not complete.  

We look another way, if seq7 =2 and number =1300 then seq8 =3 and number will be 1310 ...  
Now if seq9  = 4 then numbering will be 1311, if seq=2 then numbering will be 1400.  

I'm with sdstuber.  I don't understand.

I still don't see where tab_seq comes into play.

I thought it was node occurrence = number offset.  Where nodes occurence=numbers ABCD  

so
node 1 = 1000
node 2 = 0100
node 3 = 0010
node 4 = 0001

 adn every node occurrence increments the offeset so second node 3 would be 0020.

Please set us straight.

Please see column description.  Then you will see the logic behind numbering.  


LEVEL      DESCRIPTION      TREE_SEQ NUMBERING
                  
1      WORLD            1      1000
2      USA            2      1100
3      TEXAS            3      1110
4      HOUSTON            4      1111
2      INDIA            5      1200
3      INDIAN STATE1      6      1210
2      CHINA            7      1300
3      CHINA PROVINCE1      8      1310
4       PROVINCE1 CITY      9      1311

sdstuber:  Looks good.  I am going to test with more data.  

Thanks for the help!