CASE in computed column
-
I've successfully used the following statement in queries
CASE WHEN TYPE='O' THEN ORGNAME ELSE LAST_NAME + ', ' + FIRST_NAME END as CUSTNAME
I've tried to create a computed column with this expression and it doesn't like it. I'm new to computed colums stored in the table itself. What am I doing wrong?
-
Can you post your code? This should work, but there may be something a second set of eyes might catch. [You may want to look at a Trigger for Update and Insert as well as Names can change...].
I wasn't born stupid - I had to study.
-
Whoops, never mind, I got it to work. There wasn't any code, I just set up a varchar field in Enterprise Manager and pasted CASE WHEN TYPE='O' THEN ORGNAME ELSE LAST_NAME + ', ' + FIRST_NAME END in the Formula.
You can answer a question about computed fields, though. My understanding is that it should be a readonly field (ie, you never save anything to it), and it will automatically reflect any changes to the underlying columns (TYPE, ORGNAME, LAST_NAME, FIRST_NAME), right? It should basically act just like building the ....as CUSTNAME in a SELECT statement? I shouldn't need a Trigger.
-
Exactly.
-
And...
They are computed on the fly (not saved). But if you need a complex expression you may want to create an index on it and then you would have effectively persisted the information so that next query will perform just an index seek on it
hth
* Noel -
Ah - I do have an index on it. It's a name though - not very like to chnage much.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply