Conference ilbbak::ibi_focus

134.0. "alternate view not accessing all data" by JIFFY::BLUM_JO () Thu Sep 22 1988 17:58

    I have a report fex which uses 3 databases, each with a different
    year's data. Each database has the same master file description.
    The problem is the report will only access data from from FY '87.
    I was able to get it to access FY '88 and '89 data by changing the
    table request from an alternate view (TABLE FILE INVOICE.PART_NO) to
    the standard (?) view (TABLE FILE INVOICE).  Unfortunately, using
    the standard view, FOCUS takes roughly an hour to generate the report
    as opposed to 10-15 minutes with the alternate view.  Any suggestions
    on how to get the alternate view to work?
    
    The report portion of the fex follows.  After the fex, I'll list the
    code of the -INCLUDE.

    This note is posted in FOCUS, IBI_FOCUS, and USADSS.
    
    
    Regards,

    John

    
    ========================== REPORT FEX  ===============================

-* Database info.

-INCLUDE AIC_FEX:USEINV


TABLE FILE INVOICE.PART_NO

    SUM LI_GRS AS 'LINE ITEM,GROSS AMOUNT'
        BY SOLD_CUST_SN AS 'CUSTOMER NAME' SUBTOTAL AS 'TOTAL'
        BY SLS_DIST     AS 'DISTRICT'
        BY DEC_NO       AS 'DEC NUMBER'
        BY LI_INV_DT    AS 'DATE'
        BY LI_INV_QTY   AS 'LINE,INVOICE,QUANTITY'
        BY PART_NO      AS 'PART NUMBER'

    IF LI_INV_DT FROM &BEG_DT TO &END_DT  
    IF PART_NO   EQ '$$QSH$$$$' OR '$$HARDC$$' OR '$$HARDW$$'

END                                                                           


   ========================== -INCLUDE CODE ===============================
-* Database info.                                                               
-* PROGRAM: AIC_FEX:INV_USE.FEX                                                 
-* USE STATEMENT FOR DATABASES INVOICE, INV87 AND INV88                         
VMS @AIC_COMMAND:AIC_ASGN                                                       
USE                                                                             
-* UNREAL LOCAL DATA BASE                                                       
  AIC_LOCAL:SGKWOOPS.FOC NEW AS WOOPSSGK                                        
-* AIC DATA BASES                                                               
  AIC_DATA:INV87.FOC   READ                                                     
  AIC_DATA:INV87.FOC   READ AS INVOICE                                          
  AIC_DATA:INV88.FOC   READ                                                     
  AIC_DATA:INV88.FOC   READ AS INVOICE                                          
  AIC_DATA:INV89.FOC   READ                                                     
  AIC_DATA:INV89.FOC   READ AS INVOICE                                          
-* AIC MGR                                                                      
  AIC_DATA:MGR_AIC.FOC READ                                                     
-* REF FILES                                                                    
  AIC_DATA:AM03.FOC    READ                                                     
  AIC_DATA:APPL.FOC    READ                                                     
  AIC_DATA:BSRF.FOC    READ                                                     
  AIC_DATA:CHANNEL.FOC READ                                                     
  AIC_DATA:DEP.FOC     READ                                                     
  AIC_DATA:DEPT.FOC    READ                                                     
  AIC_DATA:DUNS.FOC    READ                                                     
  AIC_DATA:GEO.FOC     READ                                                     
  AIC_DATA:GOVT.FOC    READ                                                     
  AIC_DATA:IMC.FOC     READ                                                     
  AIC_DATA:MAJ_GEO.FOC READ                                                     
  AIC_DATA:NICKNAME.FOC READ                                                    
  AIC_DATA:PBOM.FOC    READ                                                     
  AIC_DATA:SEG.FOC     READ                                                     
  AIC_DATA:SIC.FOC     READ                                                     
  AIC_DATA:SLSREP.FOC  READ                                                     
  AIC_DATA:SOURCE.FOC  READ                                                     
  AIC_DATA:TABLE35.FOC READ                                                     
  AIC_DATA:TABLE40.FOC READ                                                     
  XAIC1USE.FOC NEW AS XAIC1USE                                                  
END                                                                             

    For those interested:
        
    I just got off the phone with a local IBI support person.  Here
    is IBI's response:

    	The problem is a FOCUS problem; using indexed views
	on concatenated databases is unsupported.  A New
   	Feature Request for this has been taken out by a
    	DEC employee in Maynard.  This request has also
    	been forwarded to a local FUSE group.  The request
        is being considered for possible inclusion in a later
	version.
    
    I asked why it worked once and not now, and basically it was
    luck --  sometimes it may work, soemtimes it won't.
    
    Now, being that this is an unsupported feature, does anyone have
    any suggestions for an efficient alternative?  I'd like to avoid
    having to create an extract database which gets updated weekly.
    

    Regards,
    
    John
    
    P.S.  The base note was posted in IBI-FOCUS and FOCUS.

    IBI called me to give me more info in this.  They said that although
    it won't work on indexed views, it will work on alternate views.
    
    Since PART_NO is not an indexed field, it looks like it's a problem
    with the application which uses the report or in the databases.
    
    Regards,
    
    John