IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

DB2 table , to calculate sum of the 6 characters


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Sanath sekhar Reddy N

New User


Joined: 31 Oct 2012
Posts: 30
Location: INDIA

PostPosted: Mon Oct 14, 2013 12:15 pm
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

Senior Member


Joined: 07 Feb 2009
Posts: 1306
Location: Vilnius, Lithuania

PostPosted: Mon Oct 14, 2013 1:43 pm
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

Moderator Emeritus


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

PostPosted: Tue Oct 15, 2013 1:00 am
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: 1788
Location: Bloomington, IL

PostPosted: Tue Oct 15, 2013 1:05 am
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
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
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

Global Moderator


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

PostPosted: Wed Oct 23, 2013 1:26 pm
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: 1788
Location: Bloomington, IL

PostPosted: Wed Oct 23, 2013 3:32 pm
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: 30
Location: INDIA

PostPosted: Thu Oct 31, 2013 12:49 pm
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: 765
Location: Whitby, ON, Canada

PostPosted: Thu Oct 31, 2013 10:07 pm
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

Moderator Emeritus


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

PostPosted: Thu Oct 31, 2013 10:19 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Load new table with Old unload - DB2 DB2 6
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Substring number between 2 characters... DFSORT/ICETOOL 2
No new posts Multiple table unload using INZUTILB DB2 2
No new posts Check data with Exception Table DB2 0
Search our Forums:

Back to Top