RE: SQL loval date/timestamp--(function in SQL to select a value from a list)

coalesce(db_field,default_value)

example:

Select a.custno, a.custname, coalesce(sum(b.invtotal),0) as TotalSales
from               cust_mast a
  left outer join  sales_hist b
  on (a.custno=b.custno)
group by a.custno, a.custname
where b.invdate between xxxxxx and xxxxxx

Without coalesce, the left outer join could return nulls for totalsales.
Using coalesce allows us to override the null with 0, which is compatible
with the RPG way....


Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-898-7863 or ext. 1863



-----Original Message-----
From: Alan E. [mailto:steelville@xxxxxxxxxxx]
Sent: Saturday, October 11, 2003 6:11 PM
To: RPG programming on the AS400 / iSeries
Subject: Re: SQL loval date/timestamp--(function in SQL to select a
value from a list)


This reminds me of a pending question I've had that might
help. I've checked the manual, but can someone refresh my
mind on the SQL function that takes one value from a list of
two, where one is a database value, and the other is a value
to select in case the database value is null? (Or something
like that?)

- Alan






----- Original Message -----
From: "Joel Cochran" <jrc@xxxxxxxxxx>
To: "RPG programming on the AS400 / iSeries"
<rpg400-l@xxxxxxxxxxxx>
Sent: Friday, October 10, 2003 2:56 PM
Subject: Re: SQL loval date/timestamp


| Hi guys,
|
| Sorry I haven't gotten back to this yet, I've been out of
town since
| Tuesday.
|
| I have some traditional, DDS based, PFs with Date fields
that may or may
| not get populated at the initial write.  If they do not
get populated,
| then their default value is '0001-01-01' as one might
expect.  However,
| what I've run into is that in an SQLRPGLE program, when I
try to fetch a
| record that has one of these fields in it, I get an SQL
error saying "A
| Date or Timestamp is Invalid" and the record is NOT
fetched into the
| DS.  If I put a date value in the record then it processes
as desired.
|
| I remember hearing this from other people, and I remember
reading that
| the lowest Date SQLRPGLE will process is like '1940-01-01'
or something
| like that, but I can't seem to find the reference again.
That way, when
| I write a record I can insert a date that SQL will
process, and if I
| know what it is I can programmatically ignore it.
|
| Thanks,
|
| Joel
| http://www.rpgnext.com
|
|
| On Tue, 2003-10-07 at 18:02, Buck wrote:
| > > However, when we display the column that
| > >does have a null value, the display
| > >shows '0001-01-01'.
| >
| > That depends on how you display the column.  Some IBM
utilities will show
| > you *LOVAL out of 'courtesy' I suppose.  In earlier
releases I used to be
| > able to set a field value, update the record, set NULL,
update the record,
| > CHAIN to a new record, CHAIN to the first record and see
the original value,
| > despite it being NULL.
| >
| > The moral is that one can't depend on the value of a
field when it is NULL.
| >   --buck
| >
| >
| >
| > _______________________________________________
| > This is the RPG programming on the AS400 / iSeries
(RPG400-L) mailing list
| > To post a message email: RPG400-L@xxxxxxxxxxxx
| > To subscribe, unsubscribe, or change list options,
| > visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
| > or email: RPG400-L-request@xxxxxxxxxxxx
| > Before posting, please take a moment to review the
archives
| > at http://archive.midrange.com/rpg400-l.
|
| _______________________________________________
| This is the RPG programming on the AS400 / iSeries
(RPG400-L) mailing list
| To post a message email: RPG400-L@xxxxxxxxxxxx
| To subscribe, unsubscribe, or change list options,
| visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
| or email: RPG400-L-request@xxxxxxxxxxxx
| Before posting, please take a moment to review the
archives
| at http://archive.midrange.com/rpg400-l.
|

_______________________________________________
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.