Update or insert of view or function failed because it contains a derived or constant field.

Question

0.00/5 (No votes)

See more:

SQL

SQL-Server

database

, +

i use this code to collect the name

SQL

SELECT        ISNULL(Col2, '') + ' ' + ISNULL(Col3, '') + ' ' + ISNULL(Col4, '') + ' ' + ISNULL(Col5, '') + ' ' + ISNULL(Col6, '') + ' ' + ISNULL(Col7, '') + ' ' + ISNULL(Col8, '') + ' ' + ISNULL(Col9, '') + ' ' + ISNULL(Col10, '') AS Fullname
FROM            dbo.tabl

What I have tried:

and i try to remove the space form it by this code
bout it didn't work with views so what should i do

GO

UPDATE tabl SET Fullname= LTRIM(Fullname)
GO

Posted 30-Dec-18 23:36pm

el_tot93

Updated 31-Dec-18 3:10am

v2

Add a Solution

Comments

[no name] 31-Dec-18 7:14am

Not sure wheter LTRIM is really what you Need... Anyway Fullname seems not to be a column of table tabl (it is an alias fieldname for your concatenation) and therefore no Chance to update it ;)

Maybe this is what you like to do?


SELECT LTRIM(       ISNULL(Col2, '') + ' ' + ISNULL(Col3, '') + ' ' + ISNULL(Col4, '') + ' ' + ISNULL(Col5, '') + ' ' + ISNULL(Col6, '') + ' ' + ISNULL(Col7, '') + ' ' + ISNULL(Col8, '') + ' ' + ISNULL(Col9, '') + ' ' + ISNULL(Col10, '')) AS Fullname
FROM            dbo.tabl

el_tot93 31-Dec-18 7:22am

THAT WHAT I LOOKING FOR THX

2 solutions

Solution 2

Just to give you an alternative. If the full name is used in multiple queries, it could be a good idea to define it as a computed column in the database. In other words

SQL

ALTER TABLE dbo.tabl
  ADD FullName AS RTRIM( LTRIM( 
   ISNULL(Col2, '') + ' ' + ISNULL(Col3, '') + ' ' + ISNULL(Col4, '') + ' ' + 
   ISNULL(Col5, '') + ' ' + ISNULL(Col6, '') + ' ' + ISNULL(Col7, '') + ' ' + 
   ISNULL(Col8, '') + ' ' + ISNULL(Col9, '') + ' ' + ISNULL(Col10, '')
   ));

After this, you can use the FullName column in your queries without repeating the formula everywhere:

SQL

SELECT FullName FROM dbo.tabl;

As a side note, I would suggest using meaningful names for the columns instead of col1, col2, ... Later when you come back to the code, it's hard to remember which column was which. If these are part of names perhaps something like NamePart1, NamePart2, ...

Posted 31-Dec-18 3:11am

Wendelius

Comments

[no name] 31-Dec-18 9:14am

Very good alternative and comments, a 5

Wendelius 1-Jan-19 7:29am

Thank you!

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

User 11060979 · Accepted Answer · 2018-12-31T01:43:00

Ok then, Looks like you are really looking for this:

SELECT LTRIM( 
   ISNULL(Col2, '') + ' ' + ISNULL(Col3, '') + ' ' + ISNULL(Col4, '') + ' ' + 
   ISNULL(Col5, '') + ' ' + ISNULL(Col6, '') + ' ' + ISNULL(Col7, '') + ' ' + 
   ISNULL(Col8, '') + ' ' + ISNULL(Col9, '') + ' ' + ISNULL(Col10, '')
   ) AS Fullname
 FROM dbo.tabl