using case in select with int field 1

[jlsmithhartfiel]

Hi! I've successfully used the case statement with character fields but am having trouble with numbers.

select case c.label
       when 'sold'
	     then c.label + ' Units'
	   when 'pending'
	     then c.label + ' Units'
	   else
	     'Cancelled/Expired/Withdrawn' 	    
	   end
	   as type,
	   mlsnum,
	   p.proptype,
	   s.label,
	   listprice,
	   case p.salesprice
	     when 0 
		   then 'Zero Price'
      	 /*when is null
		   then 'Null'*/
		 when p.salesprice > 1000
		   then 'Too High'
		 /*when < 1000
		   then '- 30%'*/
		 else 'ok'
	   end as errors
from prp p 
left join customsearchlistbox c
       on c.fieldname = 'liststatus'
      and c.value = p.liststatus
left join customsearchlistbox s
       on s.fieldname = 'style'
	  and s.value = p.style
where p.liststatus <> 'act' 
  and (p.proptype in (@PropTypes)
   or (p.proptype = 'res'
  and p.proptype in (@PropTypes)
  and s.value = p.style
  and substring(s.label,1,4) in (@Styles)
      )
	  ) order by p.proptype, type desc, mlsnum

I get an error &quot;incorrect syntax near < &quot;. I've tried:
when > 1000

when p.salesprice > 1000

Any suggestions would be appreciated!

TIA, Jessica

 

[sunila7]

Hi,

Try this............


select case c.label
when 'sold'
then c.label + ' Units'
when 'pending'
then c.label + ' Units'
else
'Cancelled/Expired/Withdrawn'
end
as type,
mlsnum,
p.proptype,
s.label,
listprice,
case
when p.salesprice=0
then 'Zero Price'
/*when is null
then 'Null'*/
when p.salesprice> 1000
then 'Too High'
/*when < 1000
then '- 30%'*/
else 'ok'
end as errors
from prp p
left join customsearchlistbox c
on c.fieldname = 'liststatus'
and c.value = p.liststatus
left join customsearchlistbox s
on s.fieldname = 'style'
and s.value = p.style
where p.liststatus <> 'act'
and (p.proptype in (@PropTypes)
or (p.proptype = 'res'
and p.proptype in (@PropTypes)
and s.value = p.style
and substring(s.label,1,4) in (@Styles)
)
) order by p.proptype, type desc, mlsnum


Sunil

 

[jlsmithhartfiel]

Thanks Sunil for the quick and accurate response! I didn't realize you could use the case without specifying the field. Thanks! Jessica