Re: [U2] Convert to Numeric
On 01/07/11 15:11, Ed Clark wrote: The behavior varies somewhat by platform. Are you using universe or unidata? I'm guessing you're on unidata because you are using the MD conversion code (I haven't seen that used much on universe) I'm guessing MD/MR is site specific, not platform/flavour specific. Coming from a PI/UV background, I've never used UD, and I've almost never ever seen MR conversions. Always MD. Cheers, Wol ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Convert to Numeric
Kevin, Sorry - of course line 212 - it was getting late... Line 212 and the code leading up to it is as follows: X.B.ORIG.APPROVED.AMT = OCONV(V.B.ORIG.APPROVED.AMT, MD2) X.B.ORIG.APPROVED.AMT = FMT(X.B.ORIG.APPROVED.AMT,15R) X.MDEBITS.YTD = SUM(VL.B.APPR.MDEBITS) X.MDEBITS.YTD = OCONV(X.MDEBITS.YTD,MD2) X.MDEBITS.YTD = FMT(X.MDEBITS.YTD,15R) X.MCREDITS.YTD = SUM(VL.B.APPR.MCREDITS) X.MCREDITS.YTD = OCONV(X.MCREDITS.YTD, MD2) X.MCREDITS.YTD = FMT(X.MCREDITS.YTD, 15R) Line 212 X.CRNT.BUDGT.AMT = X.B.ORIG.APPROVED.AMT + X.MDEBITS.YTD - X.MCREDITS.YTD When I pull the data, from the database, straight into Excel V.B.ORIG.APPROVED.AMT does not appear numeric (LEFT justified etc.) though it is defined as numeric in the DICT. Thanks, Greg From: Kevin King precisonl...@gmail.com To: U2 Users List u2-users@listserver.u2ug.org Date: 06/30/2011 09:16 PM Subject:Re: [U2] Convert to Numeric Sent by:u2-users-boun...@listserver.u2ug.org What does A26S.GET.GLS.INFO line 212 say? Yes, you can force values numeric but at what expense? ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Convert to Numeric
aside from the ICONV/OCONV, I'd try moving the three FMT() statements after line 212. Might the FMT() be forcing some spaces to the beginning of the numbers in order to make it a length of 15? X.B.ORIG.APPROVED.AMT = OCONV(V.B.ORIG.APPROVED.AMT, MD2) X.MDEBITS.YTD = SUM(VL.B.APPR.MDEBITS) X.MDEBITS.YTD = OCONV(X.MDEBITS.YTD,MD2) X.MCREDITS.YTD = SUM(VL.B.APPR.MCREDITS) X.MCREDITS.YTD = OCONV(X.MCREDITS.YTD, MD2) Line 212 X.CRNT.BUDGT.AMT = X.B.ORIG.APPROVED.AMT + X.MDEBITS.YTD - X.MCREDITS.YTD X.B.ORIG.APPROVED.AMT = FMT(X.B.ORIG.APPROVED.AMT,15R) X.MDEBITS.YTD = FMT(X.MDEBITS.YTD,15R) X.MCREDITS.YTD = FMT(X.MCREDITS.YTD, 15R) and probably X.CRNT.BUGT.AMT = FMT(X.CRNT.BUGT.AMT,15R) George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users- boun...@listserver.u2ug.org] On Behalf Of Greg Coelho Sent: Friday, July 01, 2011 7:49 AM To: U2 Users List Subject: Re: [U2] Convert to Numeric Kevin, Sorry - of course line 212 - it was getting late... Line 212 and the code leading up to it is as follows: X.B.ORIG.APPROVED.AMT = OCONV(V.B.ORIG.APPROVED.AMT, MD2) X.B.ORIG.APPROVED.AMT = FMT(X.B.ORIG.APPROVED.AMT,15R) X.MDEBITS.YTD = SUM(VL.B.APPR.MDEBITS) X.MDEBITS.YTD = OCONV(X.MDEBITS.YTD,MD2) X.MDEBITS.YTD = FMT(X.MDEBITS.YTD,15R) X.MCREDITS.YTD = SUM(VL.B.APPR.MCREDITS) X.MCREDITS.YTD = OCONV(X.MCREDITS.YTD, MD2) X.MCREDITS.YTD = FMT(X.MCREDITS.YTD, 15R) Line 212 X.CRNT.BUDGT.AMT = X.B.ORIG.APPROVED.AMT + X.MDEBITS.YTD - X.MCREDITS.YTD When I pull the data, from the database, straight into Excel V.B.ORIG.APPROVED.AMT does not appear numeric (LEFT justified etc.) though it is defined as numeric in the DICT. Thanks, Greg From: Kevin King precisonl...@gmail.com To: U2 Users List u2-users@listserver.u2ug.org Date: 06/30/2011 09:16 PM Subject:Re: [U2] Convert to Numeric Sent by:u2-users-boun...@listserver.u2ug.org What does A26S.GET.GLS.INFO line 212 say? Yes, you can force values numeric but at what expense? ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Convert to Numeric
ICONV doesn't convert to numeric, it just moves the decimal place to the right and ICONV moves it the left, at least when using MDx. FMT is what is converting your numbers to strings *1 or +0 will convert convert your strings to numbers (assuming no , or $) George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users- boun...@listserver.u2ug.org] On Behalf Of Greg Coelho Sent: Friday, July 01, 2011 7:55 AM To: U2 Users List Subject: Re: [U2] Convert to Numeric David, Ouch! and I know this... Yes, I need to try first ICONV to take whatever to numeric and then process. As always THANKS, Greg From: David Jordan da...@dacono.com.au To: U2 Users List u2-users@listserver.u2ug.org Date: 06/30/2011 10:14 PM Subject:Re: [U2] Convert to Numeric Sent by:u2-users-boun...@listserver.u2ug.org Hi Greg I may have misinterpreted your email. However just wanted to make sure you understood that if you want to convert $13,000.00 to numeric you need to do an ICONV. OCONV does the opposite and converts 130 to $13,000.00 Regards David Jordan -Original Message- From: u2-users-boun...@listserver.u2ug.org [ mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Greg Coelho Sent: Friday, 1 July 2011 10:50 AM To: U2 Users List Subject: Re: [U2] Convert to Numeric Guys, I'm getting some wildly unexpected values returned... I have attempted formatting (FMT) and converting (OCONV) and have looked at the data which appears to be correct(no O etc...). The DICT has them set at MD2, and 15R. I notice that I am also getting the following message: In CF.OBJ/_A26S.GET.GLS.INFO at line 212 Non-numeric found when numeric required. Any suggestions how I can force these values numeric? Thanks, Greg ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Convert to Numeric
oops meant to type OCONV moves to the left. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users- boun...@listserver.u2ug.org] On Behalf Of George Gallen Sent: Friday, July 01, 2011 9:26 AM To: U2 Users List Subject: Re: [U2] Convert to Numeric ICONV doesn't convert to numeric, it just moves the decimal place to the right and ICONV moves it the left, at least when using MDx. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Convert to Numeric
The behavior varies somewhat by platform. Are you using universe or unidata? I'm guessing you're on unidata because you are using the MD conversion code (I haven't seen that used much on universe) The 2 lines: X.B.ORIG.APPROVED.AMT = OCONV(V.B.ORIG.APPROVED.AMT, MD2) X.B.ORIG.APPROVED.AMT = FMT(X.B.ORIG.APPROVED.AMT,15R) could be replaced with one: X.B.ORIG.APPROVED.AMT = OCONV(V.B.ORIG.APPROVED.AMT,MR2#15) As a general rule, you don't want to do conversion/formatting until you actually output. The exception is when you have to deal with numbers stored with a different number of implied decimal places, and then you need to descale then to a common format before doing math. The definition of what is numeric varies by platform. For example: 0001 A=' 123' 0002 B=' 1,000' 0003 CRT A+1 0004 CRT B+1 0005 CRT ICONV(A,'MD')+1 0006 CRT ICONV(B,'MD')+1 on unidata, you will get non-numeric errors for both lines 3 and 4, but on universe you only get an error for line 4. universe is perfectly happy with leading and trailing spaces in numbers, but neither likes the comma. For both universe and unidata, lines 5 and 6 are fine. iconv does try to convert to numeric, removing spaces and valid numeric punctuation. ICONV will fail if there are alpha characters (it will return 0, but will NOT print a message--you need to check the STATUS() function yourself), and might return the incorrect number if you include descaling or decimal placement codes, but simple 'MD' doesn't do that. If you suspect invalid numeric data, you should use the NUM() function to test (or iconv, also testing the STATUS() result) On Jul 1, 2011, at 9:26 AM, George Gallen wrote: ICONV doesn't convert to numeric, it just moves the decimal place to the right and ICONV moves it the left, at least when using MDx. FMT is what is converting your numbers to strings *1 or +0 will convert convert your strings to numbers (assuming no , or $) George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users- boun...@listserver.u2ug.org] On Behalf Of Greg Coelho Sent: Friday, July 01, 2011 7:55 AM To: U2 Users List Subject: Re: [U2] Convert to Numeric David, Ouch! and I know this... Yes, I need to try first ICONV to take whatever to numeric and then process. As always THANKS, Greg From: David Jordan da...@dacono.com.au To: U2 Users List u2-users@listserver.u2ug.org Date: 06/30/2011 10:14 PM Subject:Re: [U2] Convert to Numeric Sent by:u2-users-boun...@listserver.u2ug.org Hi Greg I may have misinterpreted your email. However just wanted to make sure you understood that if you want to convert $13,000.00 to numeric you need to do an ICONV. OCONV does the opposite and converts 130 to $13,000.00 Regards David Jordan -Original Message- From: u2-users-boun...@listserver.u2ug.org [ mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Greg Coelho Sent: Friday, 1 July 2011 10:50 AM To: U2 Users List Subject: Re: [U2] Convert to Numeric Guys, I'm getting some wildly unexpected values returned... I have attempted formatting (FMT) and converting (OCONV) and have looked at the data which appears to be correct(no O etc...). The DICT has them set at MD2, and 15R. I notice that I am also getting the following message: In CF.OBJ/_A26S.GET.GLS.INFO at line 212 Non-numeric found when numeric required. Any suggestions how I can force these values numeric? Thanks, Greg ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Convert to Numeric
Guys, I'm getting some wildly unexpected values returned... I have attempted formatting (FMT) and converting (OCONV) and have looked at the data which appears to be correct(no O etc...). The DICT has them set at MD2, and 15R. I notice that I am also getting the following message: In CF.OBJ/_A26S.GET.GLS.INFO at line 212 Non-numeric found when numeric required. Any suggestions how I can force these values numeric? Thanks, Greg ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Convert to Numeric
What does A26S.GET.GLS.INFO line 212 say? Yes, you can force values numeric but at what expense? ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Convert to Numeric
Hi Greg I may have misinterpreted your email. However just wanted to make sure you understood that if you want to convert $13,000.00 to numeric you need to do an ICONV. OCONV does the opposite and converts 130 to $13,000.00 Regards David Jordan -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Greg Coelho Sent: Friday, 1 July 2011 10:50 AM To: U2 Users List Subject: Re: [U2] Convert to Numeric Guys, I'm getting some wildly unexpected values returned... I have attempted formatting (FMT) and converting (OCONV) and have looked at the data which appears to be correct(no O etc...). The DICT has them set at MD2, and 15R. I notice that I am also getting the following message: In CF.OBJ/_A26S.GET.GLS.INFO at line 212 Non-numeric found when numeric required. Any suggestions how I can force these values numeric? Thanks, Greg ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
