Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2013
    Posts
    1

    Unanswered: Mild headscratcher - query as locate search string

    Hello all,

    Before i share the query and the error, here are the premises.

    I need to search for text patterns in the description field of my incidents, the text patterns are the descriptions of Configuration items where the configuration are part of a collection.

    This works well when i need to search on the ci field because i dont need to use wildcards, with the description however.....

    locate (upper(select description from ci where cinum in (select cinum from collectdetails where collectionnum = 'NAMEOFCOLLECTION')), upper(description)) <> 0

    throws :


    BMXAA4210E - Database error number -104 has occurred.
    An unexpected token "description from ci where cinum" was found following "locate (upper(select". Expected tokens may include: "<space>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.53.95


    It looks like DB" is reading the 1st locate parameter literally as opposed to a nested query.


  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please post whole code of the query.
    Because, root cause of error(s) may be in before or after the position showed in the error message(s).


    By the way,
    please try by enclosing the select subquery in parentheses.

    And, it might be better to put the upper function in the subquery, like "(SELECT UPPER(description) FROM ci ...)".

Tags for this Thread

Posting Permissions

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