|
View previous topic :: View next topic
|
| Author |
Message |
Amit_Singh47
New User
Joined: 15 May 2013
Posts: 22
Location: India
|
|
|
|
SELECT
S.MEMBER_ID
, VALUE (A.GROUP,' ')
, VALUE (A.SUBGROUP,' ')
, VALUE (S.LAST_NAME,' ')
, S.CUMBH_FIRST_NAME
, VALUE (S.DATE_OF_BIRTH,'0001-01-01')
, VALUE (S.SSN_NO,' ')
, VALUE (S.SEX_CD,' ')
FROM
ACCOUNT_MEMBER A,
SUBSCRIBER S
WHERE
MEMBER_ID > 0
WITH UR;
this query on execution gives -420 sql code, which says "THE VALUE OF A CHARACTER STRING ARGUMENT WAS NOT ACCEPTABLE TO THE FUNCTION".
However all the fields on which I have used VALUE clause are defined as CHAR in the TABLE except Date_of_Birth which is defined as DATE in TABLE. I am currently using DB2 version 8. |
|
| Back to top |
|
 |
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
Try this
| Code: |
SELECT
S.MEMBER_ID
, VALUE (A.GROUP,' ')
, VALUE (A.SUBGROUP,' ')
, VALUE (S.LAST_NAME,' ')
, S.CUMBH_FIRST_NAME
, VALUE (S.DATE_OF_BIRTH,DATE('0001-01-01'))
, VALUE (S.SSN_NO,' ')
, VALUE (S.SEX_CD,' ')
FROM
ACCOUNT_MEMBER A,
SUBSCRIBER S
WHERE
MEMBER_ID > 0
WITH UR; |
|
|
| Back to top |
|
|
Amit_Singh47
New User
Joined: 15 May 2013
Posts: 22
Location: India
|
|
|
|
Hi Pandora,
this is not working too...can u please suggest something else? |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| When you mean not working what is the error that you get? |
|
| Back to top |
|
|
Amit_Singh47
New User
Joined: 15 May 2013
Posts: 22
Location: India
|
|
|
|
| same -420 error |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
Can you recheck the data type of these fields
| Code: |
, VALUE (A.GROUP,' ')
, VALUE (A.SUBGROUP,' ')
, VALUE (S.LAST_NAME,' ')
, VALUE (S.SSN_NO,' ')
, VALUE (S.SEX_CD,' ') |
| Quote: |
-420 THE VALUE OF A STRING
ARGUMENT WAS NOT ACCEPTABLE
TO THE function-name FUNCTION
Explanation:
A string argument did not conform to the requirements
of the function. For example, a character string passed
to the DECIMAL function did not conform to the rules
for forming an SQL integer or decimal constant.
System action:
The statement cannot be processed.
Programmer response:
Change the argument value so that it conforms to the
requirements of the function as specified in SQL
Reference.
SQLSTATE:
22018 |
|
|
| Back to top |
|
|
Amit_Singh47
New User
Joined: 15 May 2013
Posts: 22
Location: India
|
|
|
|
Pandora, i understand your concern,
but the datatypes are as follows:
group : char(15)
subgroup : char(15)
last name : char(26)
ssn : char(10)
sex : char(01) |
|
| Back to top |
|
|
Amit_Singh47
New User
Joined: 15 May 2013
Posts: 22
Location: India
|
|
|
|
the problem got solved guys...
there was a mistake not in the value clause
but the ordering of attribute names
in the FETCH was incorrect.
thanks for your inputs everyone.  |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
Ah ok .. Glad you figgured it out  |
|
| Back to top |
|
|
|
|
