473,320 Members | 2,098 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

MSSQL8.0 : Computed field, UDF, select statement

Plater
7,872 Expert 4TB
I found a few topics on this but nothing that solved my problem.
I have a table lets call P (as an example)
P has the following usefull fields:
-------------------------------------------------------------------------------------------------
P_ID | CategoryID | Pre-Text | RevNumber | ComputedColumn1
-------------------------------------------------------------------------------------------------

Now I want [ComputedColumn1] to be a string concatination of [Pre-Text]+(thing)+[RevNumber]
Where (thing) is a value from the [Name] column in my Category table, whos entry has the same ID number as the stored CategoryID.

Now I know how to do this with a select statement, but I was hoping to have that [ComputedColumn1] hold the value(or the formula to reference the value)

With a select statement:
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.  (P.[Pre-Text] + C.[Name] + P.RevNumber ) as [MyColumn] 
  3. FROM P, Category 
  4. WHERE 
  5. Category.CID=P.CategoryID 
  6.  
And supposidly, based on other posts here, my UDF would be something like
Expand|Select|Wrap|Line Numbers
  1. CREATE function MyFunc(@A_ID AS int)
  2. RETURNS varchar(50) 
  3. AS
  4. RETURN (SELECT Name FROM Category WHERE Category.CID= @A_ID)
  5. go
  6.  
but it tells me:
Expand|Select|Wrap|Line Numbers
  1. Msg 170, Level 15, State 31, Procedure eatit, Line 4
  2. Line 4: Incorrect syntax near 'RETURN'.
  3.  
I have tried all kinds of things but I would like my formula for [ComputedColumn1] to be something like ([Pre-Text]+MyFunc(CategoryID)+[RevNumber])

Any thoughts for how to do this? (Or a completely differnt and better way to do it?)
Apr 16 '07 #1
0 1397

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: ghasem | last post by:
Dear mySQL community, I have once again turned to the user groups for a problem I cannot solve myself! Atually, I have read all I can from the newsgroups on this but I could not solve it myself....
2
by: tperovic | last post by:
Using SS2K, I'm getting the following error while bulk inserting: Column 'warranty_expiration_date' cannot be modified because it is a computed column. Here is my bulk insert statement: ...
7
by: Rick Caborn | last post by:
Does anyone know of a way to execute sql code from a dynamically built text field? Before beginning, let me state that I know this db architecture is built solely for frustration and I hope to...
4
by: Henning N?rg?rd | last post by:
I'm working as software developer mostely om SQL-server platform. On SQL-server we are using a lot of "Computed Columns" Does anyone know if that is possible to do in DB2 too ? I mean - define...
3
by: John young | last post by:
I have been looking for an answer to a problem and have found this group and hope you can assist . I have been re doing a data base I have made for a car club I am with and have been trying to...
25
by: Lyn | last post by:
Hi, I am working on a genealogy form. The only table (so far) lists everybody in the family, one record per person. Each record has an autonum ID. The parent form (frmMainForm) displays the...
1
by: John Dalberg | last post by:
I am getting "Input string was not in a correct format" error in a line like this: Int32.Parse(((DataRowView)e.Item.DataItem).Row.ItemArray.ToString()) Field #8 comes from a SQL Server stored...
7
by: Aamir Mahmood | last post by:
Hi All I have DataTable object. Is there a way that I can know which fields (columns) in the table are computed. Apparantly the DataTable.Columns returns all columns both computed and other....
6
by: cj | last post by:
I have a table with 3 fields (field1 is text, field2 and field3 are int) I want to write a sql command for VB that will return field1 and the sum of field2 and field3. For instance if the table...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.