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?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
