query problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: query problem

Hybrid View

  1. #1
    Join Date
    Jul 2001
    Posts
    181

    Angry

    I have got the following cursor

    CURSOR entities_to_delete IS
    SELECT * FROM tibex_deleteAdmin
    WHERE deleteEntityId,deleteEntitytype,timestamp IN (
    SELECT deleteEntityId, deleteEntityType, max(timestamp) timestamp
    FROM tibex_deleteAdmin
    GROUP BY deleteEntityId, deleteEntitytype
    )
    AND deleteAction=(
    SELECT deleteAction
    FROM tibex_deleteActionEnum
    WHERE ShortDesc='DA_DEL'
    )
    ORDER by timestamp;

    I get an error ORA-00920: invalid relational operator,

    I know it's something basic on line
    WHERE deleteEntityId,deleteEntitytype,timestamp IN (

    Any ideas...


    Thanks in advance

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    You have > 1 values in front of IN:

    deleteEntityId,deleteEntitytype,timestamp

    Try this, I am not sure it will work but try it:

    Code:
    CURSOR entities_to_delete IS 
    SELECT * FROM tibex_deleteAdmin 
    WHERE to_char(deleteEntityId)||' '||to_char(deleteEntitytype)||' '||to_char(timestamp,'dd.mm.yyyy') IN ( 
    SELECT to_char(deleteEntityId)||' '||to_char(deleteEntitytype)||' '||to_char(max(timestamp),'dd.mm.yyyy') FROM tibex_deleteAdmin 
    GROUP BY deleteEntityId, deleteEntitytype 
    ) 
    AND deleteAction=( 
    SELECT deleteAction 
    FROM tibex_deleteActionEnum 
    WHERE ShortDesc='DA_DEL' 
    ) 
    ORDER by timestamp;

  3. #3
    Join Date
    Jul 2001
    Posts
    181
    That seem to work thanks!

    But why did it by changing the numbers to characters help the IN operator?

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by julian
    You have > 1 values in front of IN:
    Not really sure where you're going with this, but the query can be fixed by enclosing your set in parenthesis:
    Code:
    CURSOR entities_to_delete IS 
       SELECT * FROM tibex_deleteAdmin 
       WHERE (deleteEntityId,deleteEntitytype,timestamp )IN ( 
          SELECT deleteEntityId, deleteEntityType, max (timestamp) timestamp 
          FROM tibex_deleteAdmin 
          GROUP BY deleteEntityId, deleteEntitytype 
          ) 
    AND deleteAction=( 
       SELECT deleteAction 
       FROM tibex_deleteActionEnum 
       WHERE ShortDesc='DA_DEL' 
       ) 
    ORDER by timestamp;
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

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



Click Here to Expand Forum to Full Width