| View previous topic :: View next topic |
| Author |
Message |
SureshKumar
Intermediate
Joined: 23 Jan 2003
Posts: 211
Topics: 21
|
 Posted: Thu Jan 23, 2003 2:45 pm Post subject: POSSTR function in DB2 V7 |
 |
|
HI,
I have been trying to use the POSSTR function for a particular(weired) purpose.. I want to validate for an occurance of a column in a host variable. V7 of DB2 allows that funcationality - according to their manual. But I get an error if I try. Any inputs is welcome. Thanks.
SQL
| Code: |
SELECT *
FROM CFxxxxxx.MYTABLE
WHERE POSSTR('XXXXXXXXXX',NAME_TXT) <> 0
| |
Variable column name (CHAR 35)
WITH UR ;
|
QUERY MESSAGES:
Argument '2' of scalar function 'POSSTR' is invalid.
DB2 UDB for OS/390 and z/OS V7
| Code: |
-- >>-POSSTR-(--SOURCE-STRING--,--SEARCH-STRING--)---------------><
source-string
An expression that specifies the source string that is to be searched.
The source string can be a character, graphic, or binary string. The
expression can be specified by any of the following:
š A constant
š A special register
š A host variable (including a LOB locator variable)
š A scalar function whose arguments are any of the above
š A column name
š A CAST specification whose arguments are any of the above
š An expression that concatenates (using CONCAT or ||) any of the
above
search-string
An expression that specifies the string that is to be searched for.
The search string can be a character, graphic, or binary string with
an actual length that is no greater than 4000 bytes. The expression
can be specified by any of the following:
š A constant
š A special register
š A host variable (including a LOB locator variable)
š A scalar function whose arguments are any of the above
š A column name
š A CAST specification whose arguments are any of the above
š An expression that concatenates (using CONCAT or ||) any of the
above
|
|
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Thu Jan 23, 2003 3:09 pm Post subject: |
|
|
Suresh kumar,
Can you tell us a little more as to what you are trying to do ? From your query I am assuming that you wanted to search 'xxxxxxxx' in the column NAME_TEXT. Is that right?
Please tell us your requirement by showing us an example of your input and desired output from the sql query
Thanks
Kolusu |
|
| Back to top |
|
|
Bithead
Advanced
Joined: 03 Jan 2003
Posts: 550
Topics: 23
Location: Michigan, USA
|
| Posted: Thu Jan 23, 2003 4:54 pm Post subject: |
|
|
I haven't used this function yet but if the documentation says:
>>-POSSTR-(--SOURCE-STRING--,--SEARCH-STRING--)---------------><
Then shouldn't the query read:
SELECT *
FROM CFxxxxxx.MYTABLE
WHERE POSSTR(NAME_TXT, 'XXXXXXXXXX') <> 0
Just a thought. |
|
| Back to top |
|
|
SureshKumar
Intermediate
Joined: 23 Jan 2003
Posts: 211
Topics: 21
|
| Posted: Fri Jan 24, 2003 8:15 am Post subject: |
|
|
Sorry guys, busy yesterday. My question is I want to use a host variable as a 'Source String' and a DB2 Column as the 'search string'. According to the documentation both the strings can be either host variable or a column name. As Ravi mentioned in his example it will work (V6). Using column name for a search string is a V7 funcationality.
Let me tell you briefly what I am trying to do. User enters a payee name as 'MYCITIBANK'. I have most commonly used name in a table, in this case is name - 'CITIBANK' and 'CITI BANK' etc. I will check if any of these combinations are present in the host variable. There are not very many rows in the table for this combination. Any way, this works on DB2 UDB - Client/Server version according to a friend of mine. DB2 OS/390 manual also says it can be done, how? I have come to the conclusion that its a typo error on the part of IBM. |
|
| Back to top |
|
|
Himesh
CICS Forum Moderator
Joined: 20 Dec 2002
Posts: 80
Topics: 21
Location: Chicago
|
| Posted: Fri Jan 24, 2003 9:44 am Post subject: |
|
|
Hi Suresh,
Are you sure that you are using DB2 Version7 for your testing?
The SQL message that you are getting will be issued by DB2 if Version5 is being used.
Since, Version5 does not support the Column name being used as the "search-string".
Could you re-confirm the same?
regards,
Himesh |
|
| Back to top |
|
|
SureshKumar
Intermediate
Joined: 23 Jan 2003
Posts: 211
Topics: 21
|
| Posted: Fri Jan 24, 2003 10:29 am Post subject: |
|
|
| Himesh, Answer is Yes. I am using V7. |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
