Changing a null to another value in select?

Hi all,
I have a result set coming back with one item that is null, because I am
trying to sort by this column this result is always at the top of the result
set, however I need them to be at the bottom of the result set, I cant
change the ordering because then the other results will no longer be
alphabetically sorted. Is there a way to change a 'null' to something else
if it is returned in a result set? My hope was perhaps to set it to be 10
Z's (ie, zzzzzzzzzz) so that this would always (unless a REAL oddity
happens) be at the bottom?
Possible?
Regards
RobSelect isnull(mycolumn, 'zzzzzzzzzz') from MyTable
Hope this helps
Chris
"Rob Meade" <robb.meade@.NO-SPAM.kingswoodweb.net> wrote in message
news:4BbJd.17892$GG1.5654@.text.news.blueyonder.co.uk...
> Hi all,
> I have a result set coming back with one item that is null, because I am
> trying to sort by this column this result is always at the top of the
> result
> set, however I need them to be at the bottom of the result set, I cant
> change the ordering because then the other results will no longer be
> alphabetically sorted. Is there a way to change a 'null' to something
> else
> if it is returned in a result set? My hope was perhaps to set it to be 10
> Z's (ie, zzzzzzzzzz) so that this would always (unless a REAL oddity
> happens) be at the bottom?
> Possible?
> Regards
> Rob
>|||"Chris, Master of All Things Insignificant" wrote ...

> Select isnull(mycolumn, 'zzzzzzzzzz') from MyTable
Hi Chris,
Thanks for the reply. I managed something similar myself, here's my view:
SELECT '1' AS ViewOrder1, '1' AS ViewOrder2, a.PageID, a.NGID,
a.PageName, a.PageKeywords, a.PageDisplayOrder, a.PageFooterDisplayOrder,
a.PageContent, a.PageIsLive, a.PageIsDefault,
a.PageIsStatic, a.PageIsExternal, a.PageIsKB, a.PageIsSearchResults,
a.PageIsNews,
a.PageIsPromoted, a.PageIsExcludedFromNavigation,
a.PageIsIncludedInFooter, a.PageURL, a.DisplayPrint, a.DisplayEmail,
a.DateCreated,
a.DateLastUpdated, b.NGName, b.NGDescription,
b.NGDisplayOrder, b.SectionID, c.SectionName, c.SectionDesc,
c.SectionDisplayOrder,
c.SectionPlacement, d .WebsiteID, d .WebsiteName,
e.Forename + ' ' + e.Surname AS PageCreatedBy,
f.Forename + ' ' + f.Surname AS PageLastUpdatedBy
FROM tblPages AS a LEFT JOIN
tblNavigationGroups AS b ON a.NGID = b.NGID LEFT JOIN
tblSections AS c ON b.SectionID = c.SectionID LEFT
JOIN
tblWebsites AS d ON c.WebsiteID = d .WebsiteID LEFT
JOIN
tblUsers AS e ON a.CreatedBy = e.UserID LEFT JOIN
tblUsers AS f ON a.LastUpdatedBy = f.UserID
WHERE b.SectionID IS NOT NULL AND b.SectionID <> '0' AND a.NGID <> '0'
AND b.NGIsNoGroup = '0'
UNION ALL
SELECT '1' AS ViewOrder1, '2' AS ViewOrder2, a.PageID, a.NGID,
a.PageName, a.PageKeywords, a.PageDisplayOrder, a.PageFooterDisplayOrder,
a.PageContent, a.PageIsLive, a.PageIsDefault,
a.PageIsStatic, a.PageIsExternal, a.PageIsKB, a.PageIsSearchResults,
a.PageIsNews,
a.PageIsPromoted, a.PageIsExcludedFromNavigation,
a.PageIsIncludedInFooter, a.PageURL, a.DisplayPrint, a.DisplayEmail,
a.DateCreated,
a.DateLastUpdated, b.NGName, b.NGDescription,
b.NGDisplayOrder, b.SectionID, c.SectionName, c.SectionDesc,
c.SectionDisplayOrder,
c.SectionPlacement, d .WebsiteID, d .WebsiteName,
e.Forename + ' ' + e.Surname AS PageCreatedBy,
f.Forename + ' ' + f.Surname AS PageLastUpdatedBy
FROM tblPages AS a LEFT JOIN
tblNavigationGroups AS b ON a.NGID = b.NGID LEFT JOIN
tblSections AS c ON b.SectionID = c.SectionID LEFT
JOIN
tblWebsites AS d ON c.WebsiteID = d .WebsiteID LEFT
JOIN
tblUsers AS e ON a.CreatedBy = e.UserID LEFT JOIN
tblUsers AS f ON a.LastUpdatedBy = f.UserID
WHERE b.SectionID IS NOT NULL AND b.SectionID <> '0' AND a.NGID <> '0'
AND b.NGIsNoGroup = '1'
UNION ALL
SELECT '3' AS ViewOrder1, '1' AS ViewOrder2, a.PageID, a.NGID,
a.PageName, a.PageKeywords, a.PageDisplayOrder, a.PageFooterDisplayOrder,
a.PageContent, a.PageIsLive, a.PageIsDefault,
a.PageIsStatic, a.PageIsExternal, a.PageIsKB, a.PageIsSearchResults,
a.PageIsNews,
a.PageIsPromoted, a.PageIsExcludedFromNavigation,
a.PageIsIncludedInFooter, a.PageURL, a.DisplayPrint, a.DisplayEmail,
a.DateCreated,
a.DateLastUpdated, b.NGName, b.NGDescription,
b.NGDisplayOrder, b.SectionID, 'ZZZZZZZZZZ', c.SectionDesc,
c.SectionDisplayOrder,
c.SectionPlacement, d .WebsiteID, d .WebsiteName,
e.Forename + ' ' + e.Surname AS PageCreatedBy,
f.Forename + ' ' + f.Surname AS PageLastUpdatedBy
FROM tblPages AS a LEFT JOIN
tblNavigationGroups AS b ON a.NGID = b.NGID LEFT JOIN
tblSections AS c ON b.SectionID = c.SectionID LEFT
JOIN
tblWebsites AS d ON c.WebsiteID = d .WebsiteID LEFT
JOIN
tblUsers AS e ON a.CreatedBy = e.UserID LEFT JOIN
tblUsers AS f ON a.LastUpdatedBy = f.UserID
WHERE (b.SectionID IS NULL OR
b.SectionID = '0') AND a.NGID <> '0'
UNION ALL
SELECT '4' AS ViewOrder1, '1' AS ViewOrder2, a.PageID, a.NGID,
a.PageName, a.PageKeywords, a.PageDisplayOrder, a.PageFooterDisplayOrder,
a.PageContent, a.PageIsLive, a.PageIsDefault,
a.PageIsStatic, a.PageIsExternal, a.PageIsKB, a.PageIsSearchResults,
a.PageIsNews,
a.PageIsPromoted, a.PageIsExcludedFromNavigation,
a.PageIsIncludedInFooter, a.PageURL, a.DisplayPrint, a.DisplayEmail,
a.DateCreated,
a.DateLastUpdated, b.NGName, b.NGDescription,
b.NGDisplayOrder, b.SectionID, 'ZZZZZZZZZZ', c.SectionDesc,
c.SectionDisplayOrder,
c.SectionPlacement, d .WebsiteID, d .WebsiteName,
e.Forename + ' ' + e.Surname AS PageCreatedBy,
f.Forename + ' ' + f.Surname AS PageLastUpdatedBy
FROM tblPages AS a LEFT JOIN
tblNavigationGroups AS b ON a.NGID = b.NGID LEFT JOIN
tblSections AS c ON b.SectionID = c.SectionID LEFT
JOIN
tblWebsites AS d ON c.WebsiteID = d .WebsiteID LEFT
JOIN
tblUsers AS e ON a.CreatedBy = e.UserID LEFT JOIN
tblUsers AS f ON a.LastUpdatedBy = f.UserID
WHERE (b.SectionID IS NULL OR
b.SectionID = '0') AND a.NGID = '0'
As you can see the last 2 sections of this (where viewOrder1 is either 3 or
4 etc) I have inserted the 'ZZZZZZZZZZ' as the section name...
This does work, but feels a bit flakey...if someone was to actually create a
'section' in my application called 'ZZZZZZZZZZZ' it would obviously cause
problems...
I originally had in my ASP code 4 statements which went off and got the
respective results for each case, but I thought making just one hit would be
better, now I face a different problem...
Any ideas anyone?
Regards
Rob|||Use a CASE expression in the ORDER BY clause.
Example:
use northwind
go
select
customerid,
companyname,
region
from
customers
order by
case when region is null then 1 else 0 end asc,
customerid
go
AMB
"Rob Meade" wrote:

> Hi all,
> I have a result set coming back with one item that is null, because I am
> trying to sort by this column this result is always at the top of the resu
lt
> set, however I need them to be at the bottom of the result set, I cant
> change the ordering because then the other results will no longer be
> alphabetically sorted. Is there a way to change a 'null' to something els
e
> if it is returned in a result set? My hope was perhaps to set it to be 10
> Z's (ie, zzzzzzzzzz) so that this would always (unless a REAL oddity
> happens) be at the bottom?
> Possible?
> Regards
> Rob
>
>|||"Alejandro Mesa" wrote ...

> Use a CASE expression in the ORDER BY clause.

> use northwind
(wishing now I'd not deleted that recently)

> order by
> case when region is null then 1 else 0 end asc,
> customerid
That looks like what I want, I think, I cant quite work out the 1 / 0 thing
though - whats that actually going to do - in words etc...
ie, does that make region = 1 if it = null, and = 0 if its not null?
Thanks for the reply.
Regards
Rob|||"Alejandro Mesa" wrote ...

> Use a CASE expression in the ORDER BY clause.
>
[snip]
Hi Alejandro,
I was unable to get it to work in the ORDER BY clause, but your reply did
make me go and investigate the use of CASE expressions, I've now popped it
in the SELECT statement and its working perfectly.
Thank you.
Regards
Rob|||Rob,
ORDER BY
CASE WHEN itemColumn IS NULL THEN 1 ELSE 0 END,
itemColumn
Steve Kass
Drew University
Rob Meade wrote:

>Hi all,
>I have a result set coming back with one item that is null, because I am
>trying to sort by this column this result is always at the top of the resul
t
>set, however I need them to be at the bottom of the result set, I cant
>change the ordering because then the other results will no longer be
>alphabetically sorted. Is there a way to change a 'null' to something else
>if it is returned in a result set? My hope was perhaps to set it to be 10
>Z's (ie, zzzzzzzzzz) so that this would always (unless a REAL oddity
>happens) be at the bottom?
>Possible?
>Regards
>Rob
>
>|||"Steve Kass" wrote ...

> ORDER BY
> CASE WHEN itemColumn IS NULL THEN 1 ELSE 0 END,
> itemColumn
Hi Steve,
Could you explain to me what the 1 and 0 are/do please?
Regards
Rob|||Imagine that you put this in the SELECT list instead:
SELECT ... CASE WHEN itemColumn IS NULL THEN 1 ELSE 0 END AS sortcol
For a row with NULL in itemColumn, the expression will return the value 1. I
f there is a non-null
value in itemColumn, the expression will return the value 0. So, say you hav
e some rows (sortcol is
the one produced by above expression)
ItemColumn SortCol
23 0
NULL 1
1 0
NULL 1
Now, imagine that you do:
ORDER BY SortCol, ItemColumn
Get it?
The "short cut" Steve did is to put the expression in the ORDER BY clause in
stead of in the SELECT
clause. Logically, the columns didn't "materialize" before the sort operatio
n...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Rob Meade" <robb.meade@.NO-SPAM.kingswoodweb.net> wrote in message
news:8roJd.18310$GG1.8683@.text.news.blueyonder.co.uk...
> "Steve Kass" wrote ...
>
> Hi Steve,
> Could you explain to me what the 1 and 0 are/do please?
> Regards
> Rob
>|||"Tibor Karaszi" wrote ...

> ItemColumn SortCol
> 23 0
> NULL 1
> 1 0
> NULL 1
> Now, imagine that you do:
> ORDER BY SortCol, ItemColumn
> Get it?
> The "short cut" Steve did is to put the expression in the ORDER BY clause
instead of in the SELECT
> clause. Logically, the columns didn't "materialize" before the sort
operation...
Hi Tibor,
Thanks for your reply...
I get it now, the 1 / 0 were for the ordering etc, as where I was using
1,2,3,4 previously...When I first looked at it I was wondering whether they
could only 'ever' be those values or something - its not something I've come
across/used before..
Thanks for the explanation.
Regards
Rob

Changing a field value to NULL

I know the trick of using ctrl-0 to change it individually, but is there a
syntax that I can use as in the UPDATE to change a BUNCH of fields at the
same time. When changing fields I usually use the
UPDATE <table>
SET <field> = <parameter>
WHERE <field> = <parameter>
How do I do this to set a field as null where the parameter is 'No
Response'?
TIA
JCNever mind. I figured out what I was doing wrong. Dumb mistake.
"JOHN HARRIS" <harris1113@.fake.com> wrote in message
news:4F1F5AC7-E93B-4E0D-BA69-13AE90A90F71@.microsoft.com...
>I know the trick of using ctrl-0 to change it individually, but is there a
>syntax that I can use as in the UPDATE to change a BUNCH of fields at the
>same time. When changing fields I usually use the
> UPDATE <table>
> SET <field> = <parameter>
> WHERE <field> = <parameter>
> How do I do this to set a field as null where the parameter is 'No
> Response'?
> TIA
> JC

change to not allow nulls

Is there a *simple* way to change a collumn from allowing null to not
null?

I just unchecked "allow nulls" in EM and the SQL it generates to do
this one thing is astonishing, create table, drop FKs, copy data, drop
table, rename new table, rebuild FKs...

I'm saving a lot of these changes to run on another database at a later
date but would rather not require a terrabyte device to store the
script :-)I recommend you use TSQL scripts to make structure changes. You'll have
much more control and visibility over what happens and you'll be able
to test your scripts out before you go live with the change.

You can change nullability with an ALTER TABLE... ALTER COLUMN
statement but if the column is part of an index or constraint then
you'll have to drop that before you can make the change. That means
you'll also have to drop foreign keys that reference the column. EM
tries to make this easier by generating the script for you, so you
could save that script and take it as a starting point.

Another option that may be worth trying:
1. create a new column, populate it from the previous nullable one and
make it non-nullable
2. add constraints and indexes
3. drop the old column
4. rename the column you added

Possibly this method may incur less impact and downtime but that would
depend quite a lot on how the column is used and on other factors too
such as the size of the data and whether an existing index on the
column is clustered. Test it out and see.

--
David Portas
SQL Server MVP
--|||David Portas wrote:
> I recommend you use TSQL scripts to make structure changes. You'll
have
> much more control and visibility over what happens and you'll be able
> to test your scripts out before you go live with the change.

I am doing, I use EM to generate the scripts for some things if there's
a shed load to do in a table or if I don't know how to do something
(such as change a null column to a not null :-)

> You can change nullability with an ALTER TABLE... ALTER COLUMN
> statement but if the column is part of an index or constraint then
> you'll have to drop that before you can make the change. That means
> you'll also have to drop foreign keys that reference the column. EM
> tries to make this easier by generating the script for you, so you
> could save that script and take it as a starting point.

Thanks, it's just a description column so alter table should do it.|||Trevor Best (googlegroups@.besty.org.uk) writes:
> Is there a *simple* way to change a collumn from allowing null to not
> null?
> I just unchecked "allow nulls" in EM and the SQL it generates to do
> this one thing is astonishing, create table, drop FKs, copy data, drop
> table, rename new table, rebuild FKs...
> I'm saving a lot of these changes to run on another database at a later
> date but would rather not require a terrabyte device to store the
> script :-)

That is not the main problem with the scripts generated by Enterprise
Manager. The main problem is that if something goes wrong in the
middle of those scripts, you may end of with halfly-modified database,
and you may lose foreign keys forever. This is because the scripts
has a poor transaction scope.

Overall, I strongly discourage using the table designer in Enterprise
Manager to change tables. You can take the script as a starting point,
but there is a lot of problems to sort out. Some standard remedies
to apply:

o Remove all BEGIN and COMMIT TRANSACTION but the first BEGIN and last
COMMIT.
o Put all statements in EXEC('...'). (Except for calls to stored
procedures.
o Remove all GO.
o On all re-addition of foreign keys, insert WITH CHECK before
CHECK. (Yes, WITH CHECK CHCEK.)

For this particular case, adding a new column and then dropping the
old one may be a good idea, as David suggested.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

change timestamp from null to not null

Hi,
Would dropping the column and recreating it with the 'not
null' clause be the advisable way to do it?
Many thanks,
Osk

>--Original Message--
>Hi,
>I have a table with a timestamp column which allows nulls.
>I wanted to
>ALTER TABLE [dbo].[MyTbl] ALTER COLUMN [MyClmn] [timestamp]
>NOT NULL
>but it gives me msg 4927 - Cannot alter column 'MyClmn' to
>be data type timestamp.
>Is there any elegant/easy way around this?
>--
>Many thanks,
>Osk
>.
>
Osk wrote:[vbcol=seagreen]
> Hi,
> Would dropping the column and recreating it with the 'not
> null' clause be the advisable way to do it?
>
If you need to preserve the data in the table, create a second table,
copy the data, change the first table DDL and then migrate the data
back.
David Gugick
Imceda Software
www.imceda.com
|||Hi David,
Thanks for your answer. Can you please tell me why would I
lose any data except the old timestamp values (which seems
to be inevitable anyway) by dropping and
redefining/recreating the timestamp column?
Many thanks,
Osk

>--Original Message--
>Osk wrote:
>If you need to preserve the data in the table, create a
second table,
>copy the data, change the first table DDL and then migrate
the data
>back.
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>
|||anonymous@.discussions.microsoft.com wrote:
> Hi David,
> Thanks for your answer. Can you please tell me why would I
> lose any data except the old timestamp values (which seems
> to be inevitable anyway) by dropping and
> redefining/recreating the timestamp column?
>
I guess it really doesn't matter if you lose the data in the column.
David Gugick
Imceda Software
www.imceda.com
|||Yes, I lose the timestamp data in the column, but the rest
of the table data should be remaining intact. And from this
perspective the result of using either the drop-
redefine/recreate method or the export/import method would
be the same, except that the latter takes more effort to
implement.
Thanks,
Osk

>--Original Message--
>anonymous@.discussions.microsoft.com wrote:
>I guess it really doesn't matter if you lose the data in
the column.
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>

change timestamp from null to not null

Hi,
I have a table with a timestamp column which allows nulls.
I wanted to
ALTER TABLE [dbo].[MyTbl] ALTER COLUMN [MyClmn] [timestamp]
NOT NULL
but it gives me msg 4927 - Cannot alter column 'MyClmn' to
be data type timestamp.
Is there any elegant/easy way around this?
--
Many thanks,
OskHi,
Would dropping the column and recreating it with the 'not
null' clause be the advisable way to do it?
--
Many thanks,
Osk
>--Original Message--
>Hi,
>I have a table with a timestamp column which allows nulls.
>I wanted to
>ALTER TABLE [dbo].[MyTbl] ALTER COLUMN [MyClmn] [timestamp]
>NOT NULL
>but it gives me msg 4927 - Cannot alter column 'MyClmn' to
>be data type timestamp.
>Is there any elegant/easy way around this?
>--
>Many thanks,
>Osk
>.
>|||Osk wrote:
> Hi,
> Would dropping the column and recreating it with the 'not
> null' clause be the advisable way to do it?
>
>> --Original Message--
>> Hi,
>> I have a table with a timestamp column which allows nulls.
>> I wanted to
>> ALTER TABLE [dbo].[MyTbl] ALTER COLUMN [MyClmn] [timestamp]
>> NOT NULL
>> but it gives me msg 4927 - Cannot alter column 'MyClmn' to
>> be data type timestamp.
>> Is there any elegant/easy way around this?
>> --
>> Many thanks,
>> Osk
>> .
If you need to preserve the data in the table, create a second table,
copy the data, change the first table DDL and then migrate the data
back.
--
David Gugick
Imceda Software
www.imceda.com|||Hi David,
Thanks for your answer. Can you please tell me why would I
lose any data except the old timestamp values (which seems
to be inevitable anyway) by dropping and
redefining/recreating the timestamp column?
--
Many thanks,
Osk
>--Original Message--
>Osk wrote:
>> Hi,
>> Would dropping the column and recreating it with the 'not
>> null' clause be the advisable way to do it?
>>
>> --Original Message--
>> Hi,
>> I have a table with a timestamp column which allows nulls.
>> I wanted to
>> ALTER TABLE [dbo].[MyTbl] ALTER COLUMN [MyClmn] [timestamp]
>> NOT NULL
>> but it gives me msg 4927 - Cannot alter column 'MyClmn' to
>> be data type timestamp.
>> Is there any elegant/easy way around this?
>> --
>> Many thanks,
>> Osk
>> .
>If you need to preserve the data in the table, create a
second table,
>copy the data, change the first table DDL and then migrate
the data
>back.
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>|||anonymous@.discussions.microsoft.com wrote:
> Hi David,
> Thanks for your answer. Can you please tell me why would I
> lose any data except the old timestamp values (which seems
> to be inevitable anyway) by dropping and
> redefining/recreating the timestamp column?
>
I guess it really doesn't matter if you lose the data in the column.
--
David Gugick
Imceda Software
www.imceda.com|||Yes, I lose the timestamp data in the column, but the rest
of the table data should be remaining intact. And from this
perspective the result of using either the drop-
redefine/recreate method or the export/import method would
be the same, except that the latter takes more effort to
implement.
--
Thanks,
Osk
>--Original Message--
>anonymous@.discussions.microsoft.com wrote:
>> Hi David,
>> Thanks for your answer. Can you please tell me why would I
>> lose any data except the old timestamp values (which seems
>> to be inevitable anyway) by dropping and
>> redefining/recreating the timestamp column?
>I guess it really doesn't matter if you lose the data in
the column.
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>

change timestamp from null to not null

Hi,
Would dropping the column and recreating it with the 'not
null' clause be the advisable way to do it?
Many thanks,
Osk

>--Original Message--
>Hi,
>I have a table with a timestamp column which allows nulls.
>I wanted to
>ALTER TABLE [dbo].[MyTbl] ALTER COLUMN [MyClmn] [timestamp]
>NOT NULL
>but it gives me msg 4927 - Cannot alter column 'MyClmn' to
>be data type timestamp.
>Is there any elegant/easy way around this?
>--
>Many thanks,
>Osk
>.
>Osk wrote:[vbcol=seagreen]
> Hi,
> Would dropping the column and recreating it with the 'not
> null' clause be the advisable way to do it?
>
If you need to preserve the data in the table, create a second table,
copy the data, change the first table DDL and then migrate the data
back.
David Gugick
Imceda Software
www.imceda.com|||Hi David,
Thanks for your answer. Can you please tell me why would I
lose any data except the old timestamp values (which seems
to be inevitable anyway) by dropping and
redefining/recreating the timestamp column?
Many thanks,
Osk

>--Original Message--
>Osk wrote:
>If you need to preserve the data in the table, create a
second table,
>copy the data, change the first table DDL and then migrate
the data
>back.
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>|||anonymous@.discussions.microsoft.com wrote:
> Hi David,
> Thanks for your answer. Can you please tell me why would I
> lose any data except the old timestamp values (which seems
> to be inevitable anyway) by dropping and
> redefining/recreating the timestamp column?
>
I guess it really doesn't matter if you lose the data in the column.
David Gugick
Imceda Software
www.imceda.com|||Yes, I lose the timestamp data in the column, but the rest
of the table data should be remaining intact. And from this
perspective the result of using either the drop-
redefine/recreate method or the export/import method would
be the same, except that the latter takes more effort to
implement.
Thanks,
Osk

>--Original Message--
>anonymous@.discussions.microsoft.com wrote:
>I guess it really doesn't matter if you lose the data in
the column.
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>

Change of table property

Hi
May I know how can I change the property of a table in SQL.
In my table, I need to allow one of the field to be able to accept null and
the table is not empty. Please help. Thanks
Christina
ALTER TABLE MyTable
ALTER COLUMN ColABC NVARCHAR(20) NULL
Change the Table and column names to yours, and the datatype to what your
existing column is.
NULL can be specified in ALTER COLUMN to make a NOT NULL column allow null
values, except for columns in PRIMARY KEY constraints
Regards
Mike
"Chris" wrote:

> Hi
> May I know how can I change the property of a table in SQL.
> In my table, I need to allow one of the field to be able to accept null and
> the table is not empty. Please help. Thanks
> Christina

Change of table property

Hi
May I know how can I change the property of a table in SQL.
In my table, I need to allow one of the field to be able to accept null and
the table is not empty. Please help. Thanks
ChristinaALTER TABLE MyTable
ALTER COLUMN ColABC NVARCHAR(20) NULL
Change the Table and column names to yours, and the datatype to what your
existing column is.
NULL can be specified in ALTER COLUMN to make a NOT NULL column allow null
values, except for columns in PRIMARY KEY constraints
Regards
Mike
"Chris" wrote:

> Hi
> May I know how can I change the property of a table in SQL.
> In my table, I need to allow one of the field to be able to accept null an
d
> the table is not empty. Please help. Thanks
> Christina

Change of table property

Hi
May I know how can I change the property of a table in SQL.
In my table, I need to allow one of the field to be able to accept null and
the table is not empty. Please help. Thanks
ChristinaALTER TABLE MyTable
ALTER COLUMN ColABC NVARCHAR(20) NULL
Change the Table and column names to yours, and the datatype to what your
existing column is.
NULL can be specified in ALTER COLUMN to make a NOT NULL column allow null
values, except for columns in PRIMARY KEY constraints
Regards
Mike
"Chris" wrote:
> Hi
> May I know how can I change the property of a table in SQL.
> In my table, I need to allow one of the field to be able to accept null and
> the table is not empty. Please help. Thanks
> Christina

Change NULL values to default in SELECT statement

I have a stored procedure with a SELECT statement, that retrieves 1 row.

SELECT name FROM tblNames WHERE nameID = "1"

I want all the NULL values in that row to be change in some default values.

How do I do this?

You can use the IsNull Statement

SELECT isNull(name,'someDefaultValue') FROM tblNames WHERE nameID = "1"

|||

Perfect, perfect, this works great, so simple, but effective.

Now I can go celebrating Christmas.....thx.

|||

Next problem:

I call the stored procedure in the Data Access Layer via a typed dataset.

My stored procedure SELECTs 11 columns. The execute test in the stored procedure works perfect.

But when I preview the data in the DAL.xsd, I see 22 columns, with the first 11 columns having the right name, but no values and the second 11 columns having wrong names, but the right values.

What goes wrong?

|||

You will have to rename the derived columns in your select statement...

SELECT ISNULL(FirstName,'No Name') [FirstName], ISNULL(LastName,'No Last Name') [LastName]

It also sounds like you may be using SELECT *, remove the star so you don't end up with duplicate column names

Change NULL to other value

Hi,
Are there some functions in Transact-SQL which can change NULL to other
value , like change NULL to 0.SELECT COALESCE(x, 0) FROM (SELECT x = 1 UNION SELECT 4 UNION SELECT NULL) x
--
http://www.aspfaq.com/
(Reverse address to reply.)
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:Omk#8mlqEHA.536@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Are there some functions in Transact-SQL which can change NULL to other
> value , like change NULL to 0.
>|||Yes. Use ISNULL
"ad" wrote:
> Hi,
> Are there some functions in Transact-SQL which can change NULL to other
> value , like change NULL to 0.
>
>

Change NULL to other value

Hi,
Are there some functions in Transact-SQL which can change NULL to other
value , like change NULL to 0.
SELECT COALESCE(x, 0) FROM (SELECT x = 1 UNION SELECT 4 UNION SELECT NULL) x
http://www.aspfaq.com/
(Reverse address to reply.)
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:Omk#8mlqEHA.536@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Are there some functions in Transact-SQL which can change NULL to other
> value , like change NULL to 0.
>
|||Yes. Use ISNULL
"ad" wrote:

> Hi,
> Are there some functions in Transact-SQL which can change NULL to other
> value , like change NULL to 0.
>
>

change null row of roll up query

hi all

i m using rollup statement in my query. it gives me total field with null caption.

is it possible that i can change this null field with caption "total"

thanks & regards

ganesh

If I understand you question you could use following statement:

<your total field expression> as "Total"

Please, show your query

|||

Yes you can.. use the reference as the bellow query

Code Snippet

Declare @.data Table
(
Region Varchar(100),
Country Varchar(100),
Sales float
);

Insert Into @.data Values ('EMEA', 'France', 100);
Insert Into @.data Values ('EMEA', 'France', 200);
Insert Into @.data Values ('EMEA', 'Germany', 56);
Insert Into @.data Values ('EMEA', 'Germany', 12);
Insert Into @.data Values ('EMEA', 'UK', 1256);
Insert Into @.data Values ('EMEA', 'UK', 1212);
Insert Into @.data Values ('APJ', 'Japan', 130);
Insert Into @.data Values ('APJ', 'Japan', 32);
Insert Into @.data Values ('APJ', 'China', 256);
Insert Into @.data Values ('APJ', 'China', 212);
Insert Into @.data Values ('APJ', 'India', 23);
Insert Into @.data Values ('APJ', 'India', 232);
Insert Into @.data Values ('America', 'US', 23);
Insert Into @.data Values ('America', 'US', 23432);
Insert Into @.data Values ('America', 'Canada', 223256);
Insert Into @.data Values ('America', 'Canada', 2122);
Insert Into @.data Values ('America', 'Brazil', 23232);
Insert Into @.data Values ('America', 'Brazil', 223232);

Select
isnull(Case When Country is NULL Then Region + ' Total' Else Region End,'Grand Total') Region,
isnull(Country,'') Country,
Sum(Sales)
From
@.Data
Group By
Region,Country With Rollup

change null field of rollup summary

hi all

i m using sql server 2005. i m using rollup query to get total sum and sum with group within single query. it shows me total amount but with null field . i want to change this null field with "total " caption.

pls suggest how this thing is possible.

thanks and regards

Ganesh

Moved to t-sql forum...|||

That is the way a Rollup query works, and, unfortunately, as far as I know, there is no way to get around it.

Of course, your client side application could easily do that. (After all, presentation is the point of client applications, data storage and retrieval is the point of SQL Server.)

|||

Use the GROUPING function in the SELECT list like:

CASE GROUPING(col1) WHEN 1 THEN 'Total' ELSE Col1 END

See Books Online for more details. Or post your sample query here to get the corrected one.

|||Good Idea Umachardar, I consider it, but discarded the idea since it didn't quite seem to fit the OP's request. But perhaps it will.

Change NOT NULL to NULL?

Hi all,
How I change collunm that is NOT NULL to be NULL?
ThanksRetf wrote:
> Hi all,
> How I change collunm that is NOT NULL to be NULL?
> Thanks
ALTER TABLE. You need to specify the datatype as well. Example:
ALTER TABLE tbl ALTER COLUMN x INT NULL ;
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/ms130214(en-US,SQL.90).aspx
--|||You can do something like this
Use ALTER TableName ALTER COLUMN ColumnName DataType (NULL/NOT NULL)
See example below
CREATE TABLE TESTNULL (
ID INT NOT NULL,
SOMEDATE DATETIME)
INSERT INTO TESTNULL
VALUES (1,
GETDATE())
INSERT INTO TESTNULL
VALUES (NULL,
GETDATE()) --ERROR
ALTER TABLE TESTNULL ALTER COLUMN ID INT NULL
INSERT INTO TESTNULL
VALUES (NULL,
GETDATE())
SELECT *
FROM TESTNULL
DROP TABLE TESTNULL
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Try using "alter table ... alter column ...".
Example:
use northwind
go
create table t1(c1 int not null default(0))
go
insert into t1 default values
go
select * from t1
go
alter table t1
alter column c1 int null
go
insert into t1 default values
go
select * from t1
go
drop table t1
go
AMB
"Retf" wrote:

> Hi all,
> How I change collunm that is NOT NULL to be NULL?
> Thanks
>
>