How to compare a column to a parameter in Oracle?

my code goes like this..

SELECT ALL TBL_MONITOR.ITEM_ID, TBL_MONITOR.CATEGORY, 
TBL_MONITOR.BRANDNAME, TBL_MONITOR.PRICE, TBL_MONITOR.QUANTITY
FROM TBL_MONITOR
where 
case when :pricetag = 'Great' then tbl_monitor.price >= :para_price end,
case when :pricetag = 'Less' then tbl_monitor.price >= :para_price end

this part does not work, it says.. missing keyword ==> >= :para_price end

==> >= :para_price end,

wat i want to do is if the user input 'Greater' the reports will show pr开发者_运维技巧ices greater than the ':para_price' how would i fix this? Thanks a lot in advance :)

Try out this

WHERE 
(:pricetag = 'Great' AND tbl_monitor.price >= :para_price)
OR
(:pricetag = 'Less' AND tbl_monitor.price <= :para_price)

The SQL CASE statement (apart from the syntax errors you are making) is not a flow control instruction like in C or PHP. It's an expression that returns a value and not a way to decide which part of your code will be executed.

You didn't care to say what you are trying to accomplish or how is it failing, but it looks like you want a simple expression:

WHERE (:pricetag = 'Great' AND tbl_monitor.price >= :para_price)
OR (:pricetag = 'Less' AND tbl_monitor.price <= :para_price)

Try like this:

SELECT ALL TBL_MONITOR.ITEM_ID, 
      TBL_MONITOR.CATEGORY, 
      TBL_MONITOR.BRANDNAME, 
      TBL_MONITOR.PRICE, 
      TBL_MONITOR.QUANTITY
FROM TBL_MONITOR
WHERE (:pricetag = 'Great' AND tbl_monitor.price >= :para_price)
OR (:pricetag = 'Less' AND tbl_monitor.price <= :para_price)

SELECT ALL TBL_MONITOR.ITEM_ID, TBL_MONITOR.CATEGORY, 
TBL_MONITOR.BRANDNAME, TBL_MONITOR.PRICE, TBL_MONITOR.QUANTITY
FROM TBL_MONITOR
where 
1 = CASE 
      WHEN :pricetag = 'Great' 
      AND  tbl_monitor.price >= :para_price
      THEN  1
      WHEN :pricetag = 'Less' 
      AND  tbl_monitor.price <= :para_price
      THEN 1
      ELSE 0
    END