| Author |
Message
|
| ydeonia |
 Posted: Wed Feb 27, 2013 7:58 pm Post subject: Decimal value |
 |
|
Acolyte
Joined: 29 Oct 2012
Posts: 74
|
Hi
How can I chekc if the value is decimal of not . If it has two sequence decimal I need to remove it .
like if I have the value 111111.34 First I need to check if it has decimal of not . If yes then I need to remove '.' and output should be 11111134 .
How can I check for decimal value? |
|
| Back to top |
|
 |
| ydeonia |
| Posted: Wed Feb 27, 2013 11:17 pm Post subject: |
|
|
Acolyte
Joined: 29 Oct 2012
Posts: 74
|
Anybody please ..
How can I check for decimal values? |
|
| Back to top |
|
|
| rekarm01 |
| Posted: Thu Feb 28, 2013 12:48 am Post subject: Re: Decimal value |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
| ydeonia wrote: |
| How can I check for decimal values? |
What does that mean, "check for decimal values"? DECIMAL is a data type. If the question is how to remove the fractional part of a decimal number, the TRUNCATE function may be useful. |
|
| Back to top |
|
|
| nathanw |
| Posted: Thu Feb 28, 2013 1:13 am Post subject: |
|
|
Knight
Joined: 14 Jul 2004
Posts: 550
|
If there is always 2 decimal places then you can check from thr right hand start point and count the chars and then remove the . so that the number becomes non decimal as you have suggested
alternatively for any field which will contain a decimal number then simply run a replace . with NULL or '' on that particular field.
although I am not sure why when you have a valid value ie 1234.56 you would want to only have 123456
_________________
Who is General Failure and why is he reading my hard drive?
Artificial Intelligence stands no chance against Natural Stupidity.
Only the User Trace Speaks The Truth  |
|
| Back to top |
|
|
| ydeonia |
| Posted: Thu Feb 28, 2013 1:28 am Post subject: |
|
|
Acolyte
Joined: 29 Oct 2012
Posts: 74
|
Requirement is like
Price for AB to shop
[Change]
Current (Decimal): 999999(.)99
New (Integer): 99999999
I am stucked to remove the decimal . |
|
| Back to top |
|
|
| nathanw |
| Posted: Thu Feb 28, 2013 1:34 am Post subject: |
|
|
Knight
Joined: 14 Jul 2004
Posts: 550
|
if you follow my comment earlier i have pointed you to 2 methods
read up on ESQL and the processes involved
_________________
Who is General Failure and why is he reading my hard drive?
Artificial Intelligence stands no chance against Natural Stupidity.
Only the User Trace Speaks The Truth |
|
| Back to top |
|
|
| adubya |
| Posted: Thu Feb 28, 2013 1:50 am Post subject: |
|
|
Partisan
Joined: 25 Aug 2011
Posts: 377
Location: GU12, UK
|
Multiply by 100.
 |
|
| Back to top |
|
|
| ydeonia |
| Posted: Thu Feb 28, 2013 8:00 pm Post subject: |
|
|
Acolyte
Joined: 29 Oct 2012
Posts: 74
|
| adubya wrote: |
Multiply by 100.
|
Thanks I have tried this before but the problem is like the data can be without decimal . then I * 100 is not good option .
I just want to check the decimal value first then I can handle on situation .Is there is any way to check the decimal is present in data or not ?
@nathanw
I tried (here is always 2 decimal places) The data can be non decimal too. But yes when it will be there it will be 2 decimal precision.
for NULL
Do you mean to say like
| Code: |
| SET outref."ABSO_COL23_O" = REPLACE(inref."ABSO_COL23_I" ,'.',NULL); |
I tried it fails .
any other suggestion please |
|
| Back to top |
|
|
| rekarm01 |
| Posted: Fri Mar 01, 2013 1:06 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
| ydeonia wrote: |
| Code: |
| SET outref."ABSO_COL23_O" = REPLACE(inref."ABSO_COL23_I" ,'.',NULL); |
|
The double-quotes are not necessary here.
| ydeonia wrote: |
| I tried it fails . |
How does it fail? If there's an error message, it might be useful to share that.
REPLACE() works with strings, not decimals. Perhaps a CAST or two to convert the data back and forth would help. |
|
| Back to top |
|
|
| nathanw |
| Posted: Fri Mar 01, 2013 1:10 am Post subject: |
|
|
Knight
Joined: 14 Jul 2004
Posts: 550
|
as rekarm says you may need to cast
have a look at your code and try the different ways
you are on the right track just keep going
_________________
Who is General Failure and why is he reading my hard drive?
Artificial Intelligence stands no chance against Natural Stupidity.
Only the User Trace Speaks The Truth |
|
| Back to top |
|
|
| sandy vish |
| Posted: Fri Mar 01, 2013 2:31 am Post subject: |
|
|
Apprentice
Joined: 18 Nov 2012
Posts: 27
|
This might help
| Quote: |
DECLARE vDecimal DECIMAL 9999999.99;
DECLARE decimalCast CHARACTER CAST(vDecimal AS CHARACTER);
IF(CONTAINS(decimalCast , '.')) THEN
SET decimalCast = REPLACE(decimalCast,'.','');
END IF;
SET vDecimal = CAST(decimalCast AS DECIMAL); |
|
|
| Back to top |
|
|
| kimbert |
| Posted: Fri Mar 01, 2013 2:58 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
| Quote: |
| if I have the value 111111.34 First I need to check if it has decimal of not . If yes then I need to remove '.' and output should be 11111134 |
I have an important question; What is your message flow trying to do?
WMB has built-in functionality for doing this sort of thing. So you may be struggling to implement this using ESQL when the CWF or TDS parser could do it automatically. |
|
| Back to top |
|
|
| vmcgloin |
| Posted: Fri Mar 01, 2013 6:21 am Post subject: |
|
|
Knight
Joined: 04 Apr 2002
Posts: 560
Location: Scotland
|
I have not thought this through fully or checked my logic, but could you achieve what you want by checking that
(FLOOR(n) is not equal to n) AND (n - FLOOR(n) is not equal to 0) ?
The second part is to avoid .00 but it would be much much better to specify the denomination in the incoming data to avoid catastophic pricing errors when multiplying by 100. |
|
| Back to top |
|
|
| nathanw |
| Posted: Fri Mar 01, 2013 7:10 am Post subject: |
|
|
Knight
Joined: 14 Jul 2004
Posts: 550
|
as the incoming data is never always .00 then simply multiplying by 100 is not acceptable
a simple replace of . is the easiest and simplest method as it it is not there then there is no need to replace etc etc
_________________
Who is General Failure and why is he reading my hard drive?
Artificial Intelligence stands no chance against Natural Stupidity.
Only the User Trace Speaks The Truth |
|
| Back to top |
|
|
| mqjeff |
| Posted: Fri Mar 01, 2013 7:30 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| nathanw wrote: |
| as the incoming data is never always .00 then simply multiplying by 100 is not acceptable |
The only difficulty with multiplying by 100 is that the number might have more than two significant digits after the decimal.
If it is determined that the business logic does not find any meaningful value in any number that is three significant digits or more after the decimal, then there's nothing wrong with multipling by 100 and casting to an integer.
| nathanw wrote: |
| a simple replace of . is the easiest and simplest method as it it is not there then there is no need to replace etc etc |
If the intent is to remove all significant digits after the decimal, I would cast to a string and then SUBSTRING BEFORE the decimal, and then if I had to cast back to an INT.
If the intent is to remove all significant digits after the 2nd significant digits after the decimal, I would multiply by 100, cast to a string, and then SUBSTRING BEFORE.
If the intent is to accurately deal with a value that may or may not have significant digits after the decimal point and limit that value to a specific precision, I would do the correct thing and ensure that the physical parser I am working with - CWF, TDS, DFDL, was configured to understand the limitations and enforce them. |
|
| Back to top |
|
|
|
|
