kmeek@cti1.UUCP (Kevin Meek) (11/20/90)
I have come accross a interesting little bug (feature??) in RPT
When concatenating strings together if any of the fields in the input
are null then resulting output string is set to null as well.
For example.
with input section like the following
input
fname [string 2],
lname [string 4],
room [string 3]
And Data like the following
John|Smith|100
John|Smith|
John||103
|Smith|102
the following set statement will set result_str to null for all
but the first line of input.
set result_str to fname + lname + room
Basically If any of the variables are null when concatenating strings
the result will be null as well.
I am using RPT that came with Accell/IDS for our 3B2/600
The output of RPT -version is below
Database Version: 34
Revision: 1.1.1.4.8
Nap Option: POLL
Machine: ATT 3B2
Operating System: att600g 3.2.2 3 3B2
(SM:22 DB:1 CM:10 FM:26 LM:17 TM:16 CL:3)
If anyone else has this problem maybe this should be forwarded to unify
via the bug mailing list.
---
Kevin Meek
kmeek@cti.com
--
Kevin Meek
kmeek@cti.comdxs@dbrus.Unify.Com (Dale R. Shaver) (11/21/90)
In article <321@cti1.UUCP> kmeek@cti1.UUCP (Kevin Meek) writes: >When concatenating strings together if any of the fields in the input >are null then resulting output string is set to null as well. > >For example. >with input section like the following > >input > fname [string 2], > lname [string 4], > room [string 3] > >And Data like the following > >John|Smith|100 >John|Smith| >John||103 >|Smith|102 > >the following set statement will set result_str to null for all >but the first line of input. > > set result_str to fname + lname + room > >Basically If any of the variables are null when concatenating strings >the result will be null as well. > >I am using RPT that came with Accell/IDS for our 3B2/600 >The output of RPT -version is below > >Database Version: 34 >Revision: 1.1.1.4.8 >Nap Option: POLL >Machine: ATT 3B2 >Operating System: att600g 3.2.2 3 3B2 >(SM:22 DB:1 CM:10 FM:26 LM:17 TM:16 CL:3) This is ACCELL/SQL, not ACCELL/IDS. This issue is known here at Unify, but the problem is not quite as Kevin has stated. It is true that RPT will set a value to NULL if one or more of the elements is NULL. This is true whether the values are strings, numerics, amounts, etc. However, this is the way in which the RPT product works; in other words, a feature. However, the bug here is that strings of length zero are being interpreted as NULL when they should not be. Given the input that Kevin supplied: John|Smith|100 John|Smith| John||103 |Smith|102 The variable result_str should successively be: JohnSmith100 JohnSmith John103 Smith102 If the input was, however: John|Smith|100 John|Smith|* John|*|103 *|Smith|102 Then the concatenation of the last three strings will be NULL, and is correct. The "strings of length zero being interpreted as NULL" problem has been identified and fixed in our 1.2 version of ACCELL/SQL. Dale Shaver, Technical Support Engineer Phone: (916) 922-1177, x 733 Unify Corporation, Sacramento, California Fax: (916) 920-5306 dxs@unify.Com or {{ucdavis,csun,lll-crg}!csusac,pyramid,sequent}!unify!dxs "Would you please continue the petty bickering; I find it quite intriguing"
allbery@NCoast.ORG (Brandon S. Allbery KB8JRR) (11/24/90)
As quoted from <1p2ytsu@Unify.Com> by dxs@dbrus.Unify.Com (Dale R. Shaver): +--------------- | In article <321@cti1.UUCP> kmeek@cti1.UUCP (Kevin Meek) writes: | >When concatenating strings together if any of the fields in the input | >are null then resulting output string is set to null as well. | | It is true that RPT will set a value to NULL if one or more of the | elements is NULL. This is true whether the values are strings, | numerics, amounts, etc. However, this is the way in which the RPT | product works; in other words, a feature. +--------------- That maybe should be clarified: according to the ANSI SQL standard, the only operations on NULL values that do not return NULL are the "IS NULL" and "IS NOT NULL" operations. By the ANSI definition of NULL, concatenating a NULL string with anything else returns the NULL string. +--------------- | However, the bug here is that strings of length zero are being | interpreted as NULL when they should not be. Given the input that | Kevin supplied: +--------------- Interesting that you consider it a bug. I had assumed it was there for the multiple database support: the last version of Informix-SQL I used, for example, considered the empty string to be a NULL of type char(...). (Not that I consider that behavior correct, mind you; it's one reason I chose Unify over Informix.) ++Brandon -- Me: Brandon S. Allbery VHF/UHF: KB8JRR on 220, 2m, 440 Internet: allbery@NCoast.ORG Packet: KB8JRR @ WA8BXN America OnLine: KB8JRR AMPR: KB8JRR.AmPR.ORG [44.70.4.88] uunet!usenet.ins.cwru.edu!ncoast!allbery Delphi: ALLBERY