Calculated Column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Calculated Column

Author

Topic

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-05-23 : 04:16:43

I have a to generate a load of random data and need to populate the Value Column (Quantity Column * Price Column)But I don't know how to do it. See Script below. Can you help.

DECLARE @OrderNumber varchar (30)
DECLARE @OrderDate int
DECLARE @OrderLineNumber varchar(50)
DECLARE @CustomerSkey int
DECLARE @ProductSkey int
DECLARE @OrderMethodSkey int
DECLARE @Quantity int
DECLARE @Cost Decimal(18,3)

SET @OrderNumber = 1
SET @OrderDate = 0
SET @OrderLineNumber = 1
SET @CustomerSkey = 1
SET @ProductSkey = 1
SET @OrderMethodSkey = 1
SET @Quantity = 1
SET @Cost = 1

WHILE @OrderNumber <= 100
WHILE @OrderDate <= 100
WHILE @OrderLineNumber <= 100
WHILE @CustomerSkey <= 100
WHILE @ProductSkey <= 100
WHILE @OrderMethodSkey <= 100
WHILE @Quantity <= 100
WHILE @Cost <= 100

BEGIN
INSERT INTO Orders
(OrderNumber
, OrderDate
, OrderLineNumber
, CustomerSkey
, ProductSkey
, OrderMethodSkey
, OrderTime
, Quantity
, Cost)

SELECT
'ORD' + Right ('000000' + CAST (@OrderNumber AS varchar (30)), 6)
,DATEADD (day, CAST (RAND () * 1500 as int), '2008-1-1')
,(Right ('0' + CAST (@OrderLineNumber AS varchar (30)), 6))
,(99 * RAND()) + 1
,(99 * RAND()) + 1
,(2 * RAND()) + 1
,DATEADD(ms, cast(86400000 * RAND() as int), convert(time, '00:00'))
,(190 * RAND()) + 10
,(40 * RAND()) + 10

SET @OrderNumber = @OrderNumber + 1
SET @OrderDate = @OrderDate + 1
SET @OrderLineNumber = @OrderLineNumber + 1
SET @CustomerSkey = @CustomerSkey + 1
SET @ProductSkey = @ProductSkey + 1
SET @OrderMethodSkey = @OrderMethodSkey + 1
SET @Quantity = @Quantity + 1
SET @Cost = @Cost + 1

END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-23 : 04:26:59

so didnt understand. Is your attempt to populate a random set of data to your table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-05-23 : 04:28:30

Yes, I am creating a load of random data for testing purposes

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-23 : 04:30:13

Ok. But where's the Value column? I cant see it listed in INSERT column list

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-05-23 : 04:32:47

Hi Visakh

Its not in there at the moment, I took it out because I couldn't figure out what to do.

Ta

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-23 : 04:36:45

quote:
Originally posted by wafw1971

Hi Visakh

Its not in there at the moment, I took it out because I couldn't figure out what to do.

Ta

Make it as a calculated column so that you dont have to add it in insert statement and value will get automatically calculated based on component columns.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-05-23 : 04:41:49

Hi Visakh

I don't know how to that, the table is already set up so how do I change the Value column to a calculated column.

Ta

WW

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-23 : 04:43:46

quote:
Originally posted by wafw1971

Hi Visakh

I don't know how to that, the table is already set up so how do I change the Value column to a calculated column.

Ta

WW

use ALTER TABLE statement


ALTER TABLE YourTableName ALTER COLUMN [Value] AS Quantity * Price

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-05-23 : 04:46:51

I am getting the following error message

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'AS'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-23 : 04:51:22

[code]
ALTER TABLE YourTableName DROP COLUMN [Value]
GO
ALTER TABLE YourTableName ADD [Value] AS (Quantity * Price)
GO
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-05-23 : 04:56:56

I know have this error message:

Msg 1759, Level 16, State 0, Line 1
Computed column 'Price' in table 'Orders' is not allowed to be used in another computed-column definition.

This is because my Price column is Cost column +35%

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-23 : 04:58:49

quote:
Originally posted by wafw1971

I know have this error message:

Msg 1759, Level 16, State 0, Line 1
Computed column 'Price' in table 'Orders' is not allowed to be used in another computed-column definition.

This is because my Price column is Cost column +35%

then you need to replace Price with its definition

ie


ALTER TABLE YourTableName DROP COLUMN [Value] 
GO
ALTER TABLE YourTableName ADD [Value] AS (Quantity * (Cost + ..))
GO

put actual calculation for Price field for code in blue

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2013-05-23 : 05:01:51

Here's a slightly better way to do it:


; WITH random_floats AS (
	SELECT
		ROW_NUMBER() OVER (ORDER BY NEWID()) [r]
		, CAST('0.' + CAST(ABS(CAST(CAST(NEWID() AS VARBINARY(3)) AS INT)) AS VARCHAR(20)) AS FLOAT) AS [f]
	FROM 
		       (SELECT 1 AS [n] UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) a
	CROSS JOIN (SELECT 1 AS [n] UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) b
	CROSS JOIN (SELECT 1 AS [n] UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) c
	CROSS JOIN (SELECT 1 AS [n] UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) d
	)
	
,random_data AS (
	SELECT
		'ORD' + RIGHT('000000' + CAST(rf.[r] AS varchar (30)), 6) AS [OrderNumber]
		, DATEADD (day, CAST (rf.[f] * 1500 as int), '2008-1-1') AS OrderDate
		, (Right ('0' + CAST (rf.[r] AS varchar (30)), 6)) AS OrderLineNumber
		, (99 * rf.[f]) + 1 AS CustomerSkey
		, (99 * rf.[f]) + 1 AS ProductSkey
		, (2 * rf.[f]) + 1 AS OrderMethodSkey
		, DATEADD(MS, cast(86400000 * rf.[f] as int), convert(time, '00:00')) AS [OrderTime]
		, (190 * rf.[f]) + 10 AS [Quantity]
		, (40 * rf.[f]) + 10 AS [Cost]
	FROM
		random_floats AS rf
	WHERE
		rf.[r] < 101
	)

SELECT * FROM random_data

Results:


OrderNumber OrderDate               OrderLineNumber CustomerSkey           ProductSkey            OrderMethodSkey        OrderTime        Quantity               Cost
----------- ----------------------- --------------- ---------------------- ---------------------- ---------------------- ---------------- ---------------------- ----------------------
ORD000001   2011-04-24 00:00:00.000 01              80.8281055             80.8281055             2.612689               19:21:08.1640000 163.205455             42.25378
ORD000002   2008-08-18 00:00:00.000 02              16.23640987            16.23640987            1.30780626             03:41:37.2300000 39.2415947             16.1561252
ORD000003   2008-07-13 00:00:00.000 03              13.84987033            13.84987033            1.25959334             03:06:54.4320000 34.6613673             15.1918668
ORD000004   2009-08-25 00:00:00.000 04              40.7746756             40.7746756             1.8035288              09:38:32.4440000 86.335236              26.070576
ORD000005   2008-08-18 00:00:00.000 05              16.20146188            16.20146188            1.30710024             03:41:06.7300000 39.1745228             16.1420048
ORD000006   2008-08-14 00:00:00.000 06              15.94194526            15.94194526            1.30185748             03:37:20.2430000 38.6764606             16.0371496
ORD000007   2008-07-10 00:00:00.000 07              13.63691341            13.63691341            1.25529118             03:03:48.5780000 34.2526621             15.1058236
ORD000008   2008-06-07 00:00:00.000 08              11.48422276            11.48422276            1.21180248             02:32:29.8670000 30.1212356             14.2360496
ORD000009   2011-08-05 00:00:00.000 09              87.6295639             87.6295639             2.7500922              21:00:03.9830000 176.258759             45.001844
ORD000010   2009-07-22 00:00:00.000 010             38.523277              38.523277              1.758046               09:05:47.5870000 82.01437               25.16092
ORD000011   2009-06-30 00:00:00.000 011             37.0380493             37.0380493             1.7280414              08:44:11.3880000 79.163933              24.560828
ORD000012   2008-06-01 00:00:00.000 012             11.05759315            11.05759315            1.2031837              02:26:17.5350000 29.3024515             14.063674
ORD000013   2009-12-22 00:00:00.000 013             48.6489178             48.6489178             1.9626044              11:33:04.5100000 101.447418             29.252088
ORD000014   2008-06-08 00:00:00.000 014             11.52993403            11.52993403            1.21272594             02:33:09.7600000 30.2089643             14.2545188
ORD000015   2011-12-08 00:00:00.000 015             95.8428613             95.8428613             2.9160174              22:59:31.9510000 192.021653             48.320348
ORD000016   2008-08-12 00:00:00.000 016             15.79307698            15.79307698            1.29885004             03:35:10.3210000 38.3907538             15.9770008
ORD000017   2008-07-29 00:00:00.000 017             14.87196118            14.87196118            1.28024164             03:21:46.4380000 36.6229558             15.6048328
ORD000018   2008-07-02 00:00:00.000 018             13.14195499            13.14195499            1.24529202             02:56:36.6150000 33.3027419             14.9058404
ORD000019   2008-07-07 00:00:00.000 019             13.46723929            13.46723929            1.25186342             03:01:20.4990000 33.9270249             15.0372684
ORD000020   2011-07-31 00:00:00.000 020             87.3081109             87.3081109             2.7435982              20:55:23.4420000 175.641829             44.871964
ORD000021   2010-03-29 00:00:00.000 021             55.0457335             55.0457335             2.091833               13:06:07.1850000 113.724135             31.83666
ORD000022   2009-07-12 00:00:00.000 022             37.893439              37.893439              1.745322               08:56:37.9100000 80.80559               24.90644
ORD000023   2010-11-29 00:00:00.000 023             71.2185715             71.2185715             2.418557               17:01:21.6620000 144.762915             38.37114
ORD000024   2010-05-31 00:00:00.000 024             59.1525307             59.1525307             2.1747986              14:05:51.2990000 121.605867             33.495972
ORD000025   2011-09-26 00:00:00.000 025             91.0258084             91.0258084             2.8187032              21:49:27.9780000 182.776804             46.374064
ORD000026   2010-03-11 00:00:00.000 026             53.8320529             53.8320529             2.0673142              12:48:27.9730000 111.394849             31.346284
ORD000027   2008-07-08 00:00:00.000 027             13.52407618            13.52407618            1.25301164             03:02:10.1020000 34.0361058             15.0602328
ORD000028   2010-01-15 00:00:00.000 028             50.2297993             50.2297993             1.9945414              11:56:04.1880000 104.481433             29.890828
ORD000029   2008-08-23 00:00:00.000 029             16.56943498            16.56943498            1.31453404             03:46:27.8700000 39.8807338             16.2906808
ORD000030   2009-12-31 00:00:00.000 030             49.2392251             49.2392251             1.9745298              11:41:39.6870000 102.580331             29.490596
ORD000031   2010-09-06 00:00:00.000 031             65.6596423             65.6596423             2.3062554              15:40:30.2330000 134.094263             36.125108
ORD000032   2009-10-08 00:00:00.000 032             43.6460122             43.6460122             1.8615356              10:20:18.3370000 91.845882              27.230712
ORD000033   2010-11-10 00:00:00.000 033             69.9360166             69.9360166             2.3926468              16:42:42.3410000 142.301446             37.852936
ORD000034   2011-10-21 00:00:00.000 034             92.72944               92.72944               2.85312                22:14:14.7840000 186.0464               47.0624
ORD000035   2009-10-25 00:00:00.000 035             44.786908              44.786908              1.884584               10:36:54.0280000 94.03548               27.69168
ORD000036   2008-11-16 00:00:00.000 036             22.144321              22.144321              1.427158               05:07:33.2250000 50.58001               18.54316
ORD000037   2008-06-24 00:00:00.000 037             12.58790248            12.58790248            1.23409904             02:48:33.0780000 32.2394088             14.6819808
ORD000038   2008-07-26 00:00:00.000 038             14.67499672            14.67499672            1.27626256             03:18:54.5420000 36.2449432             15.5252512
ORD000039   2008-07-18 00:00:00.000 039             14.16076102            14.16076102            1.26587396             03:11:25.7550000 35.2580262             15.3174792
ORD000040   2010-12-18 00:00:00.000 040             72.4731292             72.4731292             2.4439016              17:19:36.5490000 147.170652             38.878032
ORD000041   2008-10-31 00:00:00.000 041             21.1140775             21.1140775             1.406345               04:52:34.1040000 48.602775              18.1269
ORD000042   2008-06-02 00:00:00.000 042             11.13644467            11.13644467            1.20477666             02:27:26.3510000 29.4537827             14.0955332
ORD000043   2008-06-16 00:00:00.000 043             12.0751795             12.0751795             1.223741               02:41:05.6110000 31.255395              14.47482
ORD000044   2008-08-18 00:00:00.000 044             16.19872453            16.19872453            1.30704494             03:41:04.3410000 39.1692693             16.1408988
ORD000045   2010-03-05 00:00:00.000 045             53.4362608             53.4362608             2.0593184              12:42:42.5540000 110.635248             31.186368
ORD000046   2009-09-01 00:00:00.000 046             41.2247989             41.2247989             1.8126222              09:45:05.2790000 87.199109              26.252444
ORD000047   2008-09-04 00:00:00.000 047             17.31118753            17.31118753            1.32951894             03:57:15.2180000 41.3042993             16.5903788
ORD000048   2008-09-29 00:00:00.000 048             18.9815581             18.9815581             1.3632638              04:21:32.9960000 44.510061              17.265276
ORD000049   2008-07-23 00:00:00.000 049             14.5061443             14.5061443             1.2728514              03:16:27.1800000 35.920883              15.457028
ORD000050   2011-10-19 00:00:00.000 050             92.5561999             92.5561999             2.8496202              22:11:43.5920000 185.713919             46.992404
ORD000051   2011-09-12 00:00:00.000 051             90.1021483             90.1021483             2.8000434              21:36:01.8740000 181.004123             46.000868
ORD000052   2009-02-07 00:00:00.000 052             27.6273172             27.6273172             1.5379256              06:27:18.3850000 61.102932              20.758512
ORD000053   2011-06-24 00:00:00.000 053             84.8381104             84.8381104             2.6936992              20:19:27.8050000 170.901424             43.873984
ORD000054   2011-10-05 00:00:00.000 054             91.6650019             91.6650019             2.8316162              21:58:45.8190000 184.003539             46.632324
ORD000055   2009-04-16 00:00:00.000 055             32.1208282             32.1208282             1.6287036              07:32:39.9950000 69.726842              22.574072
ORD000056   2008-06-20 00:00:00.000 056             12.32097868            12.32097868            1.22870664             02:44:40.1260000 31.7271308             14.5741328
ORD000057   2010-05-06 00:00:00.000 057             57.5137342             57.5137342             2.1416916              13:42:01.0770000 118.460702             32.833832
ORD000058   2011-10-04 00:00:00.000 058             91.5723775             91.5723775             2.829745               21:57:24.9840000 183.825775             46.5949
ORD000059   2008-07-12 00:00:00.000 059             13.79383138            13.79383138            1.25846124             03:06:05.5250000 34.5538178             15.1692248
ORD000060   2011-01-20 00:00:00.000 060             74.6220331             74.6220331             2.4873138              17:50:51.9560000 151.294811             39.746276
ORD000061   2009-08-22 00:00:00.000 061             40.5782497             40.5782497             1.7995606              09:35:41.0170000 85.958257              25.991212
ORD000062   2010-07-24 00:00:00.000 062             62.7707332             62.7707332             2.2478936              14:58:29.0030000 128.549892             34.957872
ORD000063   2008-08-15 00:00:00.000 063             15.98402026            15.98402026            1.30270748             03:37:56.9630000 38.7572106             16.0541496
ORD000064   2011-01-05 00:00:00.000 064             73.6476751             73.6476751             2.4676298              17:36:41.6070000 149.424831             39.352596
ORD000065   2008-07-17 00:00:00.000 065             14.12806825            14.12806825            1.2652135              03:10:57.2230000 35.1952825             15.30427
ORD000066   2008-11-06 00:00:00.000 066             21.4632307             21.4632307             1.4133986              04:57:38.8190000 49.272867              18.267972
ORD000067   2008-07-18 00:00:00.000 067             14.1571396             14.1571396             1.2658008              03:11:22.5940000 35.251076              15.316016
ORD000068   2009-01-22 00:00:00.000 068             26.563384              26.563384              1.516432               06:11:49.8620000 59.06104               20.32864
ORD000069   2009-05-07 00:00:00.000 069             33.5306674             33.5306674             1.6571852              07:53:10.4000000 72.432594              23.143704
ORD000070   2008-06-25 00:00:00.000 070             12.61874593            12.61874593            1.23472214             02:48:59.9960000 32.2986033             14.6944428
ORD000071   2009-05-06 00:00:00.000 071             33.4186489             33.4186489             1.6549222              07:51:32.6390000 72.217609              23.098444
ORD000072   2008-07-19 00:00:00.000 072             14.20568722            14.20568722            1.26678156             03:12:04.9630000 35.3442482             15.3356312
ORD000073   2010-08-26 00:00:00.000 073             64.9451791             64.9451791             2.2918218              15:30:06.7010000 132.723071             35.836436
ORD000074   2008-06-21 00:00:00.000 074             12.36922435            12.36922435            1.2296813              02:45:22.2320000 31.8197235             14.593626
ORD000075   2008-06-20 00:00:00.000 075             12.31137865            12.31137865            1.2285127              02:44:31.7480000 31.7087065             14.570254
ORD000076   2011-06-27 00:00:00.000 076             85.0724731             85.0724731             2.6984338              20:22:52.3400000 171.351211             43.968676
ORD000077   2008-09-06 00:00:00.000 077             17.48660959            17.48660959            1.33306282             03:59:48.3130000 41.6409679             16.6612564
ORD000078   2008-11-12 00:00:00.000 078             21.8976625             21.8976625             1.422175               05:03:57.9600000 50.106625              18.4435
ORD000079   2008-08-10 00:00:00.000 079             15.67280287            15.67280287            1.29642026             03:33:25.3550000 38.1599247             15.9284052
ORD000080   2008-06-08 00:00:00.000 080             11.53970929            11.53970929            1.21292342             02:33:18.2910000 30.2277249             14.2584684
ORD000081   2008-06-06 00:00:00.000 081             11.40986485            11.40986485            1.2103003              02:31:24.9720000 29.9785285             14.206006
ORD000082   2008-07-23 00:00:00.000 082             14.52555523            14.52555523            1.27324354             03:16:44.1200000 35.9581363             15.4648708
ORD000083   2008-06-12 00:00:00.000 083             11.8096714             11.8096714             1.2183772              02:37:13.8950000 30.745834              14.367544
ORD000084   2009-06-08 00:00:00.000 084             35.6078359             35.6078359             1.6991482              08:23:23.2020000 76.419079              23.982964
ORD000085   2008-10-20 00:00:00.000 085             20.3686768             20.3686768             1.3912864              04:41:43.5720000 47.172208              17.825728
ORD000086   2008-08-13 00:00:00.000 086             15.86840014            15.86840014            1.30037172             03:36:16.0580000 38.5353134             16.0074344
ORD000087   2008-07-26 00:00:00.000 087             14.69381266            14.69381266            1.27664268             03:19:10.9630000 36.2810546             15.5328536
ORD000088   2008-11-24 00:00:00.000 088             22.6560124             22.6560124             1.4374952              05:14:59.7920000 51.562044              18.749904
ORD000089   2008-06-28 00:00:00.000 089             12.84987331            12.84987331            1.23939138             02:52:21.7070000 32.7421811             14.7878276
ORD000090   2009-01-28 00:00:00.000 090             26.9836786             26.9836786             1.5249228              06:17:56.6640000 59.867666              20.498456
ORD000091   2008-07-03 00:00:00.000 091             13.16129167            13.16129167            1.24568266             02:56:53.4900000 33.3398527             14.9136532
ORD000092   2010-01-06 00:00:00.000 092             49.5774883             49.5774883             1.9813634              11:46:34.8980000 103.229523             29.627268
ORD000093   2008-09-03 00:00:00.000 093             17.25887593            17.25887593            1.32846214             03:56:29.5640000 41.2039033             16.5692428
ORD000094   2011-03-18 00:00:00.000 094             78.3632134             78.3632134             2.5628932              18:45:16.9860000 158.474854             41.257864
ORD000095   2011-04-18 00:00:00.000 095             80.4599245             80.4599245             2.605251               19:15:46.8430000 162.498845             42.10502
ORD000096   2010-02-08 00:00:00.000 096             51.7634578             51.7634578             2.0255244              12:18:22.6540000 107.424818             30.510488
ORD000097   2009-06-14 00:00:00.000 097             35.991055              35.991055              1.70689                08:28:57.6480000 77.15455               24.1378
ORD000098   2008-08-25 00:00:00.000 098             16.67533528            16.67533528            1.31667344             03:48:00.2920000 40.0839768             16.3334688
ORD000099   2008-07-15 00:00:00.000 099             13.94015833            13.94015833            1.26141734             03:08:13.2290000 34.8346473             15.2283468
ORD000100   2011-01-03 00:00:00.000 0100            73.517005              73.517005              2.46499                17:34:47.5680000 149.17405              39.2998

(100 row(s) affected)

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-05-23 : 05:07:32

Thanks again Visakh that's did the trick.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-23 : 05:11:06

quote:
Originally posted by wafw1971

Thanks again Visakh that's did the trick.

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources