equivalent of SPACE(2) in an SQL command needed 2

[foxup]

Hello,

I haven't used this in a while but what's the NUMERIC equivalent of SPACE(2) in an SQL command. I would need help with this example.

Example: SELECT remark, min_users, SPACE(2) as temp FROM DBF()
gives me a new Character Field of 2 characters long named "temp".

I would need: SELECT remark, min_users, SPACE(2) as temp, NUM(2) as temp2 FROM DBF()
but it does NOT create a numeric field of 2 characters long.

I've searched the web and can't seem to find the NUMERIC equivalent of SPACE(2)

Please help.

Thanks,
FOXUP!

 

[Mike Lewis]

What exactly do you want this field to contain? Do you want a 2-digit field just containing a zero? If so, try this:

 SELECT remark, min_users, SPACE(2) as temp, [b]CAST(0 as N(2))[/b] as temp2 FROM DBF()

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[foxup]

Hi Mike,

You're very close though. I need a 2-digit field just containing nothing for now (not even a zero). In other words, the end result I need is exactly as if I were to "modify the structure" and add a numeric field (it's not filled with a zero).

Your sample seems to fill in a zero. Is it possible to just get a numeric field added without having it filled in?


Thanks,
FOXUP!

 

[TamarGranor]

Maybe you're looking for null? If you want an "I don't know" value, that's null.

CASE (.null. AS N(2))

Tamar

 

[Dan Freeman]

Did you mean Cast()?

 

[Mike Lewis]

This should do it:

SELECT remark, min_users, SPACE(2) as temp, [b]CAST(NULL as N(2))[/b] as temp2 FROM DBF()

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Steve Meyerson]

Would something like

SELECT 00 AS 'Temp2' INTO CURSOR MyCursordo the trick?

Steve

 

[TamarGranor]

Yes, I meant CAST(). Fingers type the wrong thing.

Tamar

 

[foxup]

Hi,

It still puts a ".NULL." in the field, but I'll take it ! LOL

Thanks to Mike and Tamar. 2 stars !


Regards,
FOXUP!

 

[Chris Miller]

Of course, CAST(NULL as N(2)) puts a .NULL. into the field. But its type is N(2), that's what matters, doesn't it?
If you want a blank numeric field you don't have any options, the only other possibility would be a number, it's a numeric field type.

I'd suggest to put in 0 and use VFPs format optiones to display 0 values blank, if you want. Some banking apps also use the option to display negative numbers in brackets.
You could also stay with .null. as the ideal value to say "no value is set" and SET NULLDISPLAY to an empty string.



Chriss

 

[TamarGranor]

If you really, really want the numeric field to be blank, that is, neither null nor 0, you can use the BLANK command to make it blank. But from VFP's POV, that's no different than setting it to 0, except visually.

Tamar

 

[Tom Borgmann]

IMHO there are TWO ways of doing this.

version 1 (used before introducing version 2 in vfp)
select 00 as myn2column

version 2
select cast( 0 as n(2,0) ) as myn2column

with version 1 vfp seems to create an integer but in reality it creates a numeric column of n(2,0).
as this isn't a really obvious solution, version 2 is way better as it shows you what it does right from the start.
both solutions won't generate null values but simple 0 in the generated column for every found record.

for displaying a column that contains 0 as BLANK you should make use of the format property by placing a 'Z' in it.
'Z' displays the value as blank if it is 0, except when the control has focus.


JM2C

-Tom

https://www.blogger.com/profile/08903165976787522012