nvarchar and different collation comparison

Hi,
I have read some articles on Unicode characters, I understand that they use
as a "big international code page" that host thousands of characters. The
thing I don't understand is why I get an error when I compare two nvarchar
column having a different collation... as this:
I know that COLLATE in the comparison would correct the issue, but I want to
find a way to avoid hard coding COLLATE keywords and avoid to change both
collations of the fields. I though nvarchar was to resolve this. Someone can
explain why it doesn't do that?
Or please leave me a personnal mail or web link... Thanks
CREATE TABLE a
(
LatinAS nvarchar(50) COLLATE Latin1_General_CI_AS,
LatinAI nvarchar(50) COLLATE Latin1_General_CI_AI
)
GO
INSERT INTO a (LatinAS, LatinAI) VALUES ('Eric', 'Eric')
SELECT * FROM a WHERE LatinAS = LatinAI
Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.
david.parenteau@.compuware.com
TIANVARCHAR allows you to use Unicode characters. There are still differences
in character sets when you move from collation to collation. So, your
options here are pretty limited.
"David Parenteau" <DavidParenteau@.discussions.microsoft.com> wrote in
message news:5031A14F-4D36-47B8-A07F-3ABB5BF019D2@.microsoft.com...
> Hi,
> I have read some articles on Unicode characters, I understand that they
> use
> as a "big international code page" that host thousands of characters. The
> thing I don't understand is why I get an error when I compare two nvarchar
> column having a different collation... as this:
> I know that COLLATE in the comparison would correct the issue, but I want
> to
> find a way to avoid hard coding COLLATE keywords and avoid to change both
> collations of the fields. I though nvarchar was to resolve this. Someone
> can
> explain why it doesn't do that?
> Or please leave me a personnal mail or web link... Thanks
>
> CREATE TABLE a
> (
> LatinAS nvarchar(50) COLLATE Latin1_General_CI_AS,
> LatinAI nvarchar(50) COLLATE Latin1_General_CI_AI
> )
> GO
> INSERT INTO a (LatinAS, LatinAI) VALUES ('Eric', 'Eric')
> SELECT * FROM a WHERE LatinAS = LatinAI
>
> Server: Msg 446, Level 16, State 9, Line 1
> Cannot resolve collation conflict for equal to operation.
>
> david.parenteau@.compuware.com
> TIA|||David
CREATE TABLE b
(
LatinAS nvarchar(50) COLLATE Latin1_General_CI_AS,
LatinAI nvarchar(50) COLLATE Latin1_General_CI_AI
)
GO
INSERT INTO b (LatinAS, LatinAI) VALUES ('Eric', 'Eric')
SELECT * FROM b WHERE LatinAS COLLATE Latin1_General_CI_AI = LatinAI
"David Parenteau" <DavidParenteau@.discussions.microsoft.com> wrote in
message news:5031A14F-4D36-47B8-A07F-3ABB5BF019D2@.microsoft.com...
> Hi,
> I have read some articles on Unicode characters, I understand that they
> use
> as a "big international code page" that host thousands of characters. The
> thing I don't understand is why I get an error when I compare two nvarchar
> column having a different collation... as this:
> I know that COLLATE in the comparison would correct the issue, but I want
> to
> find a way to avoid hard coding COLLATE keywords and avoid to change both
> collations of the fields. I though nvarchar was to resolve this. Someone
> can
> explain why it doesn't do that?
> Or please leave me a personnal mail or web link... Thanks
>
> CREATE TABLE a
> (
> LatinAS nvarchar(50) COLLATE Latin1_General_CI_AS,
> LatinAI nvarchar(50) COLLATE Latin1_General_CI_AI
> )
> GO
> INSERT INTO a (LatinAS, LatinAI) VALUES ('Eric', 'Eric')
> SELECT * FROM a WHERE LatinAS = LatinAI
>
> Server: Msg 446, Level 16, State 9, Line 1
> Cannot resolve collation conflict for equal to operation.
>
> david.parenteau@.compuware.com
> TIA|||If nvarchar keep the same characters bytes for every characters in a
collation A, does the same character in collation B will be the same bytes
values?
Collation A
Characters A correspond to 265 for example.
In Collation B, does the character A will have the same bits and be 265 or
it could be something else?
Does the sort order and other things (except code page) in collations differ
also for nvarchar columns?
David
"Aaron Bertrand [SQL Server MVP]" wrote:

> NVARCHAR allows you to use Unicode characters. There are still difference
s
> in character sets when you move from collation to collation. So, your
> options here are pretty limited.
>
>
> "David Parenteau" <DavidParenteau@.discussions.microsoft.com> wrote in
> message news:5031A14F-4D36-47B8-A07F-3ABB5BF019D2@.microsoft.com...
>
>

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
>

Numbers of rows returned from mquery

I am using the following code to find if a use exits in the table:
declare @.UserName char(30)
set @.UserName = (select fullname from udf_GetUserName(@.UserId))
SELECT @.@.ROWCOUNT
but that always returns 1. How do I solve that?
ThanksYou are getting the rowcount value for the SET variable statement, which
obviously always equal to 1. If you'd like to know how many full name values
exist for a given identifier, you might consider using COUNT(*) like:
SELECT COUNT(*) FROM tbl_valued_udf ;
or avoid the assignment and do:
SELECT col FROM tbl_valued_udf ;
SELECT @.@.ROWCOUNT ;
Alternatively to check the existence, you could do:
IF EXISTS ( SELECT * FROM tbl_valued_udf )
Anith|||You solve it by asking the correct query. :)
What you are asking SQL to return is the number of rows affected by the
previous statement. Since the previous SELECT always returns a single row,
you get a rowcount of 1. What you really should do is select the fullname
from the underlying user table (or from an abstraction such as a view or
procedure) where the userID = @.userID (supplied parameter). If you get an
empty result set, then the user doesn't exist.
It looks like someone is trying to write an abstraction layer for
programmers so they won't have to learn any SQL. That leads to some really
bad code.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Markgoldin" <Markgoldin@.discussions.microsoft.com> wrote in message
news:A494674C-147F-4009-8C40-99C163F6568D@.microsoft.com...
>I am using the following code to find if a use exits in the table:
> declare @.UserName char(30)
> set @.UserName = (select fullname from udf_GetUserName(@.UserId))
> SELECT @.@.ROWCOUNT
> but that always returns 1. How do I solve that?
> Thanks|||Why not just test IF IS NULL(@.UserName) instead?
Roy Harvey
Beacon Falls, CT
On Wed, 5 Apr 2006 12:47:01 -0700, Markgoldin
<Markgoldin@.discussions.microsoft.com> wrote:

>I am using the following code to find if a use exits in the table:
>declare @.UserName char(30)
>set @.UserName = (select fullname from udf_GetUserName(@.UserId))
>SELECT @.@.ROWCOUNT
>but that always returns 1. How do I solve that?
>Thanks

Number of lines of code in a SP

Hi,
How to find out the total number of lines in a stored proc programmatically?
Regards
Pradeepselect
sum(len(replace(sc.text, ' ', '_')) - len(replace(replace(sc.text, ' ', '_')
, char(13), ''))) + 1
from sysobjects so
inner join syscomments sc on
so.id = sc.id
where
name = 'myStoredProc'|||I assume this was not a question or am I wrong. And why would you be
interested in the number of lines. If you change the formatting, the
number of lines might change, but the SP is till doing the same ?
Markus|||> And why would you be
> interested in the number of lines.
I can't speak for the OP, but the number procedure lines can be a rough
indicator of application complexity. These kind of metrics can be useful in
estimating development effort for major changes, such as a rewrite or
conversion.
Hope this helps.
Dan Guzman
SQL Server MVP
<m.bohse@.quest-consultants.com> wrote in message
news:1133867063.700631.79970@.g49g2000cwa.googlegroups.com...
>I assume this was not a question or am I wrong. And why would you be
> interested in the number of lines. If you change the formatting, the
> number of lines might change, but the SP is till doing the same ?
> Markus
>|||Thats right Dan. I need it for metrics purpose only.
Regards
Pradeep
"Dan Guzman" wrote:

> I can't speak for the OP, but the number procedure lines can be a rough
> indicator of application complexity. These kind of metrics can be useful
in
> estimating development effort for major changes, such as a rewrite or
> conversion.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <m.bohse@.quest-consultants.com> wrote in message
> news:1133867063.700631.79970@.g49g2000cwa.googlegroups.com...
>
>|||Could this be easily extended to get the lengths of all stored
procedures in a db?
"daw" <murmax@.rambler.ru> wrote:

>select
> sum(len(replace(sc.text, ' ', '_')) - len(replace(replace(sc.text, ' ', '
_'), char(13), ''))) + 1
>from sysobjects so
> inner join syscomments sc on
> so.id = sc.id
>where
> name = 'myStoredProc'
>|||On Sat, 10 Dec 2005 14:36:24 GMT, John Baima wrote:

>Could this be easily extended to get the lengths of all stored
>procedures in a db?
Hi John,
Length in lines, you mean?
Try
select
name,
sum(len(replace(sc.text, ' ', '_')) - len(replace(replace(sc.text, '
', '_'), char(13), ''))) + 1
from sysobjects so
inner join syscomments sc on
so.id = sc.id
group by
name
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Number Of Business Days

Hello,

Without creating an additional table and with the least amount of code, I'm looking for a way to determine the number of business days in a given month.

Thanks

Synergy:

Your question contains a huge amount of subjectivity. What is your view of what constitutes a business day? Do you mean every Monday through Sunday? Exclude New Years day? Ground Hogs day? More information in this case is necessary.

Dave

|||

My apologies. Standard business days as viewed by most American businesses. Mon-Fri excluding holidays where businesses would typically be closed.

|||

Man.

Is this list of holidays the ones that you mean?

New Year's Day|||Let's start by clarifying that my name is not 'man'. Though I appreciate your willingness to help, it seems you are unable to do so without having every single detail given to you. Possibly I plan to edit the code depending on what holidays are observed by a particular organization. Unless you have a function tucked in your back pocket which automatically excludes certain holidays, I don't see that it matters which ones are used for example purposes. If you do have such a function, we can use whatever holidays it uses.|||

Fair enough; sorry for being too rammy.

Dave

|||Best way is to generate a calendar table (you can generate the data in Excel) and represent attributes like holidays, week days etc. Then you can answer your question with a simple query. You can write a scalar UDF that has the logic to eliminate holidays and weekends in a month but that is going to be cumbersome to maintain. For example, government organizations observe different set of holidays than companies. So if you have an application that is deployed in different environments you will have to special case lot of stuff. If you go with the calendar table approach then it is easy to maintain different types of calendars, attributes and keep your queries simple. Search the web for "calendar table" and you will find lot of examples.|||

Synergy:

I agree with Umachandar. I have implemented a number of business day calculation functions with all of them based on a calendar table. I re-tooled a function I once messed around with. I think it performs rather mediocre, but you really seemed to be asking for a starting point and this routine might be of some value.

Dave

alter function dbo.businessDays
( @.arg_month integer,
@.arg_year integer
)
returns integer
as

begin

if @.arg_month is null
or @.arg_month > 12
or @.arg_month < 1
or @.arg_year is null
or @.arg_year < 1
return (null)

declare @.yr varchar (4) set @.yr = convert(varchar(4), @.arg_year)
declare @.mo varchar (2) set @.mo = convert(varchar(2),@.arg_month)
declare @.busDays integer set @.busDays = 20

if isDate ( @.mo + '/1/' + @.yr ) = 0
return (null)

--
-- If it is February and not a leap year, return the default number of
-- business days.
--
-- If President's day Monday is observed, modify to subtract another day.
--

if @.arg_month = 2
if @.arg_year % 4 <> 0
or @.arg_year % 100 = 0 and @.arg_year % 400 <> 0
return ( @.busDays )

--
-- Add in additional work days if the 29, 30 or 31 are also business days.
--

if datepart (dw, @.mo+'/29/'+@.yr) between 2 and 6
set @.busDays = @.busDays + 1

if @.arg_month = 2
return ( @.busDays )

--
-- Check the thirtieth day of the month for business.
--
-- Subtract a business day for Mondays of each of these holidays:
-- o Labor Day
-- o Thanksgiving
--

if datepart (dw, @.mo+'/30/'+@.yr) between 2 and 6
set @.busDays = @.busDays + 1

if @.arg_month = 11
or @.arg_month = 9
begin
set @.busDays = @.busDays - 1
return (@.busDays)
end

if @.arg_month = 4
or @.arg_month = 6
return (@.busDays)

--
-- Check the 31st day of the month for business.
--
-- Subtract a business day for each of these holidays:
-- o New Year's Day
-- o Memorial Day
-- o Independence Day
-- o Christmas
--

if datepart (dw, @.mo+'/31/'+@.yr) between 2 and 6
set @.busDays = @.busDays + 1

if @.arg_month = 5
begin
set @.busDays = @.busDays - 1
return (@.busDays)
end

if @.arg_month = 1
begin
if datepart (dw, @.mo+'/1/'+@.yr) between 2 and 6
begin
set @.busDays = @.busDays - 1
end
return (@.busDays)
end

if @.arg_month = 7
begin
if datepart (dw, @.mo+'/4/'+@.yr) between 2 and 6
begin
set @.busDays = @.busDays - 1
end
return (@.busDays)
end

if @.arg_month = 12
if datepart (dw, @.mo+'/25/'+@.yr) between 2 and 6
set @.busDays = @.busDays - 1

return (@.busDays)

end

|||

I am using this query to get amount of business days (days from mon to fri)

Code Snippet

set datefirst 1
declare @.sdate datetime
declare @.edate datetime

select @.sdate = '20070516' --for example, start date May, 16th
select @.edate='20070531' --end date May, 31st

select datediff(day, @.sdate, @.edate)+1-(
select (case datepart(dw, @.sdate)
when 7 then (datepart(ww, @.edate)-datepart(ww, @.sdate))*2-1
else (datepart(ww, @.edate)-datepart(ww, @.sdate))*2
end)+
(case datepart(dw, @.edate)
when 6 then 1
when 7 then 2
else 0
end)
)

Number Of Business Days

Hello,

Without creating an additional table and with the least amount of code, I'm looking for a way to determine the number of business days in a given month.

Thanks

Synergy:

Your question contains a huge amount of subjectivity. What is your view of what constitutes a business day? Do you mean every Monday through Sunday? Exclude New Years day? Ground Hogs day? More information in this case is necessary.

Dave

|||

My apologies. Standard business days as viewed by most American businesses. Mon-Fri excluding holidays where businesses would typically be closed.

|||

Man.

Is this list of holidays the ones that you mean?

New Year's Day|||Let's start by clarifying that my name is not 'man'. Though I appreciate your willingness to help, it seems you are unable to do so without having every single detail given to you. Possibly I plan to edit the code depending on what holidays are observed by a particular organization. Unless you have a function tucked in your back pocket which automatically excludes certain holidays, I don't see that it matters which ones are used for example purposes. If you do have such a function, we can use whatever holidays it uses.|||

Fair enough; sorry for being too rammy.

Dave

|||Best way is to generate a calendar table (you can generate the data in Excel) and represent attributes like holidays, week days etc. Then you can answer your question with a simple query. You can write a scalar UDF that has the logic to eliminate holidays and weekends in a month but that is going to be cumbersome to maintain. For example, government organizations observe different set of holidays than companies. So if you have an application that is deployed in different environments you will have to special case lot of stuff. If you go with the calendar table approach then it is easy to maintain different types of calendars, attributes and keep your queries simple. Search the web for "calendar table" and you will find lot of examples.|||

Synergy:

I agree with Umachandar. I have implemented a number of business day calculation functions with all of them based on a calendar table. I re-tooled a function I once messed around with. I think it performs rather mediocre, but you really seemed to be asking for a starting point and this routine might be of some value.

Dave

alter function dbo.businessDays
( @.arg_month integer,
@.arg_year integer
)
returns integer
as

begin

if @.arg_month is null
or @.arg_month > 12
or @.arg_month < 1
or @.arg_year is null
or @.arg_year < 1
return (null)

declare @.yr varchar (4) set @.yr = convert(varchar(4), @.arg_year)
declare @.mo varchar (2) set @.mo = convert(varchar(2),@.arg_month)
declare @.busDays integer set @.busDays = 20

if isDate ( @.mo + '/1/' + @.yr ) = 0
return (null)

--
-- If it is February and not a leap year, return the default number of
-- business days.
--
-- If President's day Monday is observed, modify to subtract another day.
--

if @.arg_month = 2
if @.arg_year % 4 <> 0
or @.arg_year % 100 = 0 and @.arg_year % 400 <> 0
return ( @.busDays )

--
-- Add in additional work days if the 29, 30 or 31 are also business days.
--

if datepart (dw, @.mo+'/29/'+@.yr) between 2 and 6
set @.busDays = @.busDays + 1

if @.arg_month = 2
return ( @.busDays )

--
-- Check the thirtieth day of the month for business.
--
-- Subtract a business day for Mondays of each of these holidays:
-- o Labor Day
-- o Thanksgiving
--

if datepart (dw, @.mo+'/30/'+@.yr) between 2 and 6
set @.busDays = @.busDays + 1

if @.arg_month = 11
or @.arg_month = 9
begin
set @.busDays = @.busDays - 1
return (@.busDays)
end

if @.arg_month = 4
or @.arg_month = 6
return (@.busDays)

--
-- Check the 31st day of the month for business.
--
-- Subtract a business day for each of these holidays:
-- o New Year's Day
-- o Memorial Day
-- o Independence Day
-- o Christmas
--

if datepart (dw, @.mo+'/31/'+@.yr) between 2 and 6
set @.busDays = @.busDays + 1

if @.arg_month = 5
begin
set @.busDays = @.busDays - 1
return (@.busDays)
end

if @.arg_month = 1
begin
if datepart (dw, @.mo+'/1/'+@.yr) between 2 and 6
begin
set @.busDays = @.busDays - 1
end
return (@.busDays)
end

if @.arg_month = 7
begin
if datepart (dw, @.mo+'/4/'+@.yr) between 2 and 6
begin
set @.busDays = @.busDays - 1
end
return (@.busDays)
end

if @.arg_month = 12
if datepart (dw, @.mo+'/25/'+@.yr) between 2 and 6
set @.busDays = @.busDays - 1

return (@.busDays)

end

|||

I am using this query to get amount of business days (days from mon to fri)

Code Snippet

set datefirst 1
declare @.sdate datetime
declare @.edate datetime

select @.sdate = '20070516' --for example, start date May, 16th
select @.edate='20070531' --end date May, 31st

select datediff(day, @.sdate, @.edate)+1-(
select (case datepart(dw, @.sdate)
when 7 then (datepart(ww, @.edate)-datepart(ww, @.sdate))*2-1
else (datepart(ww, @.edate)-datepart(ww, @.sdate))*2
end)+
(case datepart(dw, @.edate)
when 6 then 1
when 7 then 2
else 0
end)
)

number manipulation in non-identity columns

hi.

i am using ms sql server 2000.

can somebody tell me what the code would be to remove all the values
in a given column and replace them with the associated number of the row
with each execution.

so, if i have a column:

nums
|1|
|2|
|3|
|4|

and somebody deletes record |2|

i would like the nums colum to update to

|1|
|2|
|3|

not:

|1|
|3|
|4|

it seems simple but i am having a hard time with this.
how is it done?

thanks.

SET XACT_ABORT ON
BEGIN TRANSACTION
DELETE FROM MyTable WHERE nums=@.nums
SET NOCOUNT ON
UPDATE MyTable SET nums=nums-1 WHERE nums>@.nums
SET NOCOUNT OFF
COMMIT TRANSACTION

Now I would never recommend actually doing that. It's resource intensive. You'd be better off not using a "nums" column, and using something like a CreateDate column of type datetime. Then eitehr using a subquery or SQL 2005's rank command, or a stored procedure to return a "nums" to you (In the case of using CreateDate, return back the number of other columns with a lower CreateDate), Like:

SELECT t1.*,(SELECT COUNT(*) FROM MyTable WHERE MyTable.CreateDate<t1.CreateDate) nums
FROM MyTable

Now you can go ahead and delete records and nums will adjust for you in order of CreateDate.

|||hi. thanks a lot for your reply.

i am trying to follow your last (least expensive) suggestion.
i am a little confused by the "t1" selects.

i have the following table:

PersonalPhotos
photo_id PK
photo_name
photo_location
photo_size
user_name
photo_date
photo_number

I was previouly using a stored procedure to create the non-identity number column in
photo_number.

I am now trying your code:

SELECT t1.*,(SELECT COUNT(*) FROM PersonalPhotos WHERE PersonalPhotos.photo_date<t1.CreateDate) nums
FROM PersonalPhotos

I have tried creating a t1 table with a t1_date column but that doesnt seem to work. If i replace
all the t1s with personalphoto and all the CreateDates with photo_date, i get results, but no
values in the nums column (which i could have guessed).

I am sorry, i am not a sql programmer (but i am an eager learner) and nothing is obvious.
further clarification would be appreciated.

thanks.|||i am still working on this. can somebody help clarify?
thanks|||I am pretty sure Motley meant to use a T1 table alias:

SELECT t1.*,(SELECT COUNT(*) FROM MyTable WHERE MyTable.CreateDate<t1.CreateDate) nums
FROM MyTableT1|||ok, thanks for helping out. i'll try that when i get home from work.
much appreciated.|||

hi.

i am home now and trying this. i am still confused.

as stated above i have a PersonalPhotos table with

a photo_date column that i'd like to use to delete

records and adjust numbers so they are *always* sequential.

I do not have a T1 table, but created one per Motley's suggestion.

I gave it 2 columns (t1_id, t1_date).

now, i have adjusted Motley's recommendation:

SELECT t1.*,(SELECT COUNT(*) FROM MyTable WHERE MyTable.CreateDate<t1.CreateDate) nums
FROM MyTable

to apply to the table i am working with - "PersonalPhotos":

SELECT t1.*,(SELECT COUNT(*) FROM PersonalPhotos WHERE PersonalPhotos.photo_date<t1.t1_date) nums
FROM PersonalPhotos

When that didnt work, I tried Tmorton's suggestion:

SELECT t1.*,(SELECT COUNT(*) FROM PersonalPhotos WHERE PersonalPhotos.photo_date<t1.t1_date) nums
FROM PersonalPhotos t1

None of this works :(

The errors I am getting are all about the existence (or lack thereof) of the t1

table/columns. In the above select statement, the error I get is:

"Invalid column name 't1_date'." but, t1.t1_date clearly lives in table t1.

could somebody PLEASE explain to me:

1) what is the point of creating the new t1 table (or, do i need to manually create it)?

2) what should the columns be in the new t1 table?

3) why i am getting the Invalid column error?

clarification greatly appreciated.

|||

pbd22:

1) what is the point of creating the new t1 table (or, do i need to manually create it)?

You should NOT create a new table. Using the alias "t1" makes it possible to use the same table "PersonalPhotos" twice in the same query.

pbd22:

2) what should the columns be in the new t1 table?

There should be no new t1 table.

pbd22:

3) why i am getting the Invalid column error?

And actually, I had put the table alias against the wrong table reference.

Try your query like this; you should have better luck:
SELECT t1.*,(SELECT COUNT(*) FROM PersonalPhotos t1 WHERE PersonalPhotos.photo_date<t1.photo_date) nums
FROM PersonalPhotos
ORDER BY PersonalPhotos.photo_date
|||ok, thank you.
that makes more sense to me. i am at work now but will
try your suggestion when i get home tonight. thanks for
helping to clarify.|||Hi.

I have tried your suggestion. I am not getting the following:

Server: Msg 107, Level 16, State 2, Line 1
The column prefix 't1' does not match with a table name or alias name used in the query.

The SQL i used was as you suggested:

SELECT t1.*,(SELECT COUNT(*) FROM PersonalPhotos t1 WHERE PersonalPhotos.photo_date<t1.photo_date) nums
FROM PersonalPhotos
ORDER BY PersonalPhotos.photo_date

I have also tried variations on this select statement:

SELECT t1.*,(SELECT COUNT(*) FROM PersonalPhotos t1 WHERE PersonalPhotos.photo_date<t1.photo_date) nums
FROM PersonalPhotos t1
ORDER BY PersonalPhotos.photo_date

The above throws the following error:

Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'PersonalPhotos' does not match with a table name or alias name used in the query.

I will keep trying (i seem to not be having much luck with this SQL) and will let you know if i stumble
on the answer. In the mean time, if you have more suggestions, I would appreciate it.

thank you.|||OK, this suggestion comes with Terri's Golden Guarantee that it will not generate an error:

SELECTPersonalPhotos.*,(SELECT COUNT(*) FROM PersonalPhotos t1 WHERE PersonalPhotos.photo_date<t1.photo_date) nums
FROM PersonalPhotos
ORDER BY PersonalPhotos.photo_date

Note that the t1.* was replaced with a PersonalPhotos.*, because this is the table name being referred to in the FROM clause (and is what threw me off on my first reply).|||thank you terri! you rock. that one did the trick. I have one last tiny question :)
this select statement now does exactly what i want, but i need the numbers to
read 1 - N for the current user, not all users. Right now, the select is for all users
and, as a result, a user may see (9,10,11,12,13,14) next to his six pictures. My
user column is "user_name". also, how do i get the count to start at 1 (not zero)
for each user? so:

1) how do i get the count to be user-specific?
2) how do i get the count to start at 1, not zero?

thanks.|||You can use a query like this:

SELECT PersonalPhotos.*,(SELECT COUNT(*)+1 FROM PersonalPhotos t1 WHERE PersonalPhotos.photo_date<t1.photo_date AND PersonalPhotos.user_name = t1.user_name) nums
FROM PersonalPhotos
ORDER BY PersonalPhotos.photo_date

I need to add that typically this sort of thing (adding row numbers) is done on the front end, where it is much less intensive. SQL Server has to work pretty hard to execute this query, as I believe it is doing an extra SELECT statement for each row in your table.|||hi. thanks. this solution now works.

i put the following code in my stored procedure (with a few changes):

BEGIN
SELECT PersonalPhotos.user_name,(SELECT COUNT(*)+1 FROM PersonalPhotos t1 WHERE PersonalPhotos.photo_date<t1.photo_date AND PersonalPhotos.user_name = t1.user_name) nums
FROM PersonalPhotosWHERE user_name = Context.User.Identity.Name.ToString()
ORDER BY PersonalPhotos.photo_dateDESC
END

i am assuming it returns a "nums" column that can be read by ASP. In the HTML,
I have the following line in my GridView control:

<asp:BoundField HeaderText="Number"DataField="nums" ReadOnly="True" HeaderStyle-HorizontalAlign="Center" ItemStyle-HorizontalAlign="Center" /
and, get the following error:

A field or property with the name 'nums' was not found on the selected data source.

how do i get the created "nums" column to appear next to the pics on the client?

thanks again.|||

thanks. i took your advice and am currently figuring this out for the client.

thanks for all your help!

Nulling text data

I have a table that contains text data(XML formatted code). We have decided
on a strategy to archive some of this data and null it out. Out of a 220GB
database, this history table is 153GB. After setting the text field to null,
we haven't realized any space improvements. We reindex often. The previous
DBA suggested copying the rows out and renaming the destination table but I'm
sceptical that I can finish that in any conceivable downtime due to the row
count and general query performance when accessing text columns. Any ideas
on how to reclaim this space?On SQL 2000, the best thing to do is to bcp out/in to reclaim the space.
On SQL 2005, you could use index defrag to reclaim the space.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:BA149A27-1E2F-460A-A798-E4061FCDD4E4@.microsoft.com...
>I have a table that contains text data(XML formatted code). We have
>decided
> on a strategy to archive some of this data and null it out. Out of a 220GB
> database, this history table is 153GB. After setting the text field to
> null,
> we haven't realized any space improvements. We reindex often. The
> previous
> DBA suggested copying the rows out and renaming the destination table but
> I'm
> sceptical that I can finish that in any conceivable downtime due to the
> row
> count and general query performance when accessing text columns. Any
> ideas
> on how to reclaim this space?|||use DBCC SHRINKDATABASE command
"wei xiao [MSFT]" <weix@.online.microsoft.com> wrote in message
news:OMJ0736oFHA.3756@.TK2MSFTNGP09.phx.gbl...
> On SQL 2000, the best thing to do is to bcp out/in to reclaim the space.
> On SQL 2005, you could use index defrag to reclaim the space.
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://blogs.msdn.com/weix
>
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:BA149A27-1E2F-460A-A798-E4061FCDD4E4@.microsoft.com...
> >I have a table that contains text data(XML formatted code). We have
> >decided
> > on a strategy to archive some of this data and null it out. Out of a
220GB
> > database, this history table is 153GB. After setting the text field to
> > null,
> > we haven't realized any space improvements. We reindex often. The
> > previous
> > DBA suggested copying the rows out and renaming the destination table
but
> > I'm
> > sceptical that I can finish that in any conceivable downtime due to the
> > row
> > count and general query performance when accessing text columns. Any
> > ideas
> > on how to reclaim this space?
>

Nulling text data

I have a table that contains text data(XML formatted code). We have decided
on a strategy to archive some of this data and null it out. Out of a 220GB
database, this history table is 153GB. After setting the text field to null,
we haven't realized any space improvements. We reindex often. The previous
DBA suggested copying the rows out and renaming the destination table but I'm
sceptical that I can finish that in any conceivable downtime due to the row
count and general query performance when accessing text columns. Any ideas
on how to reclaim this space?
On SQL 2000, the best thing to do is to bcp out/in to reclaim the space.
On SQL 2005, you could use index defrag to reclaim the space.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:BA149A27-1E2F-460A-A798-E4061FCDD4E4@.microsoft.com...
>I have a table that contains text data(XML formatted code). We have
>decided
> on a strategy to archive some of this data and null it out. Out of a 220GB
> database, this history table is 153GB. After setting the text field to
> null,
> we haven't realized any space improvements. We reindex often. The
> previous
> DBA suggested copying the rows out and renaming the destination table but
> I'm
> sceptical that I can finish that in any conceivable downtime due to the
> row
> count and general query performance when accessing text columns. Any
> ideas
> on how to reclaim this space?
|||use DBCC SHRINKDATABASE command
"wei xiao [MSFT]" <weix@.online.microsoft.com> wrote in message
news:OMJ0736oFHA.3756@.TK2MSFTNGP09.phx.gbl...
> On SQL 2000, the best thing to do is to bcp out/in to reclaim the space.
> On SQL 2005, you could use index defrag to reclaim the space.
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://blogs.msdn.com/weix
>
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:BA149A27-1E2F-460A-A798-E4061FCDD4E4@.microsoft.com...
220GB[vbcol=seagreen]
but
>

Nulling text data

I have a table that contains text data(XML formatted code). We have decided
on a strategy to archive some of this data and null it out. Out of a 220GB
database, this history table is 153GB. After setting the text field to null
,
we haven't realized any space improvements. We reindex often. The previous
DBA suggested copying the rows out and renaming the destination table but I'
m
sceptical that I can finish that in any conceivable downtime due to the row
count and general query performance when accessing text columns. Any ideas
on how to reclaim this space?On SQL 2000, the best thing to do is to bcp out/in to reclaim the space.
On SQL 2005, you could use index defrag to reclaim the space.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:BA149A27-1E2F-460A-A798-E4061FCDD4E4@.microsoft.com...
>I have a table that contains text data(XML formatted code). We have
>decided
> on a strategy to archive some of this data and null it out. Out of a 220GB
> database, this history table is 153GB. After setting the text field to
> null,
> we haven't realized any space improvements. We reindex often. The
> previous
> DBA suggested copying the rows out and renaming the destination table but
> I'm
> sceptical that I can finish that in any conceivable downtime due to the
> row
> count and general query performance when accessing text columns. Any
> ideas
> on how to reclaim this space?|||use DBCC SHRINKDATABASE command
"wei xiao [MSFT]" <weix@.online.microsoft.com> wrote in message
news:OMJ0736oFHA.3756@.TK2MSFTNGP09.phx.gbl...
> On SQL 2000, the best thing to do is to bcp out/in to reclaim the space.
> On SQL 2005, you could use index defrag to reclaim the space.
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://blogs.msdn.com/weix
>
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:BA149A27-1E2F-460A-A798-E4061FCDD4E4@.microsoft.com...
220GB[vbcol=seagreen]
but[vbcol=seagreen]
>

NULL+ x = NULL problem

Hi there,
Is there any setting in SQL that can disable this silly logic.
For example, in a number cleaning query I want to combine the Code and the Number

The problem is that I either the code or the number is null, I lose the number:


update tbl
set Teleh = CodeH + TeleH


I know that I can split this and check if one is null, both null, both non-null but i am sure there must be an easier way such as Accesses & operator

The way that I am using now does this, but i'm not sure if this is the best approach

update tbl
set Teleh = isnull(Codeh,'') + isnull(Teleh,'')


The problem with this is that the result is not stored as null, but as '' length 0The problem with this is that the result is not stored as null, but as '' length 0but that's exactly what you wanted!!

or are you saying that if they are both null then you want the result null?

in that case (pun intended),update tbl
set Teleh = case when CodeH is null
and TeleH is null
then null
else coalesce(CodeH,'')
+coalesce(TeleH,'')
end|||Sorry
Often, the entire number is stored in CodeH and Teleh is empty
So if a update it to Codeh + Teleh, I loose the correct number in Codeh

NULL values - finding an alternative to the IIF statement

since the IIF statement evaluates both the true and false conditions
regardless... I need to find a replacement to it...
my code is:
=IIF(Fields!SOURCE_TRANSACTION_KEY.Value = nothing, "", Fields!
SOURCE_TRANSACTION_KEY.Value.TOSTRING.SUBSTRING(3, 2))
I get the #ERROR value in my report for values that are null since the
SUBSTRING of a NULL value generates an error.
I've tried CHOOSE and the SWITCH and they behaved as IIF.
I do not want to handle this on the server side.
Any ideas?
Thanksyou're really better off modifying the query code to replace NULLS with blank
strings before the rpt code even looks at it. anything else will be an epic
kludge.
"roy@.mgk.com" wrote:
> since the IIF statement evaluates both the true and false conditions
> regardless... I need to find a replacement to it...
> my code is:
> =IIF(Fields!SOURCE_TRANSACTION_KEY.Value = nothing, "", Fields!
> SOURCE_TRANSACTION_KEY.Value.TOSTRING.SUBSTRING(3, 2))
> I get the #ERROR value in my report for values that are null since the
> SUBSTRING of a NULL value generates an error.
> I've tried CHOOSE and the SWITCH and they behaved as IIF.
> I do not want to handle this on the server side.
> Any ideas?
> Thanks
>|||On Sep 26, 5:20 pm, Carl Henthorn
<CarlHenth...@.discussions.microsoft.com> wrote:
> you're really better off modifying the query code to replace NULLS with blank
> strings before the rpt code even looks at it. anything else will be an epic
> kludge.
> "r...@.mgk.com" wrote:
> > since the IIF statement evaluates both the true and false conditions
> > regardless... I need to find a replacement to it...
> > my code is:
> > =IIF(Fields!SOURCE_TRANSACTION_KEY.Value = nothing, "", Fields!
> > SOURCE_TRANSACTION_KEY.Value.TOSTRING.SUBSTRING(3, 2))
> > I get the #ERROR value in my report for values that are null since the
> > SUBSTRING of a NULL value generates an error.
> > I've tried CHOOSE and the SWITCH and they behaved as IIF.
> > I do not want to handle this on the server side.
> > Any ideas?
> > Thanks
argh! was hoping to avoid that...|||And what about this?
=IIF(Fields!SOURCE_TRANSACTION_KEY.Value = nothing, "", Mid ("" &
Fields!SOURCE_TRANSACTION_KEY.Value,3, 2)
Hope this helps.
<roy@.mgk.com> escribió en el mensaje
news:1190895795.174272.317940@.g4g2000hsf.googlegroups.com...
> On Sep 26, 5:20 pm, Carl Henthorn
> <CarlHenth...@.discussions.microsoft.com> wrote:
>> you're really better off modifying the query code to replace NULLS with
>> blank
>> strings before the rpt code even looks at it. anything else will be an
>> epic
>> kludge.
>> "r...@.mgk.com" wrote:
>> > since the IIF statement evaluates both the true and false conditions
>> > regardless... I need to find a replacement to it...
>> > my code is:
>> > =IIF(Fields!SOURCE_TRANSACTION_KEY.Value = nothing, "", Fields!
>> > SOURCE_TRANSACTION_KEY.Value.TOSTRING.SUBSTRING(3, 2))
>> > I get the #ERROR value in my report for values that are null since the
>> > SUBSTRING of a NULL value generates an error.
>> > I've tried CHOOSE and the SWITCH and they behaved as IIF.
>> > I do not want to handle this on the server side.
>> > Any ideas?
>> > Thanks
> argh! was hoping to avoid that...
>

Null Values

I set up a new SQL database file, in that file I allowed nulls, When I went through code to save the record, the exception is saying it doesnt allow nulls.

Before I get to involved with SQL, is it a bad practice to use nulls?

If it is what do you enter in place of the null value,

which will lead to more code, right?

Davids Learning SQL

Having and using nulls is not bad practice.....

Your problem maybe that your trying to have a null on your key field........

|||

No, the primary key did have a number assigned that did not exist.

I did a msgbox to throw the value back to me just to see in case and it was the number I chose.

I intentionally did not enter a value into a int field to see what would happen.

I opened the database file, and allow nulls is checked for the field and the field is not the primary

Any thoughts?

Davids Learning

|||Is the field a date field?|||I remember running in to similar problem, but I am not sure if it is exactly the same. The work around (or the solution for that matter) was I opened the DataSet containing my dataTable. Clicked on the field in that dataTable and in properties, I Changed 'NullValue' property to (Nothing). By default, it's set to (Throw Exception), which forces the dataSet to throw exception even if its AllowDBNull is set to true. See if that solves your issue.|||

is there a way to default to "Nothing" instead of throwing an exception?

I wont be able to try your suggestion until Monday, when I get back to my server at work.

Thanks

Davids Learning

|||Move the thread in this forum in order to get better answers.|||That depends, what does nothing mean in your case ? Normally nothing is equal to NULL when "nothing" is entered.

Jens K. Suessmeyer.

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

Hey, sorry for the delay, been having ticker problems, Im back now

What I was getting at was to have the designer default or not check for null instead of throwing exceptions.

There is not but 1 field that needs not to be null and thats the primary field and I am taking care of that.

Is there a way to accomplish that.

The tables that I am dealing with contain a lot of fields and it will take some time to go through and change.

Thanks Again

Davids Learning

|||Hi,

what about setting a default value in SQL Server then ?

Jens K. Suessmeyer.

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

I need to post what I did to mask the problem

I went into the table designer in VB and changed how each column handles nulls, basically setting them to not throw an exception on dbnulls.

I didnt want values in some fields intentionally because of other factor, like dates, if I set a default date in an expiration field(that would be bad).

I will on the other hand have to watch and plan on handling nulls now, and thats alright.

Thanks

Davids Learning

|||To me, it looked like a VB bug, but I will leave the judgement for you guys. If I allow nulls in my database table and create a typed DataSet from that table, the DataSet throws an error when it encounters a null value. If the DataSet's AllowDBNull is set to true, then it should default to 'Nothing' for NullValue property. It does not seem logical to me (Allow DBNull, but throw an error!!!). For those who want to reproduce the problem, use bound DataGridview with some fields that allow DBNull and try to save without entering data in those fields.|||

Glad to see someone in the same boat

Davids Learning

Null Instance

So I have a little snippet of code as follows:

_nsServer = smo.Server(nsServerName);
_notificationServices = _nsServer.NotificationServices;
_nmoInstance = _notificationServices.Instances[instanceName];
_nmoApplication = _nmoInstance.Applications[applicationName];

This is in a class that's in a library. When I consume it with a console app, everything seems to work fine, but now I'm trying to wrap it in a web service, and I'm running into a lot of issues. Under the web service, after I initialize _nmoInstance in the above code, it's still null. In fact, the Instances property of _notificationServices is an empty collection. Anyone know why that might be? I have a feeling it might be DB permissions related, but I can't pin it down. For the record, I've granted access to the Network Service account for the database of the instance in question, and all the strings (nsServerName, instanceName, applicationName) have the correct values.

Thanks in advance,

-Francis

Never mind, was a DB permissions thing. I've got to narrow down exactly what I needed, but I've got it working now.

NULL in Temp Tables

Forum,

I am looking at another report code (constructed by another user) and wanted to know why the user coded the word NULL after each field in the Temp tables. What does the NULL tell me and why would the user use it? Sorry for the Newb question ... I'm still learning the language and could not find any information in the help files.

Report Code

/* Billing Status */
SET NOCOUNT ON
CREATE TABLE #Appt( FacilityId int NULL,
Visit datetime NULL,
OwnerId int NULL,
DoctorId int NULL,
CompanyId int NULL,
ApptSetId int NULL,
CasesId int NULL
)

CREATE TABLE #Temp( Visit datetime NULL,
PatientId varchar(15) NULL,
PatientName varchar(90) NULL,
DoctorName varchar(60) NULL,
ApprovalResults text NULL,
Description varchar(255) NULL,
Status varchar(255) NULL,
LastFiledDate datetime NULL,
PatientBal money,
InsuranceBal money,
InsuranceName varchar(50) NULL
)

Jeff:

I see that most of the columns have the explicity NULL specification and that these two columns do not:

PatientBal money,
InsuranceBal money,

It is possible that you are in a situation in which column defaults have been designated as NOT NULL and that in this case explicit NULL specifications are necessary to allow nulls for these columns. In any case, the author is explicitly describing each of these columns as nullable. Try looking up ANSI_NULL_DFLT_ON and ANSI_NULL_DFLT_OFF in books online.

|||Another thing to check is SP_DBOPTION and look at the setting for 'ANSI null default'|||

Null just means that the value is not known, (or that it is left blank intentionally is another common, though less right, usage). The programmer just wanted to make sure that if a value was NULL when data is being put into the temp tables it would not cause an error.

This is not really good practice, unless it is actually reasonable that values can be NULL, since NULLs really increases the complexity of working with data, but if the data allows nulls, then it is acceptable.