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

how to get only the number part and avoid the decimal part..


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
pjnithin

Active User


Joined: 22 Dec 2005
Posts: 116

PostPosted: Tue Jul 10, 2007 12:35 pm
Reply with quote

how to avoid the decimal part while selecting a field which is declared to have decimals in db2
Back to top
View user's profile Send private message
sandeep1dimri

New User


Joined: 30 Oct 2006
Posts: 76

PostPosted: Tue Jul 10, 2007 1:07 pm
Reply with quote

May be this will help

SELECT SUBSTR(CHAR(ur-decimal-variable),1,10)
From ur-table
where conditions

Note: I have consider the decimal variable Decimal(11,2).

Sandeep
Back to top
View user's profile Send private message
vijaysharma

New User


Joined: 04 Jul 2007
Posts: 3
Location: Bangalore

PostPosted: Tue Jul 10, 2007 2:52 pm
Reply with quote

Just adding to previous one...........

SELECT SUBSTR(CHAR(table-variable),1,10) INTO
: host-variable
FROM ur-table
WHERE conditions
Back to top
View user's profile Send private message
Raphael Bacay

New User


Joined: 04 May 2007
Posts: 58
Location: Manila, Philippines

PostPosted: Fri Aug 03, 2007 10:03 am
Reply with quote

Hi!


I just found that in SQL you can do this so maybe it is applicable to your problem.

SELECT FLOOR(column name) INTO
:host variable
FROM table
WHERE ...

*FLOOR function will return the non-decimal part of a digit.


Regards.
Back to top
View user's profile Send private message
Alan Voss

New User


Joined: 29 Nov 2006
Posts: 32
Location: Jacksonville, FL

PostPosted: Fri Aug 03, 2007 7:06 pm
Reply with quote

Be sure that floor is what you want, rather than integer:

    select A.I,
    integer(A.i) as I_INT,
    floor(A.I) as I_FLOORED
    from DECIMALS as A
    order by A.i;
    completed successfully.

    I I_INT I_FLOORED
    ------ ----------- ---------
    -5.00 -5 -5
    -4.75 -4 -5
    -4.50 -4 -5
    -4.25 -4 -5
    -4.00 -4 -4
    -3.75 -3 -4
    -3.50 -3 -4
    -3.25 -3 -4
    -3.00 -3 -3
    -2.75 -2 -3
    -2.50 -2 -3
    -2.25 -2 -3
    -2.00 -2 -2
    -1.75 -1 -2
    -1.50 -1 -2
    -1.25 -1 -2
    -1.00 -1 -1
    -0.75 0 -1
    -0.50 0 -1
    -0.25 0 -1
    0.00 0 0
    0.25 0 0
    0.50 0 0
    0.75 0 0
    1.00 1 1
    1.25 1 1
    1.50 1 1
    1.75 1 1
    2.00 2 2
    2.25 2 2
    2.50 2 2
    2.75 2 2
    3.00 3 3
    3.25 3 3
    3.50 3 3
    3.75 3 3
    4.00 4 4
    4.25 4 4
    4.50 4 4
    4.75 4 4
    5.00 5 5

    41 rows selected in 0.07 secs.

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: Fri Aug 03, 2007 7:33 pm
Reply with quote

Good catch 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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
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 Generate random number from range of ... COBOL Programming 3
No new posts Increase the number of columns in the... IBM Tools 3
Search our Forums:

Back to Top