469,323 Members | 1,567 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,323 developers. It's quick & easy.

Case with greater than/less than

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
Apr 19 '07 #1
8 34964
chandu031
78 Expert
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
Apr 20 '07 #2
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
Apr 20 '07 #3
Saii
145 Expert 100+
try removing 'change' after case keyword
Apr 20 '07 #4
Change is the column i want to compare > or < 0. If it doenst go there, where does it go?
Apr 20 '07 #5
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
Apr 20 '07 #6
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'))
Apr 20 '07 #7
Saii
145 Expert 100+
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'))
Apr 20 '07 #8
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
Apr 20 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by Ryan | last post: by
10 posts views Thread by clueless_google | last post: by
3 posts views Thread by J-P-W | last post: by
8 posts views Thread by John Ratliff | last post: by
6 posts views Thread by Jim Richards | last post: by
reply views Thread by Gurmeet2796 | last post: by
reply views Thread by mdpf | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.