@@ERROR Handling

To All Gurus:
I am trying to log some information to another table when @.@.Error returns an
error. Below is the sample code to see how @.@.error works. In this sample
code, I am purposely failing INSERT INTO TEST command and want to log
informaiton in TestLog. Even though there is a error but the insert to
TestLog never works. What is the catch here ?
/* Create Sample Tables */
Create Table Test (ID Smallint)
Create Table TestLog (ID Smallint)
/*Fail the Insert statement */
INSERT INTO TEST Values (10, 20)
/*This insert should work because error exists. */
IF @.@.Error <> 0
BEGIN
INSERT INTO TESTLOG Values (10, 20)
END
/* No Records are found*/
Select * from testlog
If I run this code in pieces one by one, the code works. When I highlight
the entire code and run it, it never makes it to TestLog table.
Thanks in advance.Sorry, the second Insert statment was
INSERT INTO TestLog Values (10)
"Mark" wrote:

> To All Gurus:
> I am trying to log some information to another table when @.@.Error returns
an
> error. Below is the sample code to see how @.@.error works. In this sample
> code, I am purposely failing INSERT INTO TEST command and want to log
> informaiton in TestLog. Even though there is a error but the insert to
> TestLog never works. What is the catch here ?
> /* Create Sample Tables */
> Create Table Test (ID Smallint)
> Create Table TestLog (ID Smallint)
> /*Fail the Insert statement */
> INSERT INTO TEST Values (10, 20)
> /*This insert should work because error exists. */
> IF @.@.Error <> 0
> BEGIN
> INSERT INTO TESTLOG Values (10, 20)
> END
> /* No Records are found*/
> Select * from testlog
> If I run this code in pieces one by one, the code works. When I highlight
> the entire code and run it, it never makes it to TestLog table.
> Thanks in advance.
>
>|||Mark,
See Erland's articles:
http://www.sommarskog.se/error-handling-I.html
and
http://www.sommarskog.se/error-handling-II.html
HTH
Jerry
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:87927DFA-D785-42B2-BF22-972996A8F852@.microsoft.com...
> To All Gurus:
> I am trying to log some information to another table when @.@.Error returns
> an
> error. Below is the sample code to see how @.@.error works. In this sample
> code, I am purposely failing INSERT INTO TEST command and want to log
> informaiton in TestLog. Even though there is a error but the insert to
> TestLog never works. What is the catch here ?
> /* Create Sample Tables */
> Create Table Test (ID Smallint)
> Create Table TestLog (ID Smallint)
> /*Fail the Insert statement */
> INSERT INTO TEST Values (10, 20)
> /*This insert should work because error exists. */
> IF @.@.Error <> 0
> BEGIN
> INSERT INTO TESTLOG Values (10, 20)
> END
> /* No Records are found*/
> Select * from testlog
> If I run this code in pieces one by one, the code works. When I highlight
> the entire code and run it, it never makes it to TestLog table.
> Thanks in advance.
>
>|||I read the article but it still didint make it clear as to why Insert into
TestLog never happened when the entire code was highlighted and executed.
Any comments on that one ?
"Mark" wrote:

> To All Gurus:
> I am trying to log some information to another table when @.@.Error returns
an
> error. Below is the sample code to see how @.@.error works. In this sample
> code, I am purposely failing INSERT INTO TEST command and want to log
> informaiton in TestLog. Even though there is a error but the insert to
> TestLog never works. What is the catch here ?
> /* Create Sample Tables */
> Create Table Test (ID Smallint)
> Create Table TestLog (ID Smallint)
> /*Fail the Insert statement */
> INSERT INTO TEST Values (10, 20)
> /*This insert should work because error exists. */
> IF @.@.Error <> 0
> BEGIN
> INSERT INTO TESTLOG Values (10, 20)
> END
> /* No Records are found*/
> Select * from testlog
> If I run this code in pieces one by one, the code works. When I highlight
> the entire code and run it, it never makes it to TestLog table.
> Thanks in advance.
>
>|||The insert failure aborts the batch, not the statement. If you put GO
between the statements, it should work. However you cannot use a
multi-batch technique inside of an object (e.g. stored procedure).
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:52B1E1E2-2422-4728-8447-FA4CB25F576E@.microsoft.com...
>I read the article but it still didint make it clear as to why Insert into
> TestLog never happened when the entire code was highlighted and executed.
> Any comments on that one ?
> "Mark" wrote:
>|||Mark,
I think it has to do with the type of error you're creating. If you add a
CHECK constraint to Test and perform your insert and it violates the insert
then the value IS inserted into the TestLog table.
HTH
Jerry
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:52B1E1E2-2422-4728-8447-FA4CB25F576E@.microsoft.com...
>I read the article but it still didint make it clear as to why Insert into
> TestLog never happened when the entire code was highlighted and executed.
> Any comments on that one ?
> "Mark" wrote:
>|||Thanks to both Jerry and Aaron. You are right, it has to do with what kind o
f
insert error it is. It either rollsback the batch or logs the info based on
the type of error. I created two scenarions and in one case it works and in
other one, it doesnt.
Once again thanks to both
"Jerry Spivey" wrote:

> Mark,
> I think it has to do with the type of error you're creating. If you add a
> CHECK constraint to Test and perform your insert and it violates the inser
t
> then the value IS inserted into the TestLog table.
> HTH
> Jerry
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:52B1E1E2-2422-4728-8447-FA4CB25F576E@.microsoft.com...
>
>|||Mark (Mark@.discussions.microsoft.com) writes:
> Thanks to both Jerry and Aaron. You are right, it has to do with what
> kind of insert error it is. It either rollsback the batch or logs the
> info based on the type of error. I created two scenarions and in one
> case it works and in other one, it doesnt.
As noted in my article, an error can lead to termination on three
different levels:
1) Statement
2) Scope
3) Bacth.
Your error was of the second kind. Scope-aborting errors are, as far as
I know, always compilation errors. What is tricky, is that due to
deferred named resolution, compilation errors can happen at run-time.
Consider:
Create Table Test (ID Smallint)
Create Table TestLog (ID Smallint)
--go
print 'Hello!'
/*Fail the Insert statement */
INSERT INTO Test Values (10, 20)
/*This insert should work because error exists. */
IF @.@.Error <> 0
BEGIN
INSERT INTO TestLog Values (10)
END
go
SELECT * FROM TestLog
This script will print Hello!, but if you uncomment the go, it will not.
This is because in the script as it stands, Test does not exist, so SQL
Server defers compilation of that statement. If you uncomment the go,
the tables exists when the batch is compiled, and thus you get the error
directly, so execution never starts.
Finally, note that if you write:
INSERT INTO Test(ID) Values(10, 20)
the batch always fails to compile, as SQL Server does not need to know
the table definition. Incidently, most people agree that INSERT without
a column list is not good practice.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

.net version of Detect Anomalies in Excel

Is there an equivalent or similar .net sample for detecting data Anomalies?

Detect Anomalies in Excel

http://zones.advisor.com/doc/14413

Please check out the Data Mining Addins for Office 2007. There's a task "Detect Outliers" which might work for you. More details about the addins are available at http://www.sqlserverdatamining.com

Thanks

|||I can say that I've never even tried it ... but I would need to obtain the Add-in source code and alter it for my purposes. Office/Excel will not suffice. I need to expose this functionality via a web interface and so it must also be a highly scalable solution. C# preferable.|||

This tip http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/861.aspx gives the basic idea. You can also check out the live sample at http://www.sqlserverdatamining.com/DMCommunity/LiveSamples/46.aspx that shows anomaly detection in a web application - source code is provided. The Excel Addin adds the automatic creation of a mining model with some nice heuristics for column selection, and the visualization part. Those are mostly dependent on your application and the Excel code wouldn't really help I believe.

The two sources above should give you everything you need. I once presented the implementation of the above web application at TechEd, so it should be in the TechEd archives (although I forget which year it was)

-Jamie

.net data provider / Data processing extention

Is there somewhere a real usable sample application for this?
I found some samples, but I'm unable to do anything with it.
There are some source code files in the knowledge base. But I don't know how
to apply the Instruction on compile them.
I need realy step by step instructions for a novice. A hint for a good book
would be ok too.
(I'm wonder about the jumbs between gradients in all the documentation and
articles. Example: Open Visual Studio, select ..., to add a class do..., then
implement correct marshaling, use all the named pipes correct, find and
implement Windows APIs and thats realy all, just doit! References: Blind link
Nr 1; Blind link Nr 2; ...)There is a section in msdn describing process of creating a data extension:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsamples/htm/rss_sampleapps_v1_16g2.asp
There is also a sample that is shipped with RS:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsamples/htm/rss_sampleapps_v1_8spu.asp
I will work with you thru the steps. Let's start with trying to compile and
run the sample.
Let me know where you get the first problem.
--
Alex Mineev
Software Design Engineer. Report expressions; Code Access Security; Xml;
SQE.
This posting is provided "AS IS" with no warranties, and confers no rights
"Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
news:F2E68AC2-DEFE-4593-A3AE-FD6FF3CB93F2@.microsoft.com...
> Is there somewhere a real usable sample application for this?
> I found some samples, but I'm unable to do anything with it.
> There are some source code files in the knowledge base. But I don't know
how
> to apply the Instruction on compile them.
> I need realy step by step instructions for a novice. A hint for a good
book
> would be ok too.
> (I'm wonder about the jumbs between gradients in all the documentation and
> articles. Example: Open Visual Studio, select ..., to add a class do...,
then
> implement correct marshaling, use all the named pipes correct, find and
> implement Windows APIs and thats realy all, just doit! References: Blind
link
> Nr 1; Blind link Nr 2; ...)|||Thank you very much!!!
This seems to be a good tip. I try it on monday and post a feedback.
"Alexandre Mineev [MSFT]" wrote:
> There is a section in msdn describing process of creating a data extension:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsamples/htm/rss_sampleapps_v1_16g2.asp
> There is also a sample that is shipped with RS:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsamples/htm/rss_sampleapps_v1_8spu.asp
> I will work with you thru the steps. Let's start with trying to compile and
> run the sample.
> Let me know where you get the first problem.
> --
> Alex Mineev
> Software Design Engineer. Report expressions; Code Access Security; Xml;
> SQE.
> This posting is provided "AS IS" with no warranties, and confers no rights
> "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> news:F2E68AC2-DEFE-4593-A3AE-FD6FF3CB93F2@.microsoft.com...
> > Is there somewhere a real usable sample application for this?
> > I found some samples, but I'm unable to do anything with it.
> >
> > There are some source code files in the knowledge base. But I don't know
> how
> > to apply the Instruction on compile them.
> >
> > I need realy step by step instructions for a novice. A hint for a good
> book
> > would be ok too.
> >
> > (I'm wonder about the jumbs between gradients in all the documentation and
> > articles. Example: Open Visual Studio, select ..., to add a class do...,
> then
> > implement correct marshaling, use all the named pipes correct, find and
> > implement Windows APIs and thats realy all, just doit! References: Blind
> link
> > Nr 1; Blind link Nr 2; ...)
>
>|||Now I know the steps required to deploy the assembly. That's an part of the
whole way.
It is still to much for me to get this transfered so I can create a
Dataprovider for a connection to a Oracle database.
I found an article about .net dataprovider with sample files for all classes
(it's called DotNetDataProviderTemplate). I found no way to get this sample
compiled and runing. Although I'm not try to implement the reporting services
interface.
I created a new Project and added all the sample .vb files. But compile this
seems to be imposible, what ever I'm doing. Error are "Type '...' not
defiened and classes are ambiguous. But the Types are defined and the
classenames are unique.
If I would overcome all immediate barriers, I don't know how to set
permission to access a oracle database. I recognized that it is necessary to
set permission. But documented is only SQL Server, not oracle.
I'm using allways VB, a VB Sample would be great. But if the sample is
complete enough C# will be ok too. It seems, that C# is easy to understand.
If no oracle sample is available a complete sample to access a SQL Server
database will be also good.
Thank you for your effort.
"Alexandre Mineev [MSFT]" wrote:
> There is a section in msdn describing process of creating a data extension:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsamples/htm/rss_sampleapps_v1_16g2.asp
> There is also a sample that is shipped with RS:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsamples/htm/rss_sampleapps_v1_8spu.asp
> I will work with you thru the steps. Let's start with trying to compile and
> run the sample.
> Let me know where you get the first problem.
> --
> Alex Mineev
> Software Design Engineer. Report expressions; Code Access Security; Xml;
> SQE.
> This posting is provided "AS IS" with no warranties, and confers no rights
> "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> news:F2E68AC2-DEFE-4593-A3AE-FD6FF3CB93F2@.microsoft.com...
> > Is there somewhere a real usable sample application for this?
> > I found some samples, but I'm unable to do anything with it.
> >
> > There are some source code files in the knowledge base. But I don't know
> how
> > to apply the Instruction on compile them.
> >
> > I need realy step by step instructions for a novice. A hint for a good
> book
> > would be ok too.
> >
> > (I'm wonder about the jumbs between gradients in all the documentation and
> > articles. Example: Open Visual Studio, select ..., to add a class do...,
> then
> > implement correct marshaling, use all the named pipes correct, find and
> > implement Windows APIs and thats realy all, just doit! References: Blind
> link
> > Nr 1; Blind link Nr 2; ...)
>
>|||Implementing RS IDbConnection, IDbCommand etc interfaces is easier than full
interfaces defined in System.Data.
The "type not found" errors are probably coming for RS interfaces. You need
to copy two dlls from reportserver/bin:
Microsoft.ReportingServices.Interfaces and ...DataExtensions.dll and you
need to add references to both of the dlls to your project
--
Alex Mineev
Software Design Engineer. Report expressions; Code Access Security; Xml;
SQE.
This posting is provided "AS IS" with no warranties, and confers no rights
"Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
news:33E43A47-1654-4879-9590-0FEE8BF911BC@.microsoft.com...
> Now I know the steps required to deploy the assembly. That's an part of
the
> whole way.
> It is still to much for me to get this transfered so I can create a
> Dataprovider for a connection to a Oracle database.
> I found an article about .net dataprovider with sample files for all
classes
> (it's called DotNetDataProviderTemplate). I found no way to get this
sample
> compiled and runing. Although I'm not try to implement the reporting
services
> interface.
> I created a new Project and added all the sample .vb files. But compile
this
> seems to be imposible, what ever I'm doing. Error are "Type '...' not
> defiened and classes are ambiguous. But the Types are defined and the
> classenames are unique.
> If I would overcome all immediate barriers, I don't know how to set
> permission to access a oracle database. I recognized that it is necessary
to
> set permission. But documented is only SQL Server, not oracle.
> I'm using allways VB, a VB Sample would be great. But if the sample is
> complete enough C# will be ok too. It seems, that C# is easy to
understand.
> If no oracle sample is available a complete sample to access a SQL Server
> database will be also good.
> Thank you for your effort.
>
> "Alexandre Mineev [MSFT]" wrote:
> > There is a section in msdn describing process of creating a data
extension:
> >
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsamples/htm/rss_sampleapps_v1_16g2.asp
> >
> > There is also a sample that is shipped with RS:
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsamples/htm/rss_sampleapps_v1_8spu.asp
> >
> > I will work with you thru the steps. Let's start with trying to compile
and
> > run the sample.
> > Let me know where you get the first problem.
> >
> > --
> > Alex Mineev
> > Software Design Engineer. Report expressions; Code Access Security; Xml;
> > SQE.
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights
> >
> > "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> > news:F2E68AC2-DEFE-4593-A3AE-FD6FF3CB93F2@.microsoft.com...
> > > Is there somewhere a real usable sample application for this?
> > > I found some samples, but I'm unable to do anything with it.
> > >
> > > There are some source code files in the knowledge base. But I don't
know
> > how
> > > to apply the Instruction on compile them.
> > >
> > > I need realy step by step instructions for a novice. A hint for a good
> > book
> > > would be ok too.
> > >
> > > (I'm wonder about the jumbs between gradients in all the documentation
and
> > > articles. Example: Open Visual Studio, select ..., to add a class
do...,
> > then
> > > implement correct marshaling, use all the named pipes correct, find
and
> > > implement Windows APIs and thats realy all, just doit! References:
Blind
> > link
> > > Nr 1; Blind link Nr 2; ...)
> >
> >
> >|||Thank you, but somehow it doesn't work, wath ever I try.
What exactly must be written in:
Public Sub Open() Implements IDbConnection.Open
to open the connection to database?
As mentioned in my first post I'm a novice in .net and reporting services
too. Something very simple must be wrong. And I only find help like you must
implement ...Interface. But I can't find how to do this exactly.
"Alexandre Mineev [MSFT]" wrote:
> Implementing RS IDbConnection, IDbCommand etc interfaces is easier than full
> interfaces defined in System.Data.
> The "type not found" errors are probably coming for RS interfaces. You need
> to copy two dlls from reportserver/bin:
> Microsoft.ReportingServices.Interfaces and ...DataExtensions.dll and you
> need to add references to both of the dlls to your project
> --
> Alex Mineev
> Software Design Engineer. Report expressions; Code Access Security; Xml;
> SQE.
> This posting is provided "AS IS" with no warranties, and confers no rights
> "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> news:33E43A47-1654-4879-9590-0FEE8BF911BC@.microsoft.com...
> > Now I know the steps required to deploy the assembly. That's an part of
> the
> > whole way.
> >
> > It is still to much for me to get this transfered so I can create a
> > Dataprovider for a connection to a Oracle database.
> >
> > I found an article about .net dataprovider with sample files for all
> classes
> > (it's called DotNetDataProviderTemplate). I found no way to get this
> sample
> > compiled and runing. Although I'm not try to implement the reporting
> services
> > interface.
> > I created a new Project and added all the sample .vb files. But compile
> this
> > seems to be imposible, what ever I'm doing. Error are "Type '...' not
> > defiened and classes are ambiguous. But the Types are defined and the
> > classenames are unique.
> >
> > If I would overcome all immediate barriers, I don't know how to set
> > permission to access a oracle database. I recognized that it is necessary
> to
> > set permission. But documented is only SQL Server, not oracle.
> >
> > I'm using allways VB, a VB Sample would be great. But if the sample is
> > complete enough C# will be ok too. It seems, that C# is easy to
> understand.
> > If no oracle sample is available a complete sample to access a SQL Server
> > database will be also good.
> >
> > Thank you for your effort.
> >
> >
> > "Alexandre Mineev [MSFT]" wrote:
> >
> > > There is a section in msdn describing process of creating a data
> extension:
> > >
> > >
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsamples/htm/rss_sampleapps_v1_16g2.asp
> > >
> > > There is also a sample that is shipped with RS:
> > >
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsamples/htm/rss_sampleapps_v1_8spu.asp
> > >
> > > I will work with you thru the steps. Let's start with trying to compile
> and
> > > run the sample.
> > > Let me know where you get the first problem.
> > >
> > > --
> > > Alex Mineev
> > > Software Design Engineer. Report expressions; Code Access Security; Xml;
> > > SQE.
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights
> > >
> > > "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> > > news:F2E68AC2-DEFE-4593-A3AE-FD6FF3CB93F2@.microsoft.com...
> > > > Is there somewhere a real usable sample application for this?
> > > > I found some samples, but I'm unable to do anything with it.
> > > >
> > > > There are some source code files in the knowledge base. But I don't
> know
> > > how
> > > > to apply the Instruction on compile them.
> > > >
> > > > I need realy step by step instructions for a novice. A hint for a good
> > > book
> > > > would be ok too.
> > > >
> > > > (I'm wonder about the jumbs between gradients in all the documentation
> and
> > > > articles. Example: Open Visual Studio, select ..., to add a class
> do...,
> > > then
> > > > implement correct marshaling, use all the named pipes correct, find
> and
> > > > implement Windows APIs and thats realy all, just doit! References:
> Blind
> > > link
> > > > Nr 1; Blind link Nr 2; ...)
> > >
> > >
> > >
>
>

**COMPLICATED RESULT**

Hi
I've a view in MS SQL 2000 with following structure and data sample, and I
want to get the following result ,how it's possible with a select statement?
table1: ( Code1 ,Price1 , Code2 ,Price2) , Code1+Code2 is uique
Code1 Price1 Code2 Price2 percent
-- -- -- -- --
A 12 B 30 3
A 12 C 40 10
A 12 D 10 7
A 12 E 15 80
B 30 C 40 2
B 30 D 10 18
B 30 E 15 80
C 40 D 10 6
C 40 E 15 24
D 10 E 15 70
The Process:
At first I want to make the price(12) of code A to 0 by dividing it to code2
(from B to E) with special percent so the reslut of this part will be like
the following:
Code1 Price1 Code2 Price2 percent
-- -- -- -- --
A 12 B 30 3
3%*12
A 12 C 40 10
10%*12
A 12 D 10 7
7%*12
A 12 E 15 80
80%*12
B 30 C 40 2
B 30 D 10 18
B 30 E 15 80
C 40 D 10 6
C 40 E 15 24
D 10 E 15 70
now we do the same for next code1 (B) ,but here there's a bit difeerence
cause here we sholud add (3%*12+30) this to the 30 which is located in
Price1 inront of B ,and then divide the whole amount to code2 (from C to E)
Code1 Price1 Code2 Price2 percent
-- -- -- -- --
A 12 B 30 3
3%*12
A 12 C 40 10
10%*12
A 12 D 10 7
7%*12
A 12 E 15 80
80%*12
B 30 C 40 2
2%*(30+ 3%*12)
B 30 D 10 18
18%*(30+ 3%*12)
B 30 E 15 80
80%*(30+ 3%*12)
C 40 D 10 6
C 40 E 15 24
D 10 E 15 70
and for C:
Code1 Price1 Code2 Price2 percent
-- -- -- -- --
A 12 B 30 3
3%*12
A 12 C 40 10
10%*12
A 12 D 10 7
7%*12
A 12 E 15 80
80%*12
B 30 C 40 2
2%*(30+ 3%*12)
B 30 D 10 18
18%*(30+ 3%*12)
B 30 E 15 80
80%*(30+ 3%*12)
C 40 D 10 6
6%*(40+2%*(30+ 3%*12))
C 40 E 15 24
24%*(40+2%*(30+ 3%*12))
D 10 E 15 70
Final result is to get the final column,Any help would be thankful.assume ur table name is a1 i give a sample query try this logic for further
results
select CASE WHEN C1='A' THEN
CONVERT(VARCHAR,C1)+CONVERT(VARCHAR,P)+'
%*'+CONVERT(VARCHAR,P1)
WHEN C1='B' THEN
CONVERT(VARCHAR,P)+'%*('+(SELECT CONVERT(VARCHAR,P2)+ '+'
+CONVERT(VARCHAR,P)+'%*'+CONVERT(VARCHAR
,P1)+')' FROM A1 WHERE C2='B' AND
C1='A')
END
AS C1,P1,C2,P2,P from A1
regards
balram
"maryam rezvani" wrote:

> Hi
> I've a view in MS SQL 2000 with following structure and data sample, and I
> want to get the following result ,how it's possible with a select statemen
t?
> table1: ( Code1 ,Price1 , Code2 ,Price2) , Code1+Code2 is uique
> Code1 Price1 Code2 Price2 percent
> -- -- -- -- --
> A 12 B 30 3
> A 12 C 40 10
> A 12 D 10 7
> A 12 E 15 80
> B 30 C 40 2
> B 30 D 10 18
> B 30 E 15 80
> C 40 D 10 6
> C 40 E 15 24
> D 10 E 15 70
>
> The Process:
> At first I want to make the price(12) of code A to 0 by dividing it to cod
e2
> (from B to E) with special percent so the reslut of this part will be like
> the following:
> Code1 Price1 Code2 Price2 percent
> -- -- -- -- --
> A 12 B 30 3
> 3%*12
> A 12 C 40 10
> 10%*12
> A 12 D 10 7
> 7%*12
> A 12 E 15 80
> 80%*12
> B 30 C 40 2
> B 30 D 10 18
> B 30 E 15 80
> C 40 D 10 6
> C 40 E 15 24
> D 10 E 15 70
> now we do the same for next code1 (B) ,but here there's a bit difeerence
> cause here we sholud add (3%*12+30) this to the 30 which is located in
> Price1 inront of B ,and then divide the whole amount to code2 (from C to E
)
> Code1 Price1 Code2 Price2 percent
> -- -- -- -- --
> A 12 B 30 3
> 3%*12
> A 12 C 40 10
> 10%*12
> A 12 D 10 7
> 7%*12
> A 12 E 15 80
> 80%*12
> B 30 C 40 2
> 2%*(30+ 3%*12)
> B 30 D 10 18
> 18%*(30+ 3%*12)
> B 30 E 15 80
> 80%*(30+ 3%*12)
> C 40 D 10 6
> C 40 E 15 24
> D 10 E 15 70
> and for C:
> Code1 Price1 Code2 Price2 percent
> -- -- -- -- --
> A 12 B 30 3
> 3%*12
> A 12 C 40 10
> 10%*12
> A 12 D 10 7
> 7%*12
> A 12 E 15 80
> 80%*12
> B 30 C 40 2
> 2%*(30+ 3%*12)
> B 30 D 10 18
> 18%*(30+ 3%*12)
> B 30 E 15 80
> 80%*(30+ 3%*12)
> C 40 D 10 6
> 6%*(40+2%*(30+ 3%*12))
> C 40 E 15 24
> 24%*(40+2%*(30+ 3%*12))
> D 10 E 15 70
> Final result is to get the final column,Any help would be thankful.
>
>
>|||On Mon, 18 Apr 2005 11:57:26 +0430, maryam rezvani wrote:
>Hi
>I've a view in MS SQL 2000 with following structure and data sample, and I
>want to get the following result ,how it's possible with a select statement?[/color
]
(snip)
Hi maryam,
I don't think it can be done with one single statement. Or rather: if
the maximum number of iterations is known in advance, it might be
possible, but you'd end up with a monster query that won't even print on
a single page. And if the maximum number of iterations is not known,
then it's impossible (though that might change with the introduction of
recursive CTEs in SQL Server 2005).
However, here's a procedure that will do this in as few passes over the
data as possible. I hope I did get the criteria for choosing which
prices should be added to which rows right - I assumed I should not base
this on the alphabetic order of the codes.
CREATE TABLE table1
(Code1 char(1) NOT NULL,
Price1 int NOT NULL,
Code2 char(1) NOT NULL,
Price2 int NOT NULL,
[Percent] int NOT NULL,
Total numeric (10,4) DEFAULT NULL,
PRIMARY KEY (Code1, Code2))
go
INSERT INTO table1 (Code1, Price1, Code2, Price2, [Percent])
SELECT 'A', 12, 'B', 30, 3 UNION ALL
SELECT 'A', 12, 'C', 40, 10 UNION ALL
SELECT 'A', 12, 'D', 10, 7 UNION ALL
SELECT 'A', 12, 'E', 15, 80 UNION ALL
SELECT 'B', 30, 'C', 40, 2 UNION ALL
SELECT 'B', 30, 'D', 10, 18 UNION ALL
SELECT 'B', 30, 'E', 15, 80 UNION ALL
SELECT 'C', 40, 'D', 10, 6 UNION ALL
SELECT 'C', 40, 'E', 15, 24 UNION ALL
SELECT 'D', 10, 'E', 15, 70
go
-- First step
UPDATE table1
SET Total = Price1 * [Percent] / 100.0
WHERE NOT EXISTS
(SELECT *
FROM table1 AS a
WHERE a.Code2 = table1.Code1)
-- Loop over remaining steps
Again:
UPDATE table1
SET Total = ((SELECT MAX(Total)
FROM table1 AS a
WHERE a.Code2 = table1.Code1)
+ Price1) * [Percent] / 100.0
WHERE NOT EXISTS
(SELECT *
FROM table1 AS a
WHERE a.Code2 = table1.Code1
AND a.Total IS NULL)
AND Total IS NULL
-- Repeat until done
IF @.@.ROWCOUNT > 0 GOTO Again
-- Show results
SELECT * FROM table1
go
DROP TABLE table1
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||The best way to do this would be to use a spreadsheet that queries for the r
aw
data. Barring that, what does the raw data on which the view is based look l
ike?
Does it look something like:
Create Table Codes
(
Code Char(1)
, Price SmallInt
)
How is the percentage calculated?
Thomas|||Your Engish is bad, but I think you are doing a hierarchical
calculation. You night want ot get a copy of TREES & HIERARCHIES IN
SQL and look at examples of such things in the book. These calculation
are much easier with the nested sets model.