Re: Interesting question and debate on ddl tables with datefieldsthatwill not always have a value

["Raul Alberto Jager Weiler" <raul@xxxxxxxxxx>]

if "marker" is associated with "in use", will be on, if it is associated 
with "has leaved" will we off, null goes with the field.  Now tr yo do
with markers:

Select count(field1), count(field2) from file  --  marker1 for field1,
marker2 for field2...

SQL is not a replacement for RPG, but rather than:

clear count;
setll (*begin) file;
dow %equal(file);
   	read file;
	if %eof(file);
		leave;
	endif;
	if marker = active;
		count += 1;
	endif;
enddo; ....

I prefer:
exec-sql select count(data) from file into :count

__________________________________________________________________

On Wed, 15 Oct 2008 18:27:55 -0400, DeLong, Eric <EDeLong@xxxxxxxxxxxxxxx>
wrote:

> Raul,
>
> Wouldn't the second need to be:
> select count(somefield) from table where marker = on
>
> In other words, you return a count of all rows where a value has been  
> stored....
>
> Just to be an old goat about this, one could argue that the latter is  
> actually more maintainable than the former, because the code completely
> describes the desired behavior.  This has been one of the leading
> arguments used to encourage developers to avoid the cycle.  In the
> cycle, one must have intimate knowledge of how and when hidden stuff
> happens.  Here, hidden stuff is knowing that null rows will not be
> counted by SQL.  Not a biggie to be sure, as this is well documented
> behavior for SQL, but....
>
> I also would not consider SQL a replacement for RPG.
>
> This has been an interesting discussion, and I will take a second look 
> at RPG's null support.
>
> Regards,
> Eric DeLong
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Raul Alberto Jager
> Weiler
> Sent: Wednesday, October 15, 2008 4:51 PM
> To: Midrange Systems Technical Discussion
> Subject: Re: Interesting question and debate on ddl tables with
> datefieldsthatwill not always have a value
>
> True for current RPG, not for SQL  ¿improved RPG?
>
> NULLs:  select count(somefield) from table
> Status: select count(somefield) from table where marker = off
>
> And a big difference if you use Relational Integrity.  Nulls don't need
> to be in the parent table, markers requiere a special record, that needs
> to be ommited in all queryes...
> _________________________________________________________________________________
> On Wed, 15 Oct 2008 17:14:54 -0400, DeLong, Eric
> <EDeLong@xxxxxxxxxxxxxxx>
> wrote:
>
> > Unlikely....  Code based on null field would look almost exactly like
> > code using status fields....
> >
> > If not %nullind(someDateField);
> >   OutData = %char(someDateField:*ISO); Endif;
> >
> >
> > If someDateField <> *loval;
> >   OutData = %char(someDateField:*ISO);
> > Endif;
> >
> >
> > If you have an example of an elegant solution involving null capable
> > fields, I would surely love to see it.
> >
> > Thanks,
> > Eric DeLong
> >
> > -----Original Message-----
> > From: midrange-l-bounces@xxxxxxxxxxxx
> > [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Raul Alberto Jager
> > Weiler
> > Sent: Wednesday, October 15, 2008 3:15 PM
> > To: Midrange Systems Technical Discussion
> > Subject: Re: Interesting question and debate on ddl tables with
> > datefieldsthat will not always have a value
> >
> > It is very likely that the problems that can be solved with "status
> > fields" are good candidates for a "more elegant" solution using null
> > capable fields.
> > ________________________________________________________________________
> > _________
> > On Wed, 15 Oct 2008 15:23:51 -0400, DeLong, Eric
> > <EDeLong@xxxxxxxxxxxxxxx>
> > wrote:
> >
> > > Yes, I understand...
> > >
> > > IMO, null capable fields offer little advantage in a well designed
> > > application.  It is simply a technology choice, and like most of
> > > these, its use needs to be applicable to the problem being solved.
> > >
> > > So, for me, the question is "what kinds of problems can be solved with
> >
> > > null capable fields?"
> > >
> > > I have a hard time with this, since I grew up on IBM midrange...  Most
> >
> > > of these problems, to me, are easily solved with status fields and/or
> > > timestamps.  My business logic can easily handle pseudo-null logic,
> > > simply by testing the field value.  If it still contains the initial
> > > default value, then treat it like a null....  This technique is
> > > understood well in the midrange world.
> > >
> > > Just to be fair, I *am* certain that there are circumstances where
> > > null is probably the best choice.  I'm just not sure what those
> > > circumstances might be... <g>
> > >
> > > Eric DeLong
> > >
> > > -----Original Message-----
> > > From: midrange-l-bounces@xxxxxxxxxxxx
> > > [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Doug Palme
> > > Sent: Wednesday, October 15, 2008 1:27 PM
> > > To: Midrange Systems Technical Discussion
> > > Subject: RE: Interesting question and debate on ddl tables with date
> > > fieldsthat will not always have a value
> > >
> > >    That is precisely what this exercise is all about Eric, the entire
> > >    department is involved in helping determine what our standards
> > > should and
> > >    will be.
> > >
> > >    From:    "DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>
> > >
> > >    To:      "Midrange Systems Technical Discussion"
> > > <midrange-l@xxxxxxxxxxxx>
> > >    Date:    10/15/2008 12:09 PM
> > >
> > >    Subject: RE: Interesting question and debate on ddl
> > tableswith
> > >                datefieldsthatwill not always have a value
> > >
> > >
> > > ----------------------------------------------------------------------
> > >
> > >    Doug,
> > >
> > >    No your example is fine, it's just that null does not necessarily
> > >    simplify ANYTHING in regard to business application logic.
> > > Whatever one
> > >    developer may do with null, another may do with control and status
> > >    fields.  There is not a right way or wrong way here.  Shop
> > standards
> > >    should dictate what approach is to be used in your environment.
> > >
> > >    Eric DeLong
> > >
> > >    -----Original Message-----
> > >    From: midrange-l-bounces@xxxxxxxxxxxx
> > >    [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Doug Palme
> > >    Sent: Wednesday, October 15, 2008 11:19 AM
> > >    To: Midrange Systems Technical Discussion
> > >    Subject: RE: Interesting question and debate on ddl tableswith
> > >    datefieldsthatwill not always have a value
> > >
> > >      Apparently I used a horrible example.......that is not a file
> > that
> > >    exists,
> > >      I was making it an example for the basis of my question....
> > >
> > >      From:    "Paul Nelson" <nelsonp@xxxxxxxxxxxxx>
> > >
> > >      To:      "'Midrange Systems Technical Discussion'"
> > >
> > >               <midrange-l@xxxxxxxxxxxx>
> > >
> > >      Date:    10/15/2008 11:04 AM
> > >
> > >      Subject: RE: Interesting question and debate on ddl tables with
> > >
> > >                datefieldsthatwill not always have a value
> > >
> > > ----------------------------------------------------------------------
> > >
> > >      A proper system would have a rehire date, as well as reason codes
> >
> > > to
> > >      describe the termination. CYA, you know?
> > >
> > >      Paul Nelson
> > >      Office 512-392-2577
> > >      Cell 708-670-6978
> > >      nelsonp@xxxxxxxxxxxxx
> > >
> > >      -----Original Message-----
> > >      From: midrange-l-bounces@xxxxxxxxxxxx
> > >      [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
> > >      lgoodbar@xxxxxxxxxxxxxx
> > >      Sent: Wednesday, October 15, 2008 10:14 AM
> > >      To: midrange-l@xxxxxxxxxxxx
> > >      Subject: RE: Interesting question and debate on ddl tables with
> > >      datefieldsthatwill not always have a value
> > >
> > >      A good employee tracking system should have an employment status
> > >    flag.
> > >      "Obviously", one would check that flag rather than a null
> > > termination
> > >      date.
> > >
> > >      More relevant: what if the employee is rehired? Should the
> > >    termination
> > >      date be reset to null? The employee was actually terminated (or
> > >    perhaps
> > >      laid off) in the past, and setting a null value loses historical
> > >    data. A
> > >      decent system will have an employment history table; but most
> > I've
> > >    seen
> > >      have standalone hire and term date fields as well.
> > >
> > >      Loyd Goodbar
> > >      Business Systems
> > >      BorgWarner Shared Services
> > >      662-473-5713
> > >
> > >      -----Original Message-----
> > >      From: midrange-l-bounces@xxxxxxxxxxxx
> > >      [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Joe Pluta
> > >      Sent: Tuesday, October 14, 2008 5:16 PM
> > >      To: Midrange Systems Technical Discussion
> > >      Subject: Re: Interesting question and debate on ddl tables with
> > > date
> > >      fieldsthatwill not always have a value
> > >
> > >      Walden H. Leverich wrote:
> > >      > Use nulls, that's what they're for!
> > >      >
> > >      I'm not picking on you specifically, Walden, because many people
> > > have
> > >      contributed to this thread, but yours is the most on-point
> > > statement.
> > >
> > >      Anyway, not everyone agrees with your blanket assessment of nulls
> > -
> > >      including folks like Chris Date and even Dr. Codd himself.  A
> > > null in
> > >    a
> > >      value (in this case, the termination date) could mean lots of
> > > things:
> > >    it
> > >
> > >      could mean that the employee is still working for us (meaning
> > there
> > >    is
> > >      no termination date).  But it could just as easily mean we don't
> > > know
> > >      what the termination date was, that the termination date we have
> > is
> > >      invalid, or that the termination date hasn't been entered yet.
> > >
> > >      No, just about every database scientist whom I've read would
> > insist
> > >    on a
> > >
> > >      flag that indicates the status of the employee rather than
> > > relying on
> > >      the NULL value to give you any more information than the fact
> > that
> > >    you
> > >      don't have a termination date.
> > >
> > >      I won't belabor the point.  I just want to make sure that the
> > > notion
> > >    of
> > >      databases that is currently in vogue today often differs
> > > dramatically
> > >      from the folks who actually came up with the concept.  That's not
> >
> > > to
> > >    say
> > >
> > >      that there is One True Database Design; Codd and Date actually
> > >    disagreed
> > >
> > >      on nulls.  But neither one agreed with the SQL approach.
> > >
> > >      So anyway, while the general assessment on this list of the use
> > of
> > >    nulls
> > >
> > >      is probably the most common one, that doesn't make it right.  And
> >
> > > it
> > >      certainly doesn't mean that RPG programmers who don't like nulls
> > > are
> > >      some sort of Luddites; indeed, it just means that they choose not
> >
> > > to
> > >    use
> > >
> > >      a specific technique - one that has arguably been misused or at
> > > least
> > >      overused by an entire generation of SQL programmers.
> > >
> > >      Joe
> > >
> > >      --
> > >      This is the Midrange Systems Technical Discussion (MIDRANGE-L)
> > >    mailing
> > >      list
> > >      To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > >      To subscribe, unsubscribe, or change list options,
> > >      visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > >      or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > >      Before posting, please take a moment to review the archives
> > >      at http://archive.midrange.com/midrange-l.
> > >
> > >      --
> > >      This is the Midrange Systems Technical Discussion (MIDRANGE-L)
> > >    mailing
> > >      list
> > >      To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > >      To subscribe, unsubscribe, or change list options,
> > >      visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > >      or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > >      Before posting, please take a moment to review the archives
> > >      at http://archive.midrange.com/midrange-l.
> > >
> > >      This transmission may contain information that is privileged,
> > >    confidential
> > >      and/or exempt from disclosure under applicable law. If you are
> > not
> > >    the
> > >      intended recipient, you are hereby notified that any disclosure,
> > >    copying,
> > >      distribution, or use of the information contained herein
> > (including
> > >    any
> > >      reliance thereon) is STRICTLY PROHIBITED. If you received this
> > >      transmission in error, please immediately contact the sender and
> > >    destroy
> > >      the material in its entirety, whether in electronic or hard copy
> > >    format.
> > >      Thank you.
> > >    --
> > >    This is the Midrange Systems Technical Discussion (MIDRANGE-L)
> > > mailing
> > >    list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
> > >    unsubscribe, or change list options,
> > >    visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > >    or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please
> > > take a
> > >    moment to review the archives at
> > > http://archive.midrange.com/midrange-l.
> > >
> > >    --
> > >    This is the Midrange Systems Technical Discussion (MIDRANGE-L)
> > > mailing
> > >    list
> > >    To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > >    To subscribe, unsubscribe, or change list options,
> > >    visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > >    or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > >    Before posting, please take a moment to review the archives
> > >    at http://archive.midrange.com/midrange-l.
> > >
> > >    This transmission may contain information that is privileged,
> > > confidential
> > >    and/or exempt from disclosure under applicable law. If you are not
> > > the
> > >    intended recipient, you are hereby notified that any disclosure,
> > > copying,
> > >    distribution, or use of the information contained herein (including
> >
> > > any
> > >    reliance thereon) is STRICTLY PROHIBITED. If you received this
> > >    transmission in error, please immediately contact the sender and
> > > destroy
> > >    the material in its entirety, whether in electronic or hard copy
> > > format.
> > >    Thank you.
> > > --
> > > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> >
> > > list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
> > > unsubscribe, or change list options,
> > > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > > or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
> > > a moment to review the archives at
> > http://archive.midrange.com/midrange-l.
> > >
> >
> >
> >
> > --
> > Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
> >
> > --
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> > list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
> > unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
> > moment to review the archives at http://archive.midrange.com/midrange-l.



--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/