Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2012
    Posts
    2

    Unanswered: db2 instr method

    Hi,

    I am using in a query the instr function, which seems to work fine with db2 v9.7. When testing with 9.5 that is the one I need it doesn't work and I get this error:

    "Caused by: java.sql.SQLSyntaxErrorException: [OWLS][DB2 JDBC Driver][DB2]No function or procedure was found with the specified name (INSTR) and compatible arguments.".

    Is it that 9.5 doesn't accept instr and 7 yes? Or might it be something else?

    Tried to replace by posstr but doesn't work either, as it doesn't accept my search-string:

    posstr(nvl(q.NODE_REFERENCE_TYPES,'???')||'???',ap s.NODE_REFERENCE_TYPE_CONDITION) > 0

    where aps is a temporary table:
    ( select nvl(cond.NODE_REFERENCE_TYPE_CONDITION,'???') NODE_REFERENCE_TYPE_CONDITION, cond.NODE_REFERENCE_CODE, app.*
    from authorization_group_apps app ,authorization_group ag ,node_reference_auth_group cond
    where 1=1
    and app.AUTHORIZATION_GROUP_ID = ag.AUTHORIZATION_GROUP_ID
    and ag.DELETED_IND='N'
    and ag.AUTHORIZATION_GROUP_ID = cond.AUTHORIZATION_GROUP_ID
    ) aps

    Any suggestions would be appreciated! Thank you

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Is it that 9.5 doesn't accept instr and 7 yes?
    Right.

    ... doesn't work ...
    What error message did you got?
    Many persons many times wrote "doesn't work" is not a good description of problem.


    Anyhow,
    search-string of POSSTR doesn't support a column name.
    If this is your case, try to use SYSFUN.LOCATE.
    Note: search-string and source-string of LOCATE function is reversed to POSSTR function.

  3. #3
    Join Date
    Jul 2012
    Posts
    2
    My bad, sorry. I only sent the caused by. Here you have the error:

    org.hibernate.exception.SQLGrammarException: could not execute query
    at org.hibernate.exception.SQLStateConverter.convert( SQLStateConverter.java:67)
    at org.hibernate.exception.JDBCExceptionHelper.conver t(JDBCExceptionHelper.java:43)
    at org.hibernate.loader.Loader.doList(Loader.java:221 6)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(L oader.java:2104)
    at org.hibernate.loader.Loader.list(Loader.java:2099)
    at org.hibernate.loader.custom.CustomLoader.list(Cust omLoader.java:289)
    at org.hibernate.impl.SessionImpl.listCustomQuery(Ses sionImpl.java:1695)
    at org.hibernate.impl.AbstractSessionImpl.list(Abstra ctSessionImpl.java:142)
    at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl. java:152)


    Now it seems to work with the function locate.

    Thank you for your prompt reply and help!
    Last edited by sergio_tino; 07-26-12 at 04:15.

Posting Permissions

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