.PL63 .PN1 L....T..T.........................................R.L................A .FO2 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA>@@ [[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[ UNOTE 51 Page # of 2 .HE4 ‰ ‰ Ref: UNOTE 51~ From‰:‰ Customer Services 24 Oct 89~ L....T..T.......T.......T.......T.......T.......T.......T.......T....R Re‰ :‰ Incorrectly formatted decimal numbers when using the spreadsheet and database.~ AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA>@@ [[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[ L.......T.......T.......T.......T.......T.......T.......T.......T....J PROBLEM@@ EEEEEEE Numerical data is incorrectly displayed and formatted when the Spreadsheet "pipe" function is used to extract information from a@@ BBBB Database. This only occurs in Uniplex Versions 6.01 and 6.10 if the decimal character specified by DECTAB in the file "UAP/uniplex.sys" is different from: DECTAB='.' CAUSE@@ EEEEE The "Database Forms" and "Database Query" applications do not respect the values defined by the DECTAB entry in the file "UAP/uniplex.sys". Therefore, databases can only contain numbers in decimal format, if the decimal separator is the "." dot (as in the English product). The Spreadsheet works correctly with different characters as decimal separators, for example the comma "," . However, the "pipe" function extracts the "." dot as part of the@@ BBBB numerical data from the database. The Spreadsheet doesn't believe that it is true numerical data because of this "." dot, and therefore inserts the information as text into the cell. H.....L.T.......T.......T.......T.......T.......T.......T.......T....J Note: In the Spreadsheet, text is left justified, and numerical data@@ IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII in right justified.@@ IIIIIIIIIIIIIIIIIII L.......T.......T.......T.......T.......T.......T.......T.......T....J For example: ...L....T.......T.......T.......T.......T.......T.......T.......T....J If the value "123.45" exists is the database and the following "pipe" call is used in the spreadsheet, (similar to the standard@@ IIIIIIIIIIIIIIIIIIIIIIIIII demo spreadsheet use file : UAP/demo/sales.fcst)@@ IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII @pipe("select anno_1987 from anno where cognome='";A2;"'") the value "123.45" will be left justified in the spreadsheet. Any other formats, such as for example "3 decimal places" will be ignored. ........T.L...R.L.....R.L.......R.......T.......T.......T.......T..... .JN [1] A B @@ TTTTTTTTTTTTTTTTTTTTTT 1 1987@@ TTTTT 2 Bianchi 123.45@@ TTTTT .JY L.......T.......T.......T.......T.......T.......T.......T.......T....J WORK-AROUNDS@@ EEEEEEEEEEEE ...L....T.......T.......T.......T.......T.......T.......T.......T....J Change decimal separator to "." dot@@ CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC The first method is to change the decimal separator for all of the Uniplex applications to match the "hard coded" separator used by "Database Forms" and "Database Query". .HE0 .PA Therefore in the file "UAP/uniplex.sys", change the DECTAB entry to be: DECTAB='.'@@ BBBBBBBBBB For the new decimal separator to take effect, Uniplex must be re-invoked, after compiling "uniplex.sys" with the command: syscomp  -s@@ BBBBBBBBBBB Use the multiply function in the formula@@ CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC For situations where it is necessary to use a different decimal separator, such as a comma, the problem can be overcome by using the "*" (multiply) function. The idea is to: ........H..L....T.......T.......T.......T.......T.......T.......T....J - first, multiply the data from the "select" statement by the number of decimal places required, - and then, to divide the result of the "pipe" statement by the same number of decimal places. ...L....T.......T.......T.......T.......T.......T.......T.......T....J Therefore the standard structure of the formula in the spreadsheet could be similar to: = 1/A * pipe("select A*field_name from table_name where clause")@@ EEEEE EEIIIIIIIIII IIIIIIIIII IIIIII and the variable "A" would take on the values:@@ E .JN ...L....T.......A.......L.......T.......T.......T.......T.......T....R 1 if no decimal places are required 10 if one decimal place is required 100 if two decimal places are required 1000 if three decimal places are required etc ... .JY ...L....T.......T.......T.......T.......T.......T.......T.......T....J Therefore, for the example given earlier, the formula should become: =1/100 * pipe("select 100*anno_1987 from anno where cognome='";A2;"'")@@ EEEEEEE EEEE And if the area of the spreadsheet was formatted to 3 decimal places, the screen would display: ........T.L...R.L.....R.L.......A.......T.......T.......T.......T..... .JN [1] A B     @@ TTTTTTTTTTTTTTTTTTTTTTT 1 1987@@ TTTTT 2 Bianchi 123,450@@ TTTTT .JY ...L....T.......T.......T.......T.......T.......T.......T.......T....J This example shows that the decimal separator is a comma (ie DECTAB=',') and that the values are correctly right justified. ...H.....L......T.......T.......T.......T.......T.......T.......T....J Note: Obviously, this increases the processing overhead for this@@ IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII function. @@ IIIIIIIIII .........L......T.......T.......T.......T.......T.......T.......T....J When DECTAB is a comma, the separator between fields in the @@ IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII functions should be a semi colon ";" as shown in the example.@@ IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII