At 10:26 2015-04-16, Richard Kaye <[email protected]> wrote:
Hi Gene,
You've already got your workarounds in place but for future
reference this is where you can use NVL() in your SQL to avoid nulls
that might arise from a left/right/outer join.
SELECT t1.column1, NVL(t2.column2,0) column2, t1.column3...
Oh, yes, there is that case, *TOO*!
The case that I was mentioning is something else. Code
follows. In the third case, the customer has not done any business
with us in 2015. The result of the appropriate query should
logically be 0, but SQL returns .null..
***** Start of Example Code *****
close all
clear all
set talk off
set exact on
set ansi on
*
create cursor invoices;
(;
custcode c(3) not null,;
invdate d not null,;
invamt n(9,2) not null;
)
insert into invoices;
(custcode,invdate,invamt) values ("ABC",{^2014-10-01},100)
insert into invoices;
(custcode,invdate,invamt) values ("ABC",{^2014-11-01},150)
insert into invoices;
(custcode,invdate,invamt) values ("ABC",{^2014-12-01},125)
insert into invoices;
(custcode,invdate,invamt) values ("ABC",{^2015-01-01},75)
insert into invoices;
(custcode,invdate,invamt) values ("ABC",{^2015-02-01},150)
insert into invoices;
(custcode,invdate,invamt) values ("ABC",{^2015-03-01},200)
insert into invoices;
(custcode,invdate,invamt) values ("ABC",{^2015-04-01},50)
insert into invoices;
(custcode,invdate,invamt) values ("DEF",{^2014-10-01},25)
insert into invoices;
(custcode,invdate,invamt) values ("DEF",{^2014-11-01},250)
insert into invoices;
(custcode,invdate,invamt) values ("DEF",{^2014-12-01},200)
* How much business has ABC ever done with us?
select sum(invamt) from invoices;
where custcode="ABC"
* How much business has ABC done with us this year?
select sum(invamt) from invoices;
where custcode="ABC" and invdate>={^2015-01-01}
* I have not seen DEF ordering lately.
* How much business has DEF done with us this year?
select sum(invamt) from invoices;
where custcode="DEF" and invdate>={^2015-01-01}
*
use in invoices
return
***** End of Example Code *****
Sincerely,
Gene Wirchenko
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
