I am not sure what is being accomplished by this
<EXPLETIVE>"peculiar"</EXPLETIVE> design.

I do not think it is possible to have arithmetical
operators resolved using column values at run-time
in SQL. The query could be constructed using Native
Dynamic SQL and used in a PL/SQL block.

Nevertheless, here is a kludge of a solution for this
bizarre problem.

Test Scenario
--------------

DROP TABLE T;

CREATE TABLE T
(
id NUMBER,
sal NUMBER,
opr VARCHAR2(3)
);

INSERT INTO T VALUES (10,1000,'>');
INSERT INTO T VALUES (20,2000,'<');
INSERT INTO T VALUES (30,3000,'>=');
INSERT INTO T VALUES (40,4000,'<=');
INSERT INTO T VALUES (50,5000,'=');
INSERT INTO T VALUES (60,6000,'<>');
INSERT INTO T VALUES (70,7000,'<');
INSERT INTO T VALUES (80,8000,'<=');
INSERT INTO T VALUES (90,9000,'>=');

COMMIT;

SELECT a.*
FROM T a,
(
SELECT *
FROM T
WHERE t.id = 40
) b
WHERE b.opr = '>' AND (a.sal > b.sal)
OR b.opr = '>=' AND (a.sal >= b.sal)
OR b.opr = '<' AND (a.sal < b.sal)
OR b.opr = '<=' AND (a.sal <= b.sal)
OR b.opr = '=' AND (a.sal = b.sal)
OR b.opr = '<>' AND (a.sal <> b.sal)
;

-----Original Message-----
From: ashok
[mailto:oracledba-ezmlmshield-x91985232.[Email address protected]
Sent: Wednesday, September 05, 2007 1:32 AM
To: LazyDBA Discussion
Subject: How to use column value as an operator


Hi Gurus,

I have column which has value of some comparison operator. I need to use
this column as a operator for my query.

ex: select * from tabl1 where salary (select opr from tabl1 where b=
1) (select salary from tabl1 where b=1);

let opr be '>' for b=1 then

I need to have the records of all from tabl1 where salary is > than
salary of b=1.

Please let me know as soon as possible.


Regards,
Ashok.

Oracle LazyDBA home page