Impact of empty tables on database size and performance

Hello All,

When creating my database I have modeled some of the tables after the Adventureworks sample database.

There are some fields or entire tables in Adventureworks that I do not see an imediate use for, however; I would hate to ommit them to find out later they would have been benificial. (.eg territory table).

In general terms what would the impact be on size and performance of a database which contains tables or fields that do not contain data.

Thanks for your help!

Blair:

There will be little impact from tables that are not used provided these tables are small and do not grow. If the tables are large then they can impact your storage requirements and the size and speed of your backups.

Fields that are not used are a different story -- especially if the fields are populated with large amounts of "spectator" data. These "spectator" fields will then impact the amount of space required for their tables and will have an impact on the amount of time required for a "table scan". This becomes amplified to some extent if these fields participate in any indexes. If your fields are all null the amount of bloating will not be as great.

Another problem that can occur if you leave in fields that have at the moment no use is that in the future it can become tempting for somebody to start "using" these fields in ways that are not planned. Eliminating unused fields can stop this kind of "cobbling" before it takes place.

I would suggest that you are better off with a well designed, well thought out database in which each column has a specific meaning with a specific intended use.

Images in SQL

I notice that SQL 2000 has a datatype of image.
My question is after I setup the table and fields how do I populate the
table with image data?Look up the usage and example of statements UPDATETEXT and WRITETEXT in SQL
Server Books Online.
Anith|||I looked those up and they just give examples of writing text to the fields.
I would like to know how to add a picture to the field.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OoEv9%23glGHA.4444@.TK2MSFTNGP02.phx.gbl...
> Look up the usage and example of statements UPDATETEXT and WRITETEXT in
> SQL Server Books Online.
> --
> Anith
>|||Image and nText fields are binary fields, like the old BLOB (binary large
object) fields.
To write an image into thsee fields you need to serialize the data into eith
er a
stream or use ADO or ADO.Net
A better question back to you is how and when do you want to get the picture
s
into the database?
Alternatively, you may wish to simply store the images somewhere and use a p
ath
to the raw image files, there are benefits and costs for both methods, in th
e
database or a pointer to a file.
HTH
JeffP...
<Preacher Man> wrote in message news:udRf$ChlGHA.4772@.TK2MSFTNGP04.phx.gbl...
> I looked those up and they just give examples of writing text to the field
s.
> I would like to know how to add a picture to the field.
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:OoEv9%23glGHA.4444@.TK2MSFTNGP02.phx.gbl...
>|||I am trying to write a simple application for printing employee badges. I
have VFP 9.0 that I will be using as the application writer.
Any ideas on how I should enter these images into the database? I can use a
VFP table or SQL is doesn't really matter to me in this case.
A path to the filename would be fine for me to use also, but how would I
implement that into a form?
Thanks for any info.
"JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
news:%23cB$WUhlGHA.4792@.TK2MSFTNGP02.phx.gbl...
> Image and nText fields are binary fields, like the old BLOB (binary large
> object) fields.
> To write an image into thsee fields you need to serialize the data into
> either a
> stream or use ADO or ADO.Net
> A better question back to you is how and when do you want to get the
> pictures
> into the database?
> Alternatively, you may wish to simply store the images somewhere and use a
> path
> to the raw image files, there are benefits and costs for both methods, in
> the
> database or a pointer to a file.
> HTH
> JeffP...
>
> <Preacher Man> wrote in message
> news:udRf$ChlGHA.4772@.TK2MSFTNGP04.phx.gbl...
>|||Sorry, I'm out of the loop on VFP since 3.2... but google this...
image data into SQL
HTH
JeffP...
<Preacher Man> wrote in message news:%23pQTiXhlGHA.3740@.TK2MSFTNGP02.phx.gbl...ed">
> I am trying to write a simple application for printing employee badges. I
> have VFP 9.0 that I will be using as the application writer.
> Any ideas on how I should enter these images into the database? I can use
a
> VFP table or SQL is doesn't really matter to me in this case.
> A path to the filename would be fine for me to use also, but how would I
> implement that into a form?
> Thanks for any info.
> "JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
> news:%23cB$WUhlGHA.4792@.TK2MSFTNGP02.phx.gbl...
>|||Here is an article that I wrote about storing and retrieving images from SQL
Server that might help:
http://www.dbazine.com/sql/sql-articles/larsen13
"Preacher Man" wrote:

> I notice that SQL 2000 has a datatype of image.
> My question is after I setup the table and fields how do I populate the
> table with image data?
>
>|||See if this helps:
http://groups.google.com/groups/sea...lum
n%22
Steve Kass
Drew University
Preacher Man wrote:

>I notice that SQL 2000 has a datatype of image.
>My question is after I setup the table and fields how do I populate the
>table with image data?
>
>|||I would store them in the filesystem, as suggested. Using the IMAGE
datatype is more of a hassle than it's worth. Here's an article describing
some of the ups and downs: http://www.aspfaq.com/show.asp?id=2149. If you
stored the path to the filename in a VARCHAR column, you would retrieve the
image by filename in your front-end application. I'm not familiar with VFP
9, so I couldn't give you specific advice on that, but I can almost
guarantee it would be easier than using TEXTPTR and READTEXT! :)
<Preacher Man> wrote in message
news:%23pQTiXhlGHA.3740@.TK2MSFTNGP02.phx.gbl...
>I am trying to write a simple application for printing employee badges. I
>have VFP 9.0 that I will be using as the application writer.
> Any ideas on how I should enter these images into the database? I can use
> a VFP table or SQL is doesn't really matter to me in this case.
> A path to the filename would be fine for me to use also, but how would I
> implement that into a form?
>

Image positioning

I have a a few image fields within the body of a report. No matter what I
do, remove vertical spacing, position manually etc. I cannot seem to get
them to render in the correct position within the body of the report while
previewing in visual studio. Apparently I do not understand the left to
right, top to bottom positioning. Does anyone have input on this? If I
remove vertical spacing, they overlap, if I move the image below the first
down a little to get rid of the overlap, I get an inch of white space. The
report has sub reports and a lot of other information and when rendered
displays one record per page. Very frustrating. Any Ideas gang?
Clint JenningsI want to thank everyone for the quick response on this. If anyone one is
interested, I wrapped the images with a rectangle and they stayed in place.
"Clint Jennings" wrote:
> I have a a few image fields within the body of a report. No matter what I
> do, remove vertical spacing, position manually etc. I cannot seem to get
> them to render in the correct position within the body of the report while
> previewing in visual studio. Apparently I do not understand the left to
> right, top to bottom positioning. Does anyone have input on this? If I
> remove vertical spacing, they overlap, if I move the image below the first
> down a little to get rid of the overlap, I get an inch of white space. The
> report has sub reports and a lot of other information and when rendered
> displays one record per page. Very frustrating. Any Ideas gang?
> Clint Jennings
>
>

I'm retarded and need help with Derived Column or Script Task

I am moving data from an oracle database to sql server. Two of the source fields are dates, which sometimes contain values I know to be incorrect ('0001/01/01' and '1900/01/01').

I'd like to use either the derived column or script task (or any other appropriate one) to update these incorrect values (in both columns) to null before inserting into sql server, smalldatetime field.

In sql 2000 dts, I would simply use a VBScript IF statement, but I'm somewhat clueless when it comes to .Net.

Does anyone have any ideas or sample code that may help?

Thank you much.

In a derived column you could do a comparison expression such as:

[ORACLE_COL] == (DT_DBTIMESTAMP)"0001/01/01" || [ORACLE_COL] == (DT_DBTIMESTAMP)"1900/01/01" ? NULL(DT_DBTIMESTAMP) : [ORACLE_COL]

|||

That worked perfectly, thank you. However it also looks like it's slowing me down quite a bit. I'm moving a lot of data, 16 million records, from oracle to sql server. Moving this into a temp table without the derived column task only took 8 minutes. After adding the new task I can see it would take far longer.

Do you know of any good learning resources for SSIS besides Books on Line?

Thank you.

|||

www.sqlis.com is the recognised community site.

The guys that run it (Darren and Allan) are also running a new wiki site: http://wiki.sqlis.com/

-Jamie

|||I found that the WROX press book "SQL Server 2005 Integration Services",, ISBN 0-7645-8435-9 is a very good resource. I normally do not like using anything other than online resources either.

I'm confused about "Null" fields and Default values

I have defined a table with a "DateCreated" field. I put "(getdate())"
(w/o quotes) in the Default Value field when I defined the table (Allow
Nulls). First I tried adding records (via a VB.NET app) and the records
added fine, but no dates. So I turned off "Allow Nulls". Now, I am getting
an error if I don't fill in the date field from the app..."Null not
allowed." I thought (wrongly, obviously) that the point of "Default Value"
was to furnish a "Default Value."
Please straighten me out...
TIA,
Larry WoodsNULL != empty string! If your column doesn't allow NULLs, and you want to
supply a default value, you should not specify that column at all in your
insert statement.
"Larry Woods" <larry@.lwoods.com> wrote in message
news:e7xPbEZoDHA.488@.tk2msftngp13.phx.gbl...
> I have defined a table with a "DateCreated" field. I put "(getdate())"
> (w/o quotes) in the Default Value field when I defined the table (Allow
> Nulls). First I tried adding records (via a VB.NET app) and the records
> added fine, but no dates. So I turned off "Allow Nulls". Now, I am
getting
> an error if I don't fill in the date field from the app..."Null not
> allowed." I thought (wrongly, obviously) that the point of "Default
Value"
> was to furnish a "Default Value."
> Please straighten me out...
> TIA,
> Larry Woods
>|||I had "hoped" that I could either (1) insert the date myself or, if not,
then SQL would insert the default date (getdate()) that I had specified.
Guess not, huh?
Larry
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:%23fgAdJZoDHA.424@.TK2MSFTNGP10.phx.gbl...
> NULL != empty string! If your column doesn't allow NULLs, and you want to
> supply a default value, you should not specify that column at all in your
> insert statement.
>
>
> "Larry Woods" <larry@.lwoods.com> wrote in message
> news:e7xPbEZoDHA.488@.tk2msftngp13.phx.gbl...
> > I have defined a table with a "DateCreated" field. I put "(getdate())"
> > (w/o quotes) in the Default Value field when I defined the table (Allow
> > Nulls). First I tried adding records (via a VB.NET app) and the records
> > added fine, but no dates. So I turned off "Allow Nulls". Now, I am
> getting
> > an error if I don't fill in the date field from the app..."Null not
> > allowed." I thought (wrongly, obviously) that the point of "Default
> Value"
> > was to furnish a "Default Value."
> >
> > Please straighten me out...
> >
> > TIA,
> >
> > Larry Woods
> >
> >
>|||> I had "hoped" that I could either (1) insert the date myself or, if not,
> then SQL would insert the default date (getdate()) that I had specified.
> Guess not, huh?
Yes, you can! You need to make sure you understand what "it not" means.
Leave the column out of your INSERT statement, rather than setting it (or
not setting it).
CREATE TABLE blat
(
id INT,
dateColumn DATETIME DEFAULT GETDATE()
)
-- now compare:
INSERT blat(id) VALUES(1)
INSERT blat(id, dateColumn) VALUES(2, '2003-10-31')
INSERT blat(id, dateColumn) VALUES(3, NULL)
INSERT blat(id, dateColumn) VALUES(4, '')
SELECT * FROM blat
DROP TABLE blat|||Aaron is it possible to do somthing like this, I'm sure I've read it
somewhere:
INSERT blat(id, dateColumn) VALUES(4, DEFAULT)
Al.
On Sun, 2 Nov 2003 18:32:31 -0500, "Aaron Bertrand [MVP]"
<aaron@.TRASHaspfaq.com> wrote:
>> I had "hoped" that I could either (1) insert the date myself or, if not,
>> then SQL would insert the default date (getdate()) that I had specified.
>> Guess not, huh?
>Yes, you can! You need to make sure you understand what "it not" means.
>Leave the column out of your INSERT statement, rather than setting it (or
>not setting it).
>CREATE TABLE blat
>(
> id INT,
> dateColumn DATETIME DEFAULT GETDATE()
>)
>-- now compare:
>INSERT blat(id) VALUES(1)
>INSERT blat(id, dateColumn) VALUES(2, '2003-10-31')
>INSERT blat(id, dateColumn) VALUES(3, NULL)
>INSERT blat(id, dateColumn) VALUES(4, '')
>SELECT * FROM blat
>DROP TABLE blat
>|||> Aaron is it possible to do somthing like this, I'm sure I've read it
> somewhere:
> INSERT blat(id, dateColumn) VALUES(4, DEFAULT)
Sure. You can use the DEFAULT keyword to let SQL Server apply a
timestamp/rowversion, value generated by a default constraint or a NULL. Not
an identity, though.
--
Tibor Karaszi
"Harag" <harag@.softGETRIDOFCAPLETTERShome.net> wrote in message
news:ro2cqv86knq0mjl89av3enng7nv3s4gemd@.4ax.com...
> Aaron is it possible to do somthing like this, I'm sure I've read it
> somewhere:
> INSERT blat(id, dateColumn) VALUES(4, DEFAULT)
> Al.
> On Sun, 2 Nov 2003 18:32:31 -0500, "Aaron Bertrand [MVP]"
> <aaron@.TRASHaspfaq.com> wrote:
> >> I had "hoped" that I could either (1) insert the date myself or, if
not,
> >> then SQL would insert the default date (getdate()) that I had
specified.
> >> Guess not, huh?
> >
> >Yes, you can! You need to make sure you understand what "it not" means.
> >Leave the column out of your INSERT statement, rather than setting it (or
> >not setting it).
> >
> >CREATE TABLE blat
> >(
> > id INT,
> > dateColumn DATETIME DEFAULT GETDATE()
> >)
> >
> >-- now compare:
> >INSERT blat(id) VALUES(1)
> >INSERT blat(id, dateColumn) VALUES(2, '2003-10-31')
> >INSERT blat(id, dateColumn) VALUES(3, NULL)
> >INSERT blat(id, dateColumn) VALUES(4, '')
> >
> >SELECT * FROM blat
> >
> >DROP TABLE blat
> >
> >
>|||To All:
Thanks for the advice. The problem comes down to this: I am using ADO.NET
and a Data Adapter. The Data Adapter gens the INSERT and obviously doesn't
handle the DEFAULT... The INSERT has a placeholder defined for the date
field, and seemingly no logic to handle the test for a default value. Guess
I will have to plug in the date myself.
My problem is that I am coming from the "kiddie" world of Access, and it
handled this situation just fine; i.e., if you didn't enter a value it
plugged in the default.
Oh, well.
Thanks, again.
Larry
"Larry Woods" <larry@.lwoods.com> wrote in message
news:%23VyH%23hZoDHA.2272@.tk2msftngp13.phx.gbl...
> I had "hoped" that I could either (1) insert the date myself or, if not,
> then SQL would insert the default date (getdate()) that I had specified.
> Guess not, huh?
> Larry
> "Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
> news:%23fgAdJZoDHA.424@.TK2MSFTNGP10.phx.gbl...
> > NULL != empty string! If your column doesn't allow NULLs, and you want
to
> > supply a default value, you should not specify that column at all in
your
> > insert statement.
> >
> >
> >
> >
> >
> > "Larry Woods" <larry@.lwoods.com> wrote in message
> > news:e7xPbEZoDHA.488@.tk2msftngp13.phx.gbl...
> > > I have defined a table with a "DateCreated" field. I put
"(getdate())"
> > > (w/o quotes) in the Default Value field when I defined the table
(Allow
> > > Nulls). First I tried adding records (via a VB.NET app) and the
records
> > > added fine, but no dates. So I turned off "Allow Nulls". Now, I am
> > getting
> > > an error if I don't fill in the date field from the app..."Null not
> > > allowed." I thought (wrongly, obviously) that the point of "Default
> > Value"
> > > was to furnish a "Default Value."
> > >
> > > Please straighten me out...
> > >
> > > TIA,
> > >
> > > Larry Woods
> > >
> > >
> >
> >
>|||Hi Larry,
Thanks for your feedback. I think this article will help you a lot.
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=gZPKVHzg
DHA.2624%40cpmsftngxa06.phx.gbl&rnum=1&prev=/groups%3Fq%3Dv-kevy%2Bdefault%2
Bvalue%2Bsql%2Bdataset%2Btyped%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%2
6selm%3DgZPKVHzgDHA.2624%2540cpmsftngxa06.phx.gbl%26rnum%3D1
Please feel free to post in the group if this solves your problem or if you
would like further assistance.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||I found the Google response. It says to put the default value in the xsd
property for the field. If I want either "now()" or "date()" as the
default, how do I specify that? I tried both and got errors both times. By
looking at the XML I can see why, I just don't know enough about the format
of the XSD to know how to specify "code" in the XSD.
Please advise...
TIA,
Larry
"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
news:RTDs5$toDHA.2700@.cpmsftngxa06.phx.gbl...
> Hi Larry,
> Thanks for your feedback. I think this article will help you a lot.
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=gZPKVHzg
>
DHA.2624%40cpmsftngxa06.phx.gbl&rnum=1&prev=/groups%3Fq%3Dv-kevy%2Bdefault%2
>
Bvalue%2Bsql%2Bdataset%2Btyped%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%2
> 6selm%3DgZPKVHzgDHA.2624%2540cpmsftngxa06.phx.gbl%26rnum%3D1
> Please feel free to post in the group if this solves your problem or if
you
> would like further assistance.
> Regards,
> Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
>|||Hi Larry,
Thanks for your feedback. As I understand, you are using ADO.NET with
Dataadapter to connect SQL Server. You want to find a way to set the
default value of the column before you update the dataset. If I have
misunderstood, please feel free to let me know.
Based on my research, I would like you to try to add the following
statements in the codes to see if they solve your problem.
this.dataset11.Tables["<TableName>"].Columns["<ColumnName>"].DefaultValue =DateTime.Now;
dataset11 is the name of the Dataset object.
For additional information regarding this issue, please refer to the
following article.
DataColumn.DefaultValue Property
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfSystemDataDataColumnClassDefaultValueTopic.asp
Also, it seems that current problem in this issue is related to ADO.NET
programming. I think the current news group is not the best one for this
problem. To resolve this problem, you may need to program the code with
DefaultValue property of the DataColumn. Therefore, I suggest that you post
this question in the microsoft.public.dotnet.framework.adonet newsgroup,
which is primarily for issues involving ADO.NET programming.
The reason why we recommend posting appropriately is you will get the most
qualified pool of respondents, and other partners who read the newsgroups
regularly can either share their knowledge or learn from your interaction
with us. I hope the problem can be resolved quickly.
Thank you for using our Newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

IIF statment in an SQL statment.

I am trying to convert a logical and date fields into number fields. I am
using IIF() but I can't seem to get the syntax correct. Help Please.
SELECT MagazineName, COUNT(Quantity) AS QTY, iif(RenewalFlag = flase, 0,
1) AS REFL, iif(CancelDate is not null,0,1) as CLRFL
FROM Order2
GROUP BY MagazineName
The overall goal is to
1) count then number of records
2) count how many are renewal's
3) count how many are canceled
example:
CHILD 50 5 1
Scott BurkeHey Scott,
Use the CASE statement in SQL, not IIF. Like this:
SELECT MagazineName, COUNT(Quantity) AS QTY,CASE WHEN RenewalFlag =false THEN 0 ELSE 1 END AS REFL, CASE WHEN CancelDate is not null THEN 0 ELSE
1 END as CLRFL
FROM Order2
GROUP BY MagazineName
Michael C
"Scott Burke" wrote:
> I am trying to convert a logical and date fields into number fields. I am
> using IIF() but I can't seem to get the syntax correct. Help Please.
> SELECT MagazineName, COUNT(Quantity) AS QTY, iif(RenewalFlag = flase, 0,
> 1) AS REFL, iif(CancelDate is not null,0,1) as CLRFL
> FROM Order2
> GROUP BY MagazineName
> The overall goal is to
> 1) count then number of records
> 2) count how many are renewal's
> 3) count how many are canceled
> example:
> CHILD 50 5 1
> Scott Burke|||Hi Michael. Thanks for the suggestion. It worked exactly the way I wonted to.
The case statement looks a lot like an IIF() statement to me.
Time to do some research.
Thanks again.
Scott Burke
"Michael C" wrote:
> Hey Scott,
> Use the CASE statement in SQL, not IIF. Like this:
>
> SELECT MagazineName, COUNT(Quantity) AS QTY,CASE WHEN RenewalFlag => false THEN 0 ELSE 1 END AS REFL, CASE WHEN CancelDate is not null THEN 0 ELSE
> 1 END as CLRFL
> FROM Order2
> GROUP BY MagazineName
>
> Michael C
> "Scott Burke" wrote:
> > I am trying to convert a logical and date fields into number fields. I am
> > using IIF() but I can't seem to get the syntax correct. Help Please.
> >
> > SELECT MagazineName, COUNT(Quantity) AS QTY, iif(RenewalFlag = flase, 0,
> > 1) AS REFL, iif(CancelDate is not null,0,1) as CLRFL
> > FROM Order2
> > GROUP BY MagazineName
> >
> > The overall goal is to
> > 1) count then number of records
> > 2) count how many are renewal's
> > 3) count how many are canceled
> >
> > example:
> > CHILD 50 5 1
> >
> > Scott Burke

Iif statement to prevent divide by zero?

Hi- I'm trying to create a calculated field that is the percentage difference
between two database fields. To prevent a divide by zero, I tried making it:
= Iif( Fields!dsPrice.Value <> 0, (Fields!eePrice.Value -
Fields!dsPrice.Value) / Fields!dsPrice.Value, 1)
This should provide the % diff, or in the case that dsPrice is 0, 1 (100%).
When I try to run the report, however, it comes back as a divide by zero for
fields where dsPrice = 0. Does reporting services evaluate both portions of
the Iif, then output one? How do I avoid this divide by zero error?
Thanks in advance!
Peter L.iif always evaluates both sides. try using the short circuit operator
'andalso' or 'orelse' in a function and add it to the code and call it from
the expression.|||If the correct zero value is 100%, you can just move the pieces around like
this:
= Iif(Fields!dsPrice.Value = 0, 1, Fields!eePrice.Value ) /
Iif(Fields!eePrice.Value = 0, 1, Fields!dsPrice.Value)
That way, the division doesn't happen at all until the values are replaced.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"plandry@.newsgroups.nospam"
<plandrynewsgroupsnospam@.discussions.microsoft.com> wrote in message
news:4B6AE1CA-72DB-4DAE-8E63-147AF61BDC21@.microsoft.com...
> Hi- I'm trying to create a calculated field that is the percentage
> difference
> between two database fields. To prevent a divide by zero, I tried making
> it:
> = Iif( Fields!dsPrice.Value <> 0, (Fields!eePrice.Value -
> Fields!dsPrice.Value) / Fields!dsPrice.Value, 1)
> This should provide the % diff, or in the case that dsPrice is 0, 1
> (100%).
> When I try to run the report, however, it comes back as a divide by zero
> for
> fields where dsPrice = 0. Does reporting services evaluate both portions
> of
> the Iif, then output one? How do I avoid this divide by zero error?
> Thanks in advance!
> Peter L.|||Whoops, I think that should have been more like this:
= Iif(Fields!dsPrice.Value = 0, 1, Fields!eePrice.Value ) /
Iif(Fields!dsPrice.Value = 0, 1, Fields!dsPrice.Value)
Anyway, you get the idea!! :-)
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:eGkBJLo8EHA.1264@.TK2MSFTNGP12.phx.gbl...
> If the correct zero value is 100%, you can just move the pieces around
> like this:
> = Iif(Fields!dsPrice.Value = 0, 1, Fields!eePrice.Value ) /
> Iif(Fields!eePrice.Value = 0, 1, Fields!dsPrice.Value)
> That way, the division doesn't happen at all until the values are
> replaced.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "plandry@.newsgroups.nospam"
> <plandrynewsgroupsnospam@.discussions.microsoft.com> wrote in message
> news:4B6AE1CA-72DB-4DAE-8E63-147AF61BDC21@.microsoft.com...
>> Hi- I'm trying to create a calculated field that is the percentage
>> difference
>> between two database fields. To prevent a divide by zero, I tried making
>> it:
>> = Iif( Fields!dsPrice.Value <> 0, (Fields!eePrice.Value -
>> Fields!dsPrice.Value) / Fields!dsPrice.Value, 1)
>> This should provide the % diff, or in the case that dsPrice is 0, 1
>> (100%).
>> When I try to run the report, however, it comes back as a divide by zero
>> for
>> fields where dsPrice = 0. Does reporting services evaluate both portions
>> of
>> the Iif, then output one? How do I avoid this divide by zero error?
>> Thanks in advance!
>> Peter L.
>|||That did the trick... Thanks a bunch!
I will file that away in the "ninja reporting tricks" :)
"Jeff A. Stucker" wrote:
> Whoops, I think that should have been more like this:
> = Iif(Fields!dsPrice.Value = 0, 1, Fields!eePrice.Value ) /
> Iif(Fields!dsPrice.Value = 0, 1, Fields!dsPrice.Value)
> Anyway, you get the idea!! :-)
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> news:eGkBJLo8EHA.1264@.TK2MSFTNGP12.phx.gbl...
> > If the correct zero value is 100%, you can just move the pieces around
> > like this:
> >
> > = Iif(Fields!dsPrice.Value = 0, 1, Fields!eePrice.Value ) /
> > Iif(Fields!eePrice.Value = 0, 1, Fields!dsPrice.Value)
> >
> > That way, the division doesn't happen at all until the values are
> > replaced.
> >
> > --
> > Cheers,
> >
> > '(' Jeff A. Stucker
> > \
> >
> > Business Intelligence
> > www.criadvantage.com
> > ---
> > "plandry@.newsgroups.nospam"
> > <plandrynewsgroupsnospam@.discussions.microsoft.com> wrote in message
> > news:4B6AE1CA-72DB-4DAE-8E63-147AF61BDC21@.microsoft.com...
> >> Hi- I'm trying to create a calculated field that is the percentage
> >> difference
> >> between two database fields. To prevent a divide by zero, I tried making
> >> it:
> >> = Iif( Fields!dsPrice.Value <> 0, (Fields!eePrice.Value -
> >> Fields!dsPrice.Value) / Fields!dsPrice.Value, 1)
> >> This should provide the % diff, or in the case that dsPrice is 0, 1
> >> (100%).
> >> When I try to run the report, however, it comes back as a divide by zero
> >> for
> >> fields where dsPrice = 0. Does reporting services evaluate both portions
> >> of
> >> the Iif, then output one? How do I avoid this divide by zero error?
> >>
> >> Thanks in advance!
> >> Peter L.
> >
> >
>
>

IIF and empty fields

Hi, apologies if this is a silly mistake on my part, but... I'm having trouble with working out if a field is empty

I've been trying to use "SELECT newfield = IIF(IsEmpty(table.field), 0, 1) ... " to return a bit value of whether the string field has something in it..

The syntax check says that IsEmpty is not a valid function - but I dont understand how it can be... am I missing something here?

I also tried using IIF(table.field = '', 0, 1) and that returns "invalid syntax near ="

Please help because its really confusing me, ThanksHi,

IsEmpty and IIF are Analysis Server functions, so you can't use it in reqular T-SQL. I don't seen any indication in your message that your using OLAP, so I'll assume you're using regular T-SQL with relational data.

What do you consider to be empty? A null value? Or what is commonly considered to be no data for a particular datatype (empty string for strings, zero for numerics, etc.)?

If nulls, consider using the ISNULL function. If the value is null it returns the value of your choice, otherwise the non-null value. The NULLIF function can help in some cases.

But if empty is an empty string or zero, etc., you can compare those values. Or use the CASE block, which lets you return different values based on various conditions.

I suspect that you'll find what you need in these T-SQL features.

Don|||ok, thanks a lot. explains why its not working anyway :)