Problem with calculated column formula

Question

Problem with calculated column formula

Andrew Shaw-487330

SSC Veteran

Points: 239
More actions
April 20, 2008 at 5:51 am

#121155

I've got the following formula in a calculated column:
(case when ([ColumnA] = 'I') then (convert(char(4),[ColumnB]))
else (case when ([ColumnC] = 1) then (convert(char(4),[ColumnD]))
else (convert(char(4),'6450')) end) end)
as you can see the value '6450' is hard coded for the final else staement. I'd like to move away from this and extract this value from the DB in case it ever changes using:
SELECT top 1 ipt_blbcode FROM IPT where ipt_unkfortlb=1 (This currently returns 6450.)
However, when I try and use this in the final else of the case statement in my calculated column, it won't allow me. The syntax is good as the follwing works OK in a SQL SELECT statement in Query Analyzer:
select
(case when ([ColumnA] = 'I') then (convert(char(4),[ColumnB]))
else (case when ([ColumnC] = 1) then (convert(char(4),[ColumnD]))
else (select top 1 ipt_blbcode from ipt where ipt_unkfortlb=1) end) end) as test, * from csd
Any ideas how I can incorporate this requirement into the calculated column?

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 1

SELECTs aren't allowed in calculated columns. Nothing you can do about it. Use a view, instead. Be careful when you write the view... improperly written views can suck the life out of a database.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Andrew Shaw-487330 SSC Veteran Points: 239 More actions · Answer 2

Thanks. I suspected as much.

Can you expand on the use of a view, not clear to me where I need to use it.

Andrew

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 3

This is how you could do it in a view... but a view is not needed. This also works directly in code. It works because there is only 1 value in "derived table d".

[font="Courier New"] SELECT CASE

WHEN csd.ColumnA = 'I' THEN CONVERT(CHAR(4),csd.ColumnB)

WHEN csd.ColumnC = 1 THEN CONVERT(CHAR(4),csd.ColumnD)

ELSE d.Ipt_BlbCode

END AS Test,

*

FROM Csd,

(SELECT TOP 1 Ipt_BlbCode FROM ipt WHERE ipt_unkfortlb=1) d[/font]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Andrew Shaw-487330 SSC Veteran Points: 239 More actions · Answer 4

Yep, I know how to do all of that. Thought you were somehow suggesting using the view in the calculated column formula.

I need this value writing up into the table and not to be derived on the fly.

Can I call a UDF in a calculated column formula ?

or am I going to have to live with what I've already got ?

Regards

Andrew

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 5

Andrew Shaw (4/21/2008)
Yep, I know how to do all of that. Thought you were somehow suggesting using the view in the calculated column formula.
I need this value writing up into the table and not to be derived on the fly.
Can I call a UDF in a calculated column formula ?
or am I going to have to live with what I've already got ?
Regards
Andrew

Yes, you can call a UDF as part of the formula for a calculated column.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Andrew Shaw-487330 SSC Veteran Points: 239 More actions · Answer 6

Can you give an example, as I tried it without much success.

Thanks

Andrew

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 7

Sure... post the CREATE TABLE command where you want the calculated column.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Andrew Shaw-487330 SSC Veteran Points: 239 More actions · Answer 8

??

No completely lost now.

Can I replace the last else with a function ?

You've lost me talking about CREATE TABLE in place of the computed column

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 9

Yes, you can include the last ELSE with a function. I wanted you to post the CREATE TABLE statement so I could build something to it to show you how.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Andrew Shaw-487330 SSC Veteran Points: 239 More actions · Answer 10

Andrew Shaw-487330

SSC Veteran

Points: 239

April 22, 2008 at 7:10 am

#805490

oops sorry

will do

Andrew Shaw-487330 SSC Veteran Points: 239 More actions · Answer 11

You already got it at the beginning of thread:

[CSD_NomIPTBLB] AS (case when ([CSD_LoanType_ContOrItemOwner] = 'I') then (convert(char(4),[CSD_IIPTBLB])) else (case when ([CSD_CIPT_Confirmed] = 1) then (convert(char(4),[CSD_CIPTBLB])) else (convert(char(4),'6450')) end) end),

I haven't included the create for whole table (it's huge). One I inherited in new job.

Andrew

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 12

Ok, so just write the function and use it in that...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Andrew Shaw-487330 SSC Veteran Points: 239 More actions · Answer 13

Thats the bit I'm stuck on.

I've written a function like this to return the value 6450 from the DB:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER FUNCTION [dbo].[fn_GetTLB] ()

RETURNS char(4)

as

BEGIN

DECLARE @TLB char(4)

SET @TLB = ''

SET @TLB =

(SELECTtop 1 ipt_blbcode

FROM IPT

where ipt_unkfortlb=1

)

RETURN (@TLB)

END

How do I call this function in the column formula to replace this bit: (convert(char(4),'6450')), that's the bit I can't get to work.

Andrew

RBarryYoung SSC Guru Points: 143329 More actions · Answer 14

Like this:

(convert(char(4),dbo.fn_GetTLB()))

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]