Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: Select statement - is there a limit in SQL language?

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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 
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •