Formula in computed column. How to

Hi

I am trying for the first time to use computed columns in Sql Server. This I need to create Order numbers and account numbers.
This one works:
(isnull(N'ON'+CONVERT([nvarchar](100),[CustomerID],(0)),N'*** ERROR ***'))
It creates a ordernumber ON(CustomerID) ...no problem.
What i want is to add more values. Like I would like to include Year() (current year when order or account war created) and maybe some more. How do I include more then one value in the formula?
ize_manAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tigin44Commented:
(isnull(N'ON'+CONVERT([nvarchar](100),[CustomerID],(0)),N'*** ERROR ***')) + DATEPART(YEAR, GETDATE())
0
tigin44Commented:
you can use any valid sql opeartion in computed columns. ie as in above post
0
ize_manAuthor Commented:
No, sorry :( not working. I have created a function to add zeroz to the account number i am creating but would weary much like to add year as well...this makes the account numbers start over each year. This formula gives me account number : CN00000000 (o minus customerID)
(isnull('CN'+[Buisness].[ufnLeadingZeros]([CustomerID]),''))

if you want here you have the function in the code widow.

I have tried to add DATEPART(YEAR,GETDATE()) section in the formula in various ways but have not succeeded yeat :)


USE [TT_Online]
GO
 
/****** Object:  UserDefinedFunction [Buisness].[ufnLeadingZeros]    Script Date: 07/13/2009 13:23:16 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
 
CREATE FUNCTION [Buisness].[ufnLeadingZeros](
    @Value int
) 
RETURNS varchar(8) 
WITH SCHEMABINDING 
AS 
BEGIN
    DECLARE @ReturnValue varchar(8);
 
    SET @ReturnValue = CONVERT(varchar(8), @Value);
    SET @ReturnValue = REPLICATE('0', 8 - DATALENGTH(@ReturnValue)) + @ReturnValue;
 
    RETURN (@ReturnValue);
END;
 
GO

Open in new window

0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

ize_manAuthor Commented:
I tried this but get following error message :- Error validating the formula for column 'Ordenumber'. ...always.

This is the formula: (isnull('CN'+[Buisness].[ufnLeadingZeros]([CustomerID]),'')) + DATEPART(YEAR, GETDATE())

I can see that there is a Year() function wich takes datetime param and i can't use that either Year(getdate()) ??
0
Mark WillsTopic AdvisorCommented:
Try :

DATENAME(YEAR,GETDATE())

It returns a string value of the year, not an integer value of the year.
0
ize_manAuthor Commented:
Yes, this works fine :) one more question, how do i get it in right after 'CN' part or maybe before?? I have tried now but just cant get it to work
0
Mark WillsTopic AdvisorCommented:
What do you mean ?


Would be more inclined to be doing :

'CN' + DATENAME(YEAR, GETDATE()) + right('00000000'+convert(varchar,isnull(customerid,0)),8)
0
ize_manAuthor Commented:
I mean this

This statement:
(isnull('CN'+[Buisness].[ufnLeadingZeros]([CustomerID]),'')+datename(year,getdate()))

gives me CN000012212009 if CustomerID is 1221 what i would like to do is move 2009 like this:
CN200900001221 as an example
0
ize_manAuthor Commented:
this does not work
'CN' + DATENAME(YEAR, GETDATE()) + right('00000000'+convert(varchar,isnull(customerid,0)),8)
0
ize_manAuthor Commented:
Ok, stupid me :-| i got it to work...a little syntax problem ;-) Solution
(isnull(('CN'+datename(year,getdate()))+[Buisness].[ufnLeadingZeros]([CustomerID]),''))

had one bracket to much ;-) Problem solved. Today i learned something new.. ;-)))
0
Mark WillsTopic AdvisorCommented:
would need to be in brackets...

What does it give you ?

If you want to use the function... then...

(isnull('CN'+datename(year,getdate())+[Buisness].[ufnLeadingZeros]([CustomerID]),''))
0
Mark WillsTopic AdvisorCommented:
Sorry about that... Didn't see your previous posting (snail mail here).
0
ize_manAuthor Commented:
The best might be to include the year into the function...I tried that as well but could not get it to work
0
ize_manAuthor Commented:
tried this as well but it did not work
(isnull('CN' + DATENAME(YEAR, GETDATE()) + right('00000000'+convert(varchar,isnull(customerid,0)),8) )
0
Mark WillsTopic AdvisorCommented:
No that won't work. The isnull() requires two arguments, you have only one...

ALTER FUNCTION [ufnLeadingZeros](@Value int)
RETURNS varchar(12)
WITH SCHEMABINDING
AS
begin
RETURN (DATENAME(YEAR, GETDATE()) + right('00000000'+convert(varchar,isnull(@value,0)),8));
end
GO


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
Or even :


ALTER FUNCTION [ufnLeadingZeros](@prefix char(2),@Value int)
RETURNS varchar(14)
WITH SCHEMABINDING
AS
begin
RETURN (@Prefix + DATENAME(YEAR, GETDATE()) + right('00000000'+convert(varchar,isnull(@value,0)),8));
end
GO

-- then use it as per :

select business.ufnleadingzeros ('CN',123)

-- or

(isnull(business.ufnleadingzeros ('CN',customerid),''))

-- the isnull is really not doing anything...
0
Mark WillsTopic AdvisorCommented:
Not sure why you are getting the other error...  this seems to work fine :

create table my_computed_column (id int identity, customerid int, date datetime, cn_number as 'CN' + DATENAME(YEAR, GETDATE()) + right('00000000'+convert(varchar,isnull(customerid,0)),8) )

insert my_computed_column (customerid, date) values (123,getdate())

insert my_computed_column (date) values (getdate())

select * from my_computed_column

update my_computed_column set customerid = 321 where customerid is NULL

select * from my_computed_column
0
ize_manAuthor Commented:
I tried this:
(isnull(business.ufnleadingzeros ('CN',customerid),'')) and could not get it to work (mine: "(isnull(Business.ufnLeadingZerosCurrYear ('CN',CustomerId),'')) ") otherwise i think this is the best solution... there was no problem crating the function  
0
ize_manAuthor Commented:
Yes...worked fine :-) ...must be syntax failure in mine
0
Mark WillsTopic AdvisorCommented:
Functions do create a bit of overhead, not the friendliest of beasts as a scaler function....

But, so long as you have a solution, and it is working, it is more important that it actually works :)
0
ize_manAuthor Commented:
Yes...thank you :-)
0
ize_manAuthor Commented:
With a great thanks for the lesson :-)
0
Mark WillsTopic AdvisorCommented:
A pleasure working with you, and happy I have been of assistance...
0
ZberteocCommented:
You don't need a function to generate the values you need you can use directly in the computed column the formula:

'CN'+cast(cast(year(getdate()) as bigint)*100000000+isnull(customerid,0) as varchar)

you can test with

select 'CN'+cast(cast(year(getdate()) as bigint)*100000000+isnull(1221,0) as varchar)


PS. I know it was answered but I didn't like the solution. No offence Mark. :o)
0
Mark WillsTopic AdvisorCommented:
None taken, I do not like the function either and was trying hard for : 'CN' + DATENAME(YEAR, GETDATE()) + right('00000000'+convert(varchar,isnull(customerid,0)),8)

But for whatever reason the Asker had more success with the Askers own function, and so let that happen - it worked for the Asker whereas other suggestions didn't.
 
And, I will always welcome a good open discussion / (constructive) criticism :)
0
ize_manAuthor Commented:
Hi guys

It actually worked after a while...and exercises from me... I don't know why it would not work before but it doe's now. The only thing is that i can not set NOT NULL property to true otherwise it works fine :-)

(('CN'+datename(year,getdate()))+right('00000000'+CONVERT([varchar],isnull([customerid],(0)),0),(8)))
0
Mark WillsTopic AdvisorCommented:
Well that is fantastic news, so much better without the function. Well done for persevering with it.
0
ZberteocCommented:
You cannot set the NOT NULL property on a computed column but you can make sure that it will never happen in your formula, which you did. ;o)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.