# logic on the Alias

Trying to run logic on the Alias.. seems simple enough but not having any luck.

Any help?

CASE
WHEN Cast(CHEMVAL.value AS NUMERIC(5, 1)) = 0
THEN Cast(CHTKVOL.value AS NUMERIC(5, 1))
ELSE Cast(CHTKVOL.value AS NUMERIC(5, 1)) / Cast(CHEMVAL.value AS NUMERIC(5, 1))
END AS [Days Left at Current Rate]
,CASE
WHEN (
SELECT Days Left at CURRENT Rate
) > 30
THEN ">30 Days"
ELSE (
SELECT Days Left at CURRENT Rate
)
END AS [30Days]
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Technology ScientistCommented:
What if you made the first into a subquery then do the last CASE on the outside:
``````SELECT s.*,
CASE
WHEN s.[Days Left at CURRENT Rate] > 30
THEN ">30 Days"
ELSE
THEN s.[Days Left at CURRENT Rate]
END AS [30Days]
FROM
(
SELECT
/* ... */
CASE
WHEN CAST(CHEMVAL.value AS NUMERIC(5, 1)) = 0
THEN Cast(CHTKVOL.value AS NUMERIC(5, 1))
ELSE
CAST(CHTKVOL.value AS NUMERIC(5, 1)) / Cast(CHEMVAL.value AS NUMERIC(5, 1))
END AS [Days Left at Current Rate],

/* ... */
) s
``````
0
freelancerCommented:
It is NOT possible to define a column alias, then re-use that same alias in a single select clause
This is true of almost all SQL variants; and SQL Server is one of those.

As NerdsOfTech has suggested you can use a "derived table" approach,

OR you can use an apply operator, like this

``````SELECT
CHEMVAL.value
, CHTKVOL.value as vol_value
, CASE
WHEN
ca.[Days Left at Current Rate]
> 30
THEN '>30 Days'
ELSE ca.[Days Left at Current Rate]
END AS [30days]
FROM CHEMVAL
INNER JOIN CHTKVOL ON CHEMVAL.somecol = CHTKVOL.othercol
CROSS APPLY (
SELECT
CASE
WHEN CAST(CHEMVAL.value AS numeric(5, 1)) = 0
THEN CAST(CHTKVOL.value AS numeric(5, 1))
ELSE CAST(CHTKVOL.value AS numeric(5, 1)) / CAST(CHEMVAL.value AS numeric(5, 1))
END AS [Days Left at Current Rate]because
) ca
``````
An alias formed in one apply can be used in subsequent uses of apply as well which can be really handy.

by the way, just in case this seems counter-intuitive, please note that the order that SQL is written is NOT the way it is executed. The FROM and WHERE clauses get executed first, so once you know this it becomes easier to understand I believe.

see: A Beginnerâ€™s Guide to the True Order of SQL Operations
1

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.