SP errmsg 'Invalid column 'real' is specified in a constraint or computed-column definition.'

I'm creating a temp table in a SQL2K SP and am getting the error mentioned in the title.

-- ALL MILLIMAN NAMED RANGES that will be passed to the Milliman Model v2.3.0
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
      drop table #tmp

CREATE TABLE #tmp (
      CASE_ID varchar(25),
      PREX_ID int,
      PREX2_ID int,
      PLCY_ID int,
      FNDC_ID int,
      OFFR_ID int,
      NumLives char(1),
      Issue_basis char(1),
      Issue_date datetime,
      product_type char(1),
      UL_Illus_AV_1 as real,
      UL_Illus_AV_2 as real,
      UL_Illus_AV_3 as real)


If anyone has any ideas on how to get around this, I'd be appreciative.

Thanks in advance.
-Jim
LVL 66
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAsked:
Who is Participating?
 
GranModCommented:
PAQ-ing the question and refunding 500 points

Thanks !

GranMod
The Experts Exchange
Community Support Moderator of all Ages
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
do you really need to use the REAL (or float) data type, anything against using DECIMAL(20,4)
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
Need?  Probably not, I'm dealing with whole numbers <= 10,000,000.

Also, I tried using float and decimal(20,4) in place of real in my sp, and received similar error messages.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
real and float are unprecise data types, hence indexes are not allowed on them.

if you deal with while numbers, you should use int or bigint
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
Actually, I just figured it out.  I need to get rid of the 'as' in the create table statement.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
good spot :-)
I see, you leave the status of N00b in M$SQL Server !!
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
Wow.  At least I accomplished something today.. :)
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
jimhorn,
>      UL_Illus_AV_1 as real,
>      UL_Illus_AV_2 as real,
>      UL_Illus_AV_3 as real)

 replace the above with the following

    UL_Illus_AV_1  real,
     UL_Illus_AV_2  real,
     UL_Illus_AV_3  real
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
jimhorn,
> I need to get rid of the 'as' in the create table statement.

Sorry I didn't see your post..
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
no objections
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
No objections form my side too
0
 
stopher2475Commented:
"Actually, I just figured it out.  I need to get rid of the 'as' in the create table statement."
You just solved my problem. Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.