Case with greater than/less than

[stu1811]

I'm trying to use a case to show increases/decreases. Not sure how to implement this is SQL. It should be something like this..
Select Case Change
Is >0 then 'Increase'
Is <0 then 'Decrease'
Else 'No Change'
From My_Table

[chandu031]

Hi,

Your case statement should look something like this:

SELECT
CASE COUNT
WHEN > 0 THEN 'INCREASE'
WHEN < 0 THEN 'DECREASE'
ELSE 'NO CHANGE'
END AS COUNT
FROM MY_TABLE

[stu1811]

I changed my query to..


Select Case Change
when >0 then 'Increase' /* <---the error points to this line */
when <0 then 'Decrease'
Else null
end as "Change"
From My_Table

and I get the following error message [1]: (Error): ORA-00936: missing expression

[Saii]

try removing 'change' after case keyword

[stu1811]

Change is the column i want to compare > or < 0. If it doenst go there, where does it go?

[SQLSilverSurfer]

I would think that CHANGE might be reserved word somewhere in CASE SQL.

SELECT <myColumnSpec>
CASE
WHEN <A> THEN <somethingA>
WHEN <B> THEN <somethingB>
ELSE <somethingE>
END

[stu1811]

I changed 'Change' to a differnt name and I get the same error. Here is the exact code I'm tryint to use..


SELECT
centers.cen_prt_code, centers.cen_center_num,
case centers.cen_center_num
when < 100 'Less then 100'
when > 200 'Greater than 200'
else 'Between 100 and 200'
end "Center is"
FROM XXXXXX.centers
WHERE ((centers.cen_prt_code = 'XXXXXXXX'))

[Saii]

chk this.....

SELECT
centers.cen_prt_code, centers.cen_center_num,
case
when centers.cen_center_num< 100 'Less then 100'
when centers.cen_center_num > 200 'Greater than 200'
else 'Between 100 and 200'
end "Center is"
FROM XXXXXX.centers
WHERE ((centers.cen_prt_code = 'XXXXXXXX'))

[guitarchick]

Is this oracle? If so, do this:

SELECT
CASE WHEN
CHANGE > 0 THEN 'INCREASE'
WHEN < 0 THEN 'DECREASE'
ELSE 'NO CHANGE'
END AS COUNT
FROM MY_TABLE