numeric equivalent of space() in select 1

You may use:

SELECT custno, SPACE(3) as type, SPACE(10) as bill_stat, 000.00 as numbs from test123 WHERE resto='5103'

or

SELECT custno, SPACE(3) as type, SPACE(10) as bill_stat, CAST(null as n(5,2)) as numbs from test123 WHERE resto='5103'

if you like to have a default null value

do I have to put a default value in "cast" ?

looks good, I'll give a star to zazzi and Paul. Thanks guys.

foxup said:

do I have to put a default value in "cast" ?

Click to expand...

You can put null as I showed above or any value you like, try:

SELECT custno, SPACE(3) as type, SPACE(10) as bill_stat, CAST(null as n(5,2)) as numbs from test123 WHERE resto='5103'
SELECT custno, SPACE(3) as type, SPACE(10) as bill_stat, CAST(123 as n(5,2)) as numbs from test123 WHERE resto='5103'
SELECT custno, CAST('MyType' as c(6)) as type, SPACE(10) as bill_stat, CAST(null as n(5,2)) as numbs from test123 WHERE resto='5103'

foxup said:

do I have to put a default value in "cast" ?

Click to expand...

I'd say try it, plus read the docs/help as well but to answer your question CAST needs a value to cast (convert), so you can't just leave it at nothing before the AS or you'll get a syntax error.

CAST isn't too picky about what you feed it as long as the source can be easily converted into the destination. So, if you do supply something that can't be converted to the target type you'll get a Type conversion is not supported error (#1532) so you'd get that error if you say something like:


but not if you say something else that looks a bit odd:


...as VFP is happy casting strings as numeric. In that scenario the value would come out as 0.00

You can even do odd things like:


...but why would anyone code that?

If you want a numeric field set to certain fixed size then I'd say either:


or


...if you want it to default to null

Your question is a bit strange though, you want a numeric field in the result set but you don't want that to have a value? The default value for a numeric is zero anyway, even if it is visually blank it is still equal to zero. Your options are set a specific value, that value can be zero, do something that results in VFP naturally defaulting it to zero, or set it as Null. Not sure you can do anything else but I'd be interested if there are other options, just out of curiosity.

What you can't do is CAST(x as type DEFAULT z), what you can do, though is CAST(z AS type) and thus have z in the generated records. When you create a READWRITE cursor or table you don't set the default value for further records added with APPEND BLANK, for example, but you also don't control that with SPACE().

You don't have to specify a constant or literal vlaue as expression, it can also be some other field or any expression that then is casted to the desired type. It doesn't even have to have the type, CAST is not just a field definition, CAST is also a conversion function and the best way to define a field and at the same time populate it, too. You can, for example also CAST strings to numeric types, which makes it a conversion on top of the definitionit is in the type part.

CAST also is simply a function just like SPACE, you cannot only use it to define fields in queries, you can write things like variable = CAST(x as y).

The AS fieldname clause is part of SQL, not part of CAST, therefore CAST is a function just like any other function you can use in the more general way to define a computed field with expression as fieldname. The big advantage of CAST is that you precisely control what field type you get, that includes whether it's nullable, but not strictly speaking the default value for all further records. So if you want to have control about that you better go for CREATE CURSOR to create a cursor with the desired structure, that could even include foreign key index tag names, and then populate it with INSERT INTO yourcursor SELECT... FROM whatever, where the SELECT subquery result structure should match the cursor structure, but automatic (implicit) conversions are done and the field types of the cursor stay as you define it.

Just by the way, if you think it's a feature of the SPACE() function to determine the default value for fields when you APPEND or INSERT even more records after the query: That's not what SPACE does, it's just that SPACE( n ) can only define CHAR( n ) fields and they have constant width and their default value will thus be the empty string of that width, which is SPACE( n ) by width of CHAR( n ), not by feature of SPACE. If you think of SPACE as field definition function, you're overestimating it, all it is is an empty strring consisting of the specified number of CHR(32). Also often used to create a buffer of that size in bytes, where you don't care whether it's initialized with all 0 bytes or all spaces, as it will be populated by an API function, for example. CAST is far more versatile than SPACE.

Not sure why people want to complicate things with "cast". Simply put a 00.00 value in there as this:

You will get your empty TYPE, BILL_STAT, and NUMBS just like you want

I also think Doug's solution is best. I rarely use CAST. 00.00 will produce a N(5,2) field in the result.

Incidentally, 000.000 will produce a N(7,3) field, 000 will produce a N(3) field, etc., etc.

Steve

CAST equalizes how to define AND convert data into results.

The specification of nummber of places overall and number of decimal places works with the examples you give, but for example this leads to a N(18,3) field, while I'd want to have a N/7,3) field:

I think you will argue then don't do such things, only use a numeric literal as you suggest and don't combine defining field with also populating them in the query. Well, you can do that, but then have postprocessing work to make use of the defined fields. That's also true when you first CREATE CURSOR, as I suggested, too.

But with CAST you can have full control, it is obvious even just reading the source dode what field types you get also when you use CAST as casting and converting mechanism in one go.

By teh way, the N(18,3) I get as result is not even what I would expected from the way VFP deermines the length and precision of a numeric result in general. When you multiply numbers, as in theory you can get to twice the number of decimal places to store the result precisely, think of 0.01*0.01 being 0.0001, so 4 decimal places necessary from multiplying two numbers with each 2 decimal places.

You don't need to make acrobatic neuroligical stunts to know you get a N(3,1) field from 0.0 or up to 9.9, but if you not just use the mechanism to define an unpopulated new field but also populate it with the result you eventually want to store it, CAST is the option giving you much better control and documents it, too.

And one further argument is that CAST is standard, you'll fail with something like SELECT 0.00 in any database but VFP.

And there are more field type than char and numeric. You don't create a varchar field without CAST, for example.

CAST and the simpler method suggested both work. As always, it's about preference and also about some drawbacks to one or the other as Chriss points out. Personally, I'm usually using the simpler method of "0000.00 as SomeNumber" using the appropriate number of whole number digits and decimal digits as the situation requires, but I also use CASE. It's good to know both.

Well it comes down to whatever floats your boat I suppose. But for me, having been a dBase developer since about 1980 and having written stuff in various other languages and SQL implementations, I'm of the philosophy to keep your code as simple and direct as possible. That's because your code must (in most cases) be maintainable. For me, if I saw "cast" somewhere in VFP code I'd be saying "WTF?" That's the kind of thing I'd expect to see in C or Java or maybe even VB but not in VFP or any other dBase dialect.

Now IF you have a reasonable expectation that you'd need to export the SQL into some other environment then MAYBE it would be acceptable to do this to make your SQL more generic. I don't know about that. But the code I wrote does NOT result in an N(18,3) field. You can try it and you'll see that it results in an N(5,2) field just as implied by what was coded.

I'll say this unless there is a specific reason not to, your code should be as simple and direct as possible. Its format and style should be easily readable also and used consistently. That's why I formatted my code line above in the normal way I code. You can quickly check what your query is and what the output should be. So I would vehemently be opposed to using "cast" unless there was a clear reason to do so.

Doug,

Doug Lindauer said:

the code I wrote does NOT result in an N(18,3) field.

Click to expand...

I never claimed that, the code I posted would look like generating an N(7,3) field as it uses a factor 001.000, but the result cursor will have an N(18,3) field as AFIELDS reports. You can use the simpler cases you and others demonstrated, not against that. But then you just establish a field and still need to populate it. I tend to do so in the same time and then I want the numeric field definition as I want it, not as the calculation decides it to be.