help on calculation including nulls

Hi everybody

I have this sql which calculates the total

MY TABLE IS:

A B C

100 5 3
100 0 5
20 0 0

sql is

A - B + C as total

the result is it gives me only the total of the first row since it has values on the on the B AND C

the result is

A B C total

100 5 3 98
100 0 5
20 0 0

with no result for the last two rows.. I want to give a condition that if the value is zero or null go to the next...

thanks for any helpRead up on the COALESCE function in the (online) help files.

COALESCE(Field, ValueIfNull)|||Thanks solved my problem and thanks for reminding me on the way of saying thanks in advance..|||you can also use isnull-

select a, b, c, (isnull(A,0) - isnull(B, 0) + isnull(C,0)) as total|||coalesce is generally preferred as it's ANSI. also it can take multiple args.|||Yes thats right, COALESCE have several advantages over ISNULL

http://databases.aspfaq.com/database/coalesce-vs-isnull-sql.html
http://odetocode.com/Blogs/scott/archive/2004/11/30/679.aspx (http://http://odetocode.com/Blogs/scott/archive/2004/11/30/679.aspx)

readability is the only advantage of using ISNULL.|||readability is the only advantage of using ISNULL.

readability? what's more readable:

select ISNULL(a, ISNULL(b, ISNULL(c,d)))

select COALESCE(a,b,c,d)

obviously I prefer coalesce. :)|||IsNull is more readable imo.
Before I learnt of coalesce I had no idea what it meant.|||IsNull is more readable imo.only for two values, and even then it's six of one, six of the other

ISNULL(foo,0)
COALESCE(foo,0)

now try it with multiple expressions, and see if ISNULL is really more readable|||Readability -

Many consider ISNULL()'s readability and common sense naming to be an advantage. While I will agree that it easier to spell and pronounce, I disagree that its naming is intuitive. In other languages such as VB/VBA/VBScript, ISNULL() accepts a single input and returns a single boolean output.

this is what databases.aspfaq.com says.

I just wanted to tell alexyeth about the alternative available.
I alway prefer COALESCE.|||IMO ISNULL is a stupid name for a function that does not tell you whether or not the input is null. The fact that there is another function of the same name in another MS language that does do this compounds confusion.

IMO - the fact you don't know what coalesce means is an advantage. Come across it and you have to look it up. Come across ISNULL as a vb programmer and you might think you know what it does.

BOOOOOOOOO to T-SQL ISNULL!|||IMO ISNULL is a stupid name for a function that does not tell you whether or not the input is null. The fact that there is another function of the same name in another MS language that does do this compounds confusion.

I'm pretty sure sql server inherited this function from sybase. doesn't change the fact that ISNULL is a stupid name for the function though. :)|||Goddam Bill lover ;)|||eh, I have a soft spot for sql server, that's all. ;)

Help needed with Instead of Update trigger on a View

I have an application where we are replacing a subsystem including portions
of the database. In order to minimize the code impact on the existing
application, we have decided to create a few "compatibility views" - i.e.
database Views that produce the same result and with the same names as the
old tables. Also in order to allow existing code to continue to function,
we are implementing INSTEAD OF triggers on the views. Even though all our
database accesses are encapsulated in stored procs, we have around 1500 of
them, and one of the tables we needed to reengineer this way is the "main"
table for the entire app. To make sure this isn't trivial, we have a new
"master" entity table with an Identity column that is referenced by the
reengineered "main" table.
At this point, we are only aware of performance impacts - everything appears
to work OK:
1. If a NON-NULL IDENTITY (or other non-required column in an INSERT
statement) is part of an index, we loose the use of the index as a result of
having to use NULLIF() or COALESCE() on those columns in the view. For the
same reason, we can't index those view(s).
2. (This is where the question comes in:) The INSTEAD OF UPDATE trigger
appears to require a large number of separate UPDATE statements against the
base tables, or building a dynamic SQL statement. We are looking for
guidance...
Now to my question:
In the INSTEAD OF UPDATE trigger, I have about 90 member columns from one
table. If I understand correctly, since the triggering update statement may
only update one column, I cannot use an UPDATE statement against the base
table that updates all the columns with the values from the 'updated'
pseudo-table. Instead, I will have to check if each column is updated, and
if so, either update it separately or build a dynamic SQL UPDATE statement
including those columns that have been updated.
What is the recommended approach to this?
TIA,
Tore.On Thu, 11 May 2006 14:30:01 -0400, "Tore" <tbostrup at agfirst> wrote:
(snip)
>1. If a NON-NULL IDENTITY (or other non-required column in an INSERT
>statement) is part of an index, we loose the use of the index as a result o
f
>having to use NULLIF() or COALESCE() on those columns in the view.
Hi Tore,
I don't think I understand what you're saying here. Where are you using
NULLIF() or COALESCE() and why? Coould you post a simplified sample of
your code?

> For the
>same reason, we can't index those view(s).
And neither should you. If you index the views, you'll create a complete
copy of your data. I don't think that yoou should do that in your
scenario.
(snip)
>In the INSTEAD OF UPDATE trigger, I have about 90 member columns from one
>table. If I understand correctly, since the triggering update statement ma
y
>only update one column, I cannot use an UPDATE statement against the base
>table that updates all the columns with the values from the 'updated'
>pseudo-table.
You undersatnd incorrectly. There is no 'updated' pseudo-table. The
'deleted' and 'inserted' pseudo-tables contain the complete before and
after image of the updated rows, including all columns that are not
affected by the update.
If your compatibility view translates to one new table, just perform the
modification in a single UPDATE statement. If your compatibility view
translates to more than one new table, use
IF UPDATE(col1) OR UPDATE(col2) ....
to find out which table(s) need updating, then use a single UPDATE
statement for all rows in each of the tables.
Sure, you'll be setting columns to the same value they already had. The
added cost of that is much less than the cost of finding out which
columns to update and executing up to 90 (!) consecutive UPDATE
statements against the same set of rows.

> Instead, I will have to check if each column is updated, and
>if so, either update it separately or build a dynamic SQL UPDATE statement
>including those columns that have been updated.
Using the dynamic SQL is even a worse option - it forces you to give
every user update permissions to the table. (And it will be slow because
of the extra recompiles).
www.sommarskog.se/dynamic_sql.html
Hugo Kornelis, SQL Server MVP|||Thanks Hugo,
I'll be looking at this tomorrow.
Tore.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:p75762lfnlmg789b20fv9jk1jfjrhs2it0@.
4ax.com...
> On Thu, 11 May 2006 14:30:01 -0400, "Tore" <tbostrup at agfirst> wrote:
> (snip)
of
> Hi Tore,
> I don't think I understand what you're saying here. Where are you using
> NULLIF() or COALESCE() and why? Coould you post a simplified sample of
> your code?
>
> And neither should you. If you index the views, you'll create a complete
> copy of your data. I don't think that yoou should do that in your
> scenario.
> (snip)
may
> You undersatnd incorrectly. There is no 'updated' pseudo-table. The
> 'deleted' and 'inserted' pseudo-tables contain the complete before and
> after image of the updated rows, including all columns that are not
> affected by the update.
> If your compatibility view translates to one new table, just perform the
> modification in a single UPDATE statement. If your compatibility view
> translates to more than one new table, use
> IF UPDATE(col1) OR UPDATE(col2) ....
> to find out which table(s) need updating, then use a single UPDATE
> statement for all rows in each of the tables.
> Sure, you'll be setting columns to the same value they already had. The
> added cost of that is much less than the cost of finding out which
> columns to update and executing up to 90 (!) consecutive UPDATE
> statements against the same set of rows.
>
statement
> Using the dynamic SQL is even a worse option - it forces you to give
> every user update permissions to the table. (And it will be slow because
> of the extra recompiles).
> www.sommarskog.se/dynamic_sql.html
> --
> Hugo Kornelis, SQL Server MVP

Help me to script a table including data

Im try to use SQL server 2000 and use Enterprise to cript a table

But my result is only table structure in the script (myfile.sql)

(something like "create table Tb1, column..") and not have any data in my table included.

how good it is if it can write some insert statement (depend on number of records existed).

Could I have any way to do it with MS SQLServer 2000?

Thank you much.

DongMT

Hi,

This is a SQL question and not related to SSIS. You will need to post this kind of questions in SQL Server Forums.
Anyways, here is a link which has answer for your query.
http://vyaskn.tripod.com/code.htm#inserts

Thanks