Mike: Mike Byerley's got your answers.
But more conceptually, nulls, blanks and spaces are apples, oranges and bananas. 'Null' literally means 'no value', which might have several meanings: a) There may be a value for this field out there, but we just don't know it (and may never know); b) There may be NO value for this field out there, but we don't know that either; c) There's NO value which makes sense in this field (in this particularly usage of the data). The formal relational model calls for the introduction of a special, reserved marker to represent fields which contain 'no value'. In R:Base, this is the string '-0-'. R:Base allows the user to introduce a character or string to represent nulls; a freedom with risk. The introduction of the semantic term 'blank' is what's tripping you up. We think of 'blank' as meaning 'no value'; and a 'space' as a representation of 'blank'. In a word processing context, this is right and proper. But a 'space' is an actual value: ASCII 32; so within a a database context, using a 'space' to represent 'blank' to represent 'null' introduces a value where there should be none; thus defeating the very purpose of 'null'. To represent nulls in reports (using the R:Base report designer), you can change the printed representation of nulls to the 'space' without doing violence to null protection: >From R:Base help: 1. [Right-click] on the column object whose nulls you wish to represent; 2. From the drop-down menu, select 'NULL Format:' 3. Enter the string (which just might be an ASCII 32 space) you wish to print in the place of '-0-' A disquieting snippet from R:Base help: "If you set null to a blank space, R:BASE does not display rows composed entirely of nulls with the SELECT command." I take that to mean that a select would return rows with NULLs and spaces, which might have a meaning totally out of sync with what you intend. Hope this helps, and Cheers, Bruce -------- Original Message -------- Subject: [RBASE-L] - Re: null From: "Mike Byerley" <[email protected]> Date: Sat, April 21, 2012 1:47 pm To: [email protected] (RBASE-L Mailing List) Mike, Try this and see what the results are before you set null to ' ' If you are expecting null to be a space, you will be disappointed. SET VAR vemptyspace TEXT = '' SET VAR vspace TEXT = ' ' SET VAR vblank TEXT = (CHAR(32)) SET VAR vtest TEXT = ('RBASE' + .vemptyspace + 'ROCKS') PAUSE 2 USING .vtest SET VAR vtest TEXT = ('RBASE' + .vspace + 'ROCKS') PAUSE 2 USING .vtest SET VAR vtest TEXT = ('RBASE' + .vblank + 'ROCKS') PAUSE 2 USING .vtest RETURN If you want null to be just invisible for display purposes (devoid of the -0- in fields that are nulls) then set the null character to an unprintable ascii value like Char(160) It has been recommended practice for as long as I can remember to set null to -0- before unloading data. you can set null to that value by the recommended method of: set null '-0-' At the start of an RBase session, I include this: set null '-0-' set var vHoldNull = (CVAL('NULL')) then at the conclusion of the session: set null .vHoldNull Then if the session ends back in RBase RDBMS interface, my null setting is back to the recommended setting for unload. I don't change the null setting to do ReLoad FWIW. In your examples, you had null set to Two blanks and Three blanks, but it doesn't matter, since RBase strips out blanks in an expression as my example shows. ----- Original Message ----- From: "mike epstein sony viao office" <[email protected]> To: "RBASE-L Mailing List" <[email protected]> Sent: Saturday, April 21, 2012 3:28 PM Subject: [RBASE-L] - null : Can anyone clarify the meaning, and usage of : : : : : : Set null ' ' : : : : Set null -0- : : : : Set var <varname> <vartype> = null : : : : Does the Set var <varname> <vartype> = null make a variable that has a : value, change it to ' ' (blank) : : : : : : On a network with 2 workstations Is the setting on the local computers cfg : file affected by changes in an eep , say that sets null to -0- inserts or : updates into a table and then before exiting the eep sets null = ' '. : : : : : : Should the -0- only be used to unload data and structure from a table? : : Should the -0- only be used to reload or pack the database ? : : : : I have set null in the configuration setting to blank from -0- , when I : close the configuration settings I get an error message that says ' cannot : save c:\windows\rbengine91_64.cfg. It does not matter if I change the order : ie -0- to blank : : Either way whatever symbol is input into the null configuration setting is : the setting in the application : : : : I am also experiencing that the -0- all of the sudden appears when the : application is run while tracing with no change in the ' ' setting by the : program : : : : : : : : : : --- RBASE-L =======================3D=======================3 D= TO POST A MESSAGE TO ALL MEMBERS: Send a plain text email to [email protected] (Don't use any of these words as your Subject: INTRO, SUBSCRIBE, UNSUBSCRIBE, SEARCH, REMOVE, SUSPEND, RESUME, DIGEST, RESEND, HELP) =======================3D=======================3 D= TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [email protected] In the message SUBJECT, put just one word: INTRO =======================3D=======================3 D= TO UNSUBSCRIBE: Send a plain text email to [email protected] In the message SUBJECT, put just one word: UNSUBSCRIBE =======================3D=======================3 D= TO SEARCH ARCHIVES: Send a plain text email to [email protected] In the message SUBJECT, put just one word: SEARCH-n (where n is the number of days). In the message body, place any text to search for. =======================3D=======================3 D=
