# I need to take the value and make it say ">30 Days"

I need to take the value and make it say ">30 Days"

Here is what my statement looks like

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]

if the value is 30 or greater just display ">30 Days"
###### 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.

Database AnalystCommented:
I think a CASE in the CASE can be added like:

``````CASE WHEN Cast(CHEMVAL.value AS NUMERIC(5, 1)) = 0
THEN Cast(CHTKVOL.value AS NUMERIC(5, 1))
ELSE
CASE WHEN (Cast(CHTKVOL.value AS NUMERIC(5, 1)) / Cast(CHEMVAL.value AS NUMERIC(5, 1)) ) >= 30
THEN ‘>30 Days’  ELSE Cast(CHTKVOL.value AS NUMERIC(5, 1)) / Cast(CHEMVAL.value AS NUMERIC(5, 1)) END
END as [Days Left at Current Rate]
``````
0
Senior DBACommented:
That will fail because you can't convert the string ">30 Days" to a numeric value.  You'd need to cast the computation to a string to avoid that issue.
0
Author Commented:
Scott could you help with the context of the problem.. I am new.. learning at a slow place. :)

Sam
0
Business Systems Analyst , ex-Senior Application EngineerCommented:
That will fail because you can't convert the string ">30 Days" to a numeric value.  You'd need to cast the computation to a string to avoid that issue.
reason being some of the rows still returning numeric values (for those < 30), hence it will prompt with a converting error.

if the value is 30 or greater just display ">30 Days"

it should shown as ">=30 Days" , inclusive of 30.

something like this should worked:

``````CASE WHEN
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 >= 30 THEN '>= 30 Days'
ELSE
concat(cast(
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 varchar(100)) , ' Days')
END	as [Days Left at Current Rate]
``````
0
You cant mix-n-match data types in a column....

So if you want a string, then any result in that column must also be a string (varchar)

``````   CASE WHEN Cast(CHEMVAL.value AS NUMERIC(5, 1)) = 0
THEN case when Cast(CHTKVOL.value AS NUMERIC(5, 1)) > 30
then '>30 Days'
else cast(Cast(CHTKVOL.value AS NUMERIC(5, 1)) as varchar)
end
ELSE case when Cast(CHTKVOL.value AS NUMERIC(5, 1)) / Cast(CHEMVAL.value AS NUMERIC(5, 1)) > 30
then  '>30 Days'
else cast( Cast(CHTKVOL.value AS NUMERIC(5, 1)) / Cast(CHEMVAL.value AS NUMERIC(5, 1)) as varchar)
end
END as [Days Left at Current Rate]
``````
0

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.

Commented: