Select statement - is there a limit in SQL language?

grofaty · #1 (**permalink**) 03-04-04, 08:52

Hi,

I am interested if the following sample can be done in SQL statement.

Code:

Table: TAB

ID   OPERATOR     COL1    COL2
------------------------------------
1             <           100        90

And many many more records.
ID is primary key of table TAB.

I would like to compare COL2 value with COL1 according to the OPERATOR.
Someting like: SELECT CASE WHEN COL2 < COL1 THEN 'GOOD' ELSE 'BAD' END FROM TAB;

but instread of specifying '<' character I would like to get that operator from OPERATOR column.
Someting like: SELECT CASE WHEN COL2 OPERATOR COL1 THEN 'GOOD' ELSE 'BAD' END FROM TAB;

But above sql returs error:
SQL0104N An unexpected token "OPERATOR" was found following "SE WHEN
COL1". Expected tokens may include: "IN". SQLSTATE=42601

Is there any way to solve this problem in SQL? Can this be done in SQL?

Thanks,
Grofaty

r937 · #2 (**permalink**) 03-04-04, 09:33

PHP Code:


			
select case when operator = '<'

                    and col2 < col1 then 'good' 

            when operator = '<='

                    and col2 <= col1 then 'good'  

            when operator = '='

                    and col2 = col1 then 'good'  

            when operator = '>'

                    and col2 > col1 then 'good'  

            when operator = '>='

                    and col2 >= col1 then 'good'  

            when operator = '<>'

                    and col2 <> col1 then 'good'  

            else 'bad' end

  from tab

grofaty · #3 (**permalink**) 03-05-04, 01:08

Hi,

Excellent!!! It works for me.

I have heard one SQL expert to say: "There is much greater change of limitation in knowledge then in SQL power".

Thank you very much,
Grofaty

Internet Services

Web Development

Digital Marketing

Consumer Tech