Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

SQL CODE -420 with VALUE CLAUSE

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Amit_Singh47

New User


Joined: 15 May 2013
Posts: 22
Location: India

PostPosted: Tue Jun 11, 2013 12:55 pm    Post subject: SQL CODE -420 with VALUE CLAUSE
Reply with quote

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
View user's profile Send private message

Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Tue Jun 11, 2013 1:59 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Amit_Singh47

New User


Joined: 15 May 2013
Posts: 22
Location: India

PostPosted: Tue Jun 11, 2013 2:45 pm    Post subject:
Reply with quote

Hi Pandora,

this is not working too...can u please suggest something else?
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Tue Jun 11, 2013 2:47 pm    Post subject:
Reply with quote

When you mean not working what is the error that you get?
Back to top
View user's profile Send private message
Amit_Singh47

New User


Joined: 15 May 2013
Posts: 22
Location: India

PostPosted: Tue Jun 11, 2013 3:20 pm    Post subject:
Reply with quote

same -420 error
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Tue Jun 11, 2013 3:33 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Amit_Singh47

New User


Joined: 15 May 2013
Posts: 22
Location: India

PostPosted: Tue Jun 11, 2013 4:06 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Amit_Singh47

New User


Joined: 15 May 2013
Posts: 22
Location: India

PostPosted: Tue Jun 11, 2013 4:29 pm    Post subject:
Reply with quote

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. icon_biggrin.gif
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Tue Jun 11, 2013 4:48 pm    Post subject:
Reply with quote

Ah ok .. Glad you figgured it out icon_smile.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts PL/I code tuning/Performance improvement Virendra Shambharkar PL/I & Assembler 3 Mon Dec 05, 2016 11:57 am
No new posts What is the code in CLIST to enable t... jackzhang75 CLIST & REXX 1 Fri Dec 02, 2016 3:02 am
No new posts IDEAL - Code Retrieval yugendran CA Products 0 Fri Nov 25, 2016 3:27 pm
This topic is locked: you cannot edit posts or make replies. Need to code a Rexx PGM sundaram.naveen CLIST & REXX 18 Thu Oct 06, 2016 6:45 pm
No new posts Need Help in understanding what is th... mainakdalal PL/I & Assembler 9 Mon Oct 03, 2016 8:03 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us