What I use as my query is:
"DELETE FROM timerecord WHERE Actual_Time_In LIKE '11.12.2006%'"
The row of Actual_Time_In is formatted with Date and time (MM.DD.YYYY HH:MM:SS) sometimes there are ten records and I'd rather not have to remove them from the table one at a time. However, even though I have a record that is '11.12.2006 22:43:00' my delete doesn't work osql states I have 0 rows affected.
This is only MSDE so I don't have anyother way to open the table.
Sometimes these records have other records that reference them. Is there anyway to do a cascading delete without it getting to complex?
Thanks of all your help, I am just a tech support guy beating his head against a wall..
I need to implement my cascading deletes on a SQL database. Is it better (performance/reliablility-wise) to use the Foreign Key Cascading Deletes or to just write my own triggers to do the deletes?I was hoping someone had experimented and found which works best.
I am having great difficulty with cascading deletes, delete triggers and referential integrity.
The database is in First Normal Form.
I have some tables that are child tables with two foreign keyes to two different parent tables, for example:
Table A / Table B Table C / Table D
So if I try to turn on cascading deletes for A/B, A/C, B/D and C/D relationships, I get an error that I cannot have cascading delete because it would create multiple cascade paths. I do understand why this is happening. If I delete a row in Table A, I want it to delete child rows in Table B and table C, and then child rows in table D as well. But if I delete a row in Table C, I want it to delete child rows in Table D, and if I delete a row in Table B, I want it to also delete child rows in Table D.
SQL sees this as cyclical, because if I delete a row in table A, both table B and table C would try to delete their child rows in table D.
Ok, so I thought, no biggie, I'll just use delete triggers. So I created delete triggers that will delete child rows in table B and table C when deleting a row in table A. Then I created triggers in both Table B and Table C that would delete child rows in Table D.
When I try to delete a row in table A, B or C, I get the error "Delete Statement Conflicted with COLUMN REFERENCE". This does not make sense to me, can anyone explain? I have a trigger in place that should be deleting the child rows before it attempts to delete the parent row...isn't that the whole point of delete triggers?????
This is an example of my delete trigger:
CREATE TRIGGER [DeleteA] ON A FOR DELETE AS Delete from B where MeetingID = ID; Delete from C where MeetingID = ID;
And then Table B and C both have delete triggers to delete child rows in table D. But it never gets to that point, none of the triggers execute because the above error happens first.
So if I then go into the relationships, and deselect the option for "Enforce relationship for INSERTs and UPDATEs" these triggers all work just fine. Only problem is that now I have no referential integrity and I can simply create unrestrained child rows that do not reference actual foreign keys in the parent table.
So the question is, how do I maintain referential integrity and also have the database delete child rows, keeping in mind that the cascading deletes will not work because of the multiple cascade paths (which are certainly required).
I use SQL Server 2005I have tables tblUserData, tblUsersAndGuestbook, tblGuestbooktblUserdata contains:UserCode intUsername nvarchar(50)tblUsersAndGuestbook contains:Usercode int (FK to tblUserData)GBEntryCode inttblGuestbookGBEntryCode int (FK to tblUsersAndGuestbook)GBText textNow...if I delete a user in tblUserData I want to also delete the entries in tblUsersAndGuestbook AND in tblGuestbook.I've heard something about cascading delete, but how can i configure that in my database?Or do I manually need to delete all entries from code?
Hi, I read all the existing material in SWYNK but still am not clear on the following question. What is the best way to perform Cascading actions (Delete & Update) with foreign Key Constraints declared? We are using SQL Server 7.0 thanks Rozina
It deletes all rows in the table specified that conform to the criteria selected, while also deleting any child/grandchild records and so on. This is designed to do the same sort of thing as Access's cascade delete function. It first reads the sysforeignkeys table to find any child tables, then deletes the soon-to-be orphan records from them using recursive calls to this procedure. Once all child records are gone, the rows are deleted from the selected table. It is designed at this time to be run at the command line. It could also be used in code, but the printed output will not be available. */ ( @cTableName varchar(50), /* name of the table where rows are to be deleted */ @cCriteria nvarchar(1000), /* criteria used to delete the rows required */ @iRowsAffected int OUTPUT /* number of records affected by the delete */ ) As set nocount on declare @cTab varchar(255), /* name of the child table */ @cCol varchar(255), /* name of the linking field on the child table */ @cRefTab varchar(255), /* name of the parent table */ @cRefCol varchar(255), /* name of the linking field in the parent table */ @cFKName varchar(255), /* name of the foreign key */ @cSQL nvarchar(1000), /* query string passed to the sp_ExecuteSQL procedure */ @cChildCriteria nvarchar(1000), /* criteria to be used to delete records from the child table */ @iChildRows int /* number of rows deleted from the child table */
/* declare the cursor containing the foreign key constraint information */ DECLARE cFKey CURSOR LOCAL FOR SELECT SO1.name AS Tab, SC1.name AS Col, SO2.name AS RefTab, SC2.name AS RefCol, FO.name AS FKName FROM dbo.sysforeignkeys FK INNER JOIN dbo.syscolumns SC1 ON FK.fkeyid = SC1.id AND FK.fkey = SC1.colid INNER JOIN dbo.syscolumns SC2 ON FK.rkeyid = SC2.id AND FK.rkey = SC2.colid INNER JOIN dbo.sysobjects SO1 ON FK.fkeyid = SO1.id INNER JOIN dbo.sysobjects SO2 ON FK.rkeyid = SO2.id INNER JOIN dbo.sysobjects FO ON FK.constid = FO.id WHERE SO2.Name = @cTableName
OPEN cFKey FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName WHILE @@FETCH_STATUS = 0 BEGIN /* build the criteria to delete rows from the child table. As it uses the criteria passed to this procedure, it gets progressively larger with recursive calls */ SET @cChildCriteria = @cCol + ' in (SELECT [' + @cRefCol + '] FROM [' + @cRefTab +'] WHERE ' + @cCriteria + ')' print 'Deleting records from table ' + @cTab /* call this procedure to delete the child rows */ EXEC spDeleteRows @cTab, @cChildCriteria, @iChildRows OUTPUT FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName END Close cFKey DeAllocate cFKey /* finally delete the rows from this table and display the rows affected */ SET @cSQL = 'DELETE FROM [' + @cTableName + '] WHERE ' + @cCriteria print @cSQL EXEC sp_ExecuteSQL @cSQL print 'Deleted ' + CONVERT(varchar, @@ROWCOUNT) + ' records from table ' + @cTableName -------- The above code is good .. but has limitation...throws an error: Server: Msg 217, Level 16, State 1, Procedure spDeleteRows, Line 58 Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
Can anyone out there suggest a better way of implementing on a database without a limitation of levels.. we are talking about a HUGE DB with lots of table and FK referentials..
I use cascading delete on my SQL Server Database. I am experiencing along query time on my highest level delete, 10 minutes. If I deletefrom each table manually and then delete the parent, I will usually bedone in less than a minute. Any suggestions?
If we want to maintain the data in relationships. There are two ways to do it. 1. Auto (Like Cascading Update And Delete) 2. Manually (Like In Stored Procedures) I read an intresting article http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=419 In this article Imar has choosen the second way (Manually). And when I talk to Imar. He said, "Cascading deletes would have worked equally well in this situation. However, I personally don't like them too much. I am much rather in control, enabling me to delete what I want and when I want it. I could, for example, keep certain data for "time travelling scenarios" (e.g. the state things were in some time ago) or I might want to keep it for other purposes." Can any one help me to choose the better one.
Is it possible to perform a cascading delete and update using TRIGGERS on a table referenced by a foreign key constraint.?To be more specific.. if the primary key is deleted does the delete trigger on the primary table deletes the record in the foreign key table or does it return an error?? if possible please send us the T SQL Statements .
hello guysi am using a table that its secondary key connected to its primary key...and as sql server 2000 doesnt allow cascade delete fore such,i had to write a trigger myselfso i wrote the following triggerCREAT TRIGGER nameON tableFOR DeleteASBEGINIF @@ROWCOUNT >0Delete from table where table.parentID in (select sortID from deleted);ENDthen i went to the table and i tried to delete...and it gave me an error....that there are records that have there parentID= sortID of the table i am trieng to delete...so i deleted the relationship...and kept the triggerand now ...when i delete one...it deletes one level down....but not more....i mean when i delete sortID=4it deletes all the records that has parentID=4...and NOT more..whereas my aim was to have it recursive not to have records lost in my databasehope i explained good as much as i hope to find an answer soon...a clear one...and thanks in advanced...
This function will generate all DELETE statements in correct order to perform a CASCADING delete. For self-joined tables, it will generate the T-SQL code to "unwind" the table, also in correct order!CREATE FUNCTION dbo.fnCascadingDelete ( @Schema NVARCHAR(128) = NULL, @Table NVARCHAR(128) = NULL ) RETURNS@Return TABLE ( RowID INT PRIMARY KEY CLUSTERED, IsSelfJoin TINYINT NOT NULL, HasPk TINYINT NOT NULL, [SQL] NVARCHAR(4000) NOT NULL ) AS BEGIN DECLARE@Constraints TABLE ( RowID INT NOT NULL, Indent SMALLINT NOT NULL, [Catalog] NVARCHAR(128) NOT NULL, [Schema] NVARCHAR(128) NOT NULL, [Table] NVARCHAR(128) NOT NULL, [Column] NVARCHAR(128), pkCatalog NVARCHAR(128), pkSchema NVARCHAR(128), pkTable NVARCHAR(128), pkColumn NVARCHAR(128), pkType NVARCHAR(128), pkSize INT, IsSelfJoin TINYINT NOT NULL, HasPk TINYINT NOT NULL )
INSERT@Constraints ( RowID, Indent, [Catalog], [Schema], [Table], [Column], pkCatalog, pkSchema, pkTable, pkColumn, pkType, pkSize, IsSelfJoin, HasPk ) SELECTRowID, Indent, [Catalog], [Schema], [Table], [Column], pkCatalog, pkSchema, pkTable, pkColumn, pkType, pkSize, SelfJoin, CASE WHEN [Column] IS NULL THEN 0 ELSE 1 END FROMdbo.fnTableTree(@Schema, @Table)
IF @@ROWCOUNT = 0 RETURN
DECLARE@SQL TABLE ( ID INT IDENTITY(1, 1), RowID INT PRIMARY KEY CLUSTERED, IsSelfJoin TINYINT NOT NULL, HasPk TINYINT NOT NULL, [SQL] NVARCHAR(4000) NOT NULL )
DECLARE@Unwind TABLE ( RowID INT NOT NULL, StepID INT IDENTITY(0, 1) PRIMARY KEY NONCLUSTERED, [SQL] NVARCHAR(4000) )
WHILE NOT EXISTS (SELECT * FROM @SQL WHERE RowID = 1) BEGIN SELECT TOP 1@RowID = c.RowID, @ID = c.RowID, @Indent = c.Indent, @TSQL = N'', @EndSQL = N'', @IsSelfJoin = c.IsSelfjoin, @HasPk = c.HasPk FROM@Constraints AS c LEFT JOIN@SQL AS s ON s.RowID = c.RowID WHEREs.RowID IS NULL ORDER BYc.Indent DESC, c.RowID DESC
WHILE @ID > 0 BEGIN IF @Indent = 0 SELECT@RowSQL = N'DELETE t' + CAST(@RowID AS NVARCHAR(12)), @RowSQL = @RowSQL + N' FROM ' + QUOTENAME(c.[Catalog]) + N'.' + QUOTENAME(c.[Schema]) + N'.' + QUOTENAME(c.[Table]) + N' AS t' + CAST(@ID AS NVARCHAR(12)), @EndSQL = N' WHERE t' + CAST(@ID AS NVARCHAR(12)) + '.' + QUOTENAME(COALESCE(c.[Column], '%0')) + N' = ''%1''', @IsSelfJoin = @IsSelfJoin | c.IsSelfJoin FROM@Constraints AS c WHEREc.RowID = @ID ELSE SELECT@RowSQL = N' INNER JOIN ' + QUOTENAME(c.[Catalog]) + N'.' + QUOTENAME(c.[Schema]) + N'.' + QUOTENAME(c.[Table]), @RowSQL = @RowSQL + N' AS t' + CAST(@ID AS NVARCHAR(12)) + N' ON t' + CAST(@ID AS NVARCHAR(12)) + N'.' + QUOTENAME(c.[Column]), @pkColumn = QUOTENAME(c.pkColumn), @IsSelfJoin = @IsSelfJoin | c.IsSelfJoin FROM@Constraints AS c WHEREc.RowID = @ID
SELECT TOP 1@ID = c.RowID, @Indent = c.Indent, @RowSQL = @RowSQL + N' = t' + CAST(c.RowID AS NVARCHAR(12)) + N'.' + @pkColumn, @IsSelfJoin = @IsSelfJoin | c.IsSelfJoin FROM@Constraints AS c WHEREc.RowID < @ID AND c.Indent < @Indent ORDER BYc.Indent DESC, c.RowID DESC
INSERT@Stage (Lvl, RowKey) SELECT@Lvl, t.' + QUOTENAME(@pkColumn) + ' FROM' + QUOTENAME(@Catalog) + '.' + QUOTENAME(@Schema) + '.' + QUOTENAME(@Table) + ' AS t INNER JOIN@Stage AS s ON s.RowKey = t.' + QUOTENAME(@Column) + ' AND s.Lvl = @Lvl - 1 LEFT JOIN@Stage AS cr ON cr.RowKey = t.' + QUOTENAME(@pkColumn) + ' WHEREcr.RowKey IS NULL END SELECT ''DELETE FROM ' + QUOTENAME(@Catalog) + '.' + QUOTENAME(@Schema) + '.' + QUOTENAME(@Table) + ' WHERE ' + QUOTENAME(@pkColumn) + ' = '' + QUOTENAME(RowKey, '''''''') FROM @Stage WHERE RowID > 0 ORDER BY RowID DESC'
INSERT@Unwind ( RowID, [SQL] ) VALUES( @RowID, @RowSQL ) END END
INSERT@Return ( RowID, IsSelfJoin, HasPk, [SQL] ) SELECTs.ID, s.IsSelfJoin, s.HasPk, CASE WHEN u.RowID IS NULL THEN s.[SQL] ELSE u.[SQL] END FROM@SQL AS s LEFT JOIN@Unwind AS u ON u.RowID = s.RowID ORDER BYs.ID, u.StepID
I have this stored procedure. I want to run a few simple SQL functions against my tables. In particular I want to take a subset of records (One or Two years worth) and calculate AVG, VAR and STDEV.
It does not work the way I thought it would. I end up with the whole input table in #tempor1 which is about 6 years worth of records.
set ANSI_NULLS ON set QUOTED_IDENTIFIER OFF
GO ALTER PROCEDURE [dbo].[findAve1YearDailyClose_MSFT] AS BEGIN SET NOCOUNT ON; SELECT adjClosed, volume INTO #tempor1 FROM dbo.dailyCl_MSFT GROUP BY dateTimed, adjClosed, volume HAVING (dateTimed > DATEADD (year, -1, MAX (dateTimed)))
SELECT AVG (adjClosed) AS "AVGAdjClose1Year", VAR (adjClosed) AS "VARAdjClose1Year", AVG (volume) AS "AVGVolume1Year", STDEV (volume) AS "STDEVVolume1Year", COUNT (*) AS "total" FROM #tempor1 END
Thus if I change the number of years I subtract from the latest date from 1 to 2 I end up with the same result. What is the problem?
What happens to these REPLication agents if SQL Agent is stopped and started: Snapshot Agents Merge Agents Misc Maintenance Agents
Can the agent be stopped and started with no thought to the status of replication, or should the replication state be modified in some way before any change to the status of the Agent?
(I know REPL depends on agent, so wuestion is, can REPL simply resume or not when agent is re-started.)
Does someone know if doing a reindex on a clustered or non-clustered index cause the snapshot file to grow? In other words, is the data that makes up the snapshot copied from the source to the snapshot database? If a normal reindex is done on the underlying database, will it block users from acessing the snapshot? Any help would be appreciated.
How can I achieve the same effect as a cross join (since the merge operator doesn't have a cross join)?
Situation is this... a flat file has some header and footer information that I need to keep and attach to each row. So for simplicity sake of an example lets just say header has only 1 thing we care about - a row that says DATE=01/01/06.
I take the file and run a split to split into "Date" "Data" and "other" (other has all the throwaway rows in header and footer I don't care about). Then I use a derived column object to get all the columns out of the "Data". Finally I want to add that Date metadata back to every row in the data...
I thought this would be an easy thing to do.. but I can't seem to figure out how to duplicate that Date info into every row.. Hopefully I am overlooking something simple.
Select Query Left Join Select Query 2 Left Join Select Query 3
How does it work actually? As in, whether Query 2 & Query 3 will work only on the records retrieved by Query 1 only. Or, all the select statements retrieves all the records and then the condition is applied to filter out the results.
Also, does the order of the Select statements make any difference on the speed?
We have a database that we have designed so all the data dependances are managed by the front end code. However the company we are writing it for has asked us to add around 50 constraints. I was just a little worried what kind of effect on performance this would have. Cheers Ed
If I have three large tables to join together should I join the two that I know will cut the number of rows down a lot first and then join the 3rd table or does it make no difference (if I join the first and 3rd - which I know will be a large result set and then join the 2nd).
Hello all, thanks in advance for any advice here. My question is, what's the effect of the Enterprise Manager > Tasks > Shrink Database function? It seems to reduce the used space on the device. Testing it on some dev machines, I've seem to have gotten back as much as 20g of space. I know it should grow back to that, but the time it took was minimal, and it didn't seem to affect my developers. They tend to add alot and delete alot during testing. What negative effects of running this should I look out for? Will it affect the DB long term? Is is preferable to schedule once a month or so? Is this done on Production DB's? Thanks for any guidence on the usage of this.
I have created a report where I have Z-Index all set correctly, but I cannot see the desired effect of toggling as can be seen in the AdventureWorks sample of SalesOrder report that gets shipped with SSRS. What am I missing?
I'm looking into the automatic recompilation of stored procedures andI have been reading up on the "Do not recompute statistics" option onindexes.Am I correct in concluding that disabling the "Do not recomputestatistics" option for an index, will ensure that no automaticrecompilations will occur as a result of updates to data in thatindex?Am I also correct in understanding that the "Update Statistics" willstill update statistics for the index even if the "Do not recomputestatistics" option is disabled?RegardsBjørn
Hello All,I have a stored procedure which will act like a main/controller scriptwhich will then invoke more stored procedures (~20). Basically, itlooks something like below:-- start scriptcreate procedure ...print 'process started'exec sp_1exec sp_2exec sp_3....print 'process ended'-- end scriptLooking at it, after running that procedure, immediately I would expectthe first PRINT statement to be printed but it won't. It seemedthat the print statements would only display the messages afterthe whole processing completed.What's the reason for this sort of behaviour. If so, then we wouldnot be able to print any progress reporting in our scripts.Please comment.Thanks in advance.
Hello,When I try to run the following query on two different SQL Servers,I get error on one of the server (both has same set of data). I wastrying to get rows for ProductCode='XYZ_Q1'.SELECT ProductName, ProductType, ProductDesc FROM Product WHEREDepartmentID=12 AND ProductType > 2000 AND CAST(SUBSTRING(ProductCode,CHARINDEX('_', ProductCode)+2, 1) AS int)=1Example dataProductCode|ProductName|ProductType|DepartmentID|P roductDescXYZ_T_1|Test1|1000|12|TestXYZ_T_2|Test2|1000|12|TestABC_T_1|Test3|1000|11|TestABC_T_2|Test4|1000||11|TestXYZ_Q1|Test5|1000|12|TestABC_Q1|Test6|1000|11|TestIt's trying to cast all values under 'ProductCode' column instead ofapplying to subset with condition 'DeparmentID=12 AND ProductType >2000 'I solved the problem by equating it to whole string rather thantrying to extract the integer part of it. But I wanted find-out reasonas to why this is happenning.Is there any SQL Server setting that's causing this?Thank you very muchManchaiah
I'm trying to grant/deny object permissions in a user database using Enterprise Manager and query tool without success (for €˜public€™ role and individual sql logins). I€™m not getting any error messages. Permission changes just don€™t take effect. Although, there are few objects, which already have permissions granted and I'm able to change permissions for these ones.
I am encountering a problem with reports built on my SQL Server 2005 Reporting Services Server. The reports parameters do not take effect at all? I chose the value from the report parameter list, but the result returned the whole report data. Why is that? And how can I solve this problem? Really need help.
Thanks a lot in advance for any guidance and help.
I just want to change the length of 2 fields. They're CHAR and NVARCHAR type. I want to change the length from 50 to 75.
Will doing this effect the data? Will it delete the data in those columns? I just want to make sure before I do anything. The table contains about 2.5 million rows, so I don't want to mess anything up. I'm aware that the script may take a while to run and finish, but I'm only concerned about data integrity.
Here is my example script syntax to show what I'm planning to do:
Code Snippet
alter table credit alter column writeoffreason char(75) not null;
Does it matter what type of data is in the columns? For now, it's only CHAR and NVARCHAR.
I also read somewhere that you can increase length but cannot decrease without repercussions?
I build reports using reporting services 2005 sp2. I have 3 parameters---> 3 multi value combo box . However when I tick "allow blank value" within the report parameter properties for this drop menu, report WILL NOT run without making selection.(The xml is ok allow blank value set to true) the reports still require you to enter a value for all parameters in the report when the report can and should be able to be run with no parameter defined. the trick "default values for all of the params to "=String.Empty"" is not good .
I found this article but i can't find microsoft hotfix for it .
I had run a stored procedure in my server that update statistics against all user defined tables in my database (MSSQL 7.0).
Since then I am getting errors in my ASP application where I am reffering to adovbs.inc.
Here is an example of errors I get.
Microsoft VBScript runtime error '800a0411'
Name redefined: 'adOpenForwardOnly'
/Essai/adovbs.inc, line 14
Below is the stored procedure I have run against the database.
Can anybody help tank you guys.
CREATE PROCEDURE update_all_stats AS /* This PROCEDURE will run UPDATE STATISTICS against ALL user-defined tables within this database. */ DECLARE @tablename varchar(30) DECLARE @tablename_header varchar(75) DECLARE tnames_cursor CURSOR FOR SELECT name FROM sysobjects WHERE type = 'U' OPEN tnames_cursor FETCH NEXT FROM tnames_cursor INTO @tablename WHILE (@@fetch_status <> -1)
BEGIN IF (@@fetch_status <> -2)
BEGIN SELECT @tablename_header = "Updating " + RTRIM(UPPER(@tablename)) PRINT @tablename_header EXEC ("UPDATE STATISTICS " + @tablename ) END FETCH NEXT FROM tnames_cursor INTO @tablename END PRINT " " PRINT " " SELECT @tablename_header = "************* NO MORE TABLES" + " *************" PRINT @tablename_header PRINT " " PRINT "Statistics have been updated FOR ALL tables." DEALLOCATE tnames_cursor
I have a view that does some calculations on data in the database. Note in the two code snippets, that the columns being used in the calculations are all type FLOAT(53).
My confusion stems from the fact that both code snippets should be functionally the same in my "view" (*snicker* I kill me...), but they return different results which I think are related to rounding issues.
The first snippet:
CREATE VIEW dbo.VIEW_Calculate_PortfolioIndex AS SELECT PP.PortfolioID AS PortfolioID, PP.CreateDate AS CreateDate, (ROUND((PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100.00)), 2) * PP.AvgHighPriceRatio) AS HighIndex, CASE WHEN PPI.CloseIndex IS NULL THEN 100.00 ELSE ROUND((PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100.00)), 2) END AS CloseIndex, (ROUND((PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100.00)), 2) * PP.AvgLowPriceRatio) AS LowIndex, PP.Volume as Volume FROM dbo.PortfolioIndex PPI INNER JOIN dbo.PortfolioPerformance PP ON PPI.PortfolioID = PP.PortfolioID AND PPI.CreateDate = PP.PrevDate GO
and it's result set: PortfolioIDCreateDateHighIndexCloseIndexLowIndexVolume 102/20/2004781.11774.17769.53527896 112/20/2004757.97750.36742.93605740 122/20/2004509.92501.72494.854180516 132/20/2004988.23980.65973.58632337 142/20/20041283.261269.571259.37416145
And the second snippet:
CREATE VIEW dbo.VIEW_Calculate_PortfolioIndex AS SELECT PP.PortfolioID AS PortfolioID, PP.CreateDate AS CreateDate, (CloseIndex * PP.AvgHighPriceRatio) AS HighIndex, CASE WHEN PPI.CloseIndex IS NULL THEN 100.00 ELSE ROUND((PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100.00)), 2) END AS CloseIndex, (CloseIndex * PP.AvgLowPriceRatio) AS LowIndex, PP.Volume as Volume FROM dbo.PortfolioIndex PPI INNER JOIN dbo.PortfolioPerformance PP ON PPI.PortfolioID = PP.PortfolioID AND PPI.CreateDate = PP.PrevDate
which returns a different result set: PortfolioIDCreateDateHighIndexCloseIndexLowIndexVolume 102/20/2004784.52774.17772.89527896 112/20/2004755.64750.36740.64605740 122/20/2004512.43501.72497.294180516 132/20/2004989.77980.65975.1632337 142/20/20041285.991269.571262.05416145
Specifically, I am concerned with the HighIndex and LowIndex values...since the only modification between the two code snippets is that in the second one, the HighIndex and LowIndex calculations use the column name of CloseIndex (as calculated in the select) in the calcs for those two columns, rather than repeating the code used to calculate the CloseIndex column's value.
I am confused as to why the results of the HighIndex and LowIndex caculations are different in the two selects, when the only change (in my view/expectations) is that one references the CloseIndex column, and the other one just reproduces the calculation itself
