Thanks again Ben,
I'll give it a shot first thing in the morning.
Bryan Langford
Analyst
National Customer Operations
Enterprise Services & Strategic Planning
Training Development and Design Team.
Desk: 714-695-4824
Cell: 714-270-8451
�
-----Original Message-----
From: Braver, Ben [mailto:[EMAIL PROTECTED]]
Sent: Monday, October 01, 2001 4:50 PM
To: CF-Talk
Subject: RE: Dealing with nulls while adding two columns together
Bryan,
Sounds like a case for a Union query.
(Excuse the explanation if you already know what one is.)
In a Union query, you run separate queries, then perform the Union of
their
two result sets.
You can do this with or without duplicate rows.
So one query returns 5, 7, 15
The other returns 5, 12, 16, 3
The Union contains 3, 5, 5 (optionally), 7, 12, 15, 16.
MS Access can do Union queries, so the Help items on this might be a
good
learning tool.
HTH.
-Ben
-----Original Message-----
From: Langford, Bryan [mailto:[EMAIL PROTECTED]]
Sent: Monday, October 01, 2001 4:36 PM
To: CF-Talk
Subject: RE: Dealing with nulls while adding two columns together
Ben,
Thanks for the suggestion. After researching this option I now have
another question: will this allow me to produce the effect I am looking
for
because both tables can have null values. An example would be:
Table1
ID Value
1 5
2 7
3
4 15
Table2
ID Value
1
2 5
3 12
4 16
5 3
So the result set I am looking for would be: 5, 12, 12, 31, 3
Thanks,
Bryan Langford
-----Original Message-----
From: Braver, Ben [mailto:[EMAIL PROTECTED]]
Sent: Monday, October 01, 2001 4:20 PM
To: CF-Talk
Subject: RE: Dealing with nulls while adding two columns together
Bryan,
2 quick comments:
1. "NZ" is a Microsoft Access function. It's basically just an IIf
saying if
the field is not null, pass the contents of the field, but if the field
is
null, pass a zero.
2. The default join type is an "inner" join where the field exists in
both
tables. Look up "outer" joins where the field is optional in one of
the
tables.
HTH.
-Ben
-----Original Message-----
From: Langford, Bryan [mailto:[EMAIL PROTECTED]]
Sent: Monday, October 01, 2001 4:10 PM
To: CF-Talk
Subject: Dealing with nulls while adding two columns together
Hey everyone,
I have two separate views that pull numeric data from a table using
SQL. I am looking to add the two together in another view before
bringing
it back with cold fusion for display. My problem is that if either
table
has a null value data field where the join fields are equal, the
request
comes back blank. So I have tried "coalesce" and even went as far as
trying
an old trick I learned when I developed Access databases which was to
use
"NZ(table.field)+NZ(table2.field2)" but as expected, that errored out.
Does
anyone have some suggestion I might try to correct this problem and if
so,
what are they?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
