Using Calculated Field/Case Labels in other Calculated/Case Fields

Question

Post reply

Using Calculated Field/Case Labels in other Calculated/Case Fields

tjp8

SSCommitted

Points: 1989
More actions
November 10, 2006 at 10:01 am

#114185

Is it possible to use the Label of a Case statement of Calculated field in another Calculated Field.
Ex.
select UM,
UM_DIV = case when um = 'ea' then 1
when um = 'bx' then 10
else 2
end,
(UM_DIV * QTYORD) as UNITNUM
from SALES
GO
When I run it like this I get a message 'Invalid Column namd 'UM_DIV'
I know that I can re-issue the Case Statement in the UNITNUM column, but my actual query is pretty complex, and I was hoping there was a way to use the Case label instead of the entire statement.
Also, I am needing to be able to save this as a view, so any programatic way that can not be created as a view is not useful in this situation.
Thanks,
TJP8

Viewing 6 posts - 1 through 5 (of 5 total)

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

Ken McKelvey SSCoach Points: 18856 More actions · Answer 1

You can do it via a derived table:

SELECT

D.UM

,D.UM_DIV

,D.UM_DIV * D.QTYORD AS UnitNum

FROM (

SELECT S.UM

,S.QTYORD

,CASE S.um

WHEN 'ea' THEN 1

WHEN 'bx' THEN 10

ELSE 2 END AS UM_DIV

FROM Sales S ) D

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 2

I cannot see any syntax error in your code. What I suspect is that the UM_DIV column does not exist.

Here is a code snippet. I tested and worked.

CREATE TABLE #1 (UM VARCHAR(10), UM_DIV INT, QTYORD INT)

INSERT INTO #1 VALUES ('ea', 1, 10)

INSERT INTO #1 VALUES ('bx', 2, 5)

INSERT INTO #1 VALUES ('else', 3, 3)

SELECT * FROM #1

select UM,

UM_DIV = case when UM = 'ea' then 1

when UM = 'bx' then 10

else 2

end,

(UM_DIV * QTYORD)

FROM #1

DROP TABLE #1

tjp8 SSCommitted Points: 1989 More actions · Answer 3

UM_DIV is not in the table. It is a field that is created on the fly with a case statement.

As for the first suggestion, that is definitely another way of doing it that I had not thought about, but I think that may get just as complex.

My biggest issue is that I have a case stament (Field1) that will be used to calculate another Field (FIELD2), That field (Field2) will then be used in another 3 calculated fields.

SO for those 3 calculated fields they will be 2 levels deep in Case statements. It works, but it is difficult to read and maintain. I was hoping there was a way to use the Label, (UM_DIV) that I gave the column in the select statement, later in these other calculated fields.

Thanks,

TJP8

PW-201837 SSC-Insane Points: 20805 More actions · Answer 4

The alias applied to an expression in a SELECT is not available for subsequent use in other expressions in the same SELECT.

Your options are:

- Use a derived table as already suggested.

- Use multiple nested views.

- Add calculated columns to the table definition.

tjp8 SSCommitted Points: 1989 More actions · Answer 5

Thanks, I was hoping to find a way to do it using the expression label, but I had my doubts. Thanks to all for the suggestions.

TJP8