Herarical Code Pattern - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Herarical Code Pattern

Author

Topic

asif372
Posting Yak Master

100 Posts

Posted - 2012-12-18 : 15:05:10

I want to work on this Criteria how can it be possible
I have One Table naming ItemInfo with following fields and Data

ITMID is UNIQUE and Auto Generated Field
ITMHEAD is Defining The Parent of Particular Item
ItemLevel is Defining the Level of Herarical pattern
i want to generate ITMCODE by Checking its ItmHEAD and ITMLEVEL
suppose if i want to add a new ItmCode selected Parent ITMCODE is 1101 and selected Parent ITMLEVEL IS 3 then my Generated ITMCODE Should be 1101032

ITMID ITMCODE ITMHEAD ITMLEVEL

1 1 NULL 1
2 11 1 2
3 1101 11 3
4 1101001 1101 4
5 1101002 1101 4
6 1101003 1101 4
7 1101004 1101 4
8 1101006 1101 4
9 1101007 1101 4
10 1101009 1101 4
11 1101010 1101 4
12 1101011 1101 4
13 1101012 1101 4
14 1101013 1101 4
15 1101014 1101 4
16 1101015 1101 4
17 1101016 1101 4
18 1101017 1101 4
19 1101018 1101 4
20 1101020 1101 4
21 1101021 1101 4
22 1101022 1101 4
23 1101023 1101 4
24 1101024 1101 4
25 1101025 1101 4
26 1101028 1101 4
27 1101029 1101 4
28 1101030 1101 4
29 1101031 1101 4

Thanks in Advance

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-12-18 : 17:42:38

I'm not sure I understand your question 100% correct, but try this:


insert into iteminfo (itmcode
                     ,itmhead
                     ,itmlevel
                     )
   select max(ifnull(b.itmcode,a.itmcode*power(10,a.itmlevel)))+1
         ,max(a.itmcode)
         ,max(a.itmlevel)+1
     from iteminfo as a
          left outer join iteminfo as b
                       on b.itmhead=a.itmcode
                      and b.itmlevel=a.itmlevel+1
    where a.itmcode=1101
      and a.itmlevel=3
    group by a.id

The above code assumes all fields are numeric.

ps.: there might be a syntax error (or several), as I currently don't have access to a SQL server - long live notepad :-D

waterduck
Aged Yak Warrior

982 Posts

Posted - 2012-12-18 : 21:58:49

like this?
select max(ITMCODE)+1 from ItemInfo where ITMHEAD = 1101

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources