Numeric value with comma separator...

Hi,
In select statement how will i get the numeric values with comma separated
format .
Is there any sql function available.
Regards,
M. SubbaiahSomeone was asleep in their Database 101 class! What is the **most
fundamental** concept in tiered architecture? DISPLAY IS ALWAYS DONE
IN THE CLIENT SIDE!!
Can you please stop programming until you have read at least one book?|||Hi
As Celko pointed out yet you will be better of douing such reports on the
client side
However T-SQL has an ability to do that .
CREATE TABLE #Test (col INT NOT NULL)
INSERT INTO #Test VALUES (1)
INSERT INTO #Test VALUES (10)
INSERT INTO #Test VALUES (20)
DECLARE @.st VARCHAR(20)
SET @.st=''
SELECT @.st=@.st+COALESCE(CAST(col AS VARCHAR(5)),'0')+','
FROM #test
SELECT LEFT(@.st,LEN(@.st)-1)
"Subbaiah" <subbaiah@.cspl.com> wrote in message
news:eqflLkeMGHA.3272@.tk2msftngp13.phx.gbl...
> Hi,
> In select statement how will i get the numeric values with comma separated
> format .
> Is there any sql function available.
> Regards,
> M. Subbaiah
>|||Hi Uri Dimant,
Thanks for your information.
I learned new sql function COALESCE( ) and the usage.
My posted query was ,
Suppose in sql table the value is 1234567.45
My out put wiill be 1,234,567.45
Can you please answer the above one.
Regards
M. Subbaiah
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OIp4qvfMGHA.3556@.TK2MSFTNGP10.phx.gbl...
> Hi
> As Celko pointed out yet you will be better of douing such reports on the
> client side
> However T-SQL has an ability to do that .
> CREATE TABLE #Test (col INT NOT NULL)
> INSERT INTO #Test VALUES (1)
> INSERT INTO #Test VALUES (10)
> INSERT INTO #Test VALUES (20)
>
> DECLARE @.st VARCHAR(20)
> SET @.st=''
> SELECT @.st=@.st+COALESCE(CAST(col AS VARCHAR(5)),'0')+','
> FROM #test
> SELECT LEFT(@.st,LEN(@.st)-1)
>
>
>
> "Subbaiah" <subbaiah@.cspl.com> wrote in message
> news:eqflLkeMGHA.3272@.tk2msftngp13.phx.gbl...
>|||NO!
Display is ALWAYS done where it is most efficient to do it.
You DO NOT pull back 1 MILLION rows into your middle tier or client tier
only to grab page 2 of 10!
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1139978481.527767.63680@.z14g2000cwz.googlegroups.com...
> Someone was asleep in their Database 101 class! What is the **most
> fundamental** concept in tiered architecture? DISPLAY IS ALWAYS DONE
> IN THE CLIENT SIDE!!
> Can you please stop programming until you have read at least one book?
>|||If you want to cheat, use the money data type and convert:
declare @.someFloat money
set @.someFloat = 1234567.45
select convert(varchar(15), @.someFloat, 1)
Gives:
1,234,567.45
Cheers,
Stefan
http://www.fotia.co.uk
> Hi Uri Dimant,
> Thanks for your information.
> I learned new sql function COALESCE( ) and the usage.
> My posted query was ,
> Suppose in sql table the value is 1234567.45
> My out put wiill be 1,234,567.45
> Can you please answer the above one.
> Regards
> M. Subbaiah
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OIp4qvfMGHA.3556@.TK2MSFTNGP10.phx.gbl...
>|||Hi
declare @.someDEC DECIMAL(18,2)
set @.someDEC = 1234567.45
SELECT CONVERT(VARCHAR,CAST(@.someDEC AS MONEY),1)
"Subbaiah" <subbaiah@.cspl.com> wrote in message
news:uqE9mUgMGHA.2668@.tk2msftngp13.phx.gbl...
> Hi Uri Dimant,
> Thanks for your information.
> I learned new sql function COALESCE( ) and the usage.
> My posted query was ,
> Suppose in sql table the value is 1234567.45
> My out put wiill be 1,234,567.45
> Can you please answer the above one.
> Regards
> M. Subbaiah
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OIp4qvfMGHA.3556@.TK2MSFTNGP10.phx.gbl...
>sql

Numeric data column and insert from ASP.NET

I am trying to insert some values into a table where the column is of the data type "numeric". The insert works fine.Update does not work.

Update BUT_BREAKDOWN_PCT SET BDP_EFFORT_BREAKDOWN_PCT=0.15 WHERE BDP_BREAKDOWN_ID =1 AND BDP_PHASE_ID = 3 AND BDP_START_EFF_DT = '12/31/2004'

BDP_EFFORT_BREAKDOWN_PCT is a numeric column with a size 5 (4,3)

When I do the updatedirectly from QA, it works fine.

I was googling it and read a KB article saying it's a problem with Service Pack of SQL Server 2000. If it is, then the query should not work even from QA...isn't it?

Anyone had this problem before? Please help.

In SQL Server 2000 you can change the data type to Decimal and your problem will go away but in SQL Server 7.0 Numeric was more stable than Decimal from my experience. Hope this helps.|||If it works OK in Query Analyzer (works fine for me, too), and it isnot working in your application, then your application is not sendingthe exact same information.

What results are you seeing? An error message? Unexpected data in theBDP_EFFORT_BREAKDOWN_PCTcolumn? No update at all?

I am suspecting that you are not seeing any update at all, which meansthat your WHERE condition is not being met. And since you seem tohave a datetime column in your WHERE condition, I would guess furtherthat that is where your problem lies. (Datetime columnstrip up a lot of people.)

If you are having difficulty troubleshooting this further, let us see your code and maybe we can point out the problem.

Numbers show as exponential

Hello,
I am using the code below to sum values for the web. Instead of getting
11911712.0, I am getting 1.19117e+007. Is there a way to prevent numbers
from being show in exponential?
IsNull(Convert(varchar(30),(SUM(CASE WHEN Treg.Region = 'AP' Then T.Value
ELSE NULL END))), '&nbsp;') AS strValue
Thanks in advance,
Steven
I'm guessing that T.Value is a floating point datatype?
If so, then the problem is deeper then a display issue. Floating point datatypes only store so many signifigant digits of precision - the other digits are lost. They are turned into 0's.
|||One thing you might try is to convert the number to a decimal before
converting it to a varchar...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Steven K" <skaper@.troop.com> wrote in message
news:OIk2gv2PEHA.3660@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I am using the code below to sum values for the web. Instead of getting
> 11911712.0, I am getting 1.19117e+007. Is there a way to prevent numbers
> from being show in exponential?
>
> IsNull(Convert(varchar(30),(SUM(CASE WHEN Treg.Region = 'AP' Then T.Value
> ELSE NULL END))), '&nbsp;') AS strValue
> --
> Thanks in advance,
> Steven
>
sql

Numbers show as exponential

Hello,
I am using the code below to sum values for the web. Instead of getting
11911712.0, I am getting 1.19117e+007. Is there a way to prevent numbers
from being show in exponential?
IsNull(Convert(varchar(30),(SUM(CASE WHEN Treg.Region = 'AP' Then T.Value
ELSE NULL END))), ' ') AS strValue
--
Thanks in advance,
StevenI'm guessing that T.Value is a floating point datatype?
If so, then the problem is deeper then a display issue. Floating point datatypes only store so many signifigant digits of precision - the other digits are lost. They are turned into 0's.|||One thing you might try is to convert the number to a decimal before
converting it to a varchar...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Steven K" <skaper@.troop.com> wrote in message
news:OIk2gv2PEHA.3660@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I am using the code below to sum values for the web. Instead of getting
> 11911712.0, I am getting 1.19117e+007. Is there a way to prevent numbers
> from being show in exponential?
>
> IsNull(Convert(varchar(30),(SUM(CASE WHEN Treg.Region = 'AP' Then T.Value
> ELSE NULL END))), ' ') AS strValue
> --
> Thanks in advance,
> Steven
>

Numbers show as exponential

Hello,
I am using the code below to sum values for the web. Instead of getting
11911712.0, I am getting 1.19117e+007. Is there a way to prevent numbers
from being show in exponential?
IsNull(Convert(varchar(30),(SUM(CASE WHEN Treg.Region = 'AP' Then T.Value
ELSE NULL END))), ' ') AS strValue
Thanks in advance,
StevenI'm guessing that T.Value is a floating point datatype'
If so, then the problem is deeper then a display issue. Floating point data
types only store so many signifigant digits of precision - the other digits
are lost. They are turned into 0's.|||One thing you might try is to convert the number to a decimal before
converting it to a varchar...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Steven K" <skaper@.troop.com> wrote in message
news:OIk2gv2PEHA.3660@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I am using the code below to sum values for the web. Instead of getting
> 11911712.0, I am getting 1.19117e+007. Is there a way to prevent numbers
> from being show in exponential?
>
> IsNull(Convert(varchar(30),(SUM(CASE WHEN Treg.Region = 'AP' Then T.Value
> ELSE NULL END))), ' ') AS strValue
> --
> Thanks in advance,
> Steven
>

numbering rows of unordered table?

create table t (colA int, colB char(1), colC int)
insert into t(colB, colC) Values('C', 3)
insert into t(colB, colC) Values('C', 1)
insert into t(colB, colC) Values('C', 4)
insert into t(colB, colC) Values('C', 2)
insert into t(colB, colC) Values('A', 4)
insert into t(colB, colC) Values('A', 1)
insert into t(colB, colC) Values('A', 3)
insert into t(colB, colC) Values('A', 2)
insert into t(colB, colC) Values('B', 2)
insert into t(colB, colC) Values('B', 3)
insert into t(colB, colC) Values('B', 4)
insert into t(colB, colC) Values('B', 1)
so colA of table t contains all nulls right now.
Select * from t
NULL C 3
NULL C 1
NULL C 4
NULL C 2
NULL A 4
NULL A 1
NULL A 3
NULL A 2
NULL B 2
NULL B 3
NULL B 4
NULL B 1
I need to number each row of table t so it looks like this
Select * from t Order By colB, colC
1 A 1
2 A 2
3 A 3
4 A 4
5 B 1
6 B 2
7 B 3
8 B 4
9 C 1
10 C 2
11 C 3
12 C 4
In my actual app Table t already exists with colA = null
and colB and colC as above and thousands of rows. Thus,
to say
Update t set colA = 1 Where colB = 'A' And colC = '1'
Update t set colA = 2 Where colB = 'A' And colC = '2'
Update t set colA = 3 Where colB = 'A' And colC = '3'
...
Update t set colA = 9 Where colB = 'C' And colC = '1'
Update t set colA = 10 Where colB = 'C' And colC = '2'
...
is clearly is not the way to go. I humbly request if
someone could show me how to number the rows with T-sql
the correct way. My problem is that I don't know how to
increment the seed number and how to apply it to the
desired order. I am thinking a while loop, but what flag
to use to stop the loop? How to order the rows?
Thanks,
RonTry,
select
count(*) as colA,
a.colB,
a.colC
from
t as a
inner join
t as b
on a.colB + ltrim(a.colC) >= b.colB + ltrim(b.colC)
group by
a.colB,
a.colC
order by
1
go
How to dynamically number rows in a SELECT Statement
http://support.microsoft.com/defaul...kb;en-us;186133
AMB
"Ron" wrote:

> create table t (colA int, colB char(1), colC int)
> insert into t(colB, colC) Values('C', 3)
> insert into t(colB, colC) Values('C', 1)
> insert into t(colB, colC) Values('C', 4)
> insert into t(colB, colC) Values('C', 2)
> insert into t(colB, colC) Values('A', 4)
> insert into t(colB, colC) Values('A', 1)
> insert into t(colB, colC) Values('A', 3)
> insert into t(colB, colC) Values('A', 2)
> insert into t(colB, colC) Values('B', 2)
> insert into t(colB, colC) Values('B', 3)
> insert into t(colB, colC) Values('B', 4)
> insert into t(colB, colC) Values('B', 1)
> so colA of table t contains all nulls right now.
> Select * from t
> NULL C 3
> NULL C 1
> NULL C 4
> NULL C 2
> NULL A 4
> NULL A 1
> NULL A 3
> NULL A 2
> NULL B 2
> NULL B 3
> NULL B 4
> NULL B 1
> I need to number each row of table t so it looks like this
> Select * from t Order By colB, colC
> 1 A 1
> 2 A 2
> 3 A 3
> 4 A 4
> 5 B 1
> 6 B 2
> 7 B 3
> 8 B 4
> 9 C 1
> 10 C 2
> 11 C 3
> 12 C 4
> In my actual app Table t already exists with colA = null
> and colB and colC as above and thousands of rows. Thus,
> to say
> Update t set colA = 1 Where colB = 'A' And colC = '1'
> Update t set colA = 2 Where colB = 'A' And colC = '2'
> Update t set colA = 3 Where colB = 'A' And colC = '3'
> ...
> Update t set colA = 9 Where colB = 'C' And colC = '1'
> Update t set colA = 10 Where colB = 'C' And colC = '2'
> ...
> is clearly is not the way to go. I humbly request if
> someone could show me how to number the rows with T-sql
> the correct way. My problem is that I don't know how to
> increment the seed number and how to apply it to the
> desired order. I am thinking a while loop, but what flag
> to use to stop the loop? How to order the rows?
> Thanks,
> Ron
>|||here is the update.
update
t
set
colA = (select count(*) from t as a where t.colB + ltrim(t.colC) >= a.colB
+ ltrim(a.colC))
go
AMB
"Alejandro Mesa" wrote:
> Try,
> select
> count(*) as colA,
> a.colB,
> a.colC
> from
> t as a
> inner join
> t as b
> on a.colB + ltrim(a.colC) >= b.colB + ltrim(b.colC)
> group by
> a.colB,
> a.colC
> order by
> 1
> go
> How to dynamically number rows in a SELECT Statement
> http://support.microsoft.com/defaul...kb;en-us;186133
>
> AMB
>
> "Ron" wrote:
>|||Thanks very much for your reply. I guess the trick was in
the self join. I took this one step further and performed
an update (as I need to hardcode these numbers):
update t set t.colA = t2.colA
From t Join
(select count(*) as colA, a.colB, a.colC from t as a inner
join t as b on a.colB + ltrim(a.colC) >= b.colB + ltrim
(b.colC) group by a.colB, a.colC) t2
on t.colB = t2.colB and t.colC = t2.colC
Question: someone advised me that using joins in an
update statement is not correct. But this Update
statement accomplished what I needed. Any comments
appreciated.
Thanks again for your help.
Ron

>--Original Message--
>Try,
>select
> count(*) as colA,
> a.colB,
> a.colC
>from
> t as a
> inner join
> t as b
> on a.colB + ltrim(a.colC) >= b.colB + ltrim(b.colC)
>group by
> a.colB,
> a.colC
>order by
> 1
>go
>How to dynamically number rows in a SELECT Statement
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;186133
>
>AMB|||Read my last post.
AMB
"Ron" wrote:

> Thanks very much for your reply. I guess the trick was in
> the self join. I took this one step further and performed
> an update (as I need to hardcode these numbers):
> update t set t.colA = t2.colA
> From t Join
> (select count(*) as colA, a.colB, a.colC from t as a inner
> join t as b on a.colB + ltrim(a.colC) >= b.colB + ltrim
> (b.colC) group by a.colB, a.colC) t2
> on t.colB = t2.colB and t.colC = t2.colC
> Question: someone advised me that using joins in an
> update statement is not correct. But this Update
> statement accomplished what I needed. Any comments
> appreciated.
> Thanks again for your help.
> Ron
>
> us;186133
>|||Thanks again for this correction.

>--Original Message--
>here is the update.
>update
> t
>set
> colA = (select count(*) from t as a where t.colB +
ltrim(t.colC) >= a.colB
>+ ltrim(a.colC))
>go
>
>AMB
>"Alejandro Mesa" wrote:
>
us;186133
this
null
Thus,
sql
to
flag
>.
>

numbering rows

How do i number the rows in the table? My data does not have any unique
values if that helpsThere is a RowNumber(<scope>) function, where <scope> is a string that
identifies a data region, dataset or grouping if you need that context. For
just a simple running row total, use RowNumber(Nothing). You can also use
it for visual effects, and the most frequent example of this is to create
"green bar" reports by setting the background colour of a table row with the
expression:
=iif(RowNumber(Nothing) Mod 2, "Green", "White")
Cheers, Mark
"Marvin" <Marvin@.discussions.microsoft.com> wrote in message
news:25E67CAB-76C0-450A-B4DE-87788DD19E80@.microsoft.com...
> How do i number the rows in the table? My data does not have any unique
> values if that helps

Numbering groups of rows?

create table t (colA int, colB char(1), colC int)
insert into t(colB) Values('C')
insert into t(colB) Values('C')
insert into t(colB) Values('C')
insert into t(colB) Values('C')
insert into t(colB) Values('A')
insert into t(colB) Values('A')
insert into t(colB) Values('A')
insert into t(colB) Values('A')
insert into t(colB) Values('B')
insert into t(colB) Values('B')
insert into t(colB) Values('B')
insert into t(colB) Values('B')
update t set colc =
(select count(*) from t as a where t.colb >= a.colb)
yields
NULL C 12
NULL C 12
NULL C 12
NULL C 12
NULL A 4
NULL A 4
NULL A 4
NULL A 4
NULL B 8
NULL B 8
NULL B 8
NULL B 8
how can I make it yield
NULL C 3
NULL C 3
NULL C 3
NULL C 3
NULL A 1
NULL A 1
NULL A 1
NULL A 1
NULL B 2
NULL B 2
NULL B 2
NULL B 2
Thanks,
Ronupdate t set colc =
(select count(DISTINCT a.colb) from t as a where t.colb >= a.colb)
Jacco Schalkwijk
SQL Server MVP
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:01b401c50fb9$5a806ee0$a501280a@.phx.gbl...
> create table t (colA int, colB char(1), colC int)
> insert into t(colB) Values('C')
> insert into t(colB) Values('C')
> insert into t(colB) Values('C')
> insert into t(colB) Values('C')
> insert into t(colB) Values('A')
> insert into t(colB) Values('A')
> insert into t(colB) Values('A')
> insert into t(colB) Values('A')
> insert into t(colB) Values('B')
> insert into t(colB) Values('B')
> insert into t(colB) Values('B')
> insert into t(colB) Values('B')
> update t set colc =
> (select count(*) from t as a where t.colb >= a.colb)
> yields
> NULL C 12
> NULL C 12
> NULL C 12
> NULL C 12
> NULL A 4
> NULL A 4
> NULL A 4
> NULL A 4
> NULL B 8
> NULL B 8
> NULL B 8
> NULL B 8
> how can I make it yield
> NULL C 3
> NULL C 3
> NULL C 3
> NULL C 3
> NULL A 1
> NULL A 1
> NULL A 1
> NULL A 1
> NULL B 2
> NULL B 2
> NULL B 2
> NULL B 2
> Thanks,
> Ron
>|||On Thu, 10 Feb 2005 13:42:01 -0800, Ron wrote:

>update t set colc =
>(select count(*) from t as a where t.colb >= a.colb)
>yields
(snip)
>how can I make it yield
(snip)
Hi Ron,
Better not to store this information at all - you'll find yourself
constantly fighting to keep the rankingf column current after each
modification to the underlying data. It's better to drop the column from
the table and create a view to calculate it.
If you MUST do it in an update, try
update t set colc =
(select count(distinct a.colb) from t as a where t.colb >= a.colb)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks very much. That worked well. But I also tried
using this in a select statement as follows for ranking
the values A, B, C
select a.colB, count(distinct a.colb) as colC
from t as a inner join t as b
on a.colB >= b.colB
group by a.colB
yielded this ranking
A 1
B 1
C 1
without the Distinct keyword I get this ranking
A 16
B 32
C 48
But I would like to get a ranking as follows
A 1
B 2
C 3
I ask this because I am trying to understand the sql logic
to achieve these results. Hopefully, after I do enough of
these kinds of queries I will get the idea how they work.
May I ask how I could achieve the ranking from result3?
Thanks again,
Ron

>--Original Message--
> update t set colc =
>(select count(DISTINCT a.colb) from t as a where t.colb
>= a.colb)
>
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Ron" <anonymous@.discussions.microsoft.com> wrote in
message
>news:01b401c50fb9$5a806ee0$a501280a@.phx.gbl...
>
>.
>|||Ron,
Try count(distinct b.colb) instead of count(distinct a.colb). I suspect
that's what you had in mind.
Steve Kass
Drew University
Ron wrote:
>Thanks very much. That worked well. But I also tried
>using this in a select statement as follows for ranking
>the values A, B, C
>select a.colB, count(distinct a.colb) as colC
>from t as a inner join t as b
>on a.colB >= b.colB
>group by a.colB
>yielded this ranking
>A 1
>B 1
>C 1
>without the Distinct keyword I get this ranking
>A 16
>B 32
>C 48
>But I would like to get a ranking as follows
>A 1
>B 2
>C 3
>I ask this because I am trying to understand the sql logic
>to achieve these results. Hopefully, after I do enough of
>these kinds of queries I will get the idea how they work.
>May I ask how I could achieve the ranking from result3?
>Thanks again,
>Ron
>
>
>message
>|||On Thu, 10 Feb 2005 14:21:58 -0800, Ron wrote:

>Thanks very much. That worked well. But I also tried
>using this in a select statement as follows for ranking
>the values A, B, C
>select a.colB, count(distinct a.colb) as colC
>from t as a inner join t as b
>on a.colB >= b.colB
>group by a.colB
Hi Ron,
Try this one instead:
select a.colB, count(distinct b.colb) as colC
from t as a inner join t as b
on a.colB >= b.colB
group by a.colB
(Note: only one letter weas changed!!)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks very much. I am also trying to figure out how to
rank A, B, C
select a.colB, count(distinct a.colb) as colC
from t as a inner join t as b
on a.colB >= b.colB
group by a.colB
yielded this ranking
A 1
B 1
C 1
without the Distinct keyword I get this ranking
A 16
B 32
C 48
But I would like to get a ranking as follows
A 1
B 2
C 3
May I ask how I could achieve the ranking from result3?
This way, as you say, I don't really store the ranks, just
retrieve them dynamically.
Thanks again,
Ron

>--Original Message--
>On Thu, 10 Feb 2005 13:42:01 -0800, Ron wrote:
>
>(snip)
>(snip)
>Hi Ron,
>Better not to store this information at all - you'll find
yourself
>constantly fighting to keep the rankingf column current
after each
>modification to the underlying data. It's better to drop
the column from
>the table and create a view to calculate it.
>If you MUST do it in an update, try
>update t set colc =
>(select count(distinct a.colb) from t as a where t.colb
>= a.colb)
>Best, Hugo
>--
>(Remove _NO_ and _SPAM_ to get my e-mail address)
>.
>|||Thanks all. I am sort of starting to get the idea. But I
wonder if I could belabor this thing one more notch:
Instead of using distinct is it possible to plant a group
by query in there? Pseudocode here:
select a.colB, count(select a.colb from a group by a.colb)
as colC from t as a inner join t as b
on a.colB >= b.colB group by a.colB
Again, I just ask because I don't really know all the
rules for t-sql, let alone the tricks. I am guessing that
t-sql does not allow Selects inside of Count(..)
Thanks again,
Ron

>--Original Message--
>Thanks very much. That worked well. But I also tried
>using this in a select statement as follows for ranking
>the values A, B, C
>select a.colB, count(distinct a.colb) as colC
>from t as a inner join t as b
>on a.colB >= b.colB
>group by a.colB
>yielded this ranking
>A 1
>B 1
>C 1
>without the Distinct keyword I get this ranking
>A 16
>B 32
>C 48
>But I would like to get a ranking as follows
>A 1
>B 2
>C 3
>I ask this because I am trying to understand the sql
logic
>to achieve these results. Hopefully, after I do enough
of
>these kinds of queries I will get the idea how they
work.
>May I ask how I could achieve the ranking from result3?
>Thanks again,
>Ron
>
>message
>.
>|||On Thu, 10 Feb 2005 14:45:11 -0800, Ron wrote:

>Thanks all. I am sort of starting to get the idea. But I
>wonder if I could belabor this thing one more notch:
>Instead of using distinct is it possible to plant a group
>by query in there? Pseudocode here:
>select a.colB, count(select a.colb from a group by a.colb)
>as colC from t as a inner join t as b
>on a.colB >= b.colB group by a.colB
Hi Ron,
This code won't work. I'm sure there is some way to do this with a group
by in the subquery, but it's not trivial and it'll be more complex than
the version with DISTINCT that I suggested.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> Instead of using distinct is it possible to plant a group by query
in there? <<
This will use a GROUP BY and get you a bit more information in the VIEW
aggregate functions. I am not sure that there is any advantage. .
CREATE TABLE Foobar (letter CHAR(1) NOT NULL);
INSERT INTO Foobar (letter) VALUES('C');
INSERT INTO Foobar (letter) VALUES('C');
INSERT INTO Foobar (letter) VALUES('C');
INSERT INTO Foobar (letter) VALUES('C');
INSERT INTO Foobar (letter) VALUES('A');
INSERT INTO Foobar (letter) VALUES('A');
INSERT INTO Foobar (letter) VALUES('A');
INSERT INTO Foobar (letter) VALUES('A');
INSERT INTO Foobar (letter) VALUES('B');
INSERT INTO Foobar (letter) VALUES('B');
INSERT INTO Foobar (letter) VALUES('B');
INSERT INTO Foobar (letter) VALUES('B');
CREATE VIEW FoobarReport (letter, occurs, place)
AS
SELECT F1.letter, COUNT(*),
(SELECT COUNT (DISTINCT F2.letter)
FROM Foobar AS F2
WHERE F2.letter <= F1.letter)
FROM Foobar AS F1
GROUP BY F1.letter;

number of locks

Hi!
I did not change parameter locks after installation, so it
has following values:
name minimum maximum config_value run_value
locks 5000 2147483647 0 0
So far did not experience any problem, Do I have to change
this in order to prevent problem with locks? Which number
of locks is available at this moment?You're probably fine with the default numbers. Most people never change
them. Is there a particular problem you're worried about or think you may be
having?
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"mirce" <anonymous@.discussions.microsoft.com> wrote in message
news:2f1001c4a231$27bf6b50$a601280a@.phx.gbl...
> Hi!
> I did not change parameter locks after installation, so it
> has following values:
> name minimum maximum config_value run_value
> locks 5000 2147483647 0 0
> So far did not experience any problem, Do I have to change
> this in order to prevent problem with locks? Which number
> of locks is available at this moment?|||No I did not have any problem. What is current value of
available locks?
>--Original Message--
>You're probably fine with the default numbers. Most
people never change
>them. Is there a particular problem you're worried about
or think you may be
>having?
>--
>Brian Moran
>Principal Mentor
>Solid Quality Learning
>SQL Server MVP
>http://www.solidqualitylearning.com
>
>"mirce" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2f1001c4a231$27bf6b50$a601280a@.phx.gbl...
>> Hi!
>> I did not change parameter locks after installation, so
it
>> has following values:
>> name minimum maximum config_value run_value
>> locks 5000 2147483647 0 0
>> So far did not experience any problem, Do I have to
change
>> this in order to prevent problem with locks? Which
number
>> of locks is available at this moment?
>
>.
>

Number Formatting in SQLSERVER

say, i have a column in the database that has number values. I want to display these number with comma separators (Eg: if the column values is 1654, then i want to display it as 1,654).
How can i achieve this in my sQL queryFormatting is done on the client. The simplest answer is to use a client such as MS-Access, MS-Excel, Crystal Reports, etc that does this formatting for you.

You can choose to do the formatting on the server, but this is a really poor choice.

-PatP|||it's a really poor choice, but it's also a fun challenge for a monday morning

w00h00!!
create table teststuff
( id tinyint not null primary key identity
, foo integer
);
insert into teststuff(foo) values(-1)
insert into teststuff(foo) values(0)
insert into teststuff(foo) values(937)
insert into teststuff(foo) values(1000)
insert into teststuff(foo) values(345678)
insert into teststuff(foo) values(1234567890)
insert into teststuff(foo) values(-111111111)
insert into teststuff(foo) values(3456789)
insert into teststuff(foo) values(-1000)
insert into teststuff(foo) values(-11444)
insert into teststuff(foo) values(-1555666)
insert into teststuff(foo) values(-1333555777)

select id, foo
, case when foo > -999 and foo < 999
then right(space(14)
+cast(foo as varchar)
,14)
when foo > -999999 and foo < 999999
then right(space(14)
+reverse(
stuff(reverse(foo),4,0,','))
,14)
when foo > -999999999 and foo < 999999999
then right(space(14)
+reverse(
stuff(
stuff(reverse(foo),7,0,',')
,4,0,','))
,14)
else right(space(14)
+reverse(
stuff(
stuff(
stuff(reverse(foo),10,0,',')
,7,0,',')
,4,0,','))
,14)
end as formatted
from teststuff

1 -1 -1
2 0 0
3 937 937
4 1000 1,000
5 345678 345,678
6 1234567890 1,234,567,890
7 -111111111 -111,111,111
8 3456789 3,456,789
9 -1000 -1,000
10 -11444 -11,444
11 -1555666 -1,555,666
12 -1333555777 -1,333,555,777

Number formatting in a SQL select statement

Hi

I'm trying to convert and format integer values in a SQL Server select statement to a string
representation of the number formated with ,'s (1000000 becomes 1,000,000 for example).

I've been looking at CAST and CONVERT and think the answers there somewhere. I just don't
seem to be able to work it out.

Anyone out there able to help me please?

Thanks,
Keith.

My suggestion would be to do this on the front end rather than the back end.
In any case, I think you will need to CAST your column as a money data type, and then CONVERT it using style 1, like this:
SELECT
CONVERT(varchar(20),CAST(myColumn AS money) ,1)
This will unfortuately also return the 2 digits after the decimal point. So my next step would be to strip them out.
This seems very messy, though. Hopefully someone else will have a better idea.

|||Yeah, that is messy.
<soapbox>
The first question I have is why isn't this being done in yourpresentation layer? SQL's strong suit is selecting data, not formattingit. Your ASP.NET environment already has tools that make this mucheasier than anything that we can come up with in SQL.
</soapbox>
Even messier would be this code sample, which is a function and/orstored procedure that accomplish what you are looking for. I've neverused it, but it looks right to me.
Jason
Update: Forgot to link - http://www.issociate.de/board/post/176502/How_do_I_format_an_integer.html
|||

Definitely a front-end issue. I've had to use SQL to format results when using SQLMail and it's a nightmare. Possible using combinations of cast, convert, charindex, substring, etc., but a nightmare. Use the front-end.

|||

Try this url it is using Strings and Formatting in the Framework Class library to do custom formatting. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomnumericformatstringsoutputexample.asp

Kind regards,

Gift Peddie

Nulls in columns additions when 1 or more column values is blank

I am running into an issue when adding data from multiple columns into
one alias:

P.ADDR1 + ' - ' + P.CITY + ',' + ' ' + P.STATE AS LOCATION

If one of the 3 values is blank, the value LOCATION becomes NULL. How
can I inlcude any of the 3 values without LOCATION becoming NULL?

Example, if ADDR1 and CITY have values but STATE is blank, I get a
NULL statement for LOCATION. I still want it to show ADDR1 and CITY
even if STATE is blank.

ThanksISNULL(P.CITY,'')
Techhead wrote:

Quote:

Originally Posted by

I am running into an issue when adding data from multiple columns into
one alias:
>
P.ADDR1 + ' - ' + P.CITY + ',' + ' ' + P.STATE AS LOCATION
>
If one of the 3 values is blank, the value LOCATION becomes NULL. How
can I inlcude any of the 3 values without LOCATION becoming NULL?
>
Example, if ADDR1 and CITY have values but STATE is blank, I get a
NULL statement for LOCATION. I still want it to show ADDR1 and CITY
even if STATE is blank.
>
Thanks
>

|||You can use COALESCE, something like this will do it:

COALESCE(P.ADDR1, '') + ' - ' + COALESCE(P.CITY, '') + ', ' +
COALESCE(P.STATE, '') AS LOCATION

Also, you can play with formatting variations based on what you want to get
when one of the columns is NULL, like this:

COALESCE(P.ADDR1, '') + COALESCE(' - ' + P.CITY, '') + COALESCE(', ' +
P.STATE, '') AS LOCATION

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||On Jun 4, 3:29 pm, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:

Quote:

Originally Posted by

You can use COALESCE, something like this will do it:
>
COALESCE(P.ADDR1, '') + ' - ' + COALESCE(P.CITY, '') + ', ' +
COALESCE(P.STATE, '') AS LOCATION
>
Also, you can play with formatting variations based on what you want to get
when one of the columns is NULL, like this:
>
COALESCE(P.ADDR1, '') + COALESCE(' - ' + P.CITY, '') + COALESCE(', ' +
P.STATE, '') AS LOCATION
>
HTH,
>
Plamen Ratchevhttp://www.SQLStudio.com

Somebody at work told me to use this:

SELECT CASE WHEN P.STATE IS NULL THEN '' ELSE P.STATE END

It seems to work. Is this similar as to what is described above?|||Techhead (jorgenson.b@.gmail.com) writes:

Quote:

Originally Posted by

Somebody at work told me to use this:
>
SELECT CASE WHEN P.STATE IS NULL THEN '' ELSE P.STATE END
>
It seems to work. Is this similar as to what is described above?

Yes, coalesce is a shortcut for the above. The nice thing with coalesce is
that it accept a list of values, and will return the first value that
is non-NULL.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Nulls -conditional display of value in Visual Studio

I am trying to display one of 2 values based on an input parameter. I have
created a field which is supposed to evaluate the parameter and display the
appropriate field.
My code is as follows: =iif( Parameters!LastWeek.Value = "Y",
Fields!Measures_M30N_MTD_Last_Week_of_Month.Value,
Fields!Measures_M9N_MTD.Value)
When I preview my report and select "Y" as the parameter, my field displays
nothing. I have checked the data and know there is a value in the field
represented by the "true" portion of my statement.
If I change the staement to this:=iif( Parameters!LastWeek.Value = "Y",
Fields!Measures_M30N_MTD_Last_Week_of_Month.Value, 0)
I actually get the value I am expecting.
My conclusion is the data in the "false" portion of the statement is
actually null or non-existant. I think this is causing my statement to
malfunction. How do I account for this and make my statement work properly?
There will eventually be data in the field represented in the "false"
portion of the statement. The data will reside in either/or both depending
on the time of month.
Thank you... PB> My conclusion is the data in the "false" portion of the statement is
> actually null or non-existant. I think this is causing my statement to
> malfunction.
Assuming this conclusion is correct, you could use a nested IIF() test using
the ISNOTHING() function, like this:
=iif( Parameters!LastWeek.Value = "Y",
Fields!Measures_M30N_MTD_Last_Week_of_Month.Value,
iif(ISNOTHING( Fields!Measures_M9N_MTD.Value), 0,
Fields!Measures_M9N_MTD.Value) )
... however (still assuming your conclusion about why it's not working is
correct) it might be safer to assume that *either* value could be missing,
so you could do the test in both places:
=iif( Parameters!LastWeek.Value = "Y",
iif(ISNOTHING(Fields!Measures_M30N_MTD_Last_Week_of_Month.Value),0,
Fields!Measures_M30N_MTD_Last_Week_of_Month.Value),
iif(ISNOTHING(Fields!Measures_M9N_MTD.Value), 0,
Fields!Measures_M9N_MTD.Value) )
... double-however <g> when you look at it this way, it might be simpler to
do the same work in your data query. IOW use ISNULL() or COALESCE() in your
SELECT statement, to provide a default value for values that might be
missing, before you get to the report level.
Regards,
>L<
"ppbedz" <ppbedz@.discussions.microsoft.com> wrote in message
news:7F96FEB5-806D-4761-8077-4C3A7B85973D@.microsoft.com...
>I am trying to display one of 2 values based on an input parameter. I have
> created a field which is supposed to evaluate the parameter and display
> the
> appropriate field.
> My code is as follows: =iif( Parameters!LastWeek.Value = "Y",
> Fields!Measures_M30N_MTD_Last_Week_of_Month.Value,
> Fields!Measures_M9N_MTD.Value)
> When I preview my report and select "Y" as the parameter, my field
> displays
> nothing. I have checked the data and know there is a value in the field
> represented by the "true" portion of my statement.
> If I change the staement to this:=iif( Parameters!LastWeek.Value = "Y",
> Fields!Measures_M30N_MTD_Last_Week_of_Month.Value, 0)
> I actually get the value I am expecting.
> My conclusion is the data in the "false" portion of the statement is
> actually null or non-existant. I think this is causing my statement to
> malfunction. How do I account for this and make my statement work
> properly?
> There will eventually be data in the field represented in the "false"
> portion of the statement. The data will reside in either/or both
> depending
> on the time of month.
> Thank you... PB

nulls

create table t1(c1 int, c2 varchar(10))
insert t1 values(1,'Hello')
insert t1 values(2,'')
insert t1 values(3,NULL)

select *
from t1

c1c2
1Hello
2
3NULL

select *
from t1
where c2 = ' '

c1c2
2

select *
from t1
where ltrim(rtrim(c2)) is null

c1c2
3NULL

The last query should have result as following. However sql server
2000 does no list row c1 = 2.
c1c2
2
3NULLOn 20 Mar, 06:28, othell...@.yahoo.com wrote:

Quote:

Originally Posted by

create table t1(c1 int, c2 varchar(10))
insert t1 values(1,'Hello')
insert t1 values(2,'')
insert t1 values(3,NULL)
>
select *
from t1
>
c1 c2
1 Hello
2
3 NULL
>
select *
from t1
where c2 = ' '
>
c1 c2
2
>
select *
from t1
where ltrim(rtrim(c2)) is null
>
c1 c2
3 NULL
>
The last query should have result as following. However sql server
2000 does no list row c1 = 2.
c1 c2
2
3 NULL

Why would you think that the result of ltrim(rtrim(c2)) would be NULL
when c2 is a non-null string? In fact the result is an empty string
(not the same as NULL) so the answer you got is correct. The row where
c1=2 should NOT be included.

In SQL, NULL is not the same as an empty string. The only common
exception that I know of is Oracle, which treats empty strings as
NULLs.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||On Mar 20, 3:52 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.orgwrote:

Quote:

Originally Posted by

On 20 Mar, 06:28, othell...@.yahoo.com wrote:
>
>
>
>
>

Quote:

Originally Posted by

create table t1(c1 int, c2 varchar(10))
insert t1 values(1,'Hello')
insert t1 values(2,'')
insert t1 values(3,NULL)

>

Quote:

Originally Posted by

select *
from t1

>

Quote:

Originally Posted by

c1 c2
1 Hello
2
3 NULL

>

Quote:

Originally Posted by

select *
from t1
where c2 = ' '

>

Quote:

Originally Posted by

c1 c2
2

>

Quote:

Originally Posted by

select *
from t1
where ltrim(rtrim(c2)) is null

>

Quote:

Originally Posted by

c1 c2
3 NULL

>

Quote:

Originally Posted by

The last query should have result as following. However sql server
2000 does no list row c1 = 2.
c1 c2
2
3 NULL

>
Why would you think that the result of ltrim(rtrim(c2)) would be NULL
when c2 is a non-null string? In fact the result is an empty string
(not the same as NULL) so the answer you got is correct. The row where
c1=2 should NOT be included.
>
In SQL, NULL is not the same as an empty string. The only common
exception that I know of is Oracle, which treats empty strings as
NULLs.
>
--
David Portas, SQL Server MVP
>
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
>
SQL Server Books Online:http://msdn2.microsoft.com/library/...US,SQL.90).aspx
-- Hide quoted text -
>
- Show quoted text -

If it is not null then it is definitely not 'any number of spaces' and
match.

select *
from t1
where c2 = ' '|||Actually, ltrim(rtrim(c2)) is 'any number of spaces', it's zero
spaces, or empty string, not NULL. NULL is not an empty string, it is
NULL. End of story.

Cheers,
Jason Lepack

On Mar 20, 6:04 am, othell...@.yahoo.com wrote:

Quote:

Originally Posted by

On Mar 20, 3:52 pm, "David Portas"
>
>
>
<REMOVE_BEFORE_REPLYING_dpor...@.acm.orgwrote:

Quote:

Originally Posted by

On 20 Mar, 06:28, othell...@.yahoo.com wrote:

>

Quote:

Originally Posted by

Quote:

Originally Posted by

create table t1(c1 int, c2 varchar(10))
insert t1 values(1,'Hello')
insert t1 values(2,'')
insert t1 values(3,NULL)

>

Quote:

Originally Posted by

Quote:

Originally Posted by

select *
from t1

>

Quote:

Originally Posted by

Quote:

Originally Posted by

c1 c2
1 Hello
2
3 NULL

>

Quote:

Originally Posted by

Quote:

Originally Posted by

select *
from t1
where c2 = ' '

>

Quote:

Originally Posted by

Quote:

Originally Posted by

c1 c2
2

>

Quote:

Originally Posted by

Quote:

Originally Posted by

select *
from t1
where ltrim(rtrim(c2)) is null

>

Quote:

Originally Posted by

Quote:

Originally Posted by

c1 c2
3 NULL

>

Quote:

Originally Posted by

Quote:

Originally Posted by

The last query should have result as following. However sql server
2000 does no list row c1 = 2.
c1 c2
2
3 NULL

>

Quote:

Originally Posted by

Why would you think that the result of ltrim(rtrim(c2)) would be NULL
when c2 is a non-null string? In fact the result is an empty string
(not the same as NULL) so the answer you got is correct. The row where
c1=2 should NOT be included.

>

Quote:

Originally Posted by

In SQL, NULL is not the same as an empty string. The only common
exception that I know of is Oracle, which treats empty strings as
NULLs.

>

Quote:

Originally Posted by

--
David Portas, SQL Server MVP

>

Quote:

Originally Posted by

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

>

Quote:

Originally Posted by

SQL Server Books Online:http://msdn2.microsoft.com/library/...US,SQL.90).aspx
-- Hide quoted text -

>

Quote:

Originally Posted by

- Show quoted text -

>
If it is not null then it is definitely not 'any number of spaces' and
match.
>
select *
from t1
where c2 = ' '

|||On 20 Mar 2007 03:04:36 -0700, othellomy@.yahoo.com wrote:

(snip)

Quote:

Originally Posted by

>If it is not null then it is definitely not 'any number of spaces' and
>match.
>
>select *
>from t1
>where c2 = ' '

Hi othellomy,

I'm not sure if I understand you correctly, but I assume that you are
asking why a string of zero length ('') is considered equal to a string
of spaces (' ').

The reason is how ANSI has ruled that string comparisons in SQL should
be carried out: the shorter string has to be padded with spaces to match
the length of the longer string; after that, the strings are compared
position by position.

I know that this is not always the behaviour people expect and require.
The expectation can be managed by understanding the rules for string
comparisons. And the required behaviour of string comparisons can be
gotten by using one of the followinmg two workarounds:

DECLARE @.a varchar(10), @.b varchar(10);
SET @.a = 'abc';
SET @.b = 'abc ';

-- Workaround 1
IF @.a = @.b AND DATALENGTH(@.a) = DATALENGTH(@.b)
PRINT 'They are equal!';
ELSE
PRINT 'They are different!';

-- Workaround 2
IF @.a + 'X' = @.b + 'X'
PRINT 'They are equal!';
ELSE
PRINT 'They are different!';

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Mar 21, 12:12 am, Hugo Kornelis
<h...@.perFact.REMOVETHIS.info.INVALIDwrote:

Quote:

Originally Posted by

On 20 Mar 2007 03:04:36 -0700, othell...@.yahoo.com wrote:
>
(snip)
>

Quote:

Originally Posted by

If it is not null then it is definitely not 'any number of spaces' and
match.

>

Quote:

Originally Posted by

select *
from t1
where c2 = ' '

>
Hi othellomy,
>
I'm not sure if I understand you correctly, but I assume that you are
asking why a string of zero length ('') is considered equal to a string
of spaces (' ').
>
The reason is how ANSI has ruled that string comparisons in SQL should
be carried out: the shorter string has to be padded with spaces to match
the length of the longer string; after that, the strings are compared
position by position.
>
I know that this is not always the behaviour people expect and require.
The expectation can be managed by understanding the rules for string
comparisons. And the required behaviour of string comparisons can be
gotten by using one of the followinmg two workarounds:
>
DECLARE @.a varchar(10), @.b varchar(10);
SET @.a = 'abc';
SET @.b = 'abc ';
>
-- Workaround 1
IF @.a = @.b AND DATALENGTH(@.a) = DATALENGTH(@.b)
PRINT 'They are equal!';
ELSE
PRINT 'They are different!';
>
-- Workaround 2
IF @.a + 'X' = @.b + 'X'
PRINT 'They are equal!';
ELSE
PRINT 'They are different!';
>
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis

SET @.a = ''
SET @.b = ' '
if nullif(@.a,'') is null and nullif(@.b,'') is null
PRINT 'They are equal!';
ELSE
PRINT 'They are different!';|||On 21 Mar 2007 00:32:20 -0700, othellomy@.yahoo.com wrote:

(snip)

Quote:

Originally Posted by

>SET @.a = ''
>SET @.b = ' '
>if nullif(@.a,'') is null and nullif(@.b,'') is null
PRINT 'They are equal!';
>ELSE
PRINT 'They are different!';

Hi othellomy,

I'm not sure what you're trying to say here. This code will return "They
are equal!" if both @.a and @.b are either NULL or a string consisting of
zero or more space characters, regardless of whether they are equal:

DECLARE @.a varchar(10), @.b varchar(10);
SET @.a = ' ';
SET @.b = NULL;

if nullif(@.a,'') is null and nullif(@.b,'') is null
PRINT 'They are equal!';

But it will return nothing if @.a and @.b are both non-NULL and not empty,
even if they ARE equal:

DECLARE @.a varchar(10), @.b varchar(10);
SET @.a = 'X';
SET @.b = @.a;

if nullif(@.a,'') is null and nullif(@.b,'') is null
PRINT 'They are equal!';

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Null values.

Hi,
my db allows too many null values...i can't do anything to make them not nu
ll as most of the fields can be NULL..
will it affect any performance on my server..and if yes then is there any me
thod where we can avoid the degrading of performance causing by NULL values.
2) doest NULL values really affect server performance.
regards
sanjayNULL is handles like a value in SQL Server. so it doesn't affect performance
any differently compared to if you had an actual value in the column (except
for variable length columns where the NULL "value" only uses space in the
null map in the row layout compared to a real varchar value which is stored
in the row).
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:5556A9CC-C2C3-4900-AB37-C81EB2723A7B@.microsoft.com...
> Hi,
> my db allows too many null values...i can't do anything to make them not
null as most of the fields can be NULL..
> will it affect any performance on my server..and if yes then is there any
method where we can avoid the degrading of performance causing by NULL
values.
> 2) doest NULL values really affect server performance.
> regards
> sanjay
>

Null values.

Hi
my db allows too many null values...i can't do anything to make them not null as most of the fields can be NULL.
will it affect any performance on my server..and if yes then is there any method where we can avoid the degrading of performance causing by NULL values
2) doest NULL values really affect server performance
regard
sanjaNULL is handles like a value in SQL Server. so it doesn't affect performance
any differently compared to if you had an actual value in the column (except
for variable length columns where the NULL "value" only uses space in the
null map in the row layout compared to a real varchar value which is stored
in the row).
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:5556A9CC-C2C3-4900-AB37-C81EB2723A7B@.microsoft.com...
> Hi,
> my db allows too many null values...i can't do anything to make them not
null as most of the fields can be NULL..
> will it affect any performance on my server..and if yes then is there any
method where we can avoid the degrading of performance causing by NULL
values.
> 2) doest NULL values really affect server performance.
> regards
> sanjay
>

Null values when extracting data from an excel file

Hello,

I'm trying to import some data from a spreadsheet to a database table from a package in integration services. The problem is that I see the data when I open the excel file but when I try to run my package , it doesn't insert any rows in the table and it finishes with a success status.

My excel file has some formulas to get the data from other worksheets. I added a Data Viewer and all I see is null values in every cell.

I need help...does anyone know what's wrong?

This may seem to be obviuos; but meake sure you are pointing to the right sheet. What happens when you click the preview button in the Excel source?

Rafael Salas

|||It's the same thing... null values :(|||As soon as I close the Excel workbook containing links, I start getting NULLS through the dataflow.

When the workbook is open, the linked data works fine.|||

In my case, it happens in both situations, closed or opened. But it happens when I modify something in the sheet. I inserted some rows in blank and that's when I cannot read the data in SSIS. I still have the original file and I was able to import that specific sheet (before the change) to the database.

I did the same thing in another file(I′m looping through files) and it did OK, so I don't think the problem is the blank rows...

NULL Values vs empty string vs space

How do I define a field to have the default value = ''. Not NULL but not a space either in SQL Server 2005?

Use the following in the field (default value) property

('')

Regards

|||I tried that and it didn't work.|||Just to get your point, did you mean that you want to place a default empty string?|||Smiling is correct, that is how you do it. When you say "it didn't work", perhaps you mean it didn't do what you wanted/expected it to, but that is how you define a default of a zero-length string (from within management studio).|||

That's what I thought too. I tried it again and it does work. I think what was going on is I had the table definition open and saved it but it really did not save. So I closed the defintion window and tried it and it works.

Thanks

|||Whats even more interesting it does NOT work when using the FormView control in ASP.NET 2.0 but if I enter the data directly into the table using the server explorer it does work.

???|||Does it show NULL value though?|||

YES, when viewing the data in the database server explorer the value is NULL even though the default value is ''.

Like I said entering data directly in the server explorer it works, when using a FormView control it does not. When using the formview all code is generated by VisualStudio.

Here is the SQLDatasource code generated by Visual Studio...

<asp:SqlDataSource ID="SqlDataSourceUserProfile" runat="server" ConnectionString="<%$ ConnectionStrings:AEISITConnectionString %>"
DeleteCommand="DELETE FROM [UserProfile] WHERE [UserProfile_RecID] = @.UserProfile_RecID"
InsertCommand="INSERT INTO [UserProfile] ([UserID], [UserRole], [FirstName], [LastName],, [CampusID], [DistrictID], [RegionID], [SessionID]) VALUES (@.UserID, @.UserRole, @.FirstName, @.LastName, @.Email, @.CampusID, @.DistrictID, @.RegionID, @.SessionID)"
SelectCommand="SELECT UserProfile_RecID, UserID, UserRole, FirstName, LastName, Email, CampusID, DistrictID, RegionID, SessionID FROM UserProfile WHERE (UserProfile_RecID = @.UserProfile_RecID)"
UpdateCommand="UPDATE [UserProfile] SET [UserID] = @.UserID, [UserRole] = @.UserRole, [FirstName] = @.FirstName, [LastName] = @.LastName, = @.Email, [CampusID] = @.CampusID, [DistrictID] = @.DistrictID, [RegionID] = @.RegionID, [SessionID] = @.SessionID WHERE [UserProfile_RecID] = @.UserProfile_RecID">
<DeleteParameters>
<asp:Parameter Name="UserProfile_RecID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="UserID" Type="String" />
<asp:Parameter Name="UserRole" Type="String" />
<asp:Parameter Name="FirstName" Type="String" />
<asp:Parameter Name="LastName" Type="String" />
<asp:Parameter Name="Email" Type="String" />
<asp:Parameter Name="CampusID" Type="String" />
<asp:Parameter Name="DistrictID" Type="String" />
<asp:Parameter Name="RegionID" Type="String" />
<asp:Parameter Name="SessionID" Type="String" />
<asp:Parameter Name="UserProfile_RecID" Type="Int32" />
</UpdateParameters>
<SelectParameters>
<asp:ControlParameter ControlID="UserListBox" Name="UserProfile_RecID" PropertyName="SelectedValue"
Type="Int32" />
</SelectParameters>
<InsertParameters>
<asp:Parameter Name="UserID" Type="String" />
<asp:Parameter Name="UserRole" Type="String" />
<asp:Parameter Name="FirstName" Type="String" />
<asp:Parameter Name="LastName" Type="String" />
<asp:Parameter Name="Email" Type="String" />
<asp:Parameter Name="CampusID" Type="String" />
<asp:Parameter Name="DistrictID" Type="String" />
<asp:Parameter Name="RegionID" Type="String" />
<asp:Parameter Name="SessionID" Type="String" />
</InsertParameters>
</asp:SqlDataSource>

|||

That would explain it.

If you want the default value on insert, remove all references to the field in the insertcommand. Alternatively, change the parameter's convertemptystringtonull property to false.

|||

Yes, whatMotley said is correct.

Just change the convertemptystringtonull property to false.

NULL values returned when reading values from a text file using Data Reader.

I have a DTSX package which reads values from a fixed-length text file using a data reader and writes some of the column values from the file to an Oracle table. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Data Reader. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?Why aren't you using the flat file source connector?|||

Sorry: I reread my initial posting and it contained some incorrect details. I have modified the message accordingly and the modified content is in italics

I have a DTSX package which reads values from a delimited text file using a Flat File source component (and a Lookup for validating some of the data) and reads the data into a Table in an Oracle database. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. The typical file length is between 300,000 to 500,000 rows of data. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Flat File source component. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?

|||One additional detail which may be pertinent: The DTSX is running inside of a virtual machine.|||Since you are using a lookup, a couple of things to consider:
Lookup matching is case sensitive, so you may have to adjust your data for comparison accordingly|||It appears that memory may be the answer. The VM where the DTSX was running only had 512 MB of RAM. When this was moved to an environment with 1 GB we do not see the same issues.|||Hmmm - seems I spoke too soon. One file which was about 75,000 rows of data / 30 MB of data processed successfully. However another file which was around 300,000 rows of data / 150 MB resulted in the same "false NULL" situation. I'm going to try breaking down the larger file into 4 smaller segments and will process them each individually to see if this makes a difference. I am not aware of any explicit size limitations in SSIS that we should be bumping up against with these file sizes but can anyone tell me if there are thresholds that should not be exceeded as a best practice when dealing with flat files or lookups?|||Ok, running the smaller file also resulted in the same error as before. We're now operating under the theory that this has to do with something being kept in memory after the package initially runs since we usually seem to be able to generate a "clean" result file after the first time we try running a package. To test this theory we will reboot the computer where the DTSX is stored and will then rerun a file which has already generated errors.|||

It appears that we have a solution: We broke up the DTSX package into 4 smaller packages, broke the file up in 4 smaller files, ran the packages on a non-virtual machine with 1GB of RAM, and executed the packages through the command line. When all of these changes were combined we get the expected result without any false NULL issues. Initial testing seems to reveal that omitting any one of these steps may still result in the original error but this isn't conclusive at this point in time as we haven't tried all of the different scenarios. All these changes seem to indicate that the root cause of the issue is related to available memory, and I am interested in anyone else has any insight - thanks.

|||

I am encountering this error. My solution was to uncheck the "Retain null values..." within the Flat File Source. However, I had to change my logic that checked for nulls to check for empty strings.

I really wish this would be resolved by Microsoft because it seems to happen when the files have many rows. I'm importing about 7 mil rows a time. It would be nice to have more confidence in the SSIS product.

|||

Shizelmah wrote:

I really wish this would be resolved by Microsoft because it seems to happen when the files have many rows. I'm importing about 7 mil rows a time. It would be nice to have more confidence in the SSIS product.

So what are you going to do about it? Leaving pithy comments on here won't make the slightest bit of difference I'm afraid. The correct place to submit your bugs and suggestions is http://connect.microsoft.com/sqlserver/feedback

Hope that helps.

Regards

-Jamie

|||I have encountered the same problem. It seems to be a bug in SSIS.

After doing some investigation it turned out it was the Union All component used in the dataflow which caused this situation. When I removed the Union All component the problem disappeared.

I can't explain this weird behaviour of SSIS - I suppose it is a bug related to internal SSIS buffer management.

If you have a Union All component with more than 4 inputs in your dataflow, do try to remove it. Maybe it will help, as it was in my case.

Regards,
Grzegorz

NULL values returned when reading values from a text file using Data Reader.

I have a DTSX package which reads values from a fixed-length text file using a data reader and writes some of the column values from the file to an Oracle table. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Data Reader. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?Why aren't you using the flat file source connector?|||

Sorry: I reread my initial posting and it contained some incorrect details. I have modified the message accordingly and the modified content is in italics

I have a DTSX package which reads values from a delimited text file using a Flat File source component (and a Lookup for validating some of the data) and reads the data into a Table in an Oracle database. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. The typical file length is between 300,000 to 500,000 rows of data. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Flat File source component. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?

|||One additional detail which may be pertinent: The DTSX is running inside of a virtual machine.|||Since you are using a lookup, a couple of things to consider:
Lookup matching is case sensitive, so you may have to adjust your data for comparison accordingly|||It appears that memory may be the answer. The VM where the DTSX was running only had 512 MB of RAM. When this was moved to an environment with 1 GB we do not see the same issues.|||Hmmm - seems I spoke too soon. One file which was about 75,000 rows of data / 30 MB of data processed successfully. However another file which was around 300,000 rows of data / 150 MB resulted in the same "false NULL" situation. I'm going to try breaking down the larger file into 4 smaller segments and will process them each individually to see if this makes a difference. I am not aware of any explicit size limitations in SSIS that we should be bumping up against with these file sizes but can anyone tell me if there are thresholds that should not be exceeded as a best practice when dealing with flat files or lookups?|||Ok, running the smaller file also resulted in the same error as before. We're now operating under the theory that this has to do with something being kept in memory after the package initially runs since we usually seem to be able to generate a "clean" result file after the first time we try running a package. To test this theory we will reboot the computer where the DTSX is stored and will then rerun a file which has already generated errors.|||

It appears that we have a solution: We broke up the DTSX package into 4 smaller packages, broke the file up in 4 smaller files, ran the packages on a non-virtual machine with 1GB of RAM, and executed the packages through the command line. When all of these changes were combined we get the expected result without any false NULL issues. Initial testing seems to reveal that omitting any one of these steps may still result in the original error but this isn't conclusive at this point in time as we haven't tried all of the different scenarios. All these changes seem to indicate that the root cause of the issue is related to available memory, and I am interested in anyone else has any insight - thanks.

|||

I am encountering this error. My solution was to uncheck the "Retain null values..." within the Flat File Source. However, I had to change my logic that checked for nulls to check for empty strings.

I really wish this would be resolved by Microsoft because it seems to happen when the files have many rows. I'm importing about 7 mil rows a time. It would be nice to have more confidence in the SSIS product.

|||

Shizelmah wrote:

I really wish this would be resolved by Microsoft because it seems to happen when the files have many rows. I'm importing about 7 mil rows a time. It would be nice to have more confidence in the SSIS product.

So what are you going to do about it? Leaving pithy comments on here won't make the slightest bit of difference I'm afraid. The correct place to submit your bugs and suggestions is http://connect.microsoft.com/sqlserver/feedback

Hope that helps.

Regards

-Jamie

|||I have encountered the same problem. It seems to be a bug in SSIS.

After doing some investigation it turned out it was the Union All component used in the dataflow which caused this situation. When I removed the Union All component the problem disappeared.

I can't explain this weird behaviour of SSIS - I suppose it is a bug related to internal SSIS buffer management.

If you have a Union All component with more than 4 inputs in your dataflow, do try to remove it. Maybe it will help, as it was in my case.

Regards,
Grzegorz