RE: Single record access really required (was RE: Views and Indexes)

["Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx>]

> From: rob@xxxxxxxxx
> 
> Your first example, getting the GL number account number from the item
> class.
> SELECT iim.IPROD, iim.IDESC, iim.ICLAS, iic.ICGL
> FROM iim
> join iic on iim.iclas=iic.iclas

Rob, what is this an example of?  Getting a list of items and their GL
numbers?  That's really not the same thing as getting the GL account for
a record during a transaction, or validating that one is set up for the
item, which is what we'd be doing in order entry.

In any case, it gets more fun.  How about if you can have an override
value in the item master.  Also, either the item master or the item
class can have a special value called *COMPANY which uses the default
for the company.  But only if a switch is set in the system globals that
allows defaults.  The switch is accessed through an API which returns
'Y' or 'N', although technically you could probably read the files which
store it, remembering that the file is hierarchical, with a global value
that can be overridden by a company record (which is why we use the API
in the first place).

File COM has a field DFTGLACT
File SYS has a field level (S=System, C=Company)
         and a field ALWDFT (Y or N)

I realize this isn't a single-record fetch.  But it's butt-simple to do
in native I/O.  Let's say we start with item number (ITEM) and customer
(CUST):


Chain ITEM IIM;
Assert(%found(IIM), 'IIM record not found');
WorkGL = IGLOVR;
If (WorkGL = *blanks);
  Chain IMCLAS IIC;
  Assert(%found(IIC), 'IIC record not found');
  WorkGL = ICGL;
Endif;
If (WorkGL = '*COMPANY');
  Chain CUST CUS;
  Assert(%found(CUS), 'CUS record not found');
  Assert(GetSysVal(CUCOMP, "ALWDFT") = 'Y', 'Defaults not allowed!');
  Chain CUCOMP COM;
  Assert(%found(COM), 'COM record not found');
  WorkGL = DFTGLACT;
Endif;

Note that GetSysVal is written to get the setting for the appropriate
field based on the company and the key.  This routine could also cache
the key values as needed, which is a nice use of procedures.

Okee dokee, let's see the SQL equivalent.  Note that I've got error
assertions throughout the code, so I know exactly which master record is
not found.

I think I'm going to use this as an example on the new site.


> And if this is something users are doing all over the place in query's
and
> whatnot

What is the deal with queries, and users?  User queries are only a
subset of business applications.  Queries are views of data.  There are
other applications, applications which modify the database based on
other data input by users.  This is a fundamental difference... like the
difference between SQL and native I/O, not coincidentally.


> Your second example on checking the item number.
> Technically you could write the order record out and check the status
of
> the write.  It it was a constraint error you could check the
constraint
> message and then find out that the error was due to the item number
being
> invalid.  This may sound clunky to you,

Yeah. It's clunky.  Real clunky.  In fact it reminds me of the guy who
only has a hammer in his toolbox - he'll figure out how to use that
hammer, dammit!  The right way IMO?  Send the record to a server and get
the results.

Joe