Converting COBOL picture format number to regular decimal number using SQL (2 Viewers)

[Summer123]

Hello,
does anyone know what built in function i can use to conver a COBOL number into a regular decimal number? For example

COBOL Picture
9(09)v9(06)

so the number would be 000009059790000

and the conversion would be 6 digits to the right of decimal and 9 digits to the left. so the output i would want is 9059.79

i was doing the following but its displaying the 0's in front and i didnt want that...so i am getting 000009059.790000

LEFT(Income, 9) + '.' + RIGHT(Income, 6)

please help...

 

[Summer123]

ok so i got the last 2 digits to work
Expr1: Left([Income],9)+'.'+Mid([Income],10,2)

but see the first part before the decimal point can vary... so some numbers could be as follows...does anyone know how to loose the leading zeros's?

000009059790000 000009059.79
000009343350000 000009343.35
000011736770000 000011736.77
000011121940000 000011121.94

 

[gemma-the-husky]

very close - you need a cdbl to convert your new string to a number - try

cdbl(Left([Income],9) & '.' &right(income,6))

or

cdbl(Left([Income],9) &'.'& mid(income,10))

(the 2 in your mod statement is optional - using mid without a qualifying number takes all the characters - depends whether you are sure you only need 2)

note you need to be careful with real numbers. A number like 123.79 MAY not be completely represented in access, and may actually be 123.78999964 (say!) - so be careful with how you use the reals, especially testing for specific equalities.

there are other formats such as currency or decimal that may work better - depemds how much precision you need.

 

[Summer123]

that worked like a charm! thank you!

 

[Summer123]

one question gemma-the-husky, please

how do i write an if else statement in the select statement itself? so for example:

SELECT Income, '$' & cdbl(Left([Income],9) & '.' &right(income,6)) AS income_Dollar_amt
FROM Income_Record;

gives me the result below

Income income_Dollar_amt
00000905979000{ #Error
000011121940000 $11121.94
00000934335000{ #Error
00000368050000{ #Error
00000504018000{ #Error
000011736770000 $11736.77


I want to right a simple if-else statement that would say if the results is #Error then right "Amount is not formatted correctly", Iknow we have to IIF statement but not sure how to do it.. any help is appreciated..
thank you
Summer

 

[Beetle]

Well, essentially, if the length of the income field is not 15 characters then your formatting won't work, so (for example);

SELECT Income, IIf(Len([Income])=15, "$" & CDbl(Left([Income],9) & '.' & Right([Income],6)), "Invalid Income Format") AS income_Dollar_amt
FROM Income_Record;

 

[Summer123]

Thank you for the post Sean, however my question is more if the length is correct but instead of having 9(09)V(06) format they sent in 9(09)V99 format, then it would show the output as #Error, but instead i'd want it to say "Invalid Format" or instead of the #Error it would say "Invalid Format"...

thanks Summer

 

[Beetle]

Well, I don't know anything about COBOL picture number formats, but it seems like the verification you're describing should be taking place at the point at which this;

9(09)v9(06)

gets converted to;

000009059790000

Maybe I'm missing something?

 

[gemma-the-husky]

if the left is always 9 chars

then you want this sort of logic

so given (with a space to make it clear)

000009079 36000

integerbit = val(left(strg,9) returns 9079
decimalbit = val(mid(strg,10) - ie all the other characters return 36000

now we need to move the decimal point right by the number of characters

so

decimalbit = decimalbit /(1- len(cstrg(decimalbit)))

which should turn 36000 into .36000

and finalanswer = integerbit + decimalbit

so something like this - not tested, but will be close
integerlen is 9 in this case - the length of the whole number portion. just mid the string to get the rest - irrespective of the number of chars

function coboltoreal(instrg as string, integerlen as long) as double
integerbit = val(left(strg,integerlen) 
decimalbit = val(mid(strg,integerlen+1)
decimalbit = decimalbit /(1 ^ len(cstrg(decimalbit)))
coboltoreal = integerbit + decimalbit
end function

 

[Summer123]

thank you. the other option above works just fine, however when i run the query for the amount where they are spaces, it gives an output of #Error, now that is fine however I have a Code where it exports this out to Excel with a click of a button. So during the Export process, it gives a warning "DB was unable to append all the data to the table the conent of fields in 2 record were delete and 0 receord were loast due to key violations.", so in order to avoid that i was hoping to somehow replace the #Error to some string message so that it would not cause this warning in my export to excel process. Does that make sense?

 

[Summer123]

thinking more about it, i could make a table and replace the #Error to a string then export that... but is there another way to do this within the query or a better way to do it, where the #Error output is replaced by something like "Incorrectly formatted amount"?

 

[gemma-the-husky]

just change the code to test for spaces, and treat as zero.

you didn't say the data may not be well-formed!

 

[Summer123]

sorry thought my examples above gave away that the amount could be formatter incorrectly. Ok so it could be null or it can be in an incorrect format...so see the first line...
Income income_Dollar_amt
00000905979000{ #Error
000011121940000 $11121.94

i can have the null part working if i do the following:
IIf(Income Is Not Null,'$' & CDbl(Left([Income],9) & '.' & Right([Income],6)), "Format Incorrect"

but if the amount ends in *{ then i also want to say Format incorrect so something like below...
IIF(Income Like "*{", "Format Incorrect")
but can you have more then 2 arguments in IIF statement, if i read correctly then it says IIF function can have 3 arguments expr, truepart, falsepart

so then how do i do it so its 2 false parts, for example if its null or if it ends in { then i want to say "Format incorrect"??? any ideas??

thank you once again for your assistance
Summer

 

[gemma-the-husky]

if the format may not be correct then you may need to do somethingl ike this

split the first part and the second part. test that they are both numeric. if they are, then carry on, if not, then do something else

I have no idea what form cobol numbers can take. I just assumed if they were coming from a coobl system they would be well formed.


thinking about it then - does a * at the end have meaning, or is it an error? Are there any other valid codes? What indicates a minus? etc, etc

 

[Summer123]

so do u know how one can write an or if else statement? reason i ask is dont want to get too much in COBOL but there are positive values and negative value for signed numeric value and then for numeric values (no signs) there are no characters at the end.

Signed numeric values - the sign is sent as part of the right most digit in the value
{ +0
a-i represent 1-9
j-r represent -1 thru -9
} represents -0

so for example 319E would be 31.95, and 31.9N would be -31.95

But not to get carried away in signed value...

My field is numeric, but sometimes when i import the file, this field may contain signed values and at that point i need to error them out. so if the value is null, or if it contains any of the following at the end of the string ({,a, b, c, d, e, f, g, h,i, j,k, l, m, n, o, p, q, r, } ) then to error stating wrong format.
this is why i was using Like "*{" - basically stating anything ending in { ...but for some reason its not working.. not sure if it doesnt like my Like statement??? and also how do i say the following in IIF statement

If income is not null then CDbl(Left([Income],9) & '.' & Right([Income],6))
else if income is null then "Format incorrect"
else if income like "*{" then Format incorrect"
else if income like "*a" then Format incorrect"
else if income like "*b" then Format incorrect"
.
.
.
and so on... basically i dont know how to right the if else if in Query ..please help

 

[Summer123]

I've tried this but it still doesnt work... any help would be appreciated

SELECT Income,
IIf(Income Is Not Null,('$' & CDbl(Left([Income],9) & '.' & Right([Income],6))),
(IIF(Income Is Null, "Format Incorrect", (IIF(Income Like "*{", "Format Incorrect", "1"))))
)AS Income_Actual_Dollar_amt
From TableIncome;

 

[Summer123]

I've tried this as well... but still doesnt recognize the Like *{ statement

SELECT Income,
IIf(Income Is Not Null,('$' & CDbl(Left([Income],9) & '.' & Right([Income],6))),
(IIF(Income Like "*{", "Format Incorrect", "1"))
)AS Income_Actual_Dollar_amt
From TableIncome;

 

[Beetle]

Try this (as an expression in a query);

IIf([Income] Is Null Or (Right([Income],1) In ('{','a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','{')),"Format Incorrect","$" & CDbl(Left([Income],9) & "." & Right([Income],6)))

 

[Summer123]

OMG!!! Thank you!!!! That worked like a charm.... and really this was a pain for the past 3 days so THANK YOU THANK YOU THANK YOU!!!!

 

[Summer123]

i just learned something new today and THANK YOU Sean... so what u did was put the is null and the charcters as the criteria... instead of the other way around... makes me feel like an idiot but THANK YOU Sean!