how to assign null in derived column transformation editor

Dear friends, can any one tell me how to assign null to the expression value in derived column transfromation editor?

thanks,

In the editor dialog, expand the Null Functions folder, and drag the appropriate NULL(your data type here) item to the expression box. For example, you'd use NULL(DT_I4) to create a null integer.

how to assgin the null value

How to assgin null value for a variable or any value
for example
if a=1 then
customerid
else
in else part i need null but default it is taking 0
how to assign valuefor example
if a=1 then
customerid
else

try this

if a=1 then
ToText(customerid)
else
''|||this is not a NULL value, it's a blank string with is not the same. would matter if he is doing counts or anything.

to trully create a null, make a formula called @.NULL and leave it completely BLANK. then reference the formula in the IF-Statement like

IF _______
THEN _______
ELSE {@.NULL}

How to alter(add) a Table with a default value and by allowing Nulls?

Hi

I am using this query to alter a table

ALTER TABLE myTable ADD age int NULL DEFAULT(0)

But above query is adding age field by storing Nulls but not with default values

So I need to add age field to the table by storing default value as 0 and by allowing Nulls

Please advice

Thanks

Use the below query,

Code Snippet

Create table #Mytable

(

Id int,

Name varchar(100)

)

Insert Into #Mytable values(1,'One');

Insert Into #Mytable values(2,'Two');

Insert Into #Mytable values(3,'Three');

Alter table #MyTable Add Age int NOT NULL Default(0)

Alter table #MyTable Alter Column Age int NULL;

select * from #MyTable

Insert Into #Mytable(Id,Name) values(4,'Four');

select * from #MyTable

|||

Thanks Mani

I got my answer

and

Can't we do it in a single step in Sql Server?

|||

Yes, we have it,

Code Snippet

Alter table #MyTable Add Age int NULL Default(0) WITH VALUES

|||

Thanks a lot Mani, That's what I'm talking about.

With Regards

Vijay

how to ALTER COLUMN ?

Hi,
i do a create:
CREATE TABLE myTable ( id int IDENTITY(1,1) NOT NULL)
and now i want to remove the IDENTITY definition from colum id
using ALTER myTable ALTER COLUMN id ... '
so that column id has definition as when i do a:
CREATE TABLE myTable ( id int NULL)
How can i do this?
thanks, HelmutHelmut
CREATE TABLE Test (col INT NOT NULL IDENTITY(1,1))
GO
insert into Test default values
ALTER TABLE Test ADD col1 INT NULL
GO
ALTER TABLE Test DROP COLUMN col
GO
EXEC sp_rename 'TEST.COL1', 'COL', 'COLUMN'
Note: If you had some data inserted into the table before addin a new column
and need to move it ,so use UPDATE command as
UPDATE Test SET col1=col
"Helmut Woess" <user22@.inode.at> wrote in message
news:dzg357eusfly$.19hvp25nnxfh1.dlg@.40tude.net...
> Hi,
> i do a create:
> CREATE TABLE myTable ( id int IDENTITY(1,1) NOT NULL)
> and now i want to remove the IDENTITY definition from colum id
> using ALTER myTable ALTER COLUMN id ... '
> so that column id has definition as when i do a:
> CREATE TABLE myTable ( id int NULL)
> How can i do this?
> thanks, Helmut|||I hate the sp_rename command. Using SQL2000 and SQL2005 Beta 2, I managed
to loose an entire table because of that command. The table in question had
17million rows. So be carful with it.
Regards
Colin Dawson
www.cjdawson.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eIFAYrEbGHA.4060@.TK2MSFTNGP02.phx.gbl...
> Helmut
> CREATE TABLE Test (col INT NOT NULL IDENTITY(1,1))
> GO
> insert into Test default values
> ALTER TABLE Test ADD col1 INT NULL
> GO
> ALTER TABLE Test DROP COLUMN col
> GO
> EXEC sp_rename 'TEST.COL1', 'COL', 'COLUMN'
> Note: If you had some data inserted into the table before addin a new
> column and need to move it ,so use UPDATE command as
> UPDATE Test SET col1=col
>
> "Helmut Woess" <user22@.inode.at> wrote in message
> news:dzg357eusfly$.19hvp25nnxfh1.dlg@.40tude.net...
>|||Colin
This stored procedure is documented and supported officialy by MS .

>I hate the sp_rename command. Using SQL2000 and SQL2005 Beta 2, I managed
>to loose an entire table because of that command. The table in question
>had 17million rows. So be carful with it.
I just did some testing on the table that has 50 million rows and it worked
nice. Can you provide a repro where you are loosing an entire of table?
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:vJ15g.61831$wl.42277@.text.news.blueyonder.co.uk...
>I hate the sp_rename command. Using SQL2000 and SQL2005 Beta 2, I managed
>to loose an entire table because of that command. The table in question
>had 17million rows. So be carful with it.
> Regards
> Colin Dawson
> www.cjdawson.com
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eIFAYrEbGHA.4060@.TK2MSFTNGP02.phx.gbl...
>|||Yes, I know. I was at the MS Labs when the table got lost. Fortunately I
had a backup of the table, so I retried and lost the table again, and a
third time. Bottom line is, that if you try to rename a large table there
is a chance that you will loose it completely.
If you want to take your chances and risk loosing data on a production
system - or more to the point, creating unneeded downtime if you've been
cautious, and sensible enough to backup before hand. Then go ahead.
Regards
Colin Dawson
www.cjdawson.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uCabo8EbGHA.4292@.TK2MSFTNGP04.phx.gbl...
> Colin
> This stored procedure is documented and supported officialy by MS .
>
> I just did some testing on the table that has 50 million rows and it
> worked nice. Can you provide a repro where you are loosing an entire of
> table?
>
>
> "Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
> news:vJ15g.61831$wl.42277@.text.news.blueyonder.co.uk...
>

How to Alter a column?

Hi,
I am new to SQL Server 2005. Please help
what is the problem with this code..?

ALTER TABLE AM_Master
ALTER COLUMN Last_Updated datetime NOT NULL DEFAULT getdate();
Go

The error is like

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'DEFAULT'.As with the CHECK constraint, the expression for a DEFAULT constraint must be included within brackets.

Your example will then be:

ALTER TABLE AM_Master
ALTER COLUMN Last_Updated datetime NOT NULL DEFAULT (getdate());
Go|||Try this:
ALTER TABLE AM_Master
ALTER COLUMN Last_Updated datetime NOT NULL CONSTRAINT [DF_AM_Master] DEFAULT (getdate());
Go|||Indeed, as T'zozo suggested above, you may wish to name your constraints to make your physical schema easier to manage.

Regards,|||Thanks for your help...
but when i used the sql above following comments were returned;

1. ALTER TABLE AM_Master
ALTER COLUMN Last_Updated datetime NOT NULL DEFAULT (getdate());

Error Message
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'DEFAULT'.

2.ALTER TABLE AM_Master
ALTER COLUMN Last_Updated datetime NOT NULL CONSTRAINT [DF_AM_Master] DEFAULT (getdate());

Error message:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'CONSTRAINT'.

... Could you please tell why this happens...?

2.|||I was mistaken. It happens occasionally. It appears that you cannot actually modify a DEFAULT definition of a table column using the alter command statement.

From the documentation:

ALTER COLUMN

Specifies that the named column is to be changed or altered. ALTER COLUMN is not allowed if the compatibility level is 65 or lower. For more information, see sp_dbcmptlevel (Transact-SQL).

The modified column cannot be any one of the following:

...

Associated with a default definition. However, the length, precision, or scale of a column can be changed if the data type is not changed.

Perhaps you can do this using Enterprise Manager.

Regards,|||Robert, Robert, Robert...
EM / SSMS are evil ;)

[removed, because it was rubbish]|||I edited my post after you made that reply, which is the reason why your post doesn't make much sense to people who never read my original message, in which I stated something about using Enterprise Manager to make DDL changes rather than searching for that elusive DDL definition.

That view came from a year or two ago when, using SQL Server, I realised that I could perform a task, relating to object manipulation, in Enterprise Manager but not using direct SQL.|||CREATE TABLE tableName (
columnName int
CONSTRAINT constraintName DEFAULT 1
)
GO

ALTER TABLE tableName
DROP CONSTRAINT constraintName
GO

ALTER TABLE tableName
ADD CONSTRAINT constraintName DEFAULT 2
FOR columnName
GO

DROP TABLE tableName
GO

How to alter a column

Hi,
What's wrong with the following code? Please help. I am using MS SQL
2000.
create table one (
a float NOT NULL,
b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
alter table one
alter column a {drop} NOT NULL
alter column b {drop} NOT NULL
;
Thanks,
MikeSyntax usage is incorrect. Please use the below code to modify NOT NULL to NULL
alter table one alter column a Float NULL
alter table one alter column b Varchar(50) NULL
Thanks
Hari
"Michael" wrote:
> Hi,
> What's wrong with the following code? Please help. I am using MS SQL
> 2000.
> create table one (
> a float NOT NULL,
> b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> )
> alter table one
> alter column a {drop} NOT NULL
> alter column b {drop} NOT NULL
> ;
> Thanks,
> Mike
>

How to alter a column

Hi,
What's wrong with the following code? Please help. I am using MS SQL
2000.
create table one (
a float NOT NULL,
b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
alter table one
alter column a {drop} NOT NULL
alter column b {drop} NOT NULL
;
Thanks,
MikeSyntax usage is incorrect. Please use the below code to modify NOT NULL to N
ULL
alter table one alter column a Float NULL
alter table one alter column b Varchar(50) NULL
Thanks
Hari
"Michael" wrote:

> Hi,
> What's wrong with the following code? Please help. I am using MS SQL
> 2000.
> create table one (
> a float NOT NULL,
> b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> )
> alter table one
> alter column a {drop} NOT NULL
> alter column b {drop} NOT NULL
> ;
> Thanks,
> Mike
>

How to allow null by SQL

I have a field in a table, which is not allow null.
How can I set it to allow by SQL?
You can use the ALTER TABLE command:
ALTER TABLE my_table ALTER COLUMN my_column column_datatype NULL
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:uBqoduPGHHA.1252@.TK2MSFTNGP02.phx.gbl...
>I have a field in a table, which is not allow null.
> How can I set it to allow by SQL?
>
|||Hello,
Add on to Kalen; You can not chnage the column to Nullable if the column is
associated with a Primary key. Where as for a Unique column if you set a
NULL set even then
only one null will be allowed.
Thanks
Hari
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ezwlW%23PGHHA.3464@.TK2MSFTNGP05.phx.gbl...
> You can use the ALTER TABLE command:
> ALTER TABLE my_table ALTER COLUMN my_column column_datatype NULL
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
> news:uBqoduPGHHA.1252@.TK2MSFTNGP02.phx.gbl...
>

How to allow null by SQL

I have a field in a table, which is not allow null.
How can I set it to allow by SQL?You can use the ALTER TABLE command:
ALTER TABLE my_table ALTER COLUMN my_column column_datatype NULL
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:uBqoduPGHHA.1252@.TK2MSFTNGP02.phx.gbl...
>I have a field in a table, which is not allow null.
> How can I set it to allow by SQL?
>|||Hello,
Add on to Kalen; You can not chnage the column to Nullable if the column is
associated with a Primary key. Where as for a Unique column if you set a
NULL set even then
only one null will be allowed.
Thanks
Hari
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ezwlW%23PGHHA.3464@.TK2MSFTNGP05.phx.gbl...
> You can use the ALTER TABLE command:
> ALTER TABLE my_table ALTER COLUMN my_column column_datatype NULL
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
> news:uBqoduPGHHA.1252@.TK2MSFTNGP02.phx.gbl...
>

How to allow null by SQL

I have a field in a table, which is not allow null.
How can I set it to allow by SQL?You can use the ALTER TABLE command:
ALTER TABLE my_table ALTER COLUMN my_column column_datatype NULL
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:uBqoduPGHHA.1252@.TK2MSFTNGP02.phx.gbl...
>I have a field in a table, which is not allow null.
> How can I set it to allow by SQL?
>|||Hello,
Add on to Kalen; You can not chnage the column to Nullable if the column is
associated with a Primary key. Where as for a Unique column if you set a
NULL set even then
only one null will be allowed.
Thanks
Hari
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ezwlW%23PGHHA.3464@.TK2MSFTNGP05.phx.gbl...
> You can use the ALTER TABLE command:
> ALTER TABLE my_table ALTER COLUMN my_column column_datatype NULL
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
> news:uBqoduPGHHA.1252@.TK2MSFTNGP02.phx.gbl...
>>I have a field in a table, which is not allow null.
>> How can I set it to allow by SQL?
>sql

How to add the NOT NULL and PRIMAY KEY Constraints with ALTER command?

Dear All,

I wanted to alter a column in a table for adding both NOT NULL and PRIMARY KEY constraints to an existing column. How do I go about that.

I need your help.

First use alter table alter column for redefine column with not null, then use alter table add constraint

Code Snippet

createtable MyTable

(

id intnull,

namevarchar(100)

)

altertable MyTable altercolumn id intnotnull

altertable MyTable addconstraint pk_id primarykey(id)

How to add if ~else to a exist Stored Procedure

here is my original Sql Code

I add a parameters @.CustId in line 13

when CustID not Null, I want to add this to the Where clause line 146 and 156

if CustId not null then where clause will add a reference like CustId = @.CustID

if CustId is Null then not reference @.custID

how to Add a if ~else clause to my code? i tried all day.. but it doesn't work..

1SET QUOTED_IDENTIFIEROFF2GO3SET ANSI_NULLSOFF4GO5678ALTER PROCEDURE [dbo].[usp_OutDataDownQuery]9@.DTBEGDATETIME,10@.DTENDDATETIME,11@.RemarkINT,12@.BankIdVARCHAR (128)13@.CustIDCHAR141516as17181920SELECT21*22FROM23(24SELECT25ZT_Master.PriKey,26ZT_Master.BankId,27ZT_Master.TDateTime,28ZT_Master.PNo,29ZT_Master.Remark,30ZT_Master.CustId,31ZT_Master.ProcStatus,32ZT_Customer.[Name],33--ZT_Customer.AccountBAK AS Account,34--ZT_Customer.SCAccountBAK AS SCAccount35(SELECT TOP 1SUBSTRING(PCLNO, 3, 12)FROM ZT_DetailWHERE ZT_Master.PriKey = ZT_Detail.MasterKeyAND ZT_Detail.TXTYPE ='SD')AS Account,36(SELECT TOP 1SUBSTRING(PCLNO, 3, 12)FROM ZT_DetailWHERE ZT_Master.PriKey = ZT_Detail.MasterKeyAND ZT_Detail.TXTYPE ='SC')AS SCAccount37FROM38ZT_MasterLEFTJOIN ZT_CustomerON ZT_Master.CustId=ZT_Customer.Id39) a40,---------------------------------------------------41(SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),42CtcbBanSDTotal=ISNULL(本行代收筆數,0),43CtcbBanSDTotalAMT=ISNULL(本行代收金額,0),44OtherBanSDTotal=ISNULL(他行代收筆數,0),45OtherBanSDTotalAMT=ISNULL(他行代收金額,0),46CtcbBanSCTotal=ISNULL(本行代付筆數,0),47CtcbBanSCTotalAMT=ISNULL(本行代付金額,0),48OtherBanSCTotal=ISNULL(他行代付筆數,0),49OtherBanSCTotalAMT=ISNULL(他行代付金額,0),50GoodSDTotal=ISNULL(代收成功筆數,0),51GoodSDTotalAMT=ISNULL(代收成功金額,0),52GoodSCTotal=ISNULL(代付成功筆數,0),53GoodSCTotalAMT=ISNULL(代付成功金額,0),54BadSDTotal=ISNULL(代收失敗筆數,0),55BadSDTotalAMT=ISNULL(代收失敗金額,0),56BadSCTotal=ISNULL(代付失敗筆數,0),57BadSCTotalAMT=ISNULL(代付失敗金額,0)58FROM (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),59本行代收筆數,60本行代收金額,61他行代收筆數,62他行代收金額,63本行代付筆數,64本行代付金額,65他行代付筆數,66他行代付金額,67代收成功筆數,68代收成功金額,69代付成功筆數,70代付成功金額71FROM (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),72本行代收筆數,73本行代收金額,74他行代收筆數,75他行代收金額,76本行代付筆數,77本行代付金額,78他行代付筆數,79他行代付金額80--------------------代收成功筆數與代收成功金額-------------------81FROM (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),82本行代收筆數,83本行代收金額,84他行代收筆數,85他行代收金額86FROM (SELECT MasterKey,87COUNT(PriKey)AS 本行代收筆數,88SUM(AMT)AS 本行代收金額89FROM ZT_DetailWHERE TXTYPE='SD'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)ANDSUBSTRING(RBANK, 1, 3) ='822'GROUP BY MasterKey) a90FULLJOIN (SELECT MasterKey,91COUNT(PriKey)AS 他行代收筆數,92SUM(AMT)AS 他行代收金額93FROM ZT_DetailWHERE TXTYPE='SD'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)ANDSUBSTRING(RBANK, 1, 3) <>'822'GROUP BY MasterKey) b94ON a.MasterKey=b.MasterKey) a95-------------------代付成功筆數與代付成功金額-------------------96FULLJOIN (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),97本行代付筆數,98本行代付金額,99他行代付筆數,100他行代付金額101FROM (SELECT MasterKey,102COUNT(PriKey)AS 本行代付筆數,103SUM(AMT)AS 本行代付金額104FROM ZT_DetailWHERE TXTYPE='SC'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)ANDSUBSTRING(RBANK, 1, 3) ='822'GROUP BY MasterKey) a105FULLJOIN (SELECT MasterKey,106COUNT(PriKey)AS 他行代付筆數,107SUM(AMT)AS 他行代付金額108FROM ZT_DetailWHERE TXTYPE='SC'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)ANDSUBSTRING(RBANK, 1, 3) <>'822'GROUP BY MasterKey) b109ON a.MasterKey=b.MasterKey) b110ON a.MasterKey=b.MasterKey) a111--------------------成功筆數與成功金額-----------------------112FULLJOIN (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),113代收成功筆數,114代收成功金額,115代付成功筆數,116代付成功金額117FROM (SELECT MasterKey,118COUNT(PriKey)AS 代收成功筆數,119SUM(AMT)AS 代收成功金額120FROM ZT_DetailWHERE TXTYPE='SD'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)AND (RCODE='00'OR RCODE='')GROUP BY MasterKey) a121FULLJOIN (SELECT MasterKey,122COUNT(PriKey)AS 代付成功筆數,123SUM(AMT)AS 代付成功金額124FROM ZT_DetailWHERE TXTYPE='SC'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)AND (RCODE='00'OR RCODE='')GROUP BY MasterKey) b125ON a.MasterKey=b.MasterKey) b126ON a.MasterKey=b.MasterKey) a127---------------------失敗筆數與失敗金額------------------------128FULLJOIN (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),129代收失敗筆數,130代收失敗金額,131代付失敗筆數,132代付失敗金額133FROM (SELECT MasterKey,134COUNT(PriKey)AS 代收失敗筆數,135SUM(AMT)AS 代收失敗金額136FROM ZT_DetailWHERE TXTYPE='SD'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)AND (RCODE<>'00'AND RCODE<>'')GROUP BY MasterKey) a137FULLJOIN (SELECT MasterKey,138COUNT(PriKey)AS 代付失敗筆數,139SUM(AMT)AS 代付失敗金額140FROM ZT_DetailWHERE TXTYPE='SC'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)AND (RCODE<>'00'AND RCODE<>'')GROUP BY MasterKey) b141ON a.MasterKey=b.MasterKey) b142ON a.MasterKey=b.MasterKey) b143144145146WHERE a.PriKey=b.MasterKeyAND147--TDateTime BETWEEN @.DTBEG AND @.DTEND AND148TDateTimeBETWEEN'2007/9/5'AND'2007/9/5'AND149150(a.Remark=@.RemarkOR @.Remark=2)AND151(a.BankId=@.BankIdOR @.BankId='')152ORDER BY TDateTime,CustId,PNo153154155156WHERE a.PriKey=b.MasterKeyAND157--TDateTime BETWEEN @.DTBEG AND @.DTEND AND158TDateTimeBETWEEN'2007/9/5'AND'2007/9/5'AND159160(a.Remark=@.RemarkOR @.Remark=2)AND161(a.BankId=@.BankIdOR @.BankId='')162ORDER BY TDateTime,CustId,PNo163164165GO166SET QUOTED_IDENTIFIEROFF167GO168SET ANSI_NULLSON169GO170171

CustID=COALESCE(@.CustID,CustId)

COALESCE function returns the first non-null expression in its expression list.

Refer the below link for more information

http://www.sqlteam.com/article/implementing-a-dynamic-where-clause