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

NULL values in CLR TableResult UDF

Hi everyone,

I need my UDF (which returns a table) to be able to return NULL values.

My function looks like this:

<SqlFunction(FillRowMethodName:="Process_TrainingInfo", TableDefinition:=" CourseName nvarchar(80), CreditDate DateTime, " + _

"CreditResult nvarchar(1), ExpiryDate DateTime ")> _

Public Shared Function funct_GetCreditsFromTIMS(ByVal EmployeeID As String) As IEnumerable

Dim dr() as dataRow

…. This queries an oracle database which returns a small number of rows…. This all works well….

Return dr

End Function

This is the Fill Row Method:

Public Shared Sub Process_ TrainingInfo(ByVal row As Object, <Runtime.InteropServices.Out()>ByRef CourseName As String, _

<Runtime.InteropServices.Out()> ByRef CreditDate As Date, _

<Runtime.InteropServices.Out()> ByRef CreditResult As String, _

<Runtime.InteropServices.Out()> ByRef ExpiryDate As Date)

Dim dr As DataRow = CType(row, DataRow)

CourseName = dr.Item(1).ToString

CreditDate = CType(dr.Item(2), Date)

CreditResult = dr.Item(3).ToString

'dr.Item(5) Might be NULL!!!!

If Not IsDBNull(dr.Item(5)) Then

ExpiryDate = CType(dr.Item(5), Date)

End If

End Sub

The problem is that the EXPIRYDATE field may be NULL. If I leave the field empty, or I try ExpiryDate = Nothing I get this error:

An error occurred while getting new row from user defined Table Valued Function :

System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

I have also tried:

Dim nullDate as Date

ExpiryDate = nullDate

…but I get the same error.

I can’t do ExpiryDate = Dbnull.value, as I get this error “System.dbnull can not be converted to date”

Is there anyway that I can do this?

Thanks,

Forch

Try and change the ExpiryDate out param to SqlDateTime (from System.Data.SqlTypes namespace) and when the value is null set the ExpiryDate to SqlDateTime.Null.

Niels

NULL Value in Aggregate Function

When I run a query, I get the following warning message
and I would like to know whether it is a problem or not ?
Warning: Null value is eliminated by an aggregate or other
SET operation.
ThanksWhether or not the warning message is a problem depends on the results you
expect. Consider the example below:
CREATE TABLE #Table1(Col1 int NULL)
INSERT INTO #Table1 VALUES(10)
INSERT INTO #Table1 VALUES(NULL)
SELECT AVG(Col1) FROM #Table1
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Mary" <anonymous@.discussions.microsoft.com> wrote in message
news:02f401c529ca$0df65ff0$a401280a@.phx.gbl...
> When I run a query, I get the following warning message
> and I would like to know whether it is a problem or not ?
> Warning: Null value is eliminated by an aggregate or other
> SET operation.
> Thanks|||Hi,
Most aggregate functions eliminate null values in calculations; one
exception is the COUNT function. When using the COUNT function against a
column containing null values, the null values will be eliminated from the
calculation. However, if the COUNT function uses an asterisk, it will
calculate all rows regardless of null values being present.
Again depending upon the aggregate function you are using, you might have
received this warning message. You need to understand if the aggregate
function is giving the required result set. You can use ISNULL function to
convert NULL to the desired values.
--
Thanks
Yogish|||Thank you the reply from both of you.
In my select statement, aggregrate function SUM(Balance)
is used.
From the query result, I find that some of them are NULL
and some are $0. I am still looking into the reason why
some of them are NULL.
From my understanding, for NULL + $0, it will be $0. And
all balance are NULL will give me a NULL result. I
believe that the query still OK.
Thanks again.
>--Original Message--
>Hi,
>Most aggregate functions eliminate null values in
calculations; one
>exception is the COUNT function. When using the COUNT
function against a
>column containing null values, the null values will be
eliminated from the
>calculation. However, if the COUNT function uses an
asterisk, it will
>calculate all rows regardless of null values being
present.
>Again depending upon the aggregate function you are
using, you might have
>received this warning message. You need to understand if
the aggregate
>function is giving the required result set. You can use
ISNULL function to
>convert NULL to the desired values.
>--
>Thanks
>Yogish
>.
>|||> From my understanding, for NULL + $0,
No, NULL + 0 is UNKNOWN. For convenience, the SQL language designers decided to not return UNK or
NULL when you aggregate over rows where one or more have NULL in the column. They decided to skip
(ignore) the ones with NULL. And give you a reminder (this warning).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mary" <anonymous@.discussions.microsoft.com> wrote in message
news:033801c529d5$f9747920$a401280a@.phx.gbl...
> Thank you the reply from both of you.
> In my select statement, aggregrate function SUM(Balance)
> is used.
> From the query result, I find that some of them are NULL
> and some are $0. I am still looking into the reason why
> some of them are NULL.
> From my understanding, for NULL + $0, it will be $0. And
> all balance are NULL will give me a NULL result. I
> believe that the query still OK.
> Thanks again.
>>--Original Message--
>>Hi,
>>Most aggregate functions eliminate null values in
> calculations; one
>>exception is the COUNT function. When using the COUNT
> function against a
>>column containing null values, the null values will be
> eliminated from the
>>calculation. However, if the COUNT function uses an
> asterisk, it will
>>calculate all rows regardless of null values being
> present.
>>Again depending upon the aggregate function you are
> using, you might have
>>received this warning message. You need to understand if
> the aggregate
>>function is giving the required result set. You can use
> ISNULL function to
>>convert NULL to the desired values.
>>--
>>Thanks
>>Yogish
>>.

NULL Value in Aggregate Function

When I run a query, I get the following warning message
and I would like to know whether it is a problem or not ?
Warning: Null value is eliminated by an aggregate or other
SET operation.
ThanksWhether or not the warning message is a problem depends on the results you
expect. Consider the example below:
CREATE TABLE #Table1(Col1 int NULL)
INSERT INTO #Table1 VALUES(10)
INSERT INTO #Table1 VALUES(NULL)
SELECT AVG(Col1) FROM #Table1
Hope this helps.
Dan Guzman
SQL Server MVP
"Mary" <anonymous@.discussions.microsoft.com> wrote in message
news:02f401c529ca$0df65ff0$a401280a@.phx.gbl...
> When I run a query, I get the following warning message
> and I would like to know whether it is a problem or not ?
> Warning: Null value is eliminated by an aggregate or other
> SET operation.
> Thanks|||Hi,
Most aggregate functions eliminate null values in calculations; one
exception is the COUNT function. When using the COUNT function against a
column containing null values, the null values will be eliminated from the
calculation. However, if the COUNT function uses an asterisk, it will
calculate all rows regardless of null values being present.
Again depending upon the aggregate function you are using, you might have
received this warning message. You need to understand if the aggregate
function is giving the required result set. You can use ISNULL function to
convert NULL to the desired values.
Thanks
Yogish|||Thank you the reply from both of you.
In my select statement, aggregrate function SUM(Balance)
is used.
From the query result, I find that some of them are NULL
and some are $0. I am still looking into the reason why
some of them are NULL.
From my understanding, for NULL + $0, it will be $0. And
all balance are NULL will give me a NULL result. I
believe that the query still OK.
Thanks again.

>--Original Message--
>Hi,
>Most aggregate functions eliminate null values in
calculations; one
>exception is the COUNT function. When using the COUNT
function against a
>column containing null values, the null values will be
eliminated from the
>calculation. However, if the COUNT function uses an
asterisk, it will
>calculate all rows regardless of null values being
present.
>Again depending upon the aggregate function you are
using, you might have
>received this warning message. You need to understand if
the aggregate
>function is giving the required result set. You can use
ISNULL function to
>convert NULL to the desired values.
>--
>Thanks
>Yogish
>.
>|||> From my understanding, for NULL + $0,
No, NULL + 0 is UNKNOWN. For convenience, the SQL language designers decided
to not return UNK or
NULL when you aggregate over rows where one or more have NULL in the column.
They decided to skip
(ignore) the ones with NULL. And give you a reminder (this warning).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mary" <anonymous@.discussions.microsoft.com> wrote in message
news:033801c529d5$f9747920$a401280a@.phx.gbl...[vbcol=seagreen]
> Thank you the reply from both of you.
> In my select statement, aggregrate function SUM(Balance)
> is used.
> From the query result, I find that some of them are NULL
> and some are $0. I am still looking into the reason why
> some of them are NULL.
> From my understanding, for NULL + $0, it will be $0. And
> all balance are NULL will give me a NULL result. I
> believe that the query still OK.
> Thanks again.
>
> calculations; one
> function against a
> eliminated from the
> asterisk, it will
> present.
> using, you might have
> the aggregate
> ISNULL function to

NULL Value in Aggregate Function

When I run a query, I get the following warning message
and I would like to know whether it is a problem or not ?
Warning: Null value is eliminated by an aggregate or other
SET operation.
Thanks
Whether or not the warning message is a problem depends on the results you
expect. Consider the example below:
CREATE TABLE #Table1(Col1 int NULL)
INSERT INTO #Table1 VALUES(10)
INSERT INTO #Table1 VALUES(NULL)
SELECT AVG(Col1) FROM #Table1
Hope this helps.
Dan Guzman
SQL Server MVP
"Mary" <anonymous@.discussions.microsoft.com> wrote in message
news:02f401c529ca$0df65ff0$a401280a@.phx.gbl...
> When I run a query, I get the following warning message
> and I would like to know whether it is a problem or not ?
> Warning: Null value is eliminated by an aggregate or other
> SET operation.
> Thanks
|||Hi,
Most aggregate functions eliminate null values in calculations; one
exception is the COUNT function. When using the COUNT function against a
column containing null values, the null values will be eliminated from the
calculation. However, if the COUNT function uses an asterisk, it will
calculate all rows regardless of null values being present.
Again depending upon the aggregate function you are using, you might have
received this warning message. You need to understand if the aggregate
function is giving the required result set. You can use ISNULL function to
convert NULL to the desired values.
Thanks
Yogish
|||Thank you the reply from both of you.
In my select statement, aggregrate function SUM(Balance)
is used.
From the query result, I find that some of them are NULL
and some are $0. I am still looking into the reason why
some of them are NULL.
From my understanding, for NULL + $0, it will be $0. And
all balance are NULL will give me a NULL result. I
believe that the query still OK.
Thanks again.

>--Original Message--
>Hi,
>Most aggregate functions eliminate null values in
calculations; one
>exception is the COUNT function. When using the COUNT
function against a
>column containing null values, the null values will be
eliminated from the
>calculation. However, if the COUNT function uses an
asterisk, it will
>calculate all rows regardless of null values being
present.
>Again depending upon the aggregate function you are
using, you might have
>received this warning message. You need to understand if
the aggregate
>function is giving the required result set. You can use
ISNULL function to
>convert NULL to the desired values.
>--
>Thanks
>Yogish
>.
>
|||> From my understanding, for NULL + $0,
No, NULL + 0 is UNKNOWN. For convenience, the SQL language designers decided to not return UNK or
NULL when you aggregate over rows where one or more have NULL in the column. They decided to skip
(ignore) the ones with NULL. And give you a reminder (this warning).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mary" <anonymous@.discussions.microsoft.com> wrote in message
news:033801c529d5$f9747920$a401280a@.phx.gbl...[vbcol=seagreen]
> Thank you the reply from both of you.
> In my select statement, aggregrate function SUM(Balance)
> is used.
> From the query result, I find that some of them are NULL
> and some are $0. I am still looking into the reason why
> some of them are NULL.
> From my understanding, for NULL + $0, it will be $0. And
> all balance are NULL will give me a NULL result. I
> believe that the query still OK.
> Thanks again.
> calculations; one
> function against a
> eliminated from the
> asterisk, it will
> present.
> using, you might have
> the aggregate
> ISNULL function to

Null to Zero in Select Statement?

How can I do a null to 0 in a select statement? I tried the NZ() function but it is not part of SQL.

Thanks very much,you can use CASE stmt ...chk BOL for for more info

hth|||Easier, ISNULL(). If the field that might be null is an int called MyField:

SELECT IsNull(MyField,0) FROM table|||or COALESCE is the other option. i'm not always sure when ISNULL or COALESCE is more appropriate

cs|||COALESCE allows as many arguments as you want, that is:

COALESCE(v1,v2,v3,v4,0)

Will return 0 if all of v1 through v4 are null, otherwise the first non-null argument...|||that's right, I remember now. COALESCE is good if you want to pass in multiple values and get a zero if all are null, but not useful if you want to substitute another value for the null. I had forgotten about the multiple argument thing.

cs

NULL to Zero

Is there any function that would change a NULL value to zero? I am writing a
view that need to calculate the net inventory by subtracting the export qty
from import qty. However, in case of no export, 1-Null=Null. Thanks.[posted and mailed, please reply in news]

John Q (johnq@.hkayp.org) writes:
> Is there any function that would change a NULL value to zero? I am
> writing a view that need to calculate the net inventory by subtracting
> the export qty from import qty. However, in case of no export,
> 1-Null=Null. Thanks.

coalesce(val1, val2, ..., valn)

returns the first non-NULL value in the list.

(There is also a function isnull() which has a clearer name, but is
restricted to two parameters only. coalesce() is from the ANSI standard.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,

Thank you very much. This function is very useful to me.

Best Regards,
Frederick

"Erland Sommarskog" <sommar@.algonet.se> bl
news:Xns93EC5BCD76E8Yazorman@.127.0.0.1 g...
> [posted and mailed, please reply in news]
> John Q (johnq@.hkayp.org) writes:
> > Is there any function that would change a NULL value to zero? I am
> > writing a view that need to calculate the net inventory by subtracting
> > the export qty from import qty. However, in case of no export,
> > 1-Null=Null. Thanks.
> coalesce(val1, val2, ..., valn)
> returns the first non-NULL value in the list.
> (There is also a function isnull() which has a clearer name, but is
> restricted to two parameters only. coalesce() is from the ANSI standard.)
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp