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]
Sam RunyonAsked:
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.

NerdsOfTechTechnology 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

Open in new window

0
PortletPaulfreelancerCommented:
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

Open in new window

An alias formed in one apply can be used in subsequent uses of apply as well which can be really handy.

[edit]
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

Your issues matter to us.

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

Start your 7-day free trial
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.