How would you write the SQL statement to do a cross-tab queries.
My Table
DCNID-unique ID
AuditID-FK to the main table
DCN-int
Error Type (combo box with values tooth, date, provider, etc)
Line ID (combo box with values 1-40)
Adjustment Code-text
I would like my report to look like this.
DCN
LineID Tooth Date Provider
1 1 1
2 1
3
4
5
..
40
Thank you so much!
CristyYou could have done some research, this question has been asked and aswered thousands of times, here are some solutions from asktom:
http://asktom.oracle.com/pls/ask/f?p=4950:8:16663421538065257584::NO::F4950_P8_DISP LAYID,F4950_P8_CRITERIA:7086279412131,
http://asktom.oracle.com/pls/ask/f?p=4950:8:16663421538065257584::NO::F4950_P8_DISP LAYID,F4950_P8_CRITERIA:419593546543,
http://asktom.oracle.com/pls/ask/f?p=4950:8:16663421538065257584::NO::F4950_P8_DISP LAYID,F4950_P8_CRITERIA:766825833740,
http://asktom.oracle.com/pls/ask/f?p=4950:8:16663421538065257584::NO::F4950_P8_DISP LAYID,F4950_P8_CRITERIA:925229353765,
:rolleyes:|||How would you write the SQL statement to do a cross-tab queries.that depends -- what database system are you using?
Showing posts with label write. Show all posts
Showing posts with label write. Show all posts
Thursday, March 29, 2012
Help with conditional Count formula
I am trying to write a simple formula that will count all of the Bankrupt status that appear in my report but cannot seem to get anything to work properly.
The pseudocode formula I have attempted was
Count ({Field}) where {Field} = "BANKRUPT"
I realize Where is not an operator, but its in essence what I am looking at.
I have attempted Do While and While Do but I only get true returns which shouldn't occur as no bankrupts where present.
If someone could help me I would greatly appreciate it.Create a running total, let's call it 'BANKRUPT':
Fields to Summarize: {your_table.field}
Type of summary: Count
Evaluate: check 'use a formula' then ->x+2 button and enter your code:
{your_table.field} = "BANKRUPT"
Reset: check 'Never', if you want to summarize it for a whole report or
check 'On change of group' and select the group you would like to do this calculation for.|||Thanks for the help!
I got it working now! :)
The pseudocode formula I have attempted was
Count ({Field}) where {Field} = "BANKRUPT"
I realize Where is not an operator, but its in essence what I am looking at.
I have attempted Do While and While Do but I only get true returns which shouldn't occur as no bankrupts where present.
If someone could help me I would greatly appreciate it.Create a running total, let's call it 'BANKRUPT':
Fields to Summarize: {your_table.field}
Type of summary: Count
Evaluate: check 'use a formula' then ->x+2 button and enter your code:
{your_table.field} = "BANKRUPT"
Reset: check 'Never', if you want to summarize it for a whole report or
check 'On change of group' and select the group you would like to do this calculation for.|||Thanks for the help!
I got it working now! :)
Tuesday, March 27, 2012
Help with CASE statement
I need some help with a case statement that I'm trying to write. I wrote thi
s
query a couple of w
s ago, but then my sql server had a hardware failure
and I lost all of my stored procs and now I can't remember how I implemented
it. I'm trying to write a dynamic "IN" statement using a case statement, but
I can't get the syntax quite right. Here's an example of what I'm trying to
do (using different data). I realize this could be simplified using a decode
table, but that isn't an option in this case. My problem is how I'm handling
the part of the statement after each "THEN" statement (I might have used a
Convert statement, but I don't remember). I think it's reading my values as
one long string (i.e.-'Apples, Oranges, Bananas) instead of separate values
(i.e.-'Apples', 'Oranges', 'Bananas'). Thanks in advance for your help.
SELECT column1
FROM tblMyTable
WHERE column2 IN(
CASE @.inputVariable
WHEN 'Fruit' THEN 'Apples' + ',' + 'Oranges' + ', ' + 'Bananas'
WHEN 'Vegetable' THEN 'Corn' + ', ' + 'Green Beans'
END
)You cannot use in like that..
try this
SELECT column1
FROM tblMyTable
WHERE (@.inputVariable = 'fruit' and
column2 IN( 'Apples' ,'Oranges' ,'Bananas'))
or
(@.inputVariable = 'Vegetable' and column2 IN('Corn' ,'Green Beans'))
Hope this helps.
"Dustin" wrote:
> I need some help with a case statement that I'm trying to write. I wrote t
his
> query a couple of ws ago, but then my sql server had a hardware failure
> and I lost all of my stored procs and now I can't remember how I implement
ed
> it. I'm trying to write a dynamic "IN" statement using a case statement, b
ut
> I can't get the syntax quite right. Here's an example of what I'm trying t
o
> do (using different data). I realize this could be simplified using a deco
de
> table, but that isn't an option in this case. My problem is how I'm handli
ng
> the part of the statement after each "THEN" statement (I might have used a
> Convert statement, but I don't remember). I think it's reading my values a
s
> one long string (i.e.-'Apples, Oranges, Bananas) instead of separate value
s
> (i.e.-'Apples', 'Oranges', 'Bananas'). Thanks in advance for your help.
> SELECT column1
> FROM tblMyTable
> WHERE column2 IN(
> CASE @.inputVariable
> WHEN 'Fruit' THEN 'Apples' + ',' + 'Oranges' + ', ' + 'Bananas'
> WHEN 'Vegetable' THEN 'Corn' + ', ' + 'Green Beans'
> END
> )|||Hi, Justin
The best way would be to use a table for this, but since you say that
it's not an option, try something like this:
SELECT column1
FROM tblMyTable
WHERE @.inputVariable='Fruit' AND column2 IN
('Apples','Oranges','Bananas')
OR @.inputVariable='Vegetable' AND column2 IN ('Corn','Green Beans')
Razvan|||Dustin,
CASE is an expression (not a statement) that returns a scalar value. It
cannot return a set of values. In your code the CASE expression generates a
single character string value made of the concatenated elements. e.g., when
@.inputVariable = 'Fruit', you logically get:
SELECT column1
FROM tblMyTable
WHERE column2 IN('Apples, Oranges, Bananas');
Which is logically equivalent to:
SELECT column1
FROM tblMyTable
WHERE column2 = 'Apples, Oranges, Bananas';
Which probably isn't what you're after.
Here are a couple of options (not tested); I suspect the former will perform
better:
IF @.inputVariable = 'Fruit'
SELECT column1
FROM tblMyTable
WHERE column2 IN('Apples', 'Oranges', 'Bananas');
ELSE IF @.inputVariable = 'Vegetable'
SELECT column1
FROM tblMyTable
WHERE column2 IN('Corn', 'Green Beans');
SELECT column1
FROM tblMyTable
WHERE (@.inputVariable = 'Fruit'
AND column2 IN('Apples', 'Oranges', 'Bananas'))
OR (@.inputVariable = 'Vegetable'
AND column2 IN('Corn', 'Green Beans'));
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"Dustin" <Dustin@.discussions.microsoft.com> wrote in message
news:475EA188-4D13-4613-8478-E9C268572184@.microsoft.com...
>I need some help with a case statement that I'm trying to write. I wrote
>this
> query a couple of ws ago, but then my sql server had a hardware failure
> and I lost all of my stored procs and now I can't remember how I
> implemented
> it. I'm trying to write a dynamic "IN" statement using a case statement,
> but
> I can't get the syntax quite right. Here's an example of what I'm trying
> to
> do (using different data). I realize this could be simplified using a
> decode
> table, but that isn't an option in this case. My problem is how I'm
> handling
> the part of the statement after each "THEN" statement (I might have used a
> Convert statement, but I don't remember). I think it's reading my values
> as
> one long string (i.e.-'Apples, Oranges, Bananas) instead of separate
> values
> (i.e.-'Apples', 'Oranges', 'Bananas'). Thanks in advance for your help.
> SELECT column1
> FROM tblMyTable
> WHERE column2 IN(
> CASE @.inputVariable
> WHEN 'Fruit' THEN 'Apples' + ',' + 'Oranges' + ', ' + 'Bananas'
> WHEN 'Vegetable' THEN 'Corn' + ', ' + 'Green Beans'
> END
> )|||of course with the braces in place ;)
--
"Razvan Socol" wrote:
> Hi, Justin
> The best way would be to use a table for this, but since you say that
> it's not an option, try something like this:
> SELECT column1
> FROM tblMyTable
> WHERE @.inputVariable='Fruit' AND column2 IN
> ('Apples','Oranges','Bananas')
> OR @.inputVariable='Vegetable' AND column2 IN ('Corn','Green Beans')
> Razvan
>|||I believe it will work fine without the extra parenthesis, but the
parenthesis will make it easier to read and leave no question as to what is
intended.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:57F875E5-4A0C-4DBA-956D-7EE9640E5D1A@.microsoft.com...
> of course with the braces in place ;)
> --
>
>
> "Razvan Socol" wrote:
>|||boy.. thats a news to me.. Always thought AND and OR had the same precedence
.
thanks for pointing that out. Though I will never use it without braces :)
--
"Jim Underwood" wrote:
> I believe it will work fine without the extra parenthesis, but the
> parenthesis will make it easier to read and leave no question as to what i
s
> intended.
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:57F875E5-4A0C-4DBA-956D-7EE9640E5D1A@.microsoft.com...
>
>|||I never use it without parenthesis either. It just gets too confusing, and
too easy to get the wrong results. That and I still forget sometimes that
AND has a higher precedence than OR, so the parens protect me from myself.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:7EAE1F57-00B1-4002-8B32-E6BCC8DDCB84@.microsoft.com...
> boy.. thats a news to me.. Always thought AND and OR had the same
precedence..
> thanks for pointing that out. Though I will never use it without braces :)
> --
>
>
> "Jim Underwood" wrote:
>
is
that|||Thank you all for your help. I was able to solve the problem using the
following suggested syntax:
SELECT column1
FROM tblMyTable
WHERE (@.inputVariable = 'Fruit'
AND column2 IN('Apples', 'Oranges', 'Bananas'))
OR (@.inputVariable = 'Vegetable'
AND column2 IN('Corn', 'Green Beans'));
s
query a couple of w
s ago, but then my sql server had a hardware failureand I lost all of my stored procs and now I can't remember how I implemented
it. I'm trying to write a dynamic "IN" statement using a case statement, but
I can't get the syntax quite right. Here's an example of what I'm trying to
do (using different data). I realize this could be simplified using a decode
table, but that isn't an option in this case. My problem is how I'm handling
the part of the statement after each "THEN" statement (I might have used a
Convert statement, but I don't remember). I think it's reading my values as
one long string (i.e.-'Apples, Oranges, Bananas) instead of separate values
(i.e.-'Apples', 'Oranges', 'Bananas'). Thanks in advance for your help.
SELECT column1
FROM tblMyTable
WHERE column2 IN(
CASE @.inputVariable
WHEN 'Fruit' THEN 'Apples' + ',' + 'Oranges' + ', ' + 'Bananas'
WHEN 'Vegetable' THEN 'Corn' + ', ' + 'Green Beans'
END
)You cannot use in like that..
try this
SELECT column1
FROM tblMyTable
WHERE (@.inputVariable = 'fruit' and
column2 IN( 'Apples' ,'Oranges' ,'Bananas'))
or
(@.inputVariable = 'Vegetable' and column2 IN('Corn' ,'Green Beans'))
Hope this helps.
"Dustin" wrote:
> I need some help with a case statement that I'm trying to write. I wrote t
his
> query a couple of w
s ago, but then my sql server had a hardware failure> and I lost all of my stored procs and now I can't remember how I implement
ed
> it. I'm trying to write a dynamic "IN" statement using a case statement, b
ut
> I can't get the syntax quite right. Here's an example of what I'm trying t
o
> do (using different data). I realize this could be simplified using a deco
de
> table, but that isn't an option in this case. My problem is how I'm handli
ng
> the part of the statement after each "THEN" statement (I might have used a
> Convert statement, but I don't remember). I think it's reading my values a
s
> one long string (i.e.-'Apples, Oranges, Bananas) instead of separate value
s
> (i.e.-'Apples', 'Oranges', 'Bananas'). Thanks in advance for your help.
> SELECT column1
> FROM tblMyTable
> WHERE column2 IN(
> CASE @.inputVariable
> WHEN 'Fruit' THEN 'Apples' + ',' + 'Oranges' + ', ' + 'Bananas'
> WHEN 'Vegetable' THEN 'Corn' + ', ' + 'Green Beans'
> END
> )|||Hi, Justin
The best way would be to use a table for this, but since you say that
it's not an option, try something like this:
SELECT column1
FROM tblMyTable
WHERE @.inputVariable='Fruit' AND column2 IN
('Apples','Oranges','Bananas')
OR @.inputVariable='Vegetable' AND column2 IN ('Corn','Green Beans')
Razvan|||Dustin,
CASE is an expression (not a statement) that returns a scalar value. It
cannot return a set of values. In your code the CASE expression generates a
single character string value made of the concatenated elements. e.g., when
@.inputVariable = 'Fruit', you logically get:
SELECT column1
FROM tblMyTable
WHERE column2 IN('Apples, Oranges, Bananas');
Which is logically equivalent to:
SELECT column1
FROM tblMyTable
WHERE column2 = 'Apples, Oranges, Bananas';
Which probably isn't what you're after.
Here are a couple of options (not tested); I suspect the former will perform
better:
IF @.inputVariable = 'Fruit'
SELECT column1
FROM tblMyTable
WHERE column2 IN('Apples', 'Oranges', 'Bananas');
ELSE IF @.inputVariable = 'Vegetable'
SELECT column1
FROM tblMyTable
WHERE column2 IN('Corn', 'Green Beans');
SELECT column1
FROM tblMyTable
WHERE (@.inputVariable = 'Fruit'
AND column2 IN('Apples', 'Oranges', 'Bananas'))
OR (@.inputVariable = 'Vegetable'
AND column2 IN('Corn', 'Green Beans'));
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"Dustin" <Dustin@.discussions.microsoft.com> wrote in message
news:475EA188-4D13-4613-8478-E9C268572184@.microsoft.com...
>I need some help with a case statement that I'm trying to write. I wrote
>this
> query a couple of w
s ago, but then my sql server had a hardware failure> and I lost all of my stored procs and now I can't remember how I
> implemented
> it. I'm trying to write a dynamic "IN" statement using a case statement,
> but
> I can't get the syntax quite right. Here's an example of what I'm trying
> to
> do (using different data). I realize this could be simplified using a
> decode
> table, but that isn't an option in this case. My problem is how I'm
> handling
> the part of the statement after each "THEN" statement (I might have used a
> Convert statement, but I don't remember). I think it's reading my values
> as
> one long string (i.e.-'Apples, Oranges, Bananas) instead of separate
> values
> (i.e.-'Apples', 'Oranges', 'Bananas'). Thanks in advance for your help.
> SELECT column1
> FROM tblMyTable
> WHERE column2 IN(
> CASE @.inputVariable
> WHEN 'Fruit' THEN 'Apples' + ',' + 'Oranges' + ', ' + 'Bananas'
> WHEN 'Vegetable' THEN 'Corn' + ', ' + 'Green Beans'
> END
> )|||of course with the braces in place ;)
--
"Razvan Socol" wrote:
> Hi, Justin
> The best way would be to use a table for this, but since you say that
> it's not an option, try something like this:
> SELECT column1
> FROM tblMyTable
> WHERE @.inputVariable='Fruit' AND column2 IN
> ('Apples','Oranges','Bananas')
> OR @.inputVariable='Vegetable' AND column2 IN ('Corn','Green Beans')
> Razvan
>|||I believe it will work fine without the extra parenthesis, but the
parenthesis will make it easier to read and leave no question as to what is
intended.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:57F875E5-4A0C-4DBA-956D-7EE9640E5D1A@.microsoft.com...
> of course with the braces in place ;)
> --
>
>
> "Razvan Socol" wrote:
>|||boy.. thats a news to me.. Always thought AND and OR had the same precedence
.
thanks for pointing that out. Though I will never use it without braces :)
--
"Jim Underwood" wrote:
> I believe it will work fine without the extra parenthesis, but the
> parenthesis will make it easier to read and leave no question as to what i
s
> intended.
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:57F875E5-4A0C-4DBA-956D-7EE9640E5D1A@.microsoft.com...
>
>|||I never use it without parenthesis either. It just gets too confusing, and
too easy to get the wrong results. That and I still forget sometimes that
AND has a higher precedence than OR, so the parens protect me from myself.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:7EAE1F57-00B1-4002-8B32-E6BCC8DDCB84@.microsoft.com...
> boy.. thats a news to me.. Always thought AND and OR had the same
precedence..
> thanks for pointing that out. Though I will never use it without braces :)
> --
>
>
> "Jim Underwood" wrote:
>
is
that|||Thank you all for your help. I was able to solve the problem using the
following suggested syntax:
SELECT column1
FROM tblMyTable
WHERE (@.inputVariable = 'Fruit'
AND column2 IN('Apples', 'Oranges', 'Bananas'))
OR (@.inputVariable = 'Vegetable'
AND column2 IN('Corn', 'Green Beans'));
Friday, March 23, 2012
Help with aggregate functions
I am new to SQL. I am trying to write a query that will join two tables. The join works fine, however I am getting incorrect results. I would like the joined tables to be grouped by ProposalId for the most current ProposalDate listing the ProposalAmount associated with the most current ProposalDate. I have listed the query, the result I get, and the result I would like to get. Any help or direction you could give me would be greatly appreciated.
Your core problem is having the ProposalAmount field in the inner query un-aggregated. Either remove it from there or use an aggregation function. I suggest that you reference to the amount in the outer query by proposal ID. (It is however a question how and if you want to have a single amount mixed with aggregated values (dates, times submitted) in the same line. If not, use aggregation inside.)
The SQL script:
SELECT
p.ProposalId, p.ProjectManagerId, p.JobNumber, p.ClientName,
s.[Proposal Date], s.[Proposal Amount], s.[Times Submitted]
FROM
Proposals p join
(SELECT ProposalId 'ProposalId', ProposalAmount 'Proposal Amount', MAX(ProposalDate) 'Proposal Date', COUNT(ProposalId) 'Times Submitted'
FROM
SubmissionHistory
WHERE
ProjectManagerId = 1
GROUP BY
ProposalAmount, ProposalId) s on p.ProposalId = s.ProposalId
This is the result I get:
Proposal Project Job Proposal Proposal Times
Id ManagerId Number Client Date Amount Submitted
1 1 01004-001 SPS 2007-05-05 10000.00 1
2 1 01007-001 SAP2007-07-09 8000.00 1
2 1 01007-001 SAP2007-07-07 10000.00 1
4 1 01008-001 EAS2007-05-30 75000.00 1
4 1 01008-001 EAS2007-05-07 80000.00 1
4 1 01008-001 EAS2007-05-05 90000.00 1
This is the result I would like to get:
Proposal Project Job Proposal Proposal Times
Id ManagerId Number Client Date Amount Submitted
1 1 01004-001 SPS 2007-05-05 10000.00 1
2 1 01007-001 SAP2007-07-09 8000.00 2
4 1 01008-001 EAS2007-05-30 75000.00 3
Quote:
Originally Posted by yoyo35
I am new to SQL. I am trying to write a query that will join two tables. The join works fine, however I am getting incorrect results. I would like the joined tables to be grouped by ProposalId for the most current ProposalDate listing the ProposalAmount associated with the most current ProposalDate. I have listed the query, the result I get, and the result I would like to get. Any help or direction you could give me would be greatly appreciated.
The SQL script:
SELECT
p.ProposalId, p.ProjectManagerId, p.JobNumber, p.ClientName,
s.[Proposal Date], s.[Proposal Amount], s.[Times Submitted]
FROM
Proposals p join
(SELECT ProposalId 'ProposalId', ProposalAmount 'Proposal Amount', MAX(ProposalDate) 'Proposal Date', COUNT(ProposalId) 'Times Submitted'
FROM
SubmissionHistory
WHERE
ProjectManagerId = 1
GROUP BY
ProposalAmount, ProposalId) s on p.ProposalId = s.ProposalId
This is the result I get:
Proposal Project Job Proposal Proposal Times
Id ManagerId Number Client Date Amount Submitted
1 1 01004-001 SPS 2007-05-05 10000.00 1
2 1 01007-001 SAP2007-07-09 8000.00 1
2 1 01007-001 SAP2007-07-07 10000.00 1
4 1 01008-001 EAS2007-05-30 75000.00 1
4 1 01008-001 EAS2007-05-07 80000.00 1
4 1 01008-001 EAS2007-05-05 90000.00 1
This is the result I would like to get:
Proposal Project Job Proposal Proposal Times
Id ManagerId Number Client Date Amount Submitted
1 1 01004-001 SPS 2007-05-05 10000.00 1
2 1 01007-001 SAP2007-07-09 8000.00 2
4 1 01008-001 EAS2007-05-30 75000.00 3
Your core problem is having the ProposalAmount field in the inner query un-aggregated. Either remove it from there or use an aggregation function. I suggest that you reference to the amount in the outer query by proposal ID. (It is however a question how and if you want to have a single amount mixed with aggregated values (dates, times submitted) in the same line. If not, use aggregation inside.)
If you're really new to SQL this is more than decent even so far...|||Thank you for your reply. I appreciate your time. I will give it a shot and see what happens. Once again, thanks!!!!|||Hi i am not sure but i think you are trying to do a union join,, you should try and put in distinct in yout select statements so distinct(ID) for example
Wednesday, March 21, 2012
help with a SELECT
Hi, I have a table that contains orders from cliente. Suppose that each
client has more than one order, I would like to write a SELECT statement
that returns the order with the oldest date from each client.
for example:
Table:
idClient idOrder orderDate
35 100 03/10/2004
35 120 03/22/2004
35 150 04/10/2004
72 45 01/10/2004
72 56 01/29/2004
and the SELECT should return the next records:
idClient idOrder orderDate
35 150 04/10/2004
72 56 01/29/2004
Hope someone can help me with this :)
Thanks in advance
JCTry:
select
o.*
from
Orders o
join
(
select
idCLient
, max (orderDate) orderDate
from
Orders
group by
idCLient
) x on x.idClient = o.idClient
and x.orderDate = o.orderDate
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"J.C. Developer" <smartconsultANTISPAM@.hotmail.com> wrote in message
news:ehBUo$rHFHA.2784@.TK2MSFTNGP09.phx.gbl...
Hi, I have a table that contains orders from cliente. Suppose that each
client has more than one order, I would like to write a SELECT statement
that returns the order with the oldest date from each client.
for example:
Table:
idClient idOrder orderDate
35 100 03/10/2004
35 120 03/22/2004
35 150 04/10/2004
72 45 01/10/2004
72 56 01/29/2004
and the SELECT should return the next records:
idClient idOrder orderDate
35 150 04/10/2004
72 56 01/29/2004
Hope someone can help me with this :)
Thanks in advance
JC|||Thanks Tom, worked fine... sorry for multi posting
JC
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uweVaEsHFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Try:
> select
> o.*
> from
> Orders o
> join
> (
> select
> idCLient
> , max (orderDate) orderDate
> from
> Orders
> group by
> idCLient
> ) x on x.idClient = o.idClient
> and x.orderDate = o.orderDate
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "J.C. Developer" <smartconsultANTISPAM@.hotmail.com> wrote in message
> news:ehBUo$rHFHA.2784@.TK2MSFTNGP09.phx.gbl...
> Hi, I have a table that contains orders from cliente. Suppose that each
> client has more than one order, I would like to write a SELECT statement
> that returns the order with the oldest date from each client.
> for example:
> Table:
> idClient idOrder orderDate
> 35 100 03/10/2004
> 35 120 03/22/2004
> 35 150 04/10/2004
> 72 45 01/10/2004
> 72 56 01/29/2004
> and the SELECT should return the next records:
> idClient idOrder orderDate
> 35 150 04/10/2004
> 72 56 01/29/2004
> Hope someone can help me with this :)
> Thanks in advance
> JC
>
>|||Tiny detail - the specs were to retrieve the oldest order
for each client, not the newest, so you'll want MIN, not
MAX.
Another solution is
select o.*
from Orders as O1
where orderDate = (
select min(orderDate)
from Orders as O2
where O2.idClient = O1.idClient
)
One or the other formulation may be faster, but what
I like about this one is that it follows the English description
closely: select all orders where the order date is
the earliest for that client.
Note that there may not be just one earliest order
for a given client, and each of these queries will
return all orders with minimum orderDate for the client.
Steve Kass
Drew University
Tom Moreau wrote:
>Try:
>select
> o.*
>from
> Orders o
>join
>(
> select
> idCLient
> , max (orderDate) orderDate
> from
> Orders
> group by
> idCLient
> ) x on x.idClient = o.idClient
> and x.orderDate = o.orderDate
>
>|||Good catch!
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Steve Kass" <skass@.drew.edu> wrote in message
news:uWqRoPsHFHA.1860@.TK2MSFTNGP15.phx.gbl...
Tiny detail - the specs were to retrieve the oldest order
for each client, not the newest, so you'll want MIN, not
MAX.
Another solution is
select o.*
from Orders as O1
where orderDate = (
select min(orderDate)
from Orders as O2
where O2.idClient = O1.idClient
)
One or the other formulation may be faster, but what
I like about this one is that it follows the English description
closely: select all orders where the order date is
the earliest for that client.
Note that there may not be just one earliest order
for a given client, and each of these queries will
return all orders with minimum orderDate for the client.
Steve Kass
Drew University
Tom Moreau wrote:
>Try:
>select
> o.*
>from
> Orders o
>join
>(
> select
> idCLient
> , max (orderDate) orderDate
> from
> Orders
> group by
> idCLient
> ) x on x.idClient = o.idClient
> and x.orderDate = o.orderDate
>
>|||Thanks Steve, you're right...MIN is the choice for the oldest date. It
worked fine too.
JC
"Steve Kass" <skass@.drew.edu> wrote in message
news:uWqRoPsHFHA.1860@.TK2MSFTNGP15.phx.gbl...
> Tiny detail - the specs were to retrieve the oldest order
> for each client, not the newest, so you'll want MIN, not
> MAX.
> Another solution is
> select o.*
> from Orders as O1
> where orderDate = (
> select min(orderDate)
> from Orders as O2
> where O2.idClient = O1.idClient
> )
> One or the other formulation may be faster, but what
> I like about this one is that it follows the English description
> closely: select all orders where the order date is
> the earliest for that client.
> Note that there may not be just one earliest order
> for a given client, and each of these queries will
> return all orders with minimum orderDate for the client.
> Steve Kass
> Drew University
> Tom Moreau wrote:
>
client has more than one order, I would like to write a SELECT statement
that returns the order with the oldest date from each client.
for example:
Table:
idClient idOrder orderDate
35 100 03/10/2004
35 120 03/22/2004
35 150 04/10/2004
72 45 01/10/2004
72 56 01/29/2004
and the SELECT should return the next records:
idClient idOrder orderDate
35 150 04/10/2004
72 56 01/29/2004
Hope someone can help me with this :)
Thanks in advance
JCTry:
select
o.*
from
Orders o
join
(
select
idCLient
, max (orderDate) orderDate
from
Orders
group by
idCLient
) x on x.idClient = o.idClient
and x.orderDate = o.orderDate
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"J.C. Developer" <smartconsultANTISPAM@.hotmail.com> wrote in message
news:ehBUo$rHFHA.2784@.TK2MSFTNGP09.phx.gbl...
Hi, I have a table that contains orders from cliente. Suppose that each
client has more than one order, I would like to write a SELECT statement
that returns the order with the oldest date from each client.
for example:
Table:
idClient idOrder orderDate
35 100 03/10/2004
35 120 03/22/2004
35 150 04/10/2004
72 45 01/10/2004
72 56 01/29/2004
and the SELECT should return the next records:
idClient idOrder orderDate
35 150 04/10/2004
72 56 01/29/2004
Hope someone can help me with this :)
Thanks in advance
JC|||Thanks Tom, worked fine... sorry for multi posting
JC
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uweVaEsHFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Try:
> select
> o.*
> from
> Orders o
> join
> (
> select
> idCLient
> , max (orderDate) orderDate
> from
> Orders
> group by
> idCLient
> ) x on x.idClient = o.idClient
> and x.orderDate = o.orderDate
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "J.C. Developer" <smartconsultANTISPAM@.hotmail.com> wrote in message
> news:ehBUo$rHFHA.2784@.TK2MSFTNGP09.phx.gbl...
> Hi, I have a table that contains orders from cliente. Suppose that each
> client has more than one order, I would like to write a SELECT statement
> that returns the order with the oldest date from each client.
> for example:
> Table:
> idClient idOrder orderDate
> 35 100 03/10/2004
> 35 120 03/22/2004
> 35 150 04/10/2004
> 72 45 01/10/2004
> 72 56 01/29/2004
> and the SELECT should return the next records:
> idClient idOrder orderDate
> 35 150 04/10/2004
> 72 56 01/29/2004
> Hope someone can help me with this :)
> Thanks in advance
> JC
>
>|||Tiny detail - the specs were to retrieve the oldest order
for each client, not the newest, so you'll want MIN, not
MAX.
Another solution is
select o.*
from Orders as O1
where orderDate = (
select min(orderDate)
from Orders as O2
where O2.idClient = O1.idClient
)
One or the other formulation may be faster, but what
I like about this one is that it follows the English description
closely: select all orders where the order date is
the earliest for that client.
Note that there may not be just one earliest order
for a given client, and each of these queries will
return all orders with minimum orderDate for the client.
Steve Kass
Drew University
Tom Moreau wrote:
>Try:
>select
> o.*
>from
> Orders o
>join
>(
> select
> idCLient
> , max (orderDate) orderDate
> from
> Orders
> group by
> idCLient
> ) x on x.idClient = o.idClient
> and x.orderDate = o.orderDate
>
>|||Good catch!
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Steve Kass" <skass@.drew.edu> wrote in message
news:uWqRoPsHFHA.1860@.TK2MSFTNGP15.phx.gbl...
Tiny detail - the specs were to retrieve the oldest order
for each client, not the newest, so you'll want MIN, not
MAX.
Another solution is
select o.*
from Orders as O1
where orderDate = (
select min(orderDate)
from Orders as O2
where O2.idClient = O1.idClient
)
One or the other formulation may be faster, but what
I like about this one is that it follows the English description
closely: select all orders where the order date is
the earliest for that client.
Note that there may not be just one earliest order
for a given client, and each of these queries will
return all orders with minimum orderDate for the client.
Steve Kass
Drew University
Tom Moreau wrote:
>Try:
>select
> o.*
>from
> Orders o
>join
>(
> select
> idCLient
> , max (orderDate) orderDate
> from
> Orders
> group by
> idCLient
> ) x on x.idClient = o.idClient
> and x.orderDate = o.orderDate
>
>|||Thanks Steve, you're right...MIN is the choice for the oldest date. It
worked fine too.
JC
"Steve Kass" <skass@.drew.edu> wrote in message
news:uWqRoPsHFHA.1860@.TK2MSFTNGP15.phx.gbl...
> Tiny detail - the specs were to retrieve the oldest order
> for each client, not the newest, so you'll want MIN, not
> MAX.
> Another solution is
> select o.*
> from Orders as O1
> where orderDate = (
> select min(orderDate)
> from Orders as O2
> where O2.idClient = O1.idClient
> )
> One or the other formulation may be faster, but what
> I like about this one is that it follows the English description
> closely: select all orders where the order date is
> the earliest for that client.
> Note that there may not be just one earliest order
> for a given client, and each of these queries will
> return all orders with minimum orderDate for the client.
> Steve Kass
> Drew University
> Tom Moreau wrote:
>
help with a query
hi guys. I am trying to write a query but dont know how to.
TABLES: Category (id,name ) ; SubCategory (id,Category_ID) , Article (id,SubCategory_ID,title)
I would like to display TOP 5 articles for each category.
GROUP BY is probably the answer...but I just don't know how to that...I hope you know. thanks.
If you are using SQL Server 2005:
Here is one solution:
SELECT t.Category_ID,t.Name, t.title, t.TOP5 FROM (SELECT Category$.Category_ID,Category$.Name, articles$.title, ROW_NUMBER() OVER (PARTITION BY Category$.Category_ID
ORDER BY Category$.Category_ID) AS TOP5
FROM SubCategory$ INNER JOIN
articles$ ON SubCategory$.SubCategory_ID = articles$.SubCategory_ID INNER JOIN
Category$ ON SubCategory$.Category_ID = Category$.Category_ID) t
WHERE t.TOP5<=5
I tried this but i work on sqlserver express...any other option?
|||It should work on your SQL Server 2005 express database.|||error I get is:The OVER SQL construct or statement is not supported.
maybe we can use a GROUP by that will make it something that the sqlsserver will understand?
|||Here is another one:
SELECT
t3.Category_ID, t3.SubCategory_ID, t3.ID, t3.Name, t3.titleFROM(SELECT t1.Category_ID, t1.SubCategory_ID, t1.ID, t1.Name, t1.title,(SELECTCOUNT(*)FROM(SELECT Category$.Category_ID, SubCategory$.SubCategory_ID, articles$.ID, Category$.Name, articles$.titleFROM SubCategory$INNERJOIN articles$ON SubCategory$.SubCategory_ID= articles$.SubCategory_IDINNERJOIN Category$ON SubCategory$.Category_ID= Category$.Category_ID) t2WHERE t1.Category_ID=t2.Category_IDAND t2.ID<=t1.ID)as rankNumFROM
(SELECT Category$.Category_ID, SubCategory$.SubCategory_ID, articles$.ID, Category$.Name, articles$.titleFROM
SubCategory$INNERJOINarticles$
ON SubCategory$.SubCategory_ID= articles$.SubCategory_IDINNERJOINCategory$
ON SubCategory$.Category_ID= Category$.Category_ID) t1) t3WHERE
t3.rankNum<6|||it's working :)
thank you guru :)
after this was done, I need to display it on ASP.NET html table.
each 5 records will create 1 table with 5 rows. I know there is a gridview but it takes care for only 1 record each time.
thanks alot for the query i would never come to this
Help with a query
Ive been trying to find out how to write a query like this for months now and feel its time that I get some help :eek:
Im trying to export columns to text files so that they can be accessed via a website to show statistics.
(My SQL database is used for something else and I do not want the website directly connecting to it.)
So first I would have the table ordered by a specific column and then export the top 50 results for example.
I had it working to export to excell but I lost the query :(
Do I use something like EXPORT COLUMNS or INSERT INTO text file sorta thing
ThanksHi Paul
irrespective of the purpose of the db I would have the website access it directly. Why are you unhappy with this?
In any event BCP is an efficient way to get data out of the db and into text files.|||I dont want to lean on the SQL performance and im sure constant connections like this would.
I have never used BCP before so I guess I will go and have a look into that
thanks|||Still no luck BCP is very confusing :S|||is it? it's just a console app. here's how you can export data for an entire table:
bcp MyDatabase.dbo.MyTable out myfile.txt -c -T -SMYSERVER
and here's how to export the result of a query:
bcp "select foo from MyDatabase.dbo.MyTable where bar=12" out myfile.txt -c -T -SMYSERVER
Im trying to export columns to text files so that they can be accessed via a website to show statistics.
(My SQL database is used for something else and I do not want the website directly connecting to it.)
So first I would have the table ordered by a specific column and then export the top 50 results for example.
I had it working to export to excell but I lost the query :(
Do I use something like EXPORT COLUMNS or INSERT INTO text file sorta thing
ThanksHi Paul
irrespective of the purpose of the db I would have the website access it directly. Why are you unhappy with this?
In any event BCP is an efficient way to get data out of the db and into text files.|||I dont want to lean on the SQL performance and im sure constant connections like this would.
I have never used BCP before so I guess I will go and have a look into that
thanks|||Still no luck BCP is very confusing :S|||is it? it's just a console app. here's how you can export data for an entire table:
bcp MyDatabase.dbo.MyTable out myfile.txt -c -T -SMYSERVER
and here's how to export the result of a query:
bcp "select foo from MyDatabase.dbo.MyTable where bar=12" out myfile.txt -c -T -SMYSERVER
Wednesday, March 7, 2012
HELP to write stored procedure whose values are calculated automatically in database
Hi frdz,
I m creating my web-application in asp.net with C# 2005 and using sql server 2005.
I have created the stored procedure for the insert,update.
I want to know how to write the mathematical calculations in the stored procedure..
Pls tell me from the below stored procedure were i m making the mistake ??
As the discount and the total amount are not calculated by itself...and stored in the database
How to convert the
@.discpercentnumeric(5,2) to
@.discpercent ="NoDiscount" should be displayed when no discount is being given to the customers...
ALTER PROCEDURE CalculationStoredProcedure @.accountidint output, @.accountnamevarchar(20), @.opbalnumeric(10, 2), @.opbalcodechar(2), @.totalnumeric(10, 2), @.clbalnumeric(10, 2), @.clbalcodechar(2), @.discpercentnumeric(5,2), @.discamtnumeric(10, 2)asbeginset nocount on if @.opbalISNULL OR @.opbal = 0beginselect @.opbal=0select @.opbalcode=' 'select @.clbal= 0select @.total= 0select @.clbalcode=' ' @.discpercent ="NoDiscount" @.discamt=0end select @.accountid =isnull(max(accountid),0) + 1from accountmasterselect @.total=@.opbal - @.clbalfrom accountmasterselect @.discamt=@.total* @.discpercent/100from accountmasterbegin insert into accountmaster(accountname,opbal,opbalcode,clbal,clbalcode )values ( @.accountname,@.opbal,@.opbalcode,@.clbal,@.clbalcode )end set nocount offend
Thanxs in adv...
Hello my friend,
It appears you are using 1 field for 2 purposes; numeric calculation and text display. I would avoid doing this.
Declare another parameter (e.g. @.DiscDisplay) to hold a copy of the @.discpercent variable when a discount is being applied, and have it set to 'NoDiscount' if @.discpercent is 0. Then just display @.DiscDisplay on the web page in both situations.
Kind regards
Scotty
|||
thanxs for the reply...
can u pls help to calculate this for my above stored procedure its not working..
select @.total=@.opbal - @.clbalfrom accountmaster
select @.discamt=@.total* @.discpercent/100from accountmaster
|||
Looks like there is some confusion here. You are calculating a percentage from values in parameters. Then why do you have a "..FROM AccountMaster" in your SELECT?
select @.discamt=(@.opbal - @.clbal )* @.discpercent/100
should do.
select @.total=(@.opbal - @.clbal )
not working in database it shows me NULL only
I tried with this also but don't work pls help me...
if (@.total IS NULL or @.total = 0)
begin
select @.total=(@.opbal - @.clbal )
end
thanxs for ur replies....
Could it be because either @.opbal or @.clbal is null?
I would use the following: -
IF (ISNULL(@.Total, 0) = 0)
BEGIN
SET @.total = (ISNULL(@.opbal, 0) - ISNULL(@.clbal, 0))
END
SELECT @.Total
Kind regards
Scotty
|||
thanxs very much again...
Tried with ur suggestion...but don't work...why it does not returns/set/stores the value into the database...??
What can be the problem ?? Not understanding...i think everything is correct now...putting all of ur suggestions..together but not able to get the solution
Hello again Sheenaa,
You say that you have put it all together based on our help. Can you show us all of the procedure that you have put together and we can see if you have made any mistakes.
Kind regards
Scotty
Help to write query...
Hi !
There is one table tCustomers. It has following columns: ID, Name, Code...
By the mistake in this table has appeared incorrect records (duplicates).
How can I write the query to find them ?
I tried:
Select c.ID ID1,s.ID ID2, c.NAME NAME1,s.NAME NAME2, c.Code C1, s.Code C2, From tCustomers c, tCustomers s
where c.Code=s.Code and c.ID <> s.ID
But the result is not that I expected
Hi,
If only the ID field constain duplicates, you can use the following query:
SELECT tCustomers.ID, tCustomers.Name, tCustomers.Code
FROM tCustomers
WHERE (((tCustomers.ID) In (SELECT [ID] FROM [tCustomers] GROUP BY [ID] HAVING Count(*)>1 )))
ORDER BY tCustomers.ID;
If all the fields are duplicated, use the following query:
SELECT tCustomers.ID, tCustomers.Name, tCustomers.Code, Count(tCustomers.ID) NumberOfDups
FROM tCustomers
GROUP BY tCustomers.ID, tCustomers.Name, tCustomers.Code
HAVING (((Count(tCustomers.ID))>1));
Hope this helps
Help to write a query to kamal
hello,
I have a Question Paper table
Create Table QPaper (QPID, QPName,SubjectSLNo,Type, NoOfQues)
Data is like
Insert Into QPaper Values (1, 'UNIT1', 1,'Objective', 10)
Insert Into QPaper Values (2, 'UNIT1', 1,'Descriptive', 5)
Insert Into QPaper Values (3, 'UNIT2', 1,'Objective', 2)
Insert Into QPaper Values (4, 'UNIT2', 1,'Objective', 15)
My Output should be
1, 'UNIT1', 1,'Objective', 1, 1
1, 'UNIT1', 1,'Objective', 1, 2
1, 'UNIT1', 1,'Objective', 1, 3
1, 'UNIT1', 1,'Objective', 1, 4
--
--
--
1, 'UNIT1', 1,'Objective', 1, 10
2, 'UNIT1', 1,'Descriptive', 5,1
2, 'UNIT1', 1,'Descriptive', 5,2
--
--
--
2, 'UNIT1', 1,'Descriptive', 5,5
3, 'UNIT2', 1,'Objective', 2,1
3, 'UNIT2', 1,'Objective', 2,2
4, 'UNIT2', 1,'Objective', 15,1
4, 'UNIT2', 1,'Objective', 15,2
4, 'UNIT2', 1,'Objective', 15,3
--
--
--
--
--
4, 'UNIT2', 1,'Objective', 15,15
thanks on advance
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Try,
use northwind
go
select
identity(int, 1, 1) as number
into
number
from
sysobjects as a cross join sysobjects as b
go
Create Table QPaper (QPID int, QPName varchar(25) ,SubjectSLNo int,Type
varchar(25), NoOfQues int)
go
Insert Into QPaper Values (1, 'UNIT1', 1,'Objective', 10)
Insert Into QPaper Values (2, 'UNIT1', 1,'Descriptive', 5)
Insert Into QPaper Values (3, 'UNIT2', 1,'Objective', 2)
Insert Into QPaper Values (4, 'UNIT2', 1,'Objective', 15)
go
select
a.QPID,
a.QPName,
a.SubjectSLNo,
a.Type,
a.NoOfQues,
n.number
from
QPaper as a
inner join
number as n
on n.number <= a.NoOfQues
order by
a.QPID,
n.number
go
drop Table QPaper, number
go
AMB
"kamal hussain" wrote:
>
> hello,
> I have a Question Paper table
> Create Table QPaper (QPID, QPName,SubjectSLNo,Type, NoOfQues)
> Data is like
> Insert Into QPaper Values (1, 'UNIT1', 1,'Objective', 10)
> Insert Into QPaper Values (2, 'UNIT1', 1,'Descriptive', 5)
> Insert Into QPaper Values (3, 'UNIT2', 1,'Objective', 2)
> Insert Into QPaper Values (4, 'UNIT2', 1,'Objective', 15)
> My Output should be
> 1, 'UNIT1', 1,'Objective', 1, 1
> 1, 'UNIT1', 1,'Objective', 1, 2
> 1, 'UNIT1', 1,'Objective', 1, 3
> 1, 'UNIT1', 1,'Objective', 1, 4
> --
> --
> --
> 1, 'UNIT1', 1,'Objective', 1, 10
> 2, 'UNIT1', 1,'Descriptive', 5,1
> 2, 'UNIT1', 1,'Descriptive', 5,2
> --
> --
> --
> 2, 'UNIT1', 1,'Descriptive', 5,5
> 3, 'UNIT2', 1,'Objective', 2,1
> 3, 'UNIT2', 1,'Objective', 2,2
> 4, 'UNIT2', 1,'Objective', 15,1
> 4, 'UNIT2', 1,'Objective', 15,2
> 4, 'UNIT2', 1,'Objective', 15,3
> --
> --
> --
> --
> --
> 4, 'UNIT2', 1,'Objective', 15,15
>
> thanks on advance
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
>
Sunday, February 19, 2012
Help Required on Stored Procedure
Hello All,
I'm trying to write a stored procedure which tracks the insert event
in a Table A. When we insert something in Table A in Database 'FMDB',
it inserts the same information in Table B in Database 'Wartung'.
When I insert something with insert query visa SQL Query analyzer, it
works fine.
If i insert records via my C++ programme, it gives error that
ROLLBACK_TRANSACTION has no BEGIN TRANSACTION.
Trigger code is as follow:
CREATE TRIGGER [TG_TASKCOPY] ON [FMDB].[METABO_TASK]
FOR INSERT
AS
BEGIN TRANSACTION
IF (SELECT COUNT(*) FROM inserted)=1
BEGIN
DECLARE @.OBJEKT VARCHAR(32)
DECLARE @.TASK VARCHAR(32)
DECLARE @.PLANNER VARCHAR(32)
DECLARE @.RESPONSIBLE VARCHAR(32)
DECLARE @.STATUS VARCHAR(32)
DECLARE @.PLANDATE DATETIME
DECLARE @.ANLAGE_NUM int
SELECT @.OBJEKT=OBJECT FROM inserted
SELECT @.TASK=TASK FROM inserted
SELECT @.PLANDATE=PLANDATE FROM inserted
IF (SELECT COUNT(*) FROM WARTUNG.WARTUNG.IPS_ASSETS WHERE
FM_NUM=@.OBJEKT)=1
BEGIN
BEGIN TRANSACTION
SELECT @.ANLAGE_NUM=ANLAGE_NUM FROM WARTUNG.WARTUNG.IPS_ASSETS
WHERE FM_NUM=@.OBJEKT
INSERT INTO WARTUNG.WARTUNG.IPS_WARTUNG (ANLAGE,ANLAGE_NUM,
WAEHRUNG,NAECHSTEDATUM, STATUS,INTERVALTYPE,ART) VALUES(@.OBJEKT,
@.ANLAGE_NUM,'EUR', @.PLANDATE,'geplant','D',@.TASK)
COMMIT TRANSACTION
END
ELSE IF(@.@.error<>0)
BEGIN
ROLLBACK TRANSACTION
END
END
With Regards,
Attiq ur Rehman[posted and mailed, please reply in news]
Attiq ur Rehman (attiq76@.yahoo.com) writes:
> I'm trying to write a stored procedure which tracks the insert event
> in a Table A. When we insert something in Table A in Database 'FMDB',
> it inserts the same information in Table B in Database 'Wartung'.
> When I insert something with insert query visa SQL Query analyzer, it
> works fine.
> If i insert records via my C++ programme, it gives error that
> ROLLBACK_TRANSACTION has no BEGIN TRANSACTION.
It could have help if you had included the relevant parts of your
C++ code too.
Anyway, there are some problems with your trigger. You have an initial
BEGIN TRANSACTION which is not matched by a COMMIT or ROLLBACK. There
is not really any reason to issue any BEGIN TRANSACTION in a trigger,
because a trigger always runs in a transaction; defined by the statement
that fired the trigger. (Even if you don't have any user-defined
transaction, each INSERT, UPDATE or DELETE statement is its own transaction
in SQL Server).
There is also this funny line:
> ELSE IF(@.@.error<>0)
> BEGIN
> ROLLBACK TRANSACTION
> END
This is a meaningless piece of code. @.@.error is set after each
statement, and the previous statement is an IF statement, and yes,
an IF statement also sets @.@.error.
I suggest that you remove all BEGIN/COMMIT/ROLLBACK TRANSACTION
from the trigger.
Note also that when you rollback a transaction in a trigger this
cancels the execution of the entire batch. Likewise, if you get
an error in a trigger this also cancels the batch entire, and also
rolls back any transactions.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Subscribe to:
Posts (Atom)