?
Solved

Formula in computed column. How to

Posted on 2009-07-13
28
Medium Priority
?
531 Views
Last Modified: 2012-05-07
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?
0
Comment
Question by:ize_man
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 11
  • 2
  • +1
28 Comments
 
LVL 26

Expert Comment

by:tigin44
ID: 24838634
(isnull(N'ON'+CONVERT([nvarchar](100),[CustomerID],(0)),N'*** ERROR ***')) + DATEPART(YEAR, GETDATE())
0
 
LVL 26

Expert Comment

by:tigin44
ID: 24838662
you can use any valid sql opeartion in computed columns. ie as in above post
0
 

Author Comment

by:ize_man
ID: 24838716
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:ize_man
ID: 24838727
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24839595
Try :

DATENAME(YEAR,GETDATE())

It returns a string value of the year, not an integer value of the year.
0
 

Author Comment

by:ize_man
ID: 24839967
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24840135
What do you mean ?


Would be more inclined to be doing :

'CN' + DATENAME(YEAR, GETDATE()) + right('00000000'+convert(varchar,isnull(customerid,0)),8)
0
 

Author Comment

by:ize_man
ID: 24840629
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
 

Author Comment

by:ize_man
ID: 24840634
this does not work
'CN' + DATENAME(YEAR, GETDATE()) + right('00000000'+convert(varchar,isnull(customerid,0)),8)
0
 

Author Comment

by:ize_man
ID: 24840749
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24840774
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24840783
Sorry about that... Didn't see your previous posting (snail mail here).
0
 

Author Comment

by:ize_man
ID: 24840824
The best might be to include the year into the function...I tried that as well but could not get it to work
0
 

Author Comment

by:ize_man
ID: 24840847
tried this as well but it did not work
(isnull('CN' + DATENAME(YEAR, GETDATE()) + right('00000000'+convert(varchar,isnull(customerid,0)),8) )
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 2000 total points
ID: 24840889
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24840946
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24841071
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
 

Author Comment

by:ize_man
ID: 24841110
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
 

Author Comment

by:ize_man
ID: 24841141
Yes...worked fine :-) ...must be syntax failure in mine
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24841149
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
 

Author Comment

by:ize_man
ID: 24841174
Yes...thank you :-)
0
 

Author Closing Comment

by:ize_man
ID: 31602753
With a great thanks for the lesson :-)
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24841290
A pleasure working with you, and happy I have been of assistance...
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 24846319
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24847412
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
 

Author Comment

by:ize_man
ID: 24847454
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24847941
Well that is fantastic news, so much better without the function. Well done for persevering with it.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 24849879
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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question