I have two tables, Stock and Positions. Stock contains a Symbol column and a
Price column, and Symbol is the key. Positions contain the columns as well a
s
several other columns whose data should change with the Price. Positions can
have the same Symbol multiple times (it is keyed by Symbol-Account).
My design is to have a foreign key between the tables, so that when the
Price in the Stock column is updated, the Price in Positions also updates. I
am making some columns in the Positions table computed columns, so that they
recalculate when the Price updates.
My questions/concerns are:
1. Is this design any good? One alternative I was thinkning of was to use no
computed columns, and run a stored procedure frequently to update the
Positions.
2. Will the computed columns updated automatically when Price changes?
3. Will there be the possibility that some rows in Positions with the same
symbols are not updated simultaneously, so that the Price could be different
for the same symbol?
4. Do the rows lock while they are updating? This has implications because I
am querying this table often for other purposes.
5. If one computed column depends on another computed column, is there a way
to specify the order in which they calculate, or is this just a big no-no?
I am grateful for any insight.
Thank you,
CP Developer"CP Developer" <steved@.newsgroup.nospam> wrote in message
news:A40102EA-6AF1-4377-9332-09EFCAA83204@.microsoft.com...
>I have two tables, Stock and Positions. Stock contains a Symbol column and
>a
> Price column, and Symbol is the key. Positions contain the columns as well
> as
> several other columns whose data should change with the Price. Positions
> can
> have the same Symbol multiple times (it is keyed by Symbol-Account).
> My design is to have a foreign key between the tables, so that when the
> Price in the Stock column is updated, the Price in Positions also updates.
> I
> am making some columns in the Positions table computed columns, so that
> they
> recalculate when the Price updates.
> My questions/concerns are:
> 1. Is this design any good? One alternative I was thinkning of was to use
> no
> computed columns, and run a stored procedure frequently to update the
> Positions.
There are a few reasonable ways I can think of to have a comupted column
based on a column in a related table.
Put a phoney foreign key on (StockID, Price) and use cascade updates (your
idea).
Put a trigger on Stock to update the related positions.
Use a view to join the two tables and define the calculations there.
> 2. Will the computed columns updated automatically when Price changes?
> 3. Will there be the possibility that some rows in Positions with the same
> symbols are not updated simultaneously, so that the Price could be
> different
> for the same symbol?
No.
> 4. Do the rows lock while they are updating? This has implications because
> I
> am querying this table often for other purposes.
Yes. Make sure you have an index supporting the foreign key relationship.
> 5. If one computed column depends on another computed column, is there a
> way
> to specify the order in which they calculate, or is this just a big no-no?
>
No you cannot base one computed column on another. However you are free to
cut and paste the calculation for one column into the other.
Here's an example:
drop table position
drop table stock
go
create table stock
(
id int primary key,
price decimal(9,2),
constraint uk_id_price
unique (id,price)
)
create table position
(
account int not null, -- references account
stock int not null references stock,
price decimal(9,2) not null,
other_price as cast(price*.9 as decimal(9,2)),
constraint pk_position
primary key(account,stock),
constraint fk_position_stock_price
foreign key (stock,price)
references stock(id,price)
on update cascade
)
create index ix_position_stock_price
on position(stock,price)
go
insert into stock(id,price) values (1,3.50)
insert into position(account,stock,price) values (23,1,3.50)
go
update stock set price = 5.25 where id = 1
select * from position
David|||The columns will update when the price changes.
Create Table TableA
(
Symbol varchar(10),
Price Money,
Qty Int,
TotalCost As (Price * Qty)
)
Insert Into TableA
Select 'GBP', 14.52, 2
select * From TableA
Update TableA
Set Qty = 151, price = 45.65
select * From TableA
Drop table TableA
HTH
Barry|||I would store the price in one table only, and select it from there. If
you need you selects to be as fast as possible, consider using an
indexed view.
Showing posts with label stock. Show all posts
Showing posts with label stock. Show all posts
Monday, March 19, 2012
Cascading stock values?
Hi,
I have a database containing products, the tables of which are basically as
follows:
table_PRODUCTS
-->(products may or may not have colours)
table_PRODUCT_Colours
-->(colours may or may not have sizes)
table_PRODUCT_Colour_Sizes
- The tables currently hold stock values at all 3 levels, and the sum of
stock at Colour_Sizes level for each product must equal the sum of the stock
at Colours level, which must also equal the stock level held at the main
Product level.
- Some Products may not have Colour_Size records, and some may not have
Colours either.
I am trying to find out what is the best way to keep the 3 levels of stock
consistent, but I think the best way to do it would be:
- Using Triggers, and
- Always only allow stock to be adjusted at the highest level for a
particular product, i.e. Check for higher levels, and if found don't allow
updates to the level in question
- If an UPDATE, DELETE or INSERT operation occurs at the highest level, then
use the trigger to adjust the stock at the lower level automatically.
Please can you tell me whether this is the best way to do it, and if so any
pointers about how I would go about setting the triggers up - Although I hav
e
many years experience of TSQL, I have not used triggers before.
Thanks, Mike.You probably already know that these aggregate values violate database
normalization, and that problems you are talking about come from that
violation..
If these values are not used frequently OR there are few rows... I would NOT
stored the aggregates . Instead provide views over the tables which provide
the aggregate values..
If you MUST denormalize AND the upper level must always be the sum of the
lower levels, then there is a consistency problem I do not understand...If
the parent is always the sum of the children, but there may not be children
rows, then is the parent value 0 ( or null)...? If you allow the values to
be adjusted ONLY at the highest level, how does that allocate to the lower
levels?...
It seems to me you would ONLY allow changes at the lowest level, and the
higher levels would be calculated...
In the end if you mus do this... you might need to post more details ( with
some sample rows)...
By the way, it IS normal to maintain denormalized fields via Triggers... So
you are on the right technology track..
Good luck
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Mike Owen" wrote:
> Hi,
> I have a database containing products, the tables of which are basically a
s
> follows:
> table_PRODUCTS
> -->(products may or may not have colours)
> table_PRODUCT_Colours
> -->(colours may or may not have sizes)
> table_PRODUCT_Colour_Sizes
>
> - The tables currently hold stock values at all 3 levels, and the sum of
> stock at Colour_Sizes level for each product must equal the sum of the sto
ck
> at Colours level, which must also equal the stock level held at the main
> Product level.
> - Some Products may not have Colour_Size records, and some may not have
> Colours either.
> I am trying to find out what is the best way to keep the 3 levels of stock
> consistent, but I think the best way to do it would be:
> - Using Triggers, and
> - Always only allow stock to be adjusted at the highest level for a
> particular product, i.e. Check for higher levels, and if found don't allow
> updates to the level in question
> - If an UPDATE, DELETE or INSERT operation occurs at the highest level, th
en
> use the trigger to adjust the stock at the lower level automatically.
> Please can you tell me whether this is the best way to do it, and if so an
y
> pointers about how I would go about setting the triggers up - Although I h
ave
> many years experience of TSQL, I have not used triggers before.
>
> Thanks, Mike.|||You'd be in a lot less trouble if you had normalized the data model correctl
y.
As I see it:
1) Entities:
Products
2) Attributes:
Colour
Size
3) Relationships:
Products <-- Colour (one to zero or many)
Products <-- Size (one to zero or many)
With proper normalization nothing can stop you.
Consider changing the schema and just maybe the question you were trying to
ask will be answered as if by itself.
ML
http://milambda.blogspot.com/|||Thanks for the quick response Wayne.
Yes, you are right it is not necessarily a good / normalised design.
In answer to your 3rd paragraph " If you MUST denormalize AND ...", it
simply comes down to the fact that all products have the highest
(table_PRODUCT) level record, e.g. A toaster, some products also have the
second level, e.g. A car (blue, red, green etc), and a few have all 3 levels
,
e.g. A pair of trousers (blue, green, red) in various sizes (blue 32" waist,
blue 34" waist etc), so not all parent records have children.
It seems as though from your comment I was thinking along the right lines.
So it seems that as you have partly suggested I would probably need the
following triggers/rules:
- Child stock can always be updated, but when it is always update the parent
stock by adding up all of the childs peers stock
- If a parent has any children, don't let the stock be updated apart from by
a trigger from a child stock change.
Would you think this covers it?, and if so what would the triggers roughly
look like?
Thanks, Mike.
"Wayne Snyder" wrote:
> You probably already know that these aggregate values violate database
> normalization, and that problems you are talking about come from that
> violation..
> If these values are not used frequently OR there are few rows... I would N
OT
> stored the aggregates . Instead provide views over the tables which provid
e
> the aggregate values..
> If you MUST denormalize AND the upper level must always be the sum of the
> lower levels, then there is a consistency problem I do not understand...If
> the parent is always the sum of the children, but there may not be childre
n
> rows, then is the parent value 0 ( or null)...? If you allow the values t
o
> be adjusted ONLY at the highest level, how does that allocate to the lower
> levels?...
> It seems to me you would ONLY allow changes at the lowest level, and the
> higher levels would be calculated...
> In the end if you mus do this... you might need to post more details ( wit
h
> some sample rows)...
> By the way, it IS normal to maintain denormalized fields via Triggers...
So
> you are on the right technology track..
> Good luck
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Mike Owen" wrote:
>|||"ML" <ML@.discussions.microsoft.com> wrote in message
news:DDECA756-D376-4F0D-845B-1C68D229A344@.microsoft.com...
> ...With proper normalization nothing can stop you.
So essentially, "proper normalization" makes you invincible. :-)|||Unfortunately it's a system that I inhereted, but surely you would still hav
e
the same problem even if you did it as you indicated below, e.g.
Keeping it simple you might have a situation where a particular product has
no colours, very simple you would simply update the stock directly against
it, but another product may have colours, in which case you would either hav
e
to:
- ignore the stock at product record level altogether, or
- use triggers at the colour level to keep the stock value at product level
up to date
If you chose the first option you would then have to write application level
code for anything that looks at product level stock in this case, so it is
either not seen, or is swapped for colour level stock.
Cheers, Mike.
"ML" wrote:
> You'd be in a lot less trouble if you had normalized the data model correc
tly.
> As I see it:
> 1) Entities:
> Products
> 2) Attributes:
> Colour
> Size
> 3) Relationships:
> Products <-- Colour (one to zero or many)
> Products <-- Size (one to zero or many)
> With proper normalization nothing can stop you.
> Consider changing the schema and just maybe the question you were trying t
o
> ask will be answered as if by itself.
>
> ML
> --
> http://milambda.blogspot.com/|||Of course all combinations should be considered:
Product : Colour : Size
value null null
value value null
value value value
value null value
This way a specific combination of values represents an instance of a produc
t.
Is this correct? Maybe you should post some representative data, so that we
can understand the issue correctly.
ML
http://milambda.blogspot.com/|||Absolutely. :) Have you never heard of the RDBMS-Man? He's fully normalized
and bullet-proof.
ML
http://milambda.blogspot.com/|||Thanks for all of your help and support.
Following this I had a go at doing my first set of triggers, and they all
seem to work fine how ever many levels I have got.
Cheers, Mike.
"ML" wrote:
> Of course all combinations should be considered:
> Product : Colour : Size
> value null null
> value value null
> value value value
> value null value
> This way a specific combination of values represents an instance of a prod
uct.
> Is this correct? Maybe you should post some representative data, so that w
e
> can understand the issue correctly.
>
> ML
> --
> http://milambda.blogspot.com/
I have a database containing products, the tables of which are basically as
follows:
table_PRODUCTS
-->(products may or may not have colours)
table_PRODUCT_Colours
-->(colours may or may not have sizes)
table_PRODUCT_Colour_Sizes
- The tables currently hold stock values at all 3 levels, and the sum of
stock at Colour_Sizes level for each product must equal the sum of the stock
at Colours level, which must also equal the stock level held at the main
Product level.
- Some Products may not have Colour_Size records, and some may not have
Colours either.
I am trying to find out what is the best way to keep the 3 levels of stock
consistent, but I think the best way to do it would be:
- Using Triggers, and
- Always only allow stock to be adjusted at the highest level for a
particular product, i.e. Check for higher levels, and if found don't allow
updates to the level in question
- If an UPDATE, DELETE or INSERT operation occurs at the highest level, then
use the trigger to adjust the stock at the lower level automatically.
Please can you tell me whether this is the best way to do it, and if so any
pointers about how I would go about setting the triggers up - Although I hav
e
many years experience of TSQL, I have not used triggers before.
Thanks, Mike.You probably already know that these aggregate values violate database
normalization, and that problems you are talking about come from that
violation..
If these values are not used frequently OR there are few rows... I would NOT
stored the aggregates . Instead provide views over the tables which provide
the aggregate values..
If you MUST denormalize AND the upper level must always be the sum of the
lower levels, then there is a consistency problem I do not understand...If
the parent is always the sum of the children, but there may not be children
rows, then is the parent value 0 ( or null)...? If you allow the values to
be adjusted ONLY at the highest level, how does that allocate to the lower
levels?...
It seems to me you would ONLY allow changes at the lowest level, and the
higher levels would be calculated...
In the end if you mus do this... you might need to post more details ( with
some sample rows)...
By the way, it IS normal to maintain denormalized fields via Triggers... So
you are on the right technology track..
Good luck
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Mike Owen" wrote:
> Hi,
> I have a database containing products, the tables of which are basically a
s
> follows:
> table_PRODUCTS
> -->(products may or may not have colours)
> table_PRODUCT_Colours
> -->(colours may or may not have sizes)
> table_PRODUCT_Colour_Sizes
>
> - The tables currently hold stock values at all 3 levels, and the sum of
> stock at Colour_Sizes level for each product must equal the sum of the sto
ck
> at Colours level, which must also equal the stock level held at the main
> Product level.
> - Some Products may not have Colour_Size records, and some may not have
> Colours either.
> I am trying to find out what is the best way to keep the 3 levels of stock
> consistent, but I think the best way to do it would be:
> - Using Triggers, and
> - Always only allow stock to be adjusted at the highest level for a
> particular product, i.e. Check for higher levels, and if found don't allow
> updates to the level in question
> - If an UPDATE, DELETE or INSERT operation occurs at the highest level, th
en
> use the trigger to adjust the stock at the lower level automatically.
> Please can you tell me whether this is the best way to do it, and if so an
y
> pointers about how I would go about setting the triggers up - Although I h
ave
> many years experience of TSQL, I have not used triggers before.
>
> Thanks, Mike.|||You'd be in a lot less trouble if you had normalized the data model correctl
y.
As I see it:
1) Entities:
Products
2) Attributes:
Colour
Size
3) Relationships:
Products <-- Colour (one to zero or many)
Products <-- Size (one to zero or many)
With proper normalization nothing can stop you.
Consider changing the schema and just maybe the question you were trying to
ask will be answered as if by itself.
ML
http://milambda.blogspot.com/|||Thanks for the quick response Wayne.
Yes, you are right it is not necessarily a good / normalised design.
In answer to your 3rd paragraph " If you MUST denormalize AND ...", it
simply comes down to the fact that all products have the highest
(table_PRODUCT) level record, e.g. A toaster, some products also have the
second level, e.g. A car (blue, red, green etc), and a few have all 3 levels
,
e.g. A pair of trousers (blue, green, red) in various sizes (blue 32" waist,
blue 34" waist etc), so not all parent records have children.
It seems as though from your comment I was thinking along the right lines.
So it seems that as you have partly suggested I would probably need the
following triggers/rules:
- Child stock can always be updated, but when it is always update the parent
stock by adding up all of the childs peers stock
- If a parent has any children, don't let the stock be updated apart from by
a trigger from a child stock change.
Would you think this covers it?, and if so what would the triggers roughly
look like?
Thanks, Mike.
"Wayne Snyder" wrote:
> You probably already know that these aggregate values violate database
> normalization, and that problems you are talking about come from that
> violation..
> If these values are not used frequently OR there are few rows... I would N
OT
> stored the aggregates . Instead provide views over the tables which provid
e
> the aggregate values..
> If you MUST denormalize AND the upper level must always be the sum of the
> lower levels, then there is a consistency problem I do not understand...If
> the parent is always the sum of the children, but there may not be childre
n
> rows, then is the parent value 0 ( or null)...? If you allow the values t
o
> be adjusted ONLY at the highest level, how does that allocate to the lower
> levels?...
> It seems to me you would ONLY allow changes at the lowest level, and the
> higher levels would be calculated...
> In the end if you mus do this... you might need to post more details ( wit
h
> some sample rows)...
> By the way, it IS normal to maintain denormalized fields via Triggers...
So
> you are on the right technology track..
> Good luck
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Mike Owen" wrote:
>|||"ML" <ML@.discussions.microsoft.com> wrote in message
news:DDECA756-D376-4F0D-845B-1C68D229A344@.microsoft.com...
> ...With proper normalization nothing can stop you.
So essentially, "proper normalization" makes you invincible. :-)|||Unfortunately it's a system that I inhereted, but surely you would still hav
e
the same problem even if you did it as you indicated below, e.g.
Keeping it simple you might have a situation where a particular product has
no colours, very simple you would simply update the stock directly against
it, but another product may have colours, in which case you would either hav
e
to:
- ignore the stock at product record level altogether, or
- use triggers at the colour level to keep the stock value at product level
up to date
If you chose the first option you would then have to write application level
code for anything that looks at product level stock in this case, so it is
either not seen, or is swapped for colour level stock.
Cheers, Mike.
"ML" wrote:
> You'd be in a lot less trouble if you had normalized the data model correc
tly.
> As I see it:
> 1) Entities:
> Products
> 2) Attributes:
> Colour
> Size
> 3) Relationships:
> Products <-- Colour (one to zero or many)
> Products <-- Size (one to zero or many)
> With proper normalization nothing can stop you.
> Consider changing the schema and just maybe the question you were trying t
o
> ask will be answered as if by itself.
>
> ML
> --
> http://milambda.blogspot.com/|||Of course all combinations should be considered:
Product : Colour : Size
value null null
value value null
value value value
value null value
This way a specific combination of values represents an instance of a produc
t.
Is this correct? Maybe you should post some representative data, so that we
can understand the issue correctly.
ML
http://milambda.blogspot.com/|||Absolutely. :) Have you never heard of the RDBMS-Man? He's fully normalized
and bullet-proof.
ML
http://milambda.blogspot.com/|||Thanks for all of your help and support.
Following this I had a go at doing my first set of triggers, and they all
seem to work fine how ever many levels I have got.
Cheers, Mike.
"ML" wrote:
> Of course all combinations should be considered:
> Product : Colour : Size
> value null null
> value value null
> value value value
> value null value
> This way a specific combination of values represents an instance of a prod
uct.
> Is this correct? Maybe you should post some representative data, so that w
e
> can understand the issue correctly.
>
> ML
> --
> http://milambda.blogspot.com/
Wednesday, March 7, 2012
Carry Forward Balance
Hello All,
I'm creating a "stock ledger" using Crystal Reports 8.0. This report is printed monthly and requires that the previous month's closing balance become current month's opening balance. How do I do this?
Regards,
Rajmathihi.........
i dont know wts ur tables structure is.................but u can apply condition in ur query as
select sum(AmountField) from TableName where Month(TableName.DateField)=Month(TableName.DateField)-1
Best of Luck|||Hello "Silly Star"
Thanks for the reply, but the opening balance stock quantity and stock value are to be calculated as follows:
Every time new stock is received, the new quantity and its value gets added to the existing stock and a new rate-per-unit is calculated. suppose say I have received two consignments of a particular item on different dates, one of 100 units at 1 Re. each and the other also of 100 units, but at a cost of Rs. 1.10 each then the total stock with me is worth 210.
The same concept is applicable when stock is issued. Now when I have to issue an item I have to issue at Rs. 1.05 each.
Therefore directly considering the total of the quantity recieved and quantity issued can give me info on the quantity used, but there is huge variation in the cost (value) factor. There is fluctuation in the value and the RPU gets changed everytime new stock is received.
The report that I have designed could be used to get stock across months (say Apr - Oct). The closing balance of Apr should be taken as opening balance for May and so on.
It seems in D2K there is separate option using which this can be performed. I just wanted to know if there is a way out in Crystal Reports too
Regards,
Rajmathi|||RajMathi,
I am not sure about this.
I think you can use formula field having the code
currencyvar opbal;
whilprintingrecords;
if month{datefiled}="April" then
opbal:=opbal+{ClosingBalField};
Use other formula to check whether the month is may, if so
add opbal value to openingbalance value of May|||Hello madhi,
Well, I missed out one thing. There are several items in stock and the grouping is on items, so opening balance is separate for each item.
Regards|||RajMathi,
Ok then use other formula named @.Reset having this code
currencyvar opbal;
EhilePrintingRecords;
opbal:=0;
and place this in Group header and other formula in Dectails Section
I'm creating a "stock ledger" using Crystal Reports 8.0. This report is printed monthly and requires that the previous month's closing balance become current month's opening balance. How do I do this?
Regards,
Rajmathihi.........
i dont know wts ur tables structure is.................but u can apply condition in ur query as
select sum(AmountField) from TableName where Month(TableName.DateField)=Month(TableName.DateField)-1
Best of Luck|||Hello "Silly Star"
Thanks for the reply, but the opening balance stock quantity and stock value are to be calculated as follows:
Every time new stock is received, the new quantity and its value gets added to the existing stock and a new rate-per-unit is calculated. suppose say I have received two consignments of a particular item on different dates, one of 100 units at 1 Re. each and the other also of 100 units, but at a cost of Rs. 1.10 each then the total stock with me is worth 210.
The same concept is applicable when stock is issued. Now when I have to issue an item I have to issue at Rs. 1.05 each.
Therefore directly considering the total of the quantity recieved and quantity issued can give me info on the quantity used, but there is huge variation in the cost (value) factor. There is fluctuation in the value and the RPU gets changed everytime new stock is received.
The report that I have designed could be used to get stock across months (say Apr - Oct). The closing balance of Apr should be taken as opening balance for May and so on.
It seems in D2K there is separate option using which this can be performed. I just wanted to know if there is a way out in Crystal Reports too
Regards,
Rajmathi|||RajMathi,
I am not sure about this.
I think you can use formula field having the code
currencyvar opbal;
whilprintingrecords;
if month{datefiled}="April" then
opbal:=opbal+{ClosingBalField};
Use other formula to check whether the month is may, if so
add opbal value to openingbalance value of May|||Hello madhi,
Well, I missed out one thing. There are several items in stock and the grouping is on items, so opening balance is separate for each item.
Regards|||RajMathi,
Ok then use other formula named @.Reset having this code
currencyvar opbal;
EhilePrintingRecords;
opbal:=0;
and place this in Group header and other formula in Dectails Section
Friday, February 24, 2012
capturing a warning - ongoing basis
Hi all,
I currently have a series of stored procedures that capture stock prices on a daily basis, then summarize the results into a daily, and further, a weekly summary of the "index" of a group of stocks. The data is accumulated from a (to use a highly technical unit of measurement...) bunch-O-individual rows of data using aggregate functions such as AVG and SUM.
The problem is that I occasionally get a warning on such aggregate statements which is the common one complaining thusly: "Warning: Null value is eliminated by an aggregate or other SET operation"
I know where it comes from, and I know how to code to protect the aggregate from complaining (i.e., AVG(ISNULL(yadayada,0)) ) but I am interested in figuring out a way to REPORT the statement that contains null values. I can, of course, capture ERRORS in selects, but is the same mechanism used to capture these NULL warnings on my aggregate statements? I don't necessarily want to know which individual row is causing it, just want to "tag" somehow the statement that results in the warning so I can go back after the run and check into it (after capturing local "pointer" info at the time the offending aggregate is invoked).
The code I use to capture errors and trace information follows:
UPDATE PortfolioPerformance
SET PrevDate = @.PrevDate,
DailyPerChg = GPP.DailyPerChg,
DailySumPriceChg = GPP.DailySumPriceChg,
SumCurrPrice = GPP.SumCurrPrice,
SumPrevPrice = GPP.SumPrevPrice,
StockCount = GPP.StockCnt,
AvgHighPriceRatio = GPP.AvgHighPriceRatio,
AvgLowPriceRatio = GPP.AvgLowPriceRatio,
Volume = GPP.Volume
FROM PortfolioPerformance PP (nolock), VIEW_Get_PortfolioPerformance GPP
WHERE PP.PortfolioID = GPP.PortfolioID AND
(PP.CreateDate = GPP.CreateDate AND
PP.CreateDate = @.CreateDate) AND
PP.PrevDate IS NULL
SELECT @.RowCount = LTRIM(STR(@.@.ROWCOUNT)) /* capture rowcount so @.m_error select doesn't clobber it */
SELECT @.m_error = @.@.Error IF @.m_error <> 0 GOTO ErrorHandler
SET @.TraceMsg = 'Completed Daily Portfolio Performance calculations (updated ' + @.RowCount + ' rows)'
EXECUTE [dbo].[tracelog] 1, 'Index', 'sp_Set_PortfolioPerformance', @.TraceMsg
NOTE: the aggregation in the above code is performed in the view referenced as "GPP", but that's outside the realm of the question, I think, so I won't bore you with the details of that just yet.
So I think if I can capture the warning like I do the errors, I can accomplish what I want to accomplish. I haven't yet been able to find any guidance in the Books Online, so do any of you have any pointers?
Thanks!
Paulhttp://msdn.microsoft.com/library/en-us/howtosql/ht_automaem_5fi1.asp
This might be what you are looking for :
select * from sysmessages where error = 8153|||I think that's a step in the right direction, however, I need to find out what triggers that alert and try to get directly at that. Using this scheme (as I understand the web documentation) will allow me to execute a stored procedure or send an email when the problem HAPPENS, but won't allow my a way to capture the date or portfolio that caused the problem.
That's where my head-scratching comes in on the issue. It would work perfectly if there was a way to pass run-time data (data processing date - which could be different from the system timestamp/current date - and portfolioID) to the alert.
Trouble is, I am processing a buncha-days and a buncha portfolios, and was just looking for an easy way to detect the warning AT THE TIME it occurs, so I can dump out a trace that says what I was working on at the time it happened.
I think I need to know what happens in SQL Server-Land that triggers the alert...I mean, the error/message number of 8153 should be written SOMEWHERE, shouldn't it?
OR, are you suggesting (as I will try out immediately) that I can execute the SQL statement, then check for error 8153 where I check for error codes in my post-select status checking?
If THAT is the current thought, then I am confused, since I check for an sql-error code of ANYTHING other than zero, and (supposedly) bail out and report the error if the post-select status is anything other than zero (in other words, why isn't my error # 8153 being caught up in my web of lies and deceit...err...sorry, I mean my web of error checking :blush:)
I currently have a series of stored procedures that capture stock prices on a daily basis, then summarize the results into a daily, and further, a weekly summary of the "index" of a group of stocks. The data is accumulated from a (to use a highly technical unit of measurement...) bunch-O-individual rows of data using aggregate functions such as AVG and SUM.
The problem is that I occasionally get a warning on such aggregate statements which is the common one complaining thusly: "Warning: Null value is eliminated by an aggregate or other SET operation"
I know where it comes from, and I know how to code to protect the aggregate from complaining (i.e., AVG(ISNULL(yadayada,0)) ) but I am interested in figuring out a way to REPORT the statement that contains null values. I can, of course, capture ERRORS in selects, but is the same mechanism used to capture these NULL warnings on my aggregate statements? I don't necessarily want to know which individual row is causing it, just want to "tag" somehow the statement that results in the warning so I can go back after the run and check into it (after capturing local "pointer" info at the time the offending aggregate is invoked).
The code I use to capture errors and trace information follows:
UPDATE PortfolioPerformance
SET PrevDate = @.PrevDate,
DailyPerChg = GPP.DailyPerChg,
DailySumPriceChg = GPP.DailySumPriceChg,
SumCurrPrice = GPP.SumCurrPrice,
SumPrevPrice = GPP.SumPrevPrice,
StockCount = GPP.StockCnt,
AvgHighPriceRatio = GPP.AvgHighPriceRatio,
AvgLowPriceRatio = GPP.AvgLowPriceRatio,
Volume = GPP.Volume
FROM PortfolioPerformance PP (nolock), VIEW_Get_PortfolioPerformance GPP
WHERE PP.PortfolioID = GPP.PortfolioID AND
(PP.CreateDate = GPP.CreateDate AND
PP.CreateDate = @.CreateDate) AND
PP.PrevDate IS NULL
SELECT @.RowCount = LTRIM(STR(@.@.ROWCOUNT)) /* capture rowcount so @.m_error select doesn't clobber it */
SELECT @.m_error = @.@.Error IF @.m_error <> 0 GOTO ErrorHandler
SET @.TraceMsg = 'Completed Daily Portfolio Performance calculations (updated ' + @.RowCount + ' rows)'
EXECUTE [dbo].[tracelog] 1, 'Index', 'sp_Set_PortfolioPerformance', @.TraceMsg
NOTE: the aggregation in the above code is performed in the view referenced as "GPP", but that's outside the realm of the question, I think, so I won't bore you with the details of that just yet.
So I think if I can capture the warning like I do the errors, I can accomplish what I want to accomplish. I haven't yet been able to find any guidance in the Books Online, so do any of you have any pointers?
Thanks!
Paulhttp://msdn.microsoft.com/library/en-us/howtosql/ht_automaem_5fi1.asp
This might be what you are looking for :
select * from sysmessages where error = 8153|||I think that's a step in the right direction, however, I need to find out what triggers that alert and try to get directly at that. Using this scheme (as I understand the web documentation) will allow me to execute a stored procedure or send an email when the problem HAPPENS, but won't allow my a way to capture the date or portfolio that caused the problem.
That's where my head-scratching comes in on the issue. It would work perfectly if there was a way to pass run-time data (data processing date - which could be different from the system timestamp/current date - and portfolioID) to the alert.
Trouble is, I am processing a buncha-days and a buncha portfolios, and was just looking for an easy way to detect the warning AT THE TIME it occurs, so I can dump out a trace that says what I was working on at the time it happened.
I think I need to know what happens in SQL Server-Land that triggers the alert...I mean, the error/message number of 8153 should be written SOMEWHERE, shouldn't it?
OR, are you suggesting (as I will try out immediately) that I can execute the SQL statement, then check for error 8153 where I check for error codes in my post-select status checking?
If THAT is the current thought, then I am confused, since I check for an sql-error code of ANYTHING other than zero, and (supposedly) bail out and report the error if the post-select status is anything other than zero (in other words, why isn't my error # 8153 being caught up in my web of lies and deceit...err...sorry, I mean my web of error checking :blush:)
Subscribe to:
Posts (Atom)