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

[CWilt@xxxxxxxxxxxx]

>  -----Original Message-----
> From:         Joe Pluta [mailto:joepluta@xxxxxxxxxxxxxxxxx] 
> Sent: Monday, July 26, 2004 11:54 AM
> To:   'Midrange Systems Technical Discussion'
> Subject:      RE: Single record access really required (was RE: Views and
Indexes)
> 
> 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.

Joe,

You don't say where ITEM and CUST are coming from.  I'll assume a file named
INPUTFILE.  I'm simply showing the select here, not the code to create the
cursor or do the fetch.  Also, %ifnull(itm.item) is my pseudocode method of
saying check the null indicator used for the ITEM field returned from the
ITM file.

select A.*, itm.item, itm.iglovr, iic.icgl, cus.cucomp, com.dftglact
from INPUTFILE A 
        left join itm on A.item = itm.item
        left join cus on A.cust = cus.cust
        left join com on cus.cucomp = com.comp
where
  <...>  

//Application code
Assert(%ifnull(itm.item), 'IIM record not found');
WorkGL = IGLOVR;
If (WorkGL = *blanks);
  Assert(%ifnull(icc.icgl), 'IIC record not found');
  WorkGL = ICGL;
Endif;
If (WorkGL = '*COMPANY');
  Assert(%ifnull(cus.cucomp), 'CUS record not found');
  Assert(GetSysVal(CUCOMP, "ALWDFT") = 'Y', 'Defaults not allowed!');
  Assert(%ifnull(COM.dftGlAct), 'COM record not found');
  WorkGL = DFTGLACT;
Endif;

Not all that different is it?



Charles