May 7, 2009 at 2:42 pm
Well, you've got me stumped. Either you didn't run the entire statement including the parts that set each counter up by 1, or there's an insurmountable difference between MS SQL 2000 and the Sybase that you're using.
Just to show that I'd posted working code, here's my result:
number cat
----------- ----
31 A
32 A
91 B
92 B
0 C
0 C
0 C
33 A
34 A
93 B
94 B
0 C
0 C
0 C
(14 row(s) affected)
Best of luck working it out.
--John
May 7, 2009 at 3:09 pm
I took an screener so you can see the result for yourself. I think there are som syntax differences regarding how you work with variables and updates.
SQL // not working
SET @counterA = number = @counterA+1
Sybase // working
set number=number+@counterA, @counterA=@counter+1
/ Magnus
May 7, 2009 at 10:27 pm
m.berggren (5/6/2009)
Finally i got i to work!Was searching the internet back and forward regarding this clustered index issue, found a lot of information and tried a lot without any luck.
Then i changed the code you wrote Jeff and to the following. Little unsecure why it´s working now but is has something to do with changing from
SET @counter = number = @counter+1toset number=number+@counter, @counter=@counter+1;
declare @counter int
select @counter=0
update custom.table1
set number=number+@counter, @counter=@counter+1;
// Magnus
Looks good and would even work in SQL Server.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 8, 2009 at 1:06 am
Jeff:
Is this what you ment by an "anchor" ?
I wonder if there is any way to use this within an CASE statemant ?
/ Magnus
May 11, 2009 at 1:48 am
Finally!
Got it to work after many hour´s 🙂
///drop Table #custTable
create Table #custTable (num int, cat char(1) )
insert #custTable values (0,'A')
insert #custTable values (0,'A')
insert #custTable values (0,'B')
insert #custTable values (0,'B')
insert #custTable values (0,'C')
insert #custTable values (0,'C')
insert #custTable values (0,'C')
insert #custTable values (0,'A')
insert #custTable values (0,'A')
insert #custTable values (0,'B')
insert #custTable values (0,'B')
insert #custTable values (0,'C')
insert #custTable values (0,'C')
insert #custTable values (0,'C')
DECLARE @counterA int
select @counterA=20 -- starting value for this category
DECLARE @counterB int
select @counterB=90 -- starting value for this category
UPDATE #custTable
Set
@counterA = case
when cat = 'A' then @counterA + 1
else num+@counterA
end
,@counterB = case
when cat = 'B' then @counterB + 1
else num+@counterB
end
,num = case
when cat = 'A' then num+@counterA
when cat = 'B' then num+@counterB
else num
end
select * from #custTable
// Magnus
May 11, 2009 at 2:49 am
Hi,
This works perfekt. I´m thinking of maybe making this more dynamic in some way. Let´s say you don´t know how many "cat" youré having. Let´s say you have for exampel 200 different "cat". Then you have to create like 200 of the statements below manually and then make CASE statement for every INSERT.
Any idea how this should work ?
DECLARE @counterA0 int
select @counterA0=0 -- starting value for this category
DECLARE @counterA1 int
select @counterA1=100
DECLARE @counterA2
select @counterA2=200 int
DECLARE @counterA3 int
select @counterA3=300 int
DECLARE @counterA4 int
select @counterA4=400 int
...
...
DECLARE @counterA200 int
select @counterA200=20000 int
// Magnus
May 11, 2009 at 5:47 am
m.berggren (5/11/2009)
Hi,This works perfekt. I´m thinking of maybe making this more dynamic in some way. Let´s say you don´t know how many "cat" youré having. Let´s say you have for exampel 200 different "cat". Then you have to create like 200 of the statements below manually and then make CASE statement for every INSERT.
Any idea how this should work ?
DECLARE @counterA0 int
select @counterA0=0 -- starting value for this category
DECLARE @counterA1 int
select @counterA1=100
DECLARE @counterA2
select @counterA2=200 int
DECLARE @counterA3 int
select @counterA3=300 int
DECLARE @counterA4 int
select @counterA4=400 int
...
...
DECLARE @counterA200 int
select @counterA200=20000 int
// Magnus
Heh, oh my, goodness no! Not the way to do it. If it were me, I'd have a clustered index by "Cat" and whatever else was in the table to maintain temporal order. I'd do the update (which can then be done with a single variable) and be done with it. When you want to select from the table, use the order you really want, and everything will come out just fine.
Update in one order... select in another.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 11, 2009 at 5:56 am
Oh yeah... there's one other thing. You were told a long time ago that you simply can't guarantee the correct order for this type of update unless you have both a temporal related column AND a clustered index. The clustered index must be in the correct order (CAT, then temporal col) in order for you to pull this off correctly.
None of your example code contains that temporal column. Please identify include the temporal column in your example code before we do anything else because, without it, your code will be about as accurate as a beauty pageant.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 11, 2009 at 10:11 am
m.berggren (5/11/2009)
Finally!Got it to work after many hour´s 🙂
///drop Table #custTable
create Table #custTable (num int, cat char(1) )
insert #custTable values (0,'A')
insert #custTable values (0,'A')
insert #custTable values (0,'B')
insert #custTable values (0,'B')
insert #custTable values (0,'C')
insert #custTable values (0,'C')
insert #custTable values (0,'C')
insert #custTable values (0,'A')
insert #custTable values (0,'A')
insert #custTable values (0,'B')
insert #custTable values (0,'B')
insert #custTable values (0,'C')
insert #custTable values (0,'C')
insert #custTable values (0,'C')
DECLARE @counterA int
select @counterA=20 -- starting value for this category
DECLARE @counterB int
select @counterB=90 -- starting value for this category
UPDATE #custTable
Set
@counterA = case
when cat = 'A' then @counterA + 1
else num+@counterA
end
,@counterB = case
when cat = 'B' then @counterB + 1
else num+@counterB
end
,num = case
when cat = 'A' then num+@counterA
when cat = 'B' then num+@counterB
else num
end
select * from #custTable
// Magnus
The only difference I see from what I posted is that instead of assigning the value of a counter to itself directly, you assign it the sum of itself and column "num". Be aware that this works only because each row starts with a zero value. If you've included the addition operation ("num + @counterA" or "num + @counterB") only because straight-up assignment to a local variable doesn't work in Sybase, you may want to try using zero rather than num ("0 + @counterA" or "0 + @counterB").
As Jeff reminds you, the order in which rows within each category are assigned values is indeterminate as you haven't specified an "order by" nor used the specific techniques that would derive that order from a clustered index.
Also, in a "real" application I would avoid colapsing the information this way (making the range of the serial number dependant on the cat value). It introduces other problems -- how do you assign a value to a new row after all the others are already numbered? What do you do when you've filled up the range for a cat and don't want to impinge on the defined range for the next cat?
May 26, 2009 at 3:37 am
Now I have an excel sheet where you fill out item name, main cat and sub cat and then
I save it as an .csv file and the result of the .csv file i shown below.
High top 1, Shoes, High tops
High top 2, Shoes, High tops
High top 1, Shoes, High tops
Sandal 1, Shoes, Sandals
Sandal 2, Shoes, Sandals
Sandal 3, Shoes, Sandals
Boot 1, Shoes, Sandals
Boot 2, Shoes, Sandals
Boot 3, Shoes, Sandals
BS SHIRT 1, SHIRTS, BLACK SHIRTS
BS SHIRT 2, SHIRTS, BLACK SHIRTS
BS SHIRT 1, SHIRTS, BLACK SHIRTS
WS 1, SHIRTS, WHITE SHIRTS
WS 2, SHIRTS, WHITE SHIRTS
WS 3, SHIRTS, WHITE SHIRTS
BRS 1, SHIRTS, BROWN SHIRTS
BRS 2, SHIRTS, BROWN SHIRTS
BRS 3, SHIRTS, BROWN SHIRTS
After this i import it to my database with this statement:
CREATE TABLE custom.items (
mi_seq INT PRIMARY KEY NOT NULL DEFAULT AUTOINCREMENT,
obj_num varchar (16),
item_name varchar (16),
main_cat CHAR (25),
sub_cat CHAR (25)
);
INPUT INTO custom.items FROM C:\items.csv format ASCII (mi_seq, obj_num, item_name, main_cat, sub_cat);
Result of this in the database:
1,,High top 1, Shoes, High tops
2,,High top 2, Shoes, High tops
3,,High top 1, Shoes, High tops
4,,Sandal 1, Shoes, Sandals
5,,Sandal 2, Shoes, Sandals
6,,Sandal 3, Shoes, Sandals
7,,Boot 1, Shoes, Sandals
8,,Boot 2, Shoes, Sandals
9,,Boot 3, Shoes, Sandals
10,,BS SHIRT 1, SHIRTS, BLACK SHIRTS
11,,BS SHIRT 2, SHIRTS, BLACK SHIRTS
12,,BS SHIRT 1, SHIRTS, BLACK SHIRTS
13,,WS 1, SHIRTS, WHITE SHIRTS
14,,WS 2, SHIRTS, WHITE SHIRTS
15,,WS 3, SHIRTS, WHITE SHIRTS
16,,BRS 1, SHIRTS, BROWN SHIRTS
17,,BRS 2, SHIRTS, BROWN SHIRTS
18,,BRS 3, SHIRTS, BROWN SHIRTS
Now, here is the part that is tricky. I want to use the field obj_num to catagories the database with the
main cat and sub cat as shown below. So every main cat starts with 10000 and
every sub cat under main cat starts with 100 as shows in the example. I could do all of this
manually with the code written in previous posts in this tread, but it would take for ever if I have like hundred
different sub cat
Sometimes there are 10 different sub cat and sometimes there are 100 different ones. If this wasn´t the case could have predefined it.
Example what I´m trying to accomplish: (These are just examples and don´t exits in the database
// Main Header // Main catagory and // Sub Header // Sub catagory)
1,10000,***SHOES***,, // Main Header // Main catagory
2,10100,**HIGH TOPS**,, // Sub Header // Sub catagory
3,10101,High top 1, Shoes, High tops
4,10102,High top 2, Shoes, High tops
5,10103,High top 1, Shoes, High tops
6,10200,**SANDALS**,, // Sub Header //
7,10201,Sandal 1, Shoes, Sandals
8,10202,Sandal 2, Shoes, Sandals
9,10203,Sandal 3, Shoes, Sandals
10,10300,**BOOTS**,, // Sub Header //
11,10301,Boot 1, Shoes, Sandals
12,10302,Boot 2, Shoes, Sandals
13,10303,Boot 3, Shoes, Sandals
14,20000,***SHIRTS***,, // Main Header // Main catagory
15,20100,**BLACK SHIRTS**,, // Sub Header // Sub catagory
16,20101,BS SHIRT 1, SHIRTS, BLACK SHIRTS
17,20102,BS SHIRT 2, SHIRTS, BLACK SHIRTS
18,20103,BS SHIRT 1, SHIRTS, BLACK SHIRTS
19,20200,**WHITE SHIRTS**,, // Sub Header //
20,20201,WS 1, SHIRTS, WHITE SHIRTS
21,20202,WS 2, SHIRTS, WHITE SHIRTS
22,20203,WS 3, SHIRTS, WHITE SHIRTS
23,20300,**BROWN SHIRTS**,, // Sub Header //
24,20301,BRS 1, SHIRTS, BROWN SHIRTS
25,20302,BRS 2, SHIRTS, BROWN SHIRTS
26,20303,BRS 3, SHIRTS, BROWN SHIRTS
// Regards
Viewing 10 posts - 31 through 39 (of 39 total)
You must be logged in to reply to this topic. Login to reply