Conditonal SUM function, or similar conditional aggregates

Are there any conditional aggregate functions, such as SUM()?

An example would probably be the best way to describe what I'm
trying to do...

I have the following table, named Orders, with the following records:

ItemNo qty_ord paid
-- ---- --
T101B 1 199.00
T101B 1 199.00
T101B 1 199.00
T101B 1 199.00
T101B 1 199.00
T101B 1 199.00
T101B 1 199.00
T101B 1 0.00
T101B 1 0.00
T101B 1 0.00
T101B 1 0.00
Z200L 1 50.00
Z200L 2 100.00

I want to produce the following result set:

ItemNo qty_gross qty_net
-- ---- ---
T101B 11 7
Z200L 3 3

The "qty_gross" column in the result set is the sum of
total items ordered within the ItemNo grouping.
Easy enough. However, I also want a column "qty_net" that
is the sum of qty_ord but ONLY IF the amount in the
"paid" column is > 0.

I tried using the HAVING clause, but that produces a
catch 22 situation. If I say "HAVING paid > 0" then
the qty_gross column is wrong because it leaves out rows
that contain records with paid = 0 values. If I leave
out the HAVING clause, then the "qty_net" is wrong.

Any ideas?

select ItemNo, Sum(qty_ord) as qty_gross, Sum(qty_ord) as qty_net
from Orders
group by qty_ord, paid, ItemNo
having paid > 0 ???

Thanks,
RobbieOn 15 Feb 2005 06:17:24 -0800, RobbieGotNeeds@.netscape.net wrote:

>Are there any conditional aggregate functions, such as SUM()?
(snip)

Hi Robbie,

No. But you can use any expression in an aggregate function, including the
conditional CASE expression.

>An example would probably be the best way to describe what I'm
>trying to do...
(snip)
>I have the following table, named Orders, with the following records:
>ItemNo qty_ord paid
>-- ---- --
>T101B 1 199.00
>T101B 1 199.00
>T101B 1 199.00
>T101B 1 199.00
>T101B 1 199.00
>T101B 1 199.00
>T101B 1 199.00
>T101B 1 0.00
>T101B 1 0.00
>T101B 1 0.00
>T101B 1 0.00
>Z200L 1 50.00
>Z200L 2 100.00
>
>I want to produce the following result set:
>ItemNo qty_gross qty_net
>-- ---- ---
>T101B 11 7
>Z200L 3 3

SELECT ItemNo,
SUM(qty_ord) AS qty_gross,
SUM(CASE WHEN paid > 0 THEN qty_ord ELSE 0 END) AS qty_net
FROM Orders
GROUP BY ItemNo

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Conditionally skip or jump steps in a job

Is it possible to conditionally skip or jump steps in a job without failing
a
step?
For example I don’t want to run a step if the are no records in a table.
I also need to loop based on a condition.About loops:
http://www.sqldts.com/default.aspx?246
About conditional execution:
http://www.sqldts.com/default.aspx?218
http://www.sqldts.com/default.aspx?214
Chris
"Dave" wrote:

> Is it possible to conditionally skip or jump steps in a job without failin
g a
> step?
> For example I don’t want to run a step if the are no records in a table.
> I also need to loop based on a condition.
>|||You could place your (IF.. BEGIN.. END) condition within the T-SQL or stored
procedure beging called.
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:27AF395F-A67D-43E6-9F40-009ACCCB0877@.microsoft.com...
> Is it possible to conditionally skip or jump steps in a job without
> failing a
> step?
> For example I don't want to run a step if the are no records in a table.
> I also need to loop based on a condition.
>sqlsql

Conditionally counting detail field

Hello. I'm trying to conditionally count a field, for example:
iif(Fields!GrantCodeID.Value = 70, 1, 0)
GrantCode is a field and whenever it equals 70, I want to count it so
I can display the count after the detail.
Any help is much appreciated.
Thanks!=Sum(iif(Fields!GrantCodeID.Value = 70, 1, 0))
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michael" <mike1174@.comcast.net> wrote in message
news:dfd40822.0408181042.401a6798@.posting.google.com...
> Hello. I'm trying to conditionally count a field, for example:
> iif(Fields!GrantCodeID.Value = 70, 1, 0)
> GrantCode is a field and whenever it equals 70, I want to count it so
> I can display the count after the detail.
> Any help is much appreciated.
> Thanks!

Conditional Sum?

Hi,

Is it possible to have a conditional sum based on an item type existance in a set of values?

Example if i have the following set:

A

A

A

A

B

I just wanna sum B else if B doens't exist sum A

Best Regards,

Luis Simoes

Have an invisible textbox in your report with the expression and say the name of the textbox is textbox20:

Count(IIf(Fields!ItemType.Value = "A", Fields!YourField.Value, 0))

Then use this expression for summing:

Sum(IIf(ReportItems1textbox20.Value > 0, IIf(Fields!ItemType.Value = "A", Fields!YourField.Value, 0), 0))

Shyam

|||

Yes that would do the trick!

I can also use that count in the field formula right? Or it does affect performance that much?

Thanks,

Regards,

Luis Simoes

|||

Hello,

Doing this gives me two errors in the summing field.

"The value expression for the textbox 'textbox30' uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers."

"The value expression for the textbox ‘textbox30’ refers to the report item ‘textbox28’. Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope."

Any idea why?

Conditional Sum Statement

Hey all,
Quick question, I have a field that I need to sum only when another
field is a certan value.
For example, a dataset with 5 fields {row_id, dealer_id, rep_id,
sales_code, sales_amt} and grouped by dealer_id then rep_id, I would
want to sum the sales_amt field when the first character of the
sales_code field is an 'X'. So for the set:
1, 1, 999, X100, $200.00
2, 1, 999, 200, $500.00
3, 1, 999, 898, $1000.00
4, 1, 555, X340, $2000.00
5, 1, 555, X444, $23.00
The resultant sums would be:
for dealer_id 1: 2223.00
for rep_id 999: 200.00
for rep_id 555: 2023.00
I am trying to do this in MRS as opposed to making it an additional
field in my querry.
Thanks!I think this is what you are asking...
And There is probably an easier way to do this but in the expression builder
IIF(SUBSTR(sales_code),1,1) = "x", Sum(sales_amt ),"")
Or something along those lines...
Hope that helps
Kerrie
Jimmy V wrote:
>Hey all,
>Quick question, I have a field that I need to sum only when another
>field is a certan value.
>For example, a dataset with 5 fields {row_id, dealer_id, rep_id,
>sales_code, sales_amt} and grouped by dealer_id then rep_id, I would
>want to sum the sales_amt field when the first character of the
>sales_code field is an 'X'. So for the set:
>1, 1, 999, X100, $200.00
>2, 1, 999, 200, $500.00
>3, 1, 999, 898, $1000.00
>4, 1, 555, X340, $2000.00
>5, 1, 555, X444, $23.00
>The resultant sums would be:
>for dealer_id 1: 2223.00
>for rep_id 999: 200.00
>for rep_id 555: 2023.00
>I am trying to do this in MRS as opposed to making it an additional
>field in my querry.
>Thanks!
--
Message posted via http://www.sqlmonster.com|||Kerrie,
I had to create a calculated field and summed it that way, i did use
your code snippit to generate my calculated field.
Thanks!!!|||Glad I could help, That is the best thing i have heard all day.
Thanks!
Jimmy V wrote:
>Kerrie,
>I had to create a calculated field and summed it that way, i did use
>your code snippit to generate my calculated field.
>Thanks!!!
--
Message posted via http://www.sqlmonster.com|||Hi Jimmy,
Easy way of doing is, if u want a sum by Sales order =x...., AND REP_ID
create a group with the sales order =x JUST "X" and u will get the value for
it
and then subtract this one with rest of value.
regards
JERROB
"Jimmy V" wrote:
> Hey all,
> Quick question, I have a field that I need to sum only when another
> field is a certan value.
> For example, a dataset with 5 fields {row_id, dealer_id, rep_id,
> sales_code, sales_amt} and grouped by dealer_id then rep_id, I would
> want to sum the sales_amt field when the first character of the
> sales_code field is an 'X'. So for the set:
> 1, 1, 999, X100, $200.00
> 2, 1, 999, 200, $500.00
> 3, 1, 999, 898, $1000.00
> 4, 1, 555, X340, $2000.00
> 5, 1, 555, X444, $23.00
> The resultant sums would be:
> for dealer_id 1: 2223.00
> for rep_id 999: 200.00
> for rep_id 555: 2023.00
> I am trying to do this in MRS as opposed to making it an additional
> field in my querry.
> Thanks!
>

conditional query

Just wondering if someone could provide a brief example of how to do this. I
have a stored procedure and I need a condition where statement, for example
inputs are
@.name varchar(25)
@.color varchar(25)
if color is not 'none ' I want
select * from table1
where table1.name = @.name
and table1.color = @.color
if color is 'none' I want
select * from table1 where table1.name=@.name.
Thanks.
--
Paul G
Software engineer.One way
If @.color <> 'none'
select * from table1
where table1.name = @.name
Else
select * from table1
where table1.name = @.name
and table1.color = @.color
But there are lots of ways to to this type of processing. See
http://www.sommarskog.se/dyn-search.html
for a good discussion of ways to do this.
Tom
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:34367D41-4D61-4C01-ABC5-C5DD9DB969BD@.microsoft.com...
> Just wondering if someone could provide a brief example of how to do this.
> I
> have a stored procedure and I need a condition where statement, for
> example
> inputs are
> @.name varchar(25)
> @.color varchar(25)
> if color is not 'none ' I want
> select * from table1
> where table1.name = @.name
> and table1.color = @.color
> if color is 'none' I want
> select * from table1 where table1.name=@.name.
> Thanks.
> --
> Paul G
> Software engineer.|||create proc myProc
@.name varchar(25),
@.color varchar(25)
as
if @.color != 'none'
select * from table1
where table1.name = @.name and table1.color = @.color
if @.color = 'none'
select * from table1
where table1.name=@.name
Linchi
"Paul" wrote:
> Just wondering if someone could provide a brief example of how to do this. I
> have a stored procedure and I need a condition where statement, for example
> inputs are
> @.name varchar(25)
> @.color varchar(25)
> if color is not 'none ' I want
> select * from table1
> where table1.name = @.name
> and table1.color = @.color
> if color is 'none' I want
> select * from table1 where table1.name=@.name.
> Thanks.
> --
> Paul G
> Software engineer.|||This does it all in one query.
SELECT *
FROM table1
WHERE table1.name = @.name
AND (@.color = 'none '
OR table1.color = @.color)
Note that it MIGHT not perform as well as the alternatives using two
individual queries.
Roy Harvey
Beacon Falls, CT
On Tue, 29 Jan 2008 11:17:02 -0800, Paul
<Paul@.discussions.microsoft.com> wrote:
>Just wondering if someone could provide a brief example of how to do this. I
>have a stored procedure and I need a condition where statement, for example
>inputs are
>@.name varchar(25)
>@.color varchar(25)
>if color is not 'none ' I want
>select * from table1
>where table1.name = @.name
> and table1.color = @.color
>if color is 'none' I want
>select * from table1 where table1.name=@.name.
>Thanks.|||thanks for the responses. I simplified the example as I actually have
several items in the select statement as well as several in the where clause
as well as joins. I may have to use two seperate queries but may try to do it
with a sing query using OR if possible.
--
Paul G
Software engineer.
"Roy Harvey (SQL Server MVP)" wrote:
> This does it all in one query.
> SELECT *
> FROM table1
> WHERE table1.name = @.name
> AND (@.color = 'none '
> OR table1.color = @.color)
> Note that it MIGHT not perform as well as the alternatives using two
> individual queries.
> Roy Harvey
> Beacon Falls, CT
> On Tue, 29 Jan 2008 11:17:02 -0800, Paul
> <Paul@.discussions.microsoft.com> wrote:
> >Just wondering if someone could provide a brief example of how to do this. I
> >have a stored procedure and I need a condition where statement, for example
> >
> >inputs are
> >@.name varchar(25)
> >@.color varchar(25)
> >
> >if color is not 'none ' I want
> >select * from table1
> >where table1.name = @.name
> > and table1.color = @.color
> >
> >if color is 'none' I want
> >select * from table1 where table1.name=@.name.
> >Thanks.
>|||On Tue, 29 Jan 2008 12:34:23 -0800, Paul
<Paul@.discussions.microsoft.com> wrote:
>thanks for the responses. I simplified the example as I actually have
>several items in the select statement as well as several in the where clause
>as well as joins. I may have to use two seperate queries but may try to do it
>with a sing query using OR if possible.
Be aware that the warning about possible performance problems of the
all-in-one version becomes more apt as the query becomes more complex.
You may not have any problem, only trying it will determine that, but
be aware of the possibility.
Roy Harvey
Beacon Falls, CT|||ok thanks for the additional information. I have it running in the live
database, using 3 separate queries based on the condition of two input
parameters and the longest query is about 2 seconds. Fortunately the
database is relatively small and some indexes have been put into place to
enhance performance.
--
Paul G
Software engineer.
"Roy Harvey (SQL Server MVP)" wrote:
> On Tue, 29 Jan 2008 12:34:23 -0800, Paul
> <Paul@.discussions.microsoft.com> wrote:
> >thanks for the responses. I simplified the example as I actually have
> >several items in the select statement as well as several in the where clause
> >as well as joins. I may have to use two seperate queries but may try to do it
> >with a sing query using OR if possible.
> Be aware that the warning about possible performance problems of the
> all-in-one version becomes more apt as the query becomes more complex.
> You may not have any problem, only trying it will determine that, but
> be aware of the possibility.
> Roy Harvey
> Beacon Falls, CT
>

Conditional Processing from a Common Table Expression (CTE)

I want to do conditional processing depending on values in the rows of a CTE. For example, is the following kind of thing possible with a CTE?:

WITH Orders_CTE (TerritoryId, ContactId)
AS
(
SELECT TerritoryId, ContactId
FROM Sales.SalesOrderHeader
WHERE (ContactId < 200)
)
IF Orders_CTE.TerritoryId > 3
BEGIN
/* Do some processing here */

END
ELSE
BEGIN
/* Do something else here */

END

When I try this, I get a syntax error near the keyword 'IF'

Any ideas? I know this kind of thing can be done with a cursor but wanted to keep with the times and avoid using one!

WITH statement is a part of SELECT/INSERT/UPDATE/DELETE statement. As result you code doesn't work.

What processing do you need?

|||

What exactly you want to do on the /* Do Some processing here */. It might help you to give the rite solution.

You can end the CTE expression with INSERT/DELETE/UPDATE/SELECT. Othere than this 4 statement none of them are allowed.

|||

Thanks for the quick response.

All I need to do in each part of the IF ...ELSE..... is to perform a select, but which select statement to use depends on a field in the CTE. One select pulls data from a table in current database, whereas the other pulls data from a table on a linked server. The tables have similar but not exactly the same structures.

As I mentioned, I have what I need working using a cursor, I was just wondering if this were possible with a CTE, but based on what you and other repsondents have stated, it appears unlikely.

Conditional inserts in trigger

Can I have a trigger that inserts into 1 of 3 different tables based on a
column being inserted on the driving table?
For example, I issue
INSERT INTO dbo.People (SSN, CategoryCode)
VALUES (123456789, 3)
If CategoryCode inserted is 3, 10 or 11 then I need to
INSERT INTO dbo.ClientInfo (PeopleID)
VALUES (inserted.PeopleID)
If CategoryCode inserted is 1 then I need to
INSERT INTO dbo.ApplicantInfo (PeopleID)
VALUES (inserted.PeopleID)
etc.
One point that may or may not be important is that the original PeopleID is
assigned in an existing insert trigger and is a random number.
Thanks.
Davidyes - see BOL for more on CREATE TRIGGER, but e.g.
create trigger yourtrigger on People for insert
as
begin
insert into dbo.ApplicantInfo (PeopleID)
select PeopleID
from inserted
where CategoryCode=1
insert into dob.ClientInfo (PeopleID)
select PeopleID
from inserted
where CategoryCode in (3, 10, 11)
end
David Chase wrote:
> Can I have a trigger that inserts into 1 of 3 different tables based on a
> column being inserted on the driving table?
> For example, I issue
> INSERT INTO dbo.People (SSN, CategoryCode)
> VALUES (123456789, 3)
> If CategoryCode inserted is 3, 10 or 11 then I need to
> INSERT INTO dbo.ClientInfo (PeopleID)
> VALUES (inserted.PeopleID)
> If CategoryCode inserted is 1 then I need to
> INSERT INTO dbo.ApplicantInfo (PeopleID)
> VALUES (inserted.PeopleID)
> etc.
> One point that may or may not be important is that the original PeopleID i
s
> assigned in an existing insert trigger and is a random number.
> Thanks.
> David
>|||That doesn't work. I get an error when it tries to create ClientInfo
because ClientInfo table has referrential integrity rule that requires
matching record in People table. Evidently, ref. integrity check does not
know that People table record exists yet. Below is my trigger code, if that
helps.
CREATE TRIGGER T_People_ITrig ON dbo.People FOR INSERT AS
SET NOCOUNT ON
DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
/* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'PersonID' */
SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
SELECT @.newc = (SELECT PersonID FROM inserted)
UPDATE People SET PersonID = @.randc WHERE PersonID = @.newc
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:uHtw5DHHGHA.1180@.TK2MSFTNGP09.phx.gbl...
> yes - see BOL for more on CREATE TRIGGER, but e.g.
> create trigger yourtrigger on People for insert
> as
> begin
> insert into dbo.ApplicantInfo (PeopleID)
> select PeopleID
> from inserted
> where CategoryCode=1
> insert into dob.ClientInfo (PeopleID)
> select PeopleID
> from inserted
> where CategoryCode in (3, 10, 11)
> end
> David Chase wrote:|||David Chase (dlchase@.lifetimeinc.com) writes:
> That doesn't work. I get an error when it tries to create ClientInfo
> because ClientInfo table has referrential integrity rule that requires
> matching record in People table. Evidently, ref. integrity check does
> not know that People table record exists yet. Below is my trigger code,
> if that helps.
Set up the FK to have UPDATE ON CASCADE.
Or instead of an UPDATE, perform first an INSERT, update the childre,
and then delete the original.

> CREATE TRIGGER T_People_ITrig ON dbo.People FOR INSERT AS
> SET NOCOUNT ON
> DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
> /* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'PersonID' */
> SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
> SELECT @.newc = (SELECT PersonID FROM inserted)
> UPDATE People SET PersonID = @.randc WHERE PersonID = @.newc
Keep in mind that a trigger fires once per statement, and thus inserted
can hold many rows.
A better bet for a random number is probably checksum(newid()).
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.seBooks Online for SQL
Server 2005
athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000
athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Conditional Full-text search

Is there any way to use full-text search conditionally? For example, I have a query:

Code Snippet

select
Search.Rank,
Items.*
from
Items
inner join
freetexttable(Items, *, 'blablabla') as Search on
Items.ItemId = Search.[Key]
where
Items.ItemId = 1
and
Items.Type >= 0
order by
Search.Rank DESC

How can I put where clause to optimize my query? I want where clause to be executed before freetexttable, for more perfomance. It's obvious, that full-text search is not optimized well in my case, because where is executed after full-text search.

Is there any way to do it? Thank you in advance.

And if I have a query

select
Search.Rank,
Items.*
from
Items
inner join
freetexttable(Items, *, 'blablabla') as Search on
Items.ItemId = Search.[Key]

Is there any way to force freetexttable return only first 10 results, but not all of them? (I know, I can do it after freetexttable with 'select top 10', but I want inside freetexttable, to minimize the load of my SQL Server).
|||Read
"Items.ItemId = 1"
like
"Items.ItemId in (1, 2, 3, 4, 5, 10"
|||Unfortunately in this version of SQL Server, freetext search and SQL Server is separated from each other. Therefore the query optimizer is not able to optimize both queries within one step. You will need to use the optional top parameters provided for freetext search. Look in the BOL:

top_n_by_rank

When an integer value, n, is specified, FREETEXTTABLE returns only the top n matches, ordered by rank.

If filtering is performed in addition to the FREETEXTTABLE predicate, the filter is applied to the top n rows and fewer than top_n_by_rank rows will be returned. Enabling the precompute rank option in the sp_configure stored procedure can increase the prerformance of FREETEXTTABLE queries that use the top_n_by_rank parameter. For more information, see sp_configure (Transact-SQL) and sp_fulltext_service (Transact-SQL).

Jens K. Suessmeyer.

-
http://www.sqlserver2005.de
-|||

Hi Zhuravl,

As said by Jean, you can use top_n_by_rank to limit resultset. This is in the BOL:

" Limiting Result Sets to Return the Most Relevant Results

In many full-text queries, the number of items matching the search condition is very large. To prevent queries from returning too many matches, use the optional argument, top_n_by_rank, in CONTAINSTABLE and FREETEXTTABLE to specify the number of matches according to rank you want returned.

Note:

Using the top_n_by_rank argument returns a subset of rows that satisfy the full-text query. If top_n_by_rank is combined with other predicates, the query could return fewer rows than the number of rows that actually match all the predicates.
With this information, Microsoft SQL Server orders the matches by rank and returns only up to the specified number. This choice can result in a dramatic increase in performance. For example, a query that would normally return 100,000 rows from a table of one million rows are processed more quickly if only the top 100 rows are requested.

If you want only the top 3 matches returned on an earlier example using CONTAINSTABLE, the query looks like the following:

USE Northwind; GO SELECT K.RANK, CompanyName, ContactName, Address FROM Customers AS C INNER JOIN CONTAINSTABLE(Customers,Address, 'ISABOUT ("des*", Rue WEIGHT(0.5), Bouchers WEIGHT(0.9))', 3) AS K ON C.CustomerID = K.[KEY]; GO

Here is the result set:

RANK CompanyName ContactName address
- -- -
123 Bon app' Laurence Lebihan 12, rue des Bouchers 65 Du monde entier Janine Labrune 67, rue des Cinquante Otages 15 France restauration Carine Schmitt 54, rue Royale

This example returns the description and category name of the top 10 food categories where the Description column contains the words "sweet and savory" near either the word "sauces" or the word "candies."

SELECT FT_TBL.Description, FT_TBL.CategoryName, KEY_TBL.RANK FROM Categories AS FT_TBL INNER JOIN CONTAINSTABLE (Categories, Description, '("sweet and savory" NEAR sauces) OR ("sweet and savory" NEAR candies)' , 10 ) AS KEY_TBL ON FT_TBL.CategoryID = KEY_TBL.[KEY]; GO
"

Other references:

http://www.developmentnow.com/blog/SQL+Server+2005+Full+Text+Search+On+HTML+Documents.aspx

Regards

Nilton Pinheiro

www.mcdbabrasil.com.br

Conditional Formatting - Export

Using the web viewer control in VS 2005, is there a way to conditionally format items for export only?

For example, user runs a report which gets displayed in the viewer. The report contains small images and or decorated text for links to additional reports. If user selects pdf from the export dropdown list and clicks export, I would like hide the images in the pdf and or format the decorated text differently.

If the answer is no, is it due to the report already being in its intermediate state?

The answer to your first question is no. One way of getting close to the desired behavior is to either use different report definitions or use a report parameter to determine if images should be shown etc.

The answer to the second question is yes. When you export the report to PDF, it will just re-render the report from the already processed (and output-format independent) intermediate format.

-- Robert

Conditional FK Deletes

How would I use a Foreign Key to prevent deletions on the parent table?

For example, I have an Orders table and an OrderCancels table related by a FK on the iOrdID. When the app requests to delete a record on the Orders table, I need to check the OrderCancels table via the iOrdID FK for corresponding records, and if there are, not delete the order.I am a little confused as the foreign key prevents exactly that - deleting a parent record that has a child. If you application attempts to delete a parent record that has child records enforced using a foreign key constraint then the application will receive an error from sql server which will tell it that the delete was unsuccessful.|||Let me clarify - I know you can check 'Cascade deletes' in the SQL GUI so that if you delete a parent, the child gets deleted as well. This is how all of our current FKs work.

Are you saying that if I want to check for children and cancel the parental deletion if the children are found, all I have to do is uncheck 'Cascade Deletes' on the FK? If so, how do I capture this error and return it to the app.

Sorry if all this seems obvious, but I am a very green DBA...

TIA,

-Justin|||You never mentioned cascading - this is very important.

The answer is yes. SQL Server automatically sends this error to the calling application.

Take a look at the books online "Cascading Referential Integrity Constraints" article.

Conditional Colour in Report Header

All,
I need to alter the Header Bar background colour in my report
depending on who the report is being prepared for. For example, client
accounts that start **P need to have a blue background whereas client
accounts that start **A need a red background.
Any help on how to switch the colour conditionally would be greatly
appreciated.
Thanks,
Scott.Just put a formula in the appropriate Color property:
=iif(...)
Cheers,
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"scottf" <scottfuk@.yahoo.co.uk> wrote in message
news:b4349ea7.0411230619.2f1771c7@.posting.google.com...
> All,
> I need to alter the Header Bar background colour in my report
> depending on who the report is being prepared for. For example, client
> accounts that start **P need to have a blue background whereas client
> accounts that start **A need a red background.
> Any help on how to switch the colour conditionally would be greatly
> appreciated.
> Thanks,
> Scott.

Concurrency in asp.net

Hi everybody,

I need to understand how concurrency excatlywork in asp.net. For example, I'm confused what happens if two users atthe same time try to access the same record in a table or even the samevariable. Do ASP.NET handle this , I mean by locking one user andletting the other to have access OR it's up to the programmer to writesome code to lock shared resources such as database , objects andvariables?

If it's up to the programmer to do this task, I appreciate if you can show me an example that clarifies that.

Thank you

ADO.NET uses a disconnected data model, meaning that in theory, nothings knows 2 users are accessing the same record at the same time. When you read a record, and the record is displayed, there is no connection left on the database. Therefore there is no "concurrency", so to speak, because for all general purpose, the users only see the record's -content-, not the record itself. This is required because the web is a stateless environment...you cannot keep a live connection to the server.

Thus, the only really effective way of handling this (there are other ways, but this is the all purpose one), is called Optimistic Concurrency. The record is read, and when you attempt to update it, ADO.NET (only IF you ask it to do so), will check if the record changed (by storing the original values) since it was read by the user. If it did, it will complain and tell you that someone else changed the record between the last time you read it and your update. Otherwise, it will write it normally.

If 2 people attempt to write the record at the exact same time ( I mean, there are 2 open connections on the same record at the exact same instant), then it is up to the DBMS to handle this, ADO.NET has no control over it. Usualy what will happen is one of the users will be given the go (first come first serve), and the second update will trigger the Optimistic Concurrency mechanism I just talked about.

|||

This article tell you how to Handling Concurrency Issues in .NET

Concatenation Formula For Int Columns

Column A, Column B and Column C : All Integer.

I want a concatenation. For example A=111, B=222. C should be 111222 NOT 333. Is it possible? If it's possible, what is the formula?

Thanks in advance...

If you need to concatenate numeric values, you need toCAST them as character values first.

SELECTCAST(ColumnAAS varchar(10)) +CAST(ColumnBAS varchar(10))AS Column3FROM myTable

|||Thank you but i ask the formula to use in Formula Property of Column in SQL (Enterprise Manager). Isn't that possible?|||Yes. The formula would be exactly the same, without the AS clause. Did you try it?

CAST(ColumnA AS varchar(10)) + CAST(ColumnB AS Varchar(10))|||

Tried after my answer. You were right. Sorry and thank you.

|||Cool, I'm glad it worked.|||Out of subject and not so important but i wondered. I set C as Unique. When i insert record, if it's duplicate so rollback transaction but ID is increased. For example 4. record was duplicate so ID's like 1,2,3,5,6... Can i prevent this so how?|||

LacOniC:

Out of subject and not so important but i wondered. I set C as Unique. When i insert record, if it's duplicate so rollback transaction but ID is increased. For example 4. record was duplicate so ID's like 1,2,3,5,6... Can i prevent this so how?

You can't prevent this if you are using an Identity column, sorry. The only way to really prevent it is to have your own ID number table, read the next available value out of it, and assign that to your new record. All of that should be wrapped in the transaction.

Concatenating SQL query results on one line.

Guys,
Here is an example.
I have a table with hors_id, hors_name.
Another table has hors_id, owner_name.
That second table contains multiple entries for that hors_id, as there can be multiple owners.
How do I construct a query that would return the following info on one line:
hors_id, hors_name, owner_name(1), owner_name(2), owner_name(3).
The logic for the solution seems as follows:
Query should return distinct id and name for the horse, loop through the owners in the second table, and append the owners to a variable while the hors_id is the same.
Any suggestions of a generic code to implement?
I tried different coding, so far doesn't work.
Thanks.Originally posted by bigfootguy
Guys,
Here is an example.
I have a table with hors_id, hors_name.
Another table has hors_id, owner_name.
That second table contains multiple entries for that hors_id, as there can be multiple owners.
How do I construct a query that would return the following info on one line:
hors_id, hors_name, owner_name(1), owner_name(2), owner_name(3).
The logic for the solution seems as follows:
Query should return distinct id and name for the horse, loop through the owners in the second table, and append the owners to a variable while the hors_id is the same.
Any suggestions of a generic code to implement?
I tried different coding, so far doesn't work.
Thanks.

Hi BigFoot,

Since SQL Server does not support Cross-Tab constructs, you will have to do some more work. I worked out a solution, but for the Customers and Orders table in the NorthWind demo database; so please translate my answer into your problem.

First of all, you have to know, how much Orders you may expect at least. You can query the actual maximum by

SELECT MAX(Num)
FROM (SELECT COUNT(*) AS Num, CustomerID
FROM Orders
GROUP BY CustomerID) T

As much orders you expect, as much views you have to create:

1) CREATE VIEW Order1 AS SELECT MAX(orderid) AS ID, customerid FROM Orders GROUP BY customerid

2) CREATE VIEW Orders2 AS SELECT MAX(orderid) AS ID, orders.CustomerID FROM Orders, Orders1 WHERE orders.CustomerID = Orders1.CustomerID AND Orders.OrderID < Orders1.ID GROUP BY orders.CustomerID

3) CREATE VIEW Orders3 AS SELECT MAX(orderid) AS ID, orders.CustomerID FROM Orders, Orders2 O WHERE orders.CustomerID = O.CustomerID AND Orders.OrderID < O.ID GROUP BY orders.CustomerID

Got the point? Select one or no order by customer per view, excluding the orders already selected in earlier views.

Having created those views, you may select you required result as :

SELECT C.CustomerID, C.CompanyName, O1.ID AS Order1,
O2.ID AS Order2, O3.ID AS Order3
FROM Customers C LEFT OUTER JOIN
(Orders1 O1 LEFT OUTER JOIN
(Orders2 O2 LEFT OUTER JOIN
Orders3 O3 ON O2.CustomerID = O3.CustomerID) ON
O1.CustomerID = O2.CustomerID) ON
C.CustomerID = O1.CustomerID

This works fine if your expected number of orders can be limited. If not, you will have to write a stored procedure returning your recordset.

Cheers :p|||If your hors_owners table has some field for categoring the owners (say, owner_type) for each hors, you can write a cross-tab query that will place each owner_type in its own column. You could use any type description you want, as long as each hors has at most one of each type. For example, "Primary_Owner", "Secondary_Owner", "Investor", or even an ID like "1", "2", "3"... If you search books on-line for "Crosstab", they show a good example of how to write such a query. If you still have problems, post them to the forum.

If you can't create an owner_type field, well that that is a "hors of a different color". (I couldn't resist...). This is one of those rare situations where I would recommend using a cursor, because you won't need to hard-code the number of owners. If you aren't returning hundreds or thousands of hors records, then consider putting the cursor logic in a user-defined function named something like "udf_HorsOwner_String". Your end-query could then be as simple as:

Select *, dbo.udf_HorsOwnerString(hors_id) from tbl_hors

If you need more guidance, post again when you have an idea of what direction you want to take with this.

blindman

Concatenating rows into one field for summary

Hi,
I am looking for a way of putting multiple values from different rows into
one field, for example:
The dataset returns 3 rows, with two fields, first name and surname like
below:
FirstName Surname
Joe Bloggs
David Beckham
Sue Smith
I want to get all of those surnames, and put them say into the final totals
of the report so they look like Bloggs,Beckham,Smith.
Can anyone see a way of archiving this.
Cheers
LukeYou cant do it in the same query because you will be using the first name if
not then you can use this method. what you can do is to create a seperate
dataset with the following method and refer this dataset in your report in
final totals
select @.aa = COALESCE(surnames + ', ', '') from ..... etc.. etc...ofcourse
you
need to fillup. :-)
any problem let me know.
Amarnath
"lukethepunk" wrote:
> Hi,
> I am looking for a way of putting multiple values from different rows into
> one field, for example:
> The dataset returns 3 rows, with two fields, first name and surname like
> below:
> FirstName Surname
> Joe Bloggs
> David Beckham
> Sue Smith
> I want to get all of those surnames, and put them say into the final totals
> of the report so they look like Bloggs,Beckham,Smith.
> Can anyone see a way of archiving this.
> Cheers
> Luke
>|||Hi,
Thanks thats almost got me what i want (i'd never heard of the COALESCE
function!)
The only problem i've got now is there are duplicate values ending up in the
end string, eg: Bloggs, Bloggs, Smith, Beckham, Beckham
Is there anyway i can keep them out using the sql, or will i have to use
some custom code in the report to keep them uniquie?
Cheers
Luke
"Amarnath" wrote:
> You cant do it in the same query because you will be using the first name if
> not then you can use this method. what you can do is to create a seperate
> dataset with the following method and refer this dataset in your report in
> final totals
> select @.aa = COALESCE(surnames + ', ', '') from ..... etc.. etc...ofcourse
> you
> need to fillup. :-)
> any problem let me know.
> Amarnath
>
> "lukethepunk" wrote:
> > Hi,
> >
> > I am looking for a way of putting multiple values from different rows into
> > one field, for example:
> >
> > The dataset returns 3 rows, with two fields, first name and surname like
> > below:
> >
> > FirstName Surname
> > Joe Bloggs
> > David Beckham
> > Sue Smith
> >
> > I want to get all of those surnames, and put them say into the final totals
> > of the report so they look like Bloggs,Beckham,Smith.
> >
> > Can anyone see a way of archiving this.
> >
> > Cheers
> > Luke
> >|||Hi luke,
unfortunetly there is no such thing like distinct coalesce.. May be what you
can do is to take a distinct and then pass the result set to coalesce. Pl try.
Amarnath
"lukethepunk" wrote:
> Hi,
> Thanks thats almost got me what i want (i'd never heard of the COALESCE
> function!)
> The only problem i've got now is there are duplicate values ending up in the
> end string, eg: Bloggs, Bloggs, Smith, Beckham, Beckham
> Is there anyway i can keep them out using the sql, or will i have to use
> some custom code in the report to keep them uniquie?
> Cheers
> Luke
>
> "Amarnath" wrote:
> > You cant do it in the same query because you will be using the first name if
> > not then you can use this method. what you can do is to create a seperate
> > dataset with the following method and refer this dataset in your report in
> > final totals
> > select @.aa = COALESCE(surnames + ', ', '') from ..... etc.. etc...ofcourse
> > you
> > need to fillup. :-)
> >
> > any problem let me know.
> >
> > Amarnath
> >
> >
> >
> > "lukethepunk" wrote:
> >
> > > Hi,
> > >
> > > I am looking for a way of putting multiple values from different rows into
> > > one field, for example:
> > >
> > > The dataset returns 3 rows, with two fields, first name and surname like
> > > below:
> > >
> > > FirstName Surname
> > > Joe Bloggs
> > > David Beckham
> > > Sue Smith
> > >
> > > I want to get all of those surnames, and put them say into the final totals
> > > of the report so they look like Bloggs,Beckham,Smith.
> > >
> > > Can anyone see a way of archiving this.
> > >
> > > Cheers
> > > Luke
> > >|||Hi,
I have managed to get it working by passing the results to some custom code,
and looping through the string and sending back only one of each.
Thanks for your help
Luke
"Amarnath" wrote:
> Hi luke,
> unfortunetly there is no such thing like distinct coalesce.. May be what you
> can do is to take a distinct and then pass the result set to coalesce. Pl try.
> Amarnath
> "lukethepunk" wrote:
> > Hi,
> >
> > Thanks thats almost got me what i want (i'd never heard of the COALESCE
> > function!)
> >
> > The only problem i've got now is there are duplicate values ending up in the
> > end string, eg: Bloggs, Bloggs, Smith, Beckham, Beckham
> >
> > Is there anyway i can keep them out using the sql, or will i have to use
> > some custom code in the report to keep them uniquie?
> >
> > Cheers
> > Luke
> >
> >
> > "Amarnath" wrote:
> >
> > > You cant do it in the same query because you will be using the first name if
> > > not then you can use this method. what you can do is to create a seperate
> > > dataset with the following method and refer this dataset in your report in
> > > final totals
> > > select @.aa = COALESCE(surnames + ', ', '') from ..... etc.. etc...ofcourse
> > > you
> > > need to fillup. :-)
> > >
> > > any problem let me know.
> > >
> > > Amarnath
> > >
> > >
> > >
> > > "lukethepunk" wrote:
> > >
> > > > Hi,
> > > >
> > > > I am looking for a way of putting multiple values from different rows into
> > > > one field, for example:
> > > >
> > > > The dataset returns 3 rows, with two fields, first name and surname like
> > > > below:
> > > >
> > > > FirstName Surname
> > > > Joe Bloggs
> > > > David Beckham
> > > > Sue Smith
> > > >
> > > > I want to get all of those surnames, and put them say into the final totals
> > > > of the report so they look like Bloggs,Beckham,Smith.
> > > >
> > > > Can anyone see a way of archiving this.
> > > >
> > > > Cheers
> > > > Luke
> > > >