View previous topic :: View next topic
|
Author |
Message |
Sanath sekhar Reddy N
New User
Joined: 31 Oct 2012 Posts: 30 Location: INDIA
|
|
|
|
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 |
|
|
prino
Senior Member
Joined: 07 Feb 2009 Posts: 1306 Location: Vilnius, Lithuania
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009 Posts: 1788 Location: Bloomington, IL
|
|
|
|
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) |
|
Back to top |
|
|
SanathsekharReddy N
New User
Joined: 10 Dec 2012 Posts: 2 Location: INDIA
|
|
|
|
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 |
|
|
SanathsekharReddy N
New User
Joined: 10 Dec 2012 Posts: 2 Location: INDIA
|
|
|
|
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 |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
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
Sanath,
Your have almost got your answer but would suggest to give it a shot yourself |
|
Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009 Posts: 1788 Location: Bloomington, IL
|
|
|
|
Sanath, why do you believe that this must be done with SQL? |
|
Back to top |
|
|
Sanath sekhar Reddy N
New User
Joined: 31 Oct 2012 Posts: 30 Location: INDIA
|
|
|
|
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 |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010 Posts: 765 Location: Whitby, ON, Canada
|
|
|
|
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) |
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. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Or just making sure there was an odd number of digits . . . |
|
Back to top |
|
|
|