Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Unanswered: What's wrong with posstr() ?

    Friends, got stuck in simple thing I guess.

    While writing simple UDF, I came across posstr function.
    Follwing
    Code:
    select x,z,posstr(x,z) from  ( 
    
    values (cast('0123456789ABCDEF' as char(16)), cast('5' as char(1)) ,cast('A' as char(1)) )
    
        ) xy(x,y,z) ;
    gives error

    Code:
    SQL0132N  A LIKE predicate or POSSTR scalar function is not valid because the 
    first operand is not a string expression or the second operand is not a 
    string.  A LOCATE or POSITION scalar function is not valid because the first 
    operand is not a string or the second operand is not a string expression.  
    SQLSTATE=42824
    
    SQL0132N  A LIKE predicate or POSSTR scalar function is not valid because the first operand is not a string expression or the second operand is not a string.  A LOCATE or POSITION scalar function is not valid because the first operand is not a string or the second operand is not a string expression.
    
    Explanation: 
    
    A LIKE predicate or POSSTR scalar function appearing in the statement is
    not valid because either the first operand is not a string expression or
    the second operand is not a string. A LOCATE scalar function or POSITION
    scalar function appearing in the statement is not valid because either
    the first operand is not a string or the second operand is not a string
    expression.
    Please help/advise with an example if possible.
    Thanks
    DBFinder

  2. #2
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Use
    select x, z, LOCATE(x,z)
    ) instead....

    or instead of
    cast('0123456789ABCDEF' as char(16)), cast('5' as char(1)), cast('A' as char(1))
    use

    select x,z,posstr(x,char(z)) from (

    values ('0123456789ABCDEF', '5', 'A') ) xy(x,y,z) ;
    Lenny
    Last edited by Lenny77; 12-05-12 at 16:58.

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The problem is the second parameter:

    Code:
    ... or the second operand of POSSTR can be one of:
    *  a constant
    *  a special register
    *  a host variable
    *  for LOCATE and POSITION only, a column reference
    *  a scalar function whose operands are any of the above
    *  an expression concatenating any of the above
    It cannot be a column.

    Andy

  4. #4
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Question

    Quote Originally Posted by ARWinner View Post
    The problem is the second parameter:

    Code:
    ... or the second operand of POSSTR can be one of:
    *  a constant
    *  a special register
    *  a host variable
    *  for LOCATE and POSITION only, a column reference
    *  a scalar function whose operands are any of the above
    *  an expression concatenating any of the above
    It cannot be a column.

    Andy
    Ok. Why the following is working ?

    Code:
    select x, y, z, posstr(char(x),char(z)) posstr
    from  (select cast('0123456789ABCDEF' as char(16)), 
    cast('5' as char(1)), 
    cast('A' as char(1))
    from sysibm.sysdummy1
          ) xy(x,y,z) ;
    ;
    Result

    X Y Z POSSTR
    0123456789ABCDEF 5 A 11
    Lenny

  5. #5
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Nothing worked !!

    Lenny,

    Sorry, but nothing worked (only literal strings worked so far, I need to use variables in UDF). Actually I called after trying all this.

    May be something wrong with my setup.

    I am using windows XP DB29.5fp7 ESE.

    I will do test on linux box shortly.

    Thanks for yor time.

    DBFinder

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by Lenny77 View Post
    Ok. Why the following is working ?

    Code:
    select x, y, z, posstr(char(x),char(z)) posstr
    from  (select cast('0123456789ABCDEF' as char(16)), 
    cast('5' as char(1)), 
    cast('A' as char(1))
    from sysibm.sysdummy1
          ) xy(x,y,z) ;
    ;
    Result



    Lenny
    Lenny,

    I tried this under LUW V9.5 and V9.7 and it still comes up with the same error. What version are you using?

    Andy

  7. #7
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Quote Originally Posted by ARWinner View Post
    Lenny,

    I tried this under LUW V9.5 and V9.7 and it still comes up with the same error. What version are you using?

    Andy
    Same here, I tried on 9.5ese on windows and 9.7 expC on linux, none worked.

    I am going to write my own function.

    Regards for all.

    Code:
    </home/dbfinder> Welcome dbfinder !
    
    [dbfinder@centos ~]$ db2 connect to ids
    
       Database Connection Information
    
     Database server        = DB2/LINUX 9.7.1
     SQL authorization ID   = dbfinder
     Local database alias   = IDS
    
    
    [dbfinder@centos ~]$ db2 "select x,z,posstr(x,z) from  ( values (cast('0123456789ABCDEF' as char(16)), cast('5' as char(1)) ,cast('A' as char(1)) ) ) xy(x,y,z)"
    SQL0132N  A LIKE predicate or POSSTR scalar function is not valid because the
    first operand is not a string expression or the second operand is not a
    string.  A LOCATE or POSITION scalar function is not valid because the first
    operand is not a string or the second operand is not a string expression.
    SQLSTATE=42824
    [dbfinder@centos ~]$ db2 "select x,z,posstr(x,z) from ( values ('0123456789ABCDEF', '5', 'A') ) xy(x,y,z)  "
    SQL0132N  A LIKE predicate or POSSTR scalar function is not valid because the
    first operand is not a string expression or the second operand is not a
    string.  A LOCATE or POSITION scalar function is not valid because the first
    operand is not a string or the second operand is not a string expression.
    SQLSTATE=42824
    [dbfinder@centos ~]$
    
    [dbfinder@centos ~]$ db2 "select x, y, z, posstr(char(x),char(z)) posstr from  (select cast('0123456789ABCDEF' as char(16)), cast('5' as char(1)), cast('A' as char(1))from sysibm.sysdummy1 ) xy(x,y,z)"
    SQL0132N  A LIKE predicate or POSSTR scalar function is not valid because the
    first operand is not a string expression or the second operand is not a
    string.  A LOCATE or POSITION scalar function is not valid because the first
    operand is not a string or the second operand is not a string expression.
    SQLSTATE=42824
    [dbfinder@centos ~]$

  8. #8
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by ARWinner View Post
    Lenny,

    I tried this under LUW V9.5 and V9.7 and it still comes up with the same error. What version are you using?

    Andy
    I am using DB2 V10 z/os.

    The following is working too, without any problem:

    select x, y, posstr(x,char(z)) posstr
    from (
    select '0123456789ABCDEF', '5', 'A'
    from sysibm.sysdummy1
    ) xy(x,y,z) ;
    Lenny

  9. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    That explains it LUW and Z/OS are not exactly the same yet.

    Andy

  10. #10
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs down

    Quote Originally Posted by ARWinner View Post
    That explains it LUW and Z/OS are not exactly the same yet.

    Andy
    But the following works in a same way as in LUW:

    Code:
    select x, y, posstr(x,z) posstr
    from  ( 
    select '0123456789ABCDEF', '5', 'A'
    from sysibm.sysdummy1
          ) xy(x,y,z) ;
    Result:

    SQL0171N The data type, length or value of argument "2" of routine "POSSTR" is incorrect. SQLSTATE=42815
    Lenny

  11. #11
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    You have to use LOCATE instead of POSSTR.
    select x, z, LOCATE(x,z)
    It'll work perfectly.

    Lenny

  12. #12
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Quote Originally Posted by ARWinner View Post
    The problem is the second parameter:

    Code:
    ... or the second operand of POSSTR can be one of:
    *  a constant
    *  a special register
    *  a host variable
    *  for LOCATE and POSITION only, a column reference
    *  a scalar function whose operands are any of the above
    *  an expression concatenating any of the above
    It cannot be a column.

    Andy

    But Andy, my second parameter was a variable declared in body of UDF.
    I was wondering if it does not work even with a variable, what is the use of it.

    Has anybody found any workaround for this in past ??

    DBFinder

  13. #13
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Worked, Thanks x 100 !!

    Quote Originally Posted by Lenny77 View Post
    You have to use LOCATE instead of POSSTR.

    It'll work perfectly.

    Lenny
    Thanks Lenny one hundered times.

    You saved my day.

    DBFinder


    Code:
    [dbfinder@centos ~]$ db2 "values locate ('A','0123456789ABCDEF')"
    
    1
    -----------
             11
    
      1 record(s) selected.
    
    [dbfinder@centos ~]$ db2 "select locate(x,y) from ( values('A','0123456789ABCDEF')) xy(x,y)"
    
    1
    -----------
             11
    
      1 record(s) selected.
    
    [dbfinder@centos ~]$

  14. #14
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs up

    Quote Originally Posted by DBFinder View Post
    Thanks Lenny one hundered times.

    You saved my day.

    DBFinder


    Code:
    [dbfinder@centos ~]$ db2 "values locate ('A','0123456789ABCDEF')"
    
    1
    -----------
             11
    
      1 record(s) selected.
    
    [dbfinder@centos ~]$ db2 "select locate(x,y) from ( values('A','0123456789ABCDEF')) xy(x,y)"
    
    1
    -----------
             11
    
      1 record(s) selected.
    
    [dbfinder@centos ~]$
    Ok. You can use also the following (NEW!):

    Code:
    select x, y, z, position(z,x,CODEUNITS16) posstr
    from  (select cast('0123456789ABCDEF' as char(16)), 
    cast('5' as char(1)), 
    cast('A' as char(1))
    from sysibm.sysdummy1
          ) xy(x,y,z) ;
    Lenny

  15. #15
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Quote Originally Posted by Lenny77 View Post
    Ok. You can use also the following (NEW!):

    Code:
    select x, y, z, position(z,x,CODEUNITS16) posstr
    from  (select cast('0123456789ABCDEF' as char(16)), 
    cast('5' as char(1)), 
    cast('A' as char(1))
    from sysibm.sysdummy1
          ) xy(x,y,z) ;
    Lenny
    Great, I understand from the docs online, POSITION is newer and works similar to LOCATE.

    Purpose has been server and UDF is in place already.

    Regards

Posting Permissions

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