Re: AW: SQL null indicators when not all columns are null capable

I agree.

It can be even better, like automaticaly turnig off the nullind when you
assign a value. Also, if should give always false if the value is null,
(without checking NULLIND).

Also will be nice to be able to use the sql sintax "if xx is null" in
RPG, as equivalent to "if %nullind(XX)"

Marco Facchinetti wrote:

Just my opinion: the real PITA is that we have 4 ways to determine the NULL
state.

1 - SQL: is null, coalesce ...
2 - SQLRPGLE: indicator variables
3 - RPGLE: %nullind
4 - RPGLE in TRIGGER PROGRAMS: indicator variables (but not the same as for
SQLRPGLE)

and maybe more if you count other languages.

Of course accepting this situation as normal will drive to sentences like
"people use a lot of effort (hours) to
use a feature that makes a lot of trouble". The real point is that we should
have AUTOMATICLY %nullind available in case 2/3/4.

MarcoF

2010/4/15 CRPence <CRPbottle@xxxxxxxxx>

hr@xxxxxxxxxxxx wrote:

Just following this discussion from the sideline, can anybody
give me a reason to use a *NULL capable field in a commercial
database ?

It seems to me that it only makes trouble !

The database NULL value indicates that there is "no assigned
value". The NULL value is the only legitimate way to establish the
condition of /unassigned/ for any data type supported by the
database. Referring to any valid value [i.e. any non-NULL value] of
a particular data type as an effective "special value" in order to
represent the condition of /unassigned/ might be functional within
an application, but that assumption does not hold for when the
database table is being used solely by the SQL.

For example, in reporting via SQL, having used -99 to indicate
/unassigned/ will have the SQL showing the value as -99, because
that was in fact the assigned value. So although the value -99
/assigned/ for a P(2,0) column in some row might indicate to the
application that the value has not yet been assigned, the SQL sees
that the P(2,0) column has an actual and valid value of -99 for that
row. Having the different meanings for the same datum [i.e. -99 as
/unassigned/ in the application, but -99 as -99 in the SQL]
establishes an incongruous definition of that datum between an
application and by the SQL.

Similarly if the exported database table data might be sent to
another database [typically using the SQL], with an actual value
versus a NULL value representing the condition of /unassigned/, an
additional explanation to the recipient of that caveat, that the
special value is to be considered a NULL value, would be required.
That would make the process of sharing the data more complicated
than if the representation of /unassigned/ had simply used the SQL
convention of the database NULL value.

If that column [using -99 as /unassigned/] ever needs to be
referred to by an SQL predicate such as column<15 [i.e. less than
fifteen], then the writer of the query needs to know that the value
of negative ninety nine will be included. When that column appears
in the result set, the error in the query may be obvious, but
unlikely so obvious when that column is not included. If the column
had the NULL value instead of -99 meaning unassigned, the less than
fifteen predicate would not return the rows having the -99 for that
column.

If that column ever needs to be referred to by an SQL aggregate
function, the /summarized/ result would be falsely skewed by the
valid values which are intended only to represent the condition of
unassigned, unless those rows are explicitly omitted from the
SELECT. Omitting the values may not allow the query to produce the
expected results, thus introducing a CASE statement or NULLIF might
be required instead, to replace occurrences the /special value/. If
the unassigned values are established originally as the NULL value,
then the default processing by the aggregate functions would ignore
the NULL values; again, even for when the data is passed to another
database for use outside of the application.

Regards, Chuck
--
This is the RPG programming on the IBM i / System i (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.