June 10, 2011 at 2:01 pm
SKYBVI (6/9/2011)
ALZDBA (6/9/2011)
Please post the full ddl of your object tcsINVTB00026_AttribAsgn(i.e. script its create statement including all indexes and constraints )
USE [RTW]
GO
/****** Object: Table [dbo].[tcsINVTB00026_AttribAsgn] Script Date: 06/09/2011 09:28:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[tcsINVTB00026_AttribAsgn](
[tcsFLST_AttribGroup] [char](21) NOT NULL,
[tcsFLST_ItemAtrib] [char](21) NOT NULL,
[ITEMNMBR] [char](31) NOT NULL,
[SEQNUMBR] [int] NOT NULL,
[DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PKtcsINVTB00026_AttribAsgn] PRIMARY KEY NONCLUSTERED
(
[tcsFLST_AttribGroup] ASC,
[tcsFLST_ItemAtrib] ASC,
[ITEMNMBR] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Your script doesn't include the ddl for your AK3tcsINVTB00026_AttribAsgn.
Deducting from the name AK3 would be alternate key 3 = unique index/constraint)
Please script all indexes and constraints !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 10, 2011 at 2:10 pm
SKYBVI (6/10/2011)
Don Urquhart (6/10/2011)
It looks to me like you are inserting only the ITEMNUMBER field, with no values for the key fields (tcsFLST_AttribGroup,[tcsFLST_ItemAtrib) which can not be NULL. You need to have some way of specifying values for these fields, either by assigning them yourself somehow, or by making them identities.I have data for tcsFLST_AttribGroup and tcsFLST_ItemAtrib...
How do i insert all 3 columns values at a time( itemnmbr values will be derived from the query but other 2 column vlaues are fixed and I know)
For ex...each insert in itemnmbr column values should have 'x' in tcsFLST_AttribGroup
and 'y' in tcsFLST_ItemAtrib
How to do that.
Regards,
Sushant
You could use something like:
Insert into RTW.dbo.tcsINVTB00026_AttribAsgn(tcsFLST_AttribGroup,
tcsFLST_ItemAtrib, ITEMNMBR)
Select 'x', 'y', ITEMNMBR from IV00101 where ITEMNMBR not in
(
Select ITEMNMBR from dbo.tcsINVTB00026_AttribAsgn
)
and ITEMTYPE = 1 and ITEMNMBR not like '%-%';
Don Urquhart
June 10, 2011 at 2:12 pm
@ ALZDBA
USE [RTW]
GO
/****** Object: Index [AK3tcsINVTB00026_AttribAsgn] Script Date: 06/10/2011 16:12:41 ******/
CREATE UNIQUE NONCLUSTERED INDEX [AK3tcsINVTB00026_AttribAsgn] ON [dbo].[tcsINVTB00026_AttribAsgn]
(
[tcsFLST_AttribGroup] ASC,
[tcsFLST_ItemAtrib] ASC,
[SEQNUMBR] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
June 10, 2011 at 2:32 pm
Don Urquhart (6/10/2011)
SKYBVI (6/10/2011)
Don Urquhart (6/10/2011)
It looks to me like you are inserting only the ITEMNUMBER field, with no values for the key fields (tcsFLST_AttribGroup,[tcsFLST_ItemAtrib) which can not be NULL. You need to have some way of specifying values for these fields, either by assigning them yourself somehow, or by making them identities.I have data for tcsFLST_AttribGroup and tcsFLST_ItemAtrib...
How do i insert all 3 columns values at a time( itemnmbr values will be derived from the query but other 2 column vlaues are fixed and I know)
For ex...each insert in itemnmbr column values should have 'x' in tcsFLST_AttribGroup
and 'y' in tcsFLST_ItemAtrib
How to do that.
Regards,
Sushant
You could use something like:
Insert into RTW.dbo.tcsINVTB00026_AttribAsgn(tcsFLST_AttribGroup,
tcsFLST_ItemAtrib, ITEMNMBR)
Select 'x', 'y', ITEMNMBR from IV00101 where ITEMNMBR not in
(
Select ITEMNMBR from dbo.tcsINVTB00026_AttribAsgn
)
and ITEMTYPE = 1 and ITEMNMBR not like '%-%';
Ohh, same error again 🙁
Regards
Sushant Kumar
MCTS,MCP
June 10, 2011 at 2:59 pm
SKYBVI (6/10/2011)
Don Urquhart (6/10/2011)
SKYBVI (6/10/2011)
Don Urquhart (6/10/2011)
It looks to me like you are inserting only the ITEMNUMBER field, with no values for the key fields (tcsFLST_AttribGroup,[tcsFLST_ItemAtrib) which can not be NULL. You need to have some way of specifying values for these fields, either by assigning them yourself somehow, or by making them identities.I have data for tcsFLST_AttribGroup and tcsFLST_ItemAtrib...
How do i insert all 3 columns values at a time( itemnmbr values will be derived from the query but other 2 column vlaues are fixed and I know)
For ex...each insert in itemnmbr column values should have 'x' in tcsFLST_AttribGroup
and 'y' in tcsFLST_ItemAtrib
How to do that.
Regards,
Sushant
You could use something like:
Insert into RTW.dbo.tcsINVTB00026_AttribAsgn(tcsFLST_AttribGroup,
tcsFLST_ItemAtrib, ITEMNMBR)
Select 'x', 'y', ITEMNMBR from IV00101 where ITEMNMBR not in
(
Select ITEMNMBR from dbo.tcsINVTB00026_AttribAsgn
)
and ITEMTYPE = 1 and ITEMNMBR not like '%-%';
Ohh, same error again 🙁
Just saw the index for AK3tcsINVTB00026_AttribAsgn & it include field SEQNUMBR which is also not assigned in your query. You would need to include a value for it as well, with the combination of the three keys being unique. If the values for the tcsFLST_AttribGroup and
tcsFLST_ItemAtrib fields are hard coded, the value for SEQNUMBR would have to be unique.
Don Urquhart
June 11, 2011 at 9:46 am
In your query you are only checking on itemnumber.
The index is on three columns, including sequencenumber, the other two are the same columns a with the PK except itemnumber.
You should modify your query to test the full index columns of the pk as well as all columns of the AK3.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 14, 2011 at 11:03 am
ALZDBA (6/11/2011)
In your query you are only checking on itemnumber.The index is on three columns, including sequencenumber, the other two are the same columns a with the PK except itemnumber.
You should modify your query to test the full index columns of the pk as well as all columns of the AK3.
Ok, So,tht means I have to put all 4 column values in the query..
Is it possible that I generate a random number and put in SEQNMBR values thru the query?
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
June 15, 2011 at 12:00 am
Of course you could use the rownumber or rank functions to just generate a sequence number, but IMHO that column must have had a reason and usage rules during design of your data model.
The rank may help out actually starting seq from 1 per "other column combination".
Check books online.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 16, 2011 at 2:44 pm
Yes, i checked out the seqnumbr values, each value goes on increasing by 16384..
That means I have to check the max value of seqnumbr and then increment it by 16384 each time...
I tried doing it by this code :---
Insert into RTW.dbo.tcsINVTB00026_AttribAsgn(ITEMNMBR,tcsFLST_AttribGroup,tcsFLST_ItemAtrib,SEQNUMBR)
VALUES ( 'XP1171','ITEM TYPE','SALES INVENTORY','811352064')
--(*811352064 = max seqnumbr value + 16384)
I was able to insert that particular 'XP1171' item number..:)
Is there a way to automate it in the original query :-
Select ITEMNMBR from IV00101 where ITEMNMBR not in
(
Select ITEMNMBR from dbo.tcsINVTB00026_AttribAsgn
)
and ITEMTYPE = 1 and ITEMNMBR not like '%-%'
Itemnmbr from above query, Other 2 column values fixed as 'ITEM TYPE','SALES INVENTORY' and then seqnumbr is max value + increment of 16384 each time...
Thanks...
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
June 16, 2011 at 11:49 pm
How about this:
Select ITEMNMBR
, ROW_NUMBER() over (order by (select 1)) * 16384 as SeqNumbr
from IV00101
where ITEMNMBR not in ( Select ITEMNMBR
from dbo.tcsINVTB00026_AttribAsgn )
and ITEMTYPE = 1
and ITEMNMBR not like '%-%'
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 17, 2011 at 8:43 am
ALZDBA (6/16/2011)
How about this:
Select ITEMNMBR
, ROW_NUMBER() over (order by (select 1)) * 16384 as SeqNumbr
from IV00101
where ITEMNMBR not in ( Select ITEMNMBR
from dbo.tcsINVTB00026_AttribAsgn )
and ITEMTYPE = 1
and ITEMNMBR not like '%-%'
Thats nice , but the only problem is that its giving values of seqnumbr starting from 16384, whereas in my underlying table the maximum value of seqnumbr is 811352064, so I want to start from that and increment it by 16384 each time...
How to change so that each time when the code runs, it scans hte max value of seqnumbr and then increment it by 16384 each time.
Sorry, im not a developer,
Thanks.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
June 18, 2011 at 2:04 am
why aren't you just making that column an identity column.
alter table x
alter column seqnr bigint identity( yourstartvalue, yourincrementvalue ) ;
Keep in mind, you should no longer insert that column ! it's value will be provided by the engine.
insert into yourtable ( your column list except the seqnr column )
select your column list except the seqnr column
from ....
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 21, 2011 at 9:43 am
ALZDBA (6/18/2011)
why aren't you just making that column an identity column.alter table x
alter column seqnr bigint identity( yourstartvalue, yourincrementvalue ) ;
Keep in mind, you should no longer insert that column ! it's value will be provided by the engine.
insert into yourtable ( your column list except the seqnr column )
select your column list except the seqnr column
from ....
I guess, a table can have only 1 identity column and i already have dex_row_id as the identity column,
So thatsy when I try to make the SEQNUMBR an identity column, i am not able to..
Regards
Sushant Kumar
MCTS,MCP
June 21, 2011 at 10:49 am
I'm sorry to have overlooked you already had the identity property in use :blush:
Performance may be an issue it you don't have an index for the seqnumber column
Select T.Itemnumber
, T.SeqNumbr + Cur.MaxSeqNumbr
from (
Select ITEMNMBR
, ROW_NUMBER() over ( order by (
select 1
) ) * 16384 as SeqNumbr
from IV00101
where ITEMNMBR not in ( Select ITEMNMBR
from dbo.tcsINVTB00026_AttribAsgn )
and ITEMTYPE = 1
and ITEMNMBR not like '%-%'
) T
outer apply (
Select MAX(seqnumber) as MaxSeqNumbr
from dbo.tcsINVTB00026_AttribAsgn
) Cur
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 21, 2011 at 11:25 am
@ alzdba
That was it.
THANKS A LOT !!!
Its little bit difficault to understand but iam trying 🙂
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply