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
 

 

DB2 table , to calculate sum of the 6 characters

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Sanath sekhar Reddy N

New User


Joined: 31 Oct 2012
Posts: 31
Location: INDIA

PostPosted: Mon Oct 14, 2013 12:15 pm    Post subject: DB2 table , to calculate sum of the 6 characters
Reply with quote

Hi

I have the below query .
Iam having a character field in a db2 table ,say char(10).This field contains only numeric values.
I want to retrieve the 6 characters form this field.
and also i want to sum this 6 characters of all the records in the table.

Retrive 6 characters can be done by substr function.
but iam finding difficult to find solution for convert the character to numeric data type and calculating sum. ?
Please can any one help reg this.


Regards
Sanath
Back to top
View user's profile Send private message

prino

Active Member


Joined: 07 Feb 2009
Posts: 982
Location: Oostende, Belgium

PostPosted: Mon Oct 14, 2013 1:43 pm    Post subject:
Reply with quote

You should redesign your database so that rows only contain the correct datatypes, at some stage, due to the amount of data increasing, there will be non-numeric values in this row and your programs will fall over.

This is a bluddy stupid design! Period. Full stop. End of story.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Oct 15, 2013 1:00 am    Post subject:
Reply with quote

Hello,

Why does/did someone believe this is/was an acceptable design?

If you have inherited this, posting some sample of real values may help someone help you.

If possible, i'd change the definition(s) as Prino mentioned.
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Tue Oct 15, 2013 1:05 am    Post subject:
Reply with quote

dick scherrer wrote:
Why does/did someone believe this is/was an acceptable design?

Probably for the same reason that my client's DBAs think it's copacetic to define fields holding dates as CHAR (8) icon_mad.gif
Back to top
View user's profile Send private message
SanathsekharReddy N

New User


Joined: 10 Dec 2012
Posts: 2
Location: INDIA

PostPosted: Wed Oct 23, 2013 11:28 am    Post subject: Reply to: DB2 table , to calculate sum of the 6 characters
Reply with quote

Hi

For example as below
suppose the below are the table fields in the Table XYZ.

FIELD A = 92000001
FIELD B = 23
FIELD C = 10


FIELD A IS defined with data type character and length 10.
Now we have to calculate the sum of the last 6 characters of the field A for all the records.

Suppose table has 10 records.
Filed A is 00000001 then we have to calculate the sum of last 6 digits of all the records in the table for that field means suppose the field A is having 92000001 , 92000002 ,92000003 and so on then sum of (000001 + 000002 + 000003 and so on ).

I believe first we have to convert the character field to digits and then strip 6 characters and the sum the field , can all these functions can be done in single sql statement ?I hope now this will be clear ,Please tell me still if not clear.Please help me?

Regards
Sanath
Back to top
View user's profile Send private message
SanathsekharReddy N

New User


Joined: 10 Dec 2012
Posts: 2
Location: INDIA

PostPosted: Wed Oct 23, 2013 11:29 am    Post subject: Reply to: DB2 table , to calculate sum of the 6 characters
Reply with quote

Hi

For example as below
suppose the below are the table fields in the Table XYZ.

FIELD A = 92000001
FIELD B = 23
FIELD C = 10


FIELD A IS defined with data type character and length 10.
Now we have to calculate the sum of the last 6 characters of the field A for all the records.

Suppose table has 10 records.
we have to calculate the sum of last 6 digits of all the records in the table for that field A means suppose the field A is having 92000001 , 92000002 ,92000003 and so on then sum of (000001 + 000002 + 000003 and so on ).

I believe first we have to convert the character field to digits and then strip 6 characters and the sum the field , can all these functions can be done in single sql statement ?I hope now this will be clear ,Please tell me still if not clear.Please help me?

Regards
Sanath
Back to top
View user's profile Send private message
Pandora-Box

Moderator


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

PostPosted: Wed Oct 23, 2013 1:26 pm    Post subject:
Reply with quote

Akatsukami,

Yeah bad design for not using the correct data types for the values and in my last shop I had the same date problem the very first day I saw dates having mm/dd/yyyy & mm-dd-yy icon_eek.gif

Sanath,
Your have almost got your answer but would suggest to give it a shot yourself
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Wed Oct 23, 2013 3:32 pm    Post subject:
Reply with quote

Sanath, why do you believe that this must be done with SQL?
Back to top
View user's profile Send private message
Sanath sekhar Reddy N

New User


Joined: 31 Oct 2012
Posts: 31
Location: INDIA

PostPosted: Thu Oct 31, 2013 12:49 pm    Post subject: Reply to: DB2 table , to calculate sum of the 6 characters
Reply with quote

Hi

I have tried the below query and it is working fine ,
but i have tried for all characters not for 6 characters,
Table A, FIELD X having characters.

SELECT COUNT(*)
, STRIP(STRIP(
VARCHAR(SUM(CAST(X AS DECIMAL(21,2)))* 100)
,T,'0'),T,'.')
FROM DD.A
WITH UR;
---------+---------+---------+---------+---------+---------+---

---------+---------+---------+---------+---------+---------+---
100806 412157147776500
NUMBER OF ROWS DISPLAYED IS 1
STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100


Thank you for the support.

Regards
Sanath
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 641
Location: Whitby, ON, Canada

PostPosted: Thu Oct 31, 2013 10:07 pm    Post subject:
Reply with quote

Akatsukami wrote:
dick scherrer wrote:
Why does/did someone believe this is/was an acceptable design?

Probably for the same reason that my client's DBAs think it's copacetic to define fields holding dates as CHAR (8) icon_mad.gif
I recently saw a table with a date column defined as DECIMAL(9,0). The most charitable explanation was that they were attempting to prevent a Y10K problem. icon_lol.gif
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Oct 31, 2013 10:19 pm    Post subject:
Reply with quote

Or just making sure there was an odd number of digits . . . 36_2_35.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 Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm
No new posts How to calculate an specific transact... lind sh CICS 2 Tue Sep 13, 2016 9:20 pm
No new posts Command UNIQUE CONSTRAINT on any tabl... rohanthengal DB2 2 Thu Aug 18, 2016 3:48 pm
No new posts Updating online Db2 table kishpra DB2 3 Fri Aug 12, 2016 2:44 pm
No new posts Remove Special Characters from Mainfr... Rodger Zhang All Other Mainframe Topics 6 Wed Jul 06, 2016 1:12 am


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