a little confused about the transform() function problem 5

[bharons]

after I created a cursor in vfp and filled it in with some data. I found a problem with the asterix value in my report. Is there something wrong with my writing format as mentioned below?

create cursor skedul1 (norekening N(13), ke N(2), pokok_kr N(12), bunga_kr N(12), pokok_sd N(12))
insert into skedul1 (norekening, ke, pokok_kr, pokok_sd, bunga_kr) values (1312124120052, 1, 0, 26400, 850000)
insert into skedul1 (norekening, ke, pokok_kr, pokok_sd, bunga_kr) values (1312124120052, 2, 0, 26400, 850000)

then on command button with code below

select skedul1
mket01 = transform(skedul1.norekening,"999.99.9999.9999")
mket02 = skedul1.ke
mket03 = transform(skedul1.pokok_kr,"999.999.999.999")
mket04 = transform(skedul1.pokok_sd,"999.999.999.999")
mket05 = transform(skedul1.bunga_kr,"999.999.999.999")

REPORT FORM "E:\My Foxy\Vfp8\Report\myslip.frx" PREVIEW

report gives me an asterisk for mket01 = ***.**.****.****
Meanwhile for mket02 to mket 05 there are no problems.

Are there really any problems in writing my coding?
I'm using VFP 8...

before and after I thank you for your guidance

 

[costin77]

Try to increase the width of the field where you see *.

 

[Tore Bleken]

Make the fields in the report a little wider.

Having said that, the expression "999.99.9999.9999" looks weird. What's the point of that expression?

 

[bharons]

that

Make the fields in the report a little wider.

Having said that, the expression "999.99.9999.9999" looks weird. What's the point of that expression?

expression of "999.99.9999.9999" is register number,
like the number above 1312124120052 becomes 131.21.2412.0052
After I expand the report column for that number, I still get an asterix, unless I use the transform function(@L,"999,99,9999,9999")
can appear 131,21,2412,0052
looks confusing and inappropriate

 

[Tore Bleken]

I think the problem is that the number is bigger than VFP can handle. Try this formula instead:

Chrtran(transform(skedul1.norekening,"@R ###-##-####-####"),'-','.')

Update: Store the value as a string instead, c(13), since that's what it really is. Then you can use

Transform(skedul1.norekening,"@R 999-99-9999-9999")

 

[mjcmkrsr]

Hi,

You have to use the comma instead of the dot

x = 1234567891234
?TRANSFORM(x, "9999,99,999,9999")
?TRANSFORM(x, "9999.99.999.9999")

hth

MarK

 

[bharons]

Hi,

You have to use the comma instead of the dot

x = 1234567891234
?TRANSFORM(x, "9999,99,999,9999")
?TRANSFORM(x, "9999.99.999.9999")

hth

MarK

After I tried changing the textbox in the report with transform(mket01,"999.99.9999.9999")
and in the command button I still use the transform function(mket01,"999,99,9999,9999")
Only then could I get the results I expected.
My question is whether the coding I applied is right or wrong

 

[bharons]

I think the problem is that the number is bigger than VFP can handle. Try this formula instead:

Update: Store the value as a string instead, c(13), since that's what it really is. Then you can use

After I tried changing the textbox in the report with transform(mket01,"999.99.9999.9999")
and in the command button I still use the transform function(mket01,"999,99,9999,9999")
Only then could I get the results I expected.
My question is whether the coding I applied is right or wrong

 

[bharons]

I use this transform function..

 

[Tore Bleken]

The coding looks fine, but you must use the right data type. In this case 1312124120052 is NOT a number, it's a string.

From VFP's help, https://www.vfphelp.com/vfp9/_5wn12pc8v.htm: Maximum value of an integer: 2,147,483,647

 

[bharons]

The coding looks fine, but you must use the right data type. In this case 1312124120052 is NOT a number, it's a string.

From VFP's help, https://www.vfphelp.com/vfp9/_5wn12pc8v.htm: Maximum value of an integer: 2,147,483,647

string? i add that value from sql server, with datatype decimal. and thank so much for info about maximum value.

 

[mjcmkrsr]

Hi,

The INTEGER field type accepts values up to 2,147,483,647 but a numeric field type accepts values between these limits - .9999999999E+19 to .9999999999E+20

Check "Visual FoxPro Data and Field Types" in the Help File

hth

MarK

 

[Tore Bleken]

First of all, VFP can't handle such numbers, period. And it's no use to store it as a number anyway, you don't do any calculations with it. So just learn from our experience, and use a string, C(13), instead.
If it's stored on a SQL server as a number, and you don't have any control of it, then use SQL's Cast() function to convert it to a string.

 

[bharons]

I think the problem is that the number is bigger than VFP can handle. Try this formula instead:

Update: Store the value as a string instead, c(13), since that's what it really is. Then you can use

i write your code at my command button and i run it.. it is work like i want..
mket01 = Chrtran(transform(skedul1.norekening,"@R ###-##-####-####"),'-','.')

 

[bharons]

First of all, VFP can't handle such numbers, period. And it's no use to store it as a number anyway, you don't do any calculations with it. So just learn from our experience, and use a string, C(13), instead.
If it's stored on a SQL server as a number, and you don't have any control of it, then use SQL's Cast() function to convert it to a string.

With VFP I understand a little about CRUD commands, data manipulation, and now I am faced with an ODBC SQL Server based online system, I can be sure that I don't really understand CRUD SQL Server.

 

[Tore Bleken]

In this case, instead of Select ... norekening ... from ..., you write Select ... cast(norekening as char(13)) as norekening ... from .... This will make sure that the field is returned as a string, and your problem is gone.

 

[Chris Miller]

CRUD stands for CREATE, READ, UPDATE, DELETE, but most of the time is not only about these actions only, but about using http requests for that. POST or PUT for creating, GET for reading, POST or PUT for updating, and DELETE for deleting.

With SQL Server you don't use http requests, you make an ODBC connection and send SQL. SQL also has queries for CREATing records (INSERT), for READING records (SELECT), for updating records (UPDATE) and for deleting them (DELETE). This compares to CURD in the way the four actions are available, because how could you have anything else? Even if you use pen and paper you create "data" by using the pen on the paper, can read the paper, you can strike thru and write something else (update) and you can throw away the paper. So these four basic actions always exist, technologically or not, about data, you can't avoid that.

But the main thinking of SQL differs very much from CRUD. CRUD uses a URI as an identifgier of a resource that's located at that URI, that can be something like a single value, a record in json format or XML, a whole json document, etc, whereas SQL is all about tables in databases, the SQL cares mostly about records, but not just a single record in many cases (i.e. with SELECT you can select a whole table, even join muiltiple tables). The data is not organized at some URI but within a database and tables within the database.

So they compare, but differ very much at the same time. Don't think about CRUD when you think about databases, because databases work by SQL, some NOSQL databases working more closely to the CRUD principle also exist, but MSSQL Server is a relational database management system (RDBMS) and while data is commmon to both principles and sql also is about the actions of creating, reading, updating and deleting data (the data lifecycle) the means to do that differ very much. Think alone about SELCT enabling you to pick from the range of a single fiield of a single record to multiple joined tables of data. You're not comparing apples with oranges but apples with spacestations.

 

[Chris Miller]

The confusion you have is not about TRANSFORM, it's not knowing enough basics of data types. SQL Server can store much larger numbers than VFP can in integer form, biginteger, for example. So there's one more aspect of a difference between SELECT SQL and CRUDs READ request. While you SELECT from a database table, you can apply conversion functions,whereas a GET https request for a READ operation just GETs what's stored as is.

You looking at the transform failing for your case, while it's likely the getting of data in the SQL table exceeding what VFP can process as a number with 13 or 14 digits. With MySQL ODBC drivers you have the option to get bigints as integers, that works in the range of integers and won't help with 14 or 14 digit long numbers, with SQL Server you don't even have that option, so you do CONVERT within the SQL you execute to read the data and get it as a string, so you don't have to do that conversion on the VFP side with a number that's not right to begin with.

There are many details involved in all of this, but you fail on them, because you lack the basics. Knowing datatypes, knowing their limitations, choosing the right ones for the job and how to convert them into each other. Where which conversions happen indirectly automatically and when that fails.

You're also lacking the ability to look further up the chain of logic that leads to an error. If you look for the cure of an error reported at a line of code only in that line of code, you never get easier resolving of problems that stem from further up in the process, i.e. choosing the right data type for coluns of the SQL Server tables or - if that's not in your control - at least converting them as necessary when you read them with an SQL SELECT query.

By the way, VFP does not choke on 14 digit numbers, in code like

x=12345678901234

It doesn't read this as an integer number, even though it has no decimal places and no decimal point. It's also not only because it's beyond the range of a maximum signed 32 nbit integer that it's treated as double floating point number and can be put into x, it's because in variables numeric data is always the double float data type, even if you read it from an integer field type. And you can then transform it into a string, no problem.

But if the source of x is fromm SQL Server, then look closely at the column it comes from, how that's defined, the ODBC transpport of that value and how it arrives, and then what data type it is in the cursor field. There can be conversions you don't see and therefore fail on them.

There is a maximum precise integer range in double float numbers, that's a range larger than the 32bit integer range. Let's look at an example:

? 12345678901234561
? 12345678901234562
? 12345678901234563

If you do these ? commands from the command window you get displayed the same number 12345678901234560 for all three lines.
And you don't get any error. So that's also a case of needing to deal with things even though no error is triggered.

What's happening? Well, you would know, if you really learn and dig into what happens when code is executed. The first step of code execution is alwayas turning the input that is the text you write, the question mark and all the digits, into execuatable machine code that's then running on a CPU or GPU, depending on programming language, interpreter, compiler, whatever. So the digits 12345678901234561 are not simply output as they come as input, they are turned into a digital number with bits. As said, double floating point. If you don't know the details, that's your problem, because your expectation is that it's simple to reproduce the input as output and 117 digits is just 17 bytes. We're in the age of GB, so where's the problem. The problem is you don't know a lot of things and you don't dig into it.

 

[Chris Miller]

And then there's a last aspect, even if you consider technical possibilities it's not always sensible, what's technically possible. You can, for example eat poisoned food. But just beacause you can technically chew on it and swallow it, it's not working well, is it? Only some poisons will alarm you with a bad smell or bitter or otherwise unpleasant taste.

That's about the point Tore made about whether it's sensible to store an identifier like an account number into a numeric field, just because it's called a number and only consists of digits. Even within the range processible it makes no sense to store an identifier number as a number. And like your senses don't protect you from all poisons, you don't get error messages indicating mistakes to address.

Operations you need more often about an account number is cutting out pieces, like the last 4 digits or the first 5. Depending on your countries banking system the formatting with dashes or points within the account is there to indicate portions of the account number that have a detail meaning. But you never need to sum account numbers or multiply or take the square root, in short, you don't calculate with account numbers, you calculate with an account balance, but not the account number. So even in the range of integer, big integer, double floating point or other numeric data types, account numbers are not mathematical numbers, they are an identifier and you best store them as string, even if that takes more bytes to store than as a number. You avoid conflicts and unintentional errors and you enable the operations you rather need than calculations, like cutting out parts of them. Or formatting them with some commas, points or dashes or whatever else.

 

[Chris Miller]

And even with all that in mind. The different formatting characters are explained in the help documentation of TRANSFORM, and the dot "." has the meaning of decimal point in formatting a number to a string. There always is only one decimal point, so it'll only be taken into account the first time it appears in the format string. If you want to have a dot in multiple places, you're lost with TRANSFORM, as the dot has only one meaning for decimal point. Characters that you can use as spllitting characters are any that have no formatting meaning. So you're limited with the formatting as some characters have special meaning and can only be used for that special meaning and nothing else, not as literally themselves, in many cases.

Even the first dot may not show as a dot, what matters is what you have in SET("POINT"), and when you use SET SYSFORMATS on that'll be determined by system (Windows) settings for the decimal point. In many countries that's not the point but the comma. And what shows in places of a formatting character "," is the thousands separator, which in UK/US would be a comma, typpically, in countries where the decimal point is a comma it's the point, typically, so the two characters in the result are swapped. Still the format mask always has to use the dot or point (.) for the decimal point and comma (,) for thousands separator.

Since a number can get into millions, billions, trillions, etc. you can put multiple commas, multiple thousands separators into a format string, and you don't even have to space them between groups of three 9s. That makes it quite like comma is no special format character, but if your number is below a magnitude needing multiple separators, they don't show in whitespace, they only appear once they are needed for larger numbers. That's the way they are special format characters.

What remains is, that you can use many characters as literally just themselves appearing in the result of the transformation, but not the characters that have format functionality. So if you need literally have commas and points in the end result, you have to put them in post processing of the TRANSFORM result.