f



Problem with a null field

........ SUM(Amount1) +B.Amount2  as TOTAL ........

TOTAL is zero whenever the Dataset field Amount2 is NULL. Setting the field
to zero prior to the query, remedies the problem.
Is there a way to get around the problem beside checking all of the null
fields ?


0
Stark
9/23/2004 12:24:03 PM
comp.lang.pascal.delphi.misc 5769 articles. 1 followers. miniFAQ (1) is leader. Post Follow

10 Replies
442 Views

Similar Articles

[PageSpeed] 49

"Stark" <franco.jommi@tin.it> wrote in message
news:Dfz4d.295062$OR2.13423559@news3.tin.it...

> ....... SUM(Amount1) +B.Amount2  as TOTAL ........
>
> TOTAL is zero whenever the Dataset field Amount2 is NULL. Setting the
> field to zero prior to the query, remedies the problem.
> Is there a way to get around the problem beside checking all of the null
> fields ?

I suspect TOTAL is null (not zero) when any of the added fields is
null. Null is not zero. Null may not even compare equal to another null,
although it might compare equal to itself. You definitely can't
meaningfully add it to something.

It would appear that B.Amount2 must not be null. Your database should
be able to model that. For fields that are foreign keys, it _may_ be
useful to allow them to be null; for data fields, it rarely is. Use a
"neutral element" as the default value instead: zero for adding integers,
an empty string for concatenating strings. Then when you combine a bunch
of values of that type together, mixing in the neutral element decays
into a no-op.

Groetjes,
Maarten Wiltink


0
Maarten
9/23/2004 2:44:19 PM
Stark wrote:
> ........ SUM(Amount1) +B.Amount2  as TOTAL ........
> 
> TOTAL is zero whenever the Dataset field Amount2 is NULL.

I would have expected a total of Null. That's how Null works in Delphi,
isn't it?

> Setting the field to zero prior to the query, remedies the problem.

But it also changes the value of the field. I wouldn't expect that
calculating the sum of a list of numbers would change their values in
the process. If null and zero are supposed to mean the same things in
your program, then I think it would be better to disallow null values in
the first place.

-- 
Rob
0
Rob
9/23/2004 4:19:51 PM
Rob Kennedy wrote:
> Stark wrote:
>> ........ SUM(Amount1) +B.Amount2  as TOTAL ........
>>
>> TOTAL is zero whenever the Dataset field Amount2 is NULL.
>
> I would have expected a total of Null. That's how Null works in
> Delphi, isn't it?

in Delphi, a NULL field retrieved with AsFloat returns 0. The only place he
would get NULL is when the summing is done at SQL level.

>> Setting the field to zero prior to the query, remedies the problem.
>
> But it also changes the value of the field. I wouldn't expect that
> calculating the sum of a list of numbers would change their values in
> the process. If null and zero are supposed to mean the same things in
> your program, then I think it would be better to disallow null values
> in the first place.

I think this is being overformalistic - it should be perfectly legal to sum
up a field where only a few records have values assigned. Depending on the
database engine, there may exist function that convert NULL to 0 so that one
will get a non-null result.

-- 
Regards,

Bj�rge S�ther
bjorge@haha_itte.no
-------------------------------------
I'll not spend any money on American Software products
until armed forces are out of Iraq.


0
Bj
9/23/2004 7:12:17 PM
"Bj�rge S�ther" <bjorge@hahaha_itte.no> wrote in message
news:BeF4d.8775$WW4.135017@news4.e.nsc.no...

> I think this is being overformalistic - it should be perfectly legal to
sum
> up a field where only a few records have values assigned. Depending on the
> database engine, there may exist function that convert NULL to 0 so that
one
> will get a non-null result.

IIRC SQL has the IS NOT NULL predicate for filtering out rows with null
values.


0
Bruce
9/23/2004 7:47:06 PM
Bruce Roberts wrote:
> "Bj�rge S�ther" <bjorge@hahaha_itte.no> wrote in message
> news:BeF4d.8775$WW4.135017@news4.e.nsc.no...
>
>> I think this is being overformalistic - it should be perfectly legal
>> to sum up a field where only a few records have values assigned.
>> Depending on the database engine, there may exist function that
>> convert NULL to 0 so that one will get a non-null result.
>
> IIRC SQL has the IS NOT NULL predicate for filtering out rows with
> null values.

That's ok if you sum only one field. Worse if you do a SUM(FIELD1),
SUM(FIELD2).

In oracle you may do this:

SELECT
  SUM(nvl(FIELD1, 0,FIELD1)),
  SUM(nvl(FIELD2, 0,FIELD2))

...in Firebird, this:

SELECT
  SUM(NULLIF(FIELD1, 0)),
  SUM(NULLIF(FIELD2, 0))


-- 
Regards,

Bj�rge S�ther
bjorge@haha_itte.no
-------------------------------------
I'll not spend any money on American Software products
until armed forces are out of Iraq.




0
Bj
9/23/2004 9:59:41 PM
I am returning to you on this matter of the null field to you, having looked
for a way to convert null to zero at the database level. I am using dBase
and the BDE. and, unfortunately, I found no way.  But I may be wrong. Do you
have any knowledge of it ?


"Bj�rge S�ther" <bjorge@hahaha_itte.no> ha scritto nel messaggio
news:BeF4d.8775$WW4.135017@news4.e.nsc.no...
> Rob Kennedy wrote:
> > Stark wrote:
> >> ........ SUM(Amount1) +B.Amount2  as TOTAL ........
> >>
> >> TOTAL is zero whenever the Dataset field Amount2 is NULL.
> >
> > I would have expected a total of Null. That's how Null works in
> > Delphi, isn't it?
>
> in Delphi, a NULL field retrieved with AsFloat returns 0. The only place
he
> would get NULL is when the summing is done at SQL level.
>
> >> Setting the field to zero prior to the query, remedies the problem.
> >
> > But it also changes the value of the field. I wouldn't expect that
> > calculating the sum of a list of numbers would change their values in
> > the process. If null and zero are supposed to mean the same things in
> > your program, then I think it would be better to disallow null values
> > in the first place.
>
> I think this is being overformalistic - it should be perfectly legal to
sum
> up a field where only a few records have values assigned. Depending on the
> database engine, there may exist function that convert NULL to 0 so that
one
> will get a non-null result.
>
> --
> Regards,
>
> Bj�rge S�ther
> bjorge@haha_itte.no
> -------------------------------------
> I'll not spend any money on American Software products
> until armed forces are out of Iraq.
>
>


0
Stark
9/27/2004 2:43:19 PM
"Stark" <franco.jommi@tin.it> wrote in message
news:bGV5d.3261$75.143498@news3.tin.it...
> I am returning to you on this matter of the null field to you, having
looked
> for a way to convert null to zero at the database level. I am using dBase
> and the BDE. and, unfortunately, I found no way.  But I may be wrong. Do
you
> have any knowledge of it ?

Quick and dirty:

        cast ('0' || cast (aColumnReference as char (10)) as numeric)

e.g.

    Select cast ('0' || cast (B.Total as char (10)) as numeric) +
              cast ('0' || cast (C.Total as char (10)) as numeric)
GrandTotal, . . .



0
Bruce
9/27/2004 3:22:57 PM
Bruce Roberts wrote:
> "Stark" <franco.jommi@tin.it> wrote in message
> news:bGV5d.3261$75.143498@news3.tin.it...
>> I am returning to you on this matter of the null field to you,
>> having looked for a way to convert null to zero at the database
>> level. I am using dBase and the BDE. and, unfortunately, I found no
>> way.  But I may be wrong. Do you have any knowledge of it ?
>
> Quick and dirty:
>
>         cast ('0' || cast (aColumnReference as char (10)) as numeric)
>
> e.g.
>
>     Select cast ('0' || cast (B.Total as char (10)) as numeric)
>               cast ('0' || cast (C.Total as char (10)) as numeric)
> GrandTotal, . . .

I've never set my foot in a dBase table myself - hence I don't know much
about it. But if this workws, it's brilliant ;-)

-- 
Regards,

Bj�rge S�ther
bjorge@haha_itte.no
-------------------------------------
I'll not spend any money on American Software products
until armed forces are out of Iraq.


0
Bj
9/27/2004 8:01:08 PM
If it works, it does'nt look dirty at all to me ! Thanks, I am going to try
...

"Bruce Roberts" <ber@bounceitattcanada.xnet> ha scritto nel messaggio
news:vjW5d.74$LW1.840@news20.bellglobal.com...
>
> "Stark" <franco.jommi@tin.it> wrote in message
> news:bGV5d.3261$75.143498@news3.tin.it...
> > I am returning to you on this matter of the null field to you, having
> looked
> > for a way to convert null to zero at the database level. I am using
dBase
> > and the BDE. and, unfortunately, I found no way.  But I may be wrong. Do
> you
> > have any knowledge of it ?
>
> Quick and dirty:
>
>         cast ('0' || cast (aColumnReference as char (10)) as numeric)
>
> e.g.
>
>     Select cast ('0' || cast (B.Total as char (10)) as numeric) +
>               cast ('0' || cast (C.Total as char (10)) as numeric)
> GrandTotal, . . .
>
>
>


0
Stark
9/28/2004 11:52:34 AM
Go with the easiest option.

SUM(Amount1) + ISNULL(B.Amount2,0)  as TOTAL

If the value of B.Amount2 is null then the second value provided (0)
is returned. You can use this for any values to check if it is a null
value. So if you needed to check some text, you could say
ISNULL(myField, 'Hello')

HTH

Ryan

"Stark" <franco.jommi@tin.it> wrote in message news:<Dfz4d.295062$OR2.13423559@news3.tin.it>...
> ....... SUM(Amount1) +B.Amount2  as TOTAL ........
> 
> TOTAL is zero whenever the Dataset field Amount2 is NULL. Setting the field
> to zero prior to the query, remedies the problem.
> Is there a way to get around the problem beside checking all of the null
> fields ?
0
ryanofford
9/30/2004 8:01:17 AM
Reply:

Similar Artilces:

Problem Setting A Field To Null
AssignedEquipmentType and AssignedEquipment are text data type. Both are unique five character codes. I have both fields in a continuous form. The database requires that if an Equipment type is entered, an equipment item must also be entered. I have the following code' to handle the case if AssignedEquipmentType has been highlighted and deleted that AssignementEquipment must be set to Null. When the code runs, I get error 3162 - You tried to assign the Null value to a variable that isn't a Variant data type. Can someone help me to resolve the problem. Private Sub AssignedEquipmentType...

Problem with recordset with a Field being a Null
Using VB6 DAO MS Access. Here is my query that I can't seem to figure out. I want to exclude all fax_numbers that do not equal "MAT" and fax_number is not NULL and red_falg = false. Set rs = db.OpenRecordset("SELECT * From PSUSA WHERE fax_number <> 'MAT' and fax_number <> "" and red_flag=false", dbOpenDynaset) Any help is much appreciate. Thanks, jt jt wrote: I have answered you question in comp.lang.basic.visual.misc, please cross-post instead of multi-posting. -- Olof Lagerkvist ICQ: 724451 Web page: http...

Problem with recordset with a Field being a Null
Using VB6 DAO MS Access. Here is my query that I can't seem to figure out. I want to exclude all fax_numbers that do not equal "MAT" and fax_number is not NULL and red_falg = false. Set rs = db.OpenRecordset("SELECT * From PSUSA WHERE fax_number <> 'MAT' and fax_number <> "" and red_flag=false", dbOpenDynaset) Any help is much appreciate. Thanks, jt jt wrote: > Using VB6 DAO MS Access. > > Here is my query that I can't seem to figure out. I want to exclude all > fax_numbers that do not equal "MAT" and fax_...

Axis problem
Hello, I try to invoke two methods that access to a field. The first sets the value of the variable. Then the second displays the value on the Tomcat console. But the problem is that the attribute IS null when I invoke the second method. I obtain the following output: [one] FOO [two] null Where is the mistake? Cheers, -Mat ===== Foo.jws ===== public class Foo { private String foo = null; public void one() { foo = "FOO"; System.err.println("[one] "+foo); } public void two() { System.err.println("[two] "+foo); } }...

Allow Data Entry if Field is Null, Disable if Field is not Null
I have a form which has a field Start Date. I want to only let the user enter a date if there is no date in the field and never be able to edit the field. I have experimented with On Enter and On Click and enabling and disabling the field but can't get it to work. Any suggestions? Thanks in advance If Isnull(Startdate)=True then 'Allow Edit else 'Dont allow Edit Madhivanan Will wrote: > I have a form which has a field Start Date. I want to only let the user > enter a date if there is no date in the field and never be able to edit the > field. I have experiment...

Problem with null field. Plz help!
Hi all. Could someone please help me diagnose what might be wrong with this code snippet (taken from within a program) that cropped up after I upgraded from Ruby 1.68 to 1.8. The problem is that the Ruby crashes with this message: autogen.rb:301:in `initialize': Permission denied - ./boiler_install_notes/ (Errno::EACCES) from autogen.rb:301:in `open' from autogen.rb:301 from autogen.rb:92:in `foreach' from autogen.rb:92 >Exit code: 1 What's weird is that the crash happens when field[5] should be nil -- the entire if/end should be skipped. (If I insert a line # 2...

NULLS FIRST problem with numeric fields
Hi all, I have Oracle 8.1.7 with a table: CREATE TABLE PREDTABSTATOPRATICA ( IDSTATOPRATICA NUMBER(5) NOT NULL, STATOPRATICA VARCHAR2(100) ); ALTER TABLE PREDTABSTATOPRATICA ADD ( CONSTRAINT PK_PREDTABSTATOPRATICA PRIMARY KEY ( IDSTATOPRATICA ) ) ; If I ORDER BY STATOPRATICA NULLS FIRST I get the correct result: first empty strings (null), then an alfabetically ascending ordered list. If I ORDER BY IDSTATOPRATICA NULLS FIRST I get a list ordered in a numerical DESCENDING manner, even if I specify also ASC !!! :-( Is this a bug? Is there an available patch? Is there a workaround? I...

problems problems problems
(The short(?) summary) I've got an Access MDB file and a DAO connect with it.. Problem 1 of 2 The below gives me a runtime error 91 Object variable or With block variable not set. I've got the db stuff after the form.show (to make sure all the objects on the form are loaded before attempting to utilize/manipulate them) But it doesn't like it when I use the data object in the form load anyway for some reason.. pffft. Private Sub Form_Load() frmTest1Project.Show datGallery.Recordset.MoveLast datGallery.Recordset.MoveFirst Call LockTextBoxes(frmTest...

Help me, problem with java.lang.reflect.Field
Hi, I have a class like this class A{ public String abc; } And i got another class use class A { A a = new A(); Field[] fields = a.getClass().getDeclaredFields(); } The problem is that i can got the name of class A's fields but i cant got the actual object to deal with. Because i dont know the name of the class A's field, so i cant use a.a = "asd". I want to set value for class A dynamically, Please help me, thanks On 25 Jan 2007 00:36:42 -0800, Nicky wrote: > The problem is that i can got the name of class A's fields but i > cant got the actual object to deal ...

problem with inserting a generated seq no into not null field
I have the following generic query which I am trying to use to insert into a 3 field table INSERT INTO tb_files (subj, area, file_no) select '4', '8',(SELECT CASE WHEN (4||8) NOT IN (SELECT (subj||area) FROM tb_files) THEN '1' ELSE max(file_no)+1 END FROM tb_files GROUP BY (subj||area),subj,area HAVING subj = '4' AND area = '8'); However the first entry will not go in with a 'null value in column "file_no" violates not-null constraint' message Anyone any ideas on this - the CASE statement does not seem to be being ...

Update field only if field is not null
My database has 2 tables: Table1 & Table2. If a field is not null on a record in table2, then the not null fields in table1 that correspond to the records in table1 needs to be updated to match the field in table2. What I have is a form that is linked to Table2. If the users want to change a field in the main database (table1), they fill the change in to the form (which is linked to table2) If there is no change to the field, they simply leave it blank and that particular field won't be updated on the main database(table1). The users can change up to 30 fields listed on the form. For...

RE: Problem when querying null fields with wxODBC classes
I didn't have time to really read in detail your trouble below, and I'm leaving from my PC for the night right now, but I'll suggest grabbing the latest wxODBC files from PVCS and trying them as I have in the last motnh checked in a couple fixes for NULL handling and a Postgress change if I remember correctly. See if those fixes help you out. g > > I'm developing a system using wxMSW2.4.1 (VC6, Win2k) which > connect to a PostGreSQL 7.2 database using wxODBC classes. > > I'm having a problem when reading the results of a query made >...

Query
Hello all, I am trying to build a query that will compare records in two tables (Table1 and Table) Tables have identical design, however besides differences in name, they contain different records Each table has 2 fields (Field1 and Field2) Table1 has the following records Field1 Field2 Januar Month Februa March April Day (you can note that Februa and March do not have any values in Field2) Table2 has the following records: Field1 Field2 Januar Month Februa March April Month (same as above, Februa and March do...

RE: Problem when querying null fields with wxODBC classes #2
Sorry, yes. My current workplace uses PVCS, and I was over-acronymed when I wrote that. CVS is what I meant. And yes, use the HEAD g > -----Original Message----- > From: Fabio L. Fonseca [mailto:fabiolf@terabrasil.com] > Sent: Tuesday, September 16, 2003 2:57 PM > To: wx-users@lists.wxwindows.org > Cc: gtasker@allenbrook.com > Subject: Re: Problem when querying null fields with wxODBC classes > > > Hi! > > I'm going to apply the modifications now, but before, just a little > question: > when you say grab the latest wxODBC fil...

Web resources about - Problem with a null field - comp.lang.pascal.delphi.misc

Is–ought problem - Wikipedia, the free encyclopedia
The is–ought problem in meta-ethics as articulated by Scottish philosopher and historian David Hume (1711–76) is that many writers make claims ...

Thermomix, Dyson, HTC: The ethical problem with our electronics
IT IS the dirty price we pay for our electronic goods.

Deutsche's problems reverberate through global banks
Concerns that the European Central Bank might have to support Deutsche Bank reverberated into Australia's banking sector on Tuesday.

Q&A producer reveals 'disturbing' women problem
Producer Amanda Collinge lists the "disturbing" reasons many women refuse to appear on the talk show.

Manus Island asylum seekers given anti-malarial drug known to cause mental health problems; Immigration ...
The Immigration Department moves to offer asylum seekers on Manus Island alternatives to a controversial anti-malarial drug known to cause or ...

FishbowlNY Newsstand: New Hampshire’s Problem
FishbowlNY Newsstand: New Hampshire’s Problem

Cord cutters frustrated over Super Bowl 50 streaming problems
... disappointed with the outcome of that effort. I know I sure was, and the Twitter feed for CBS Sports Help Team was full of reports of problems ...

It’s a Societal Addiction Problem When It is White People
And a crime spree when they are black. The front page of my local tv station’s website has this taking up valuable real estate : From the comments ...

6 health problems that iPhones are helping to solve
Last year, Apple debuted a suite of medical tools called ResearchKit, which could forever change the way doctors study common health problems. ...

New Hampshire has a big drug problem
For the first time since 2008, New Hampshire residents see a bigger problem than jobs and the economy for the state.

Resources last updated: 2/10/2016 2:30:35 PM