Hello,

i need to print two distinct counts for a report. The report is as below.

LT ID LY ID
--------- ---------
3000021 FREP31
3000022 FREP31
3000030 FREP50
3000031 FREP51
3000034 FREP53
3000035 FREP56
3000035 FREP57
3000035 FREP59
3000037 FREP57
3000037 FREP59
3000039 FREP55
3000039 FREP58
4000001 FADDR02
4000003 FADDR02

LT COUNT 10
LY COUNT 10

The above data is generated by an SQL. Now i need to print distinct LT ID count and distinct LY ID count. I am able to get the distinct count for LT ID since it is sorted. But the problem arises with LY ID. This is what i've coded.

  
TABLE FILE SQLOUT        
PRINT *                  
ON TABLE HOLD AS TEMPLT 
FORMAT FOCUS             
END                      
OFFLINE 
-*
TABLE FILE TEMPLT
SUM CNT.DST.LY_ID AS TOT_LY_CNT        
BY LY_ID AS TEMP_LY_ID
ON TABLE HOLD AS TEMPLY
END         
-*
DEFINE FILE TEMPLT
TOT_DST_LT_CNT/I5 = IF LT_ID EQ LAST LT_ID
            THEN LAST TOT_DST_LT_CNT        
            ELSE LAST TOT_DST_LT_CNT + 1;   

TOT_DST_LY_CNT/I5 MISSING ON = DB_LOOKUP(TEMPLY,LY_ID,TEMP_LY_ID,TOT_LY_CNT);
END                                          
OFFLINE    
-* 
TABLE FILE TEMPLT
PRINT
     LT_ID               AS 'LT_ID'
     LY_ID               AS 'LY_ID'
     TOT_DST_LT_CNT      NOPRINT
     TOT_DST_LY_CNT      NOPRINT
ON TABLE SET PAGE-NUM ON
ON TABLE SUBFOOT
    "LT COUNT: <20 <TOT_DST_LT_CNT"
    "LY COUNT: <20 <TOT_DST_LY_CNT"
END 
-RUN
FIN 

When i execute this, i get this message

  
 ON TABLE SET PAGE-NUM ON                                
 ON TABLE SUBFOOT                                        
    "CLIENT COUNT:  <20 <TOT_DST_LT_CNT"                
    "POLICY COUNT:  <20 <TOT_DST_LY_CNT"                
 ON TABLE SET STYLE *                                    
(FOC1109) DATA IS NOT PRESENT FOR FOCUS FILE  : TEMPLY  
 ...RETRIEVAL KILLED                                  
(FOC026) THE REPORT IS NO LONGER AVAILABLE               

Can anyone help me in figuring this out. The task looks quite simple. Since im new to webfocus, i dont quite understand what might the problem be.

Thanks in Advance.

This message has been edited. Last edited by: <Kathryn Henning>,

Maybe try:

  
TABLE FILE SQLOUT        
PRINT *                  
ON TABLE HOLD AS TEMPLT FORMAT FOCUS             
END                      
-*
TABLE FILE TEMPLT
SUM CNT.DST.LY_ID AS 'TOT_LY_CNT'        
BY LY_ID AS 'TEMP_LY_ID'
ON TABLE HOLD AS TEMPLY
-* ensure asnames are used in hold
ON TABLE SET ASNAMES ON
-* only hold directly referenced columns
ON TABLE SET HOLDLIST PRINTONLY
END         
-*
DEFINE FILE TEMPLT
TOT_DST_LT_CNT/I5 = IF LT_ID EQ LAST LT_ID
            THEN LAST TOT_DST_LT_CNT        
            ELSE LAST TOT_DST_LT_CNT + 1;   
TOT_DST_LY_CNT/I5 MISSING ON = DB_LOOKUP(TEMPLY,LY_ID,TEMP_LY_ID,TOT_LY_CNT);
END                                          
-* 
TABLE FILE TEMPLT
PRINT
     LT_ID               AS 'LT_ID'
     LY_ID               AS 'LY_ID'
     TOT_DST_LT_CNT      NOPRINT
     TOT_DST_LY_CNT      NOPRINT
ON TABLE SET PAGE-NUM ON
ON TABLE SUBFOOT
    "LT COUNT: <20 <TOT_DST_LT_CNT"
    "LY COUNT: <20 <TOT_DST_LY_CNT"
END 
-RUN

On a side note, have you tried just doing a CNT.DST against both columns from the original table?

Maybe try to do the counts separately (in separate hold files if necessary) and store the value in a variable to be used by your final report for displaying?

If it were me I would write it something like this:

DEFINE FILE SQLOUT
BLANK/A1=' ';
END
TABLE FILE SQLOUT        
PRINT *  
BY BLANK                
ON TABLE HOLD AS TEMPLT 
FORMAT FOCUS INDEX BLANK           
END                      
OFFLINE 
-*
TABLE FILE TEMPLT
SUM CNT.DST.LY_ID AS TOT_LY_CNT 
BY BLANK
ON TABLE HOLD AS TEMP_LY FORMAT FOCUS INDEX BLANK
END

TABLE FILE TEMPLT
SUM CNT.DST.LY_ID AS TOT_LT_CNT 
BLANK  
ON TABLE HOLD AS TEMP_LT FORMAT FOCUS INDEX BLANK
END    
-*
JOIN BLANK IN TEMPLT TO BLANK IN TEMP_LY AS J0
JOIN BLANK IN TEMPLT TO BLANK IN TEMP_LT AS J1  
-* 
TABLE FILE TEMPLT
PRINT
     LT_ID               AS 'LT_ID'
     LY_ID               AS 'LY_ID'
     TOT_LT_CNT      NOPRINT
     TOT_LY_CNT      NOPRINT
ON TABLE SET PAGE-NUM ON
ON TABLE SUBFOOT
    "LT COUNT: <20 <FST.TOT_LT_CNT"
    "LY COUNT: <20 <FST.TOT_LY_CNT"
END 
-RUN

Why not doing a CNT.DST on both fields?

  
-* File Ajmal01.fex
-* Create the data file
EX -LINES 15 EDAPUT DATA,AJMAL,C,FILE
3000021 FREP31 
3000022 FREP31 
3000030 FREP31 
3000031 FREP31 
3000034 FREP53 
3000035 FREP56 
3000035 FREP57 
3000035 FREP59 
3000037 FREP57 
3000037 FREP59 
3000039 FREP55 
3000039 FREP58 
4000001 FADDR02
4000003 FADDR02
-RUN
-* Create the MASTER for the data file
EX -LINES 6 EDAPUT MASTER,AJMAL,C,MEM
FILENAME=AJMAL, SUFFIX=FIX
SEGNAME=AJMAL, SEGTYPE=S0
FIELDNAME=LTID, FORMAT=A7, ACTUAL=A7, $
FIELDNAME=LYID, FORMAT=A8, ACTUAL=A8, $
FIELDNAME=FILL, FORMAT=A1, ACTUAL=A65, $
-RUN
-* Run the report
TABLE FILE AJMAL
SUM CNT.DST.LTID CNT.DST.LYID
END

quote:

Originally posted by eric.woerle:
If it were me I would write it something like this:

DEFINE FILE SQLOUT
BLANK/A1=' ';
END
TABLE FILE SQLOUT        
PRINT *  
BY BLANK                
ON TABLE HOLD AS TEMPLT 
FORMAT FOCUS INDEX BLANK           
END                      
OFFLINE 
-*
TABLE FILE TEMPLT
SUM CNT.DST.LY_ID AS TOT_LY_CNT 
BY BLANK
ON TABLE HOLD AS TEMP_LY FORMAT FOCUS INDEX BLANK
END

TABLE FILE TEMPLT
SUM CNT.DST.LY_ID AS TOT_LT_CNT 
BLANK  
ON TABLE HOLD AS TEMP_LT FORMAT FOCUS INDEX BLANK
END    
-*
JOIN BLANK IN TEMPLT TO BLANK IN TEMP_LY AS J0
JOIN BLANK IN TEMPLT TO BLANK IN TEMP_LT AS J1  
-* 
TABLE FILE TEMPLT
PRINT
     LT_ID               AS 'LT_ID'
     LY_ID               AS 'LY_ID'
     TOT_LT_CNT      NOPRINT
     TOT_LY_CNT      NOPRINT
ON TABLE SET PAGE-NUM ON
ON TABLE SUBFOOT
    "LT COUNT: <20 <FST.TOT_LT_CNT"
    "LY COUNT: <20 <FST.TOT_LY_CNT"
END 
-RUN

Hi Eric,

I tried your way and i got the following error message

 TABLE FILE SQLOUT                                               
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: SQLOUT  
 BYPASSING TO END OF COMMAND                                     
 PRINT *                                                         
 ON TABLE HOLD AS TEMPCLI                                        
 FORMAT FOCUS                                                    
 END                                                             

quote:

Originally posted by dhagen:

On a side note, have you tried just doing a CNT.DST against both columns from the original table?

Hi Dhagen,

Yes i did that. Did not work.

Hi,

If Eric's code does not work, then you will need to start over again and test your original code step by step.
Everyone has been using the code that you originally posted. i.e:

quote:

The above data is generated by an SQL. Now i need to print distinct LT ID count and distinct LY ID count. I am able to get the distinct count for LT ID since it is sorted. But the problem arises with LY ID. This is what i've coded.


TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS TEMPLT
FORMAT FOCUS
END

So start by just running the code above. If this does not work, then first fix this error.
Only then will you be able to test the next set of code.
----
Also - have you tried to run Daniel's sample code yet?

Hello Twanette,

Thank you for your input. I have come to the conclusion that there is some problem with the SQL query which generates the original data. I am relooking at that. Because i have another requirement wherein i run my WebFOCUS query against a flat file and it works without any errors.

quote:

Also - have you tried to run Daniel's sample code yet?

Hi Daniel,
Sorry i forgot to reply to your input. I am confident that will work but the problem is i cannot hardcode the input like that since it is generated during runtime by the SQL.

quote:

Why not doing a CNT.DST on both fields?

That is also one of the many things i tried before coming to this forum. I keep getting the same error i mentioned in the original post.

Thank you all for your inputs.

Hi,

I don't think Daniel meant for to use the code with the hardcoded values - but just try to run it and let us know whether you get the result that you want.
If it runs and the two counts are displayed, then the problem isn't to do with the CNT.DST code.

In terms of the error on your original post i.e.
(FOC1109) DATA IS NOT PRESENT FOR FOCUS FILE : TEMPLY

This error is generated by the following code going wrong:

TABLE FILE TEMPLT
SUM CNT.DST.LY_ID AS TOT_LY_CNT        
BY LY_ID AS TEMP_LY_ID
ON TABLE HOLD AS TEMPLY
END 

This could be caused by one of two things:
1) Try to change: ON TABLE HOLD AS TEMPLY FORMAT FOCUS
2) Perhaps there is no data, because the first step didn't work

Ok i modified my code the following way and got rid of the "DATA IS NOT PRESENT FOR FOCUS FILE" error. Please see below code

TABLE FILE SQLOUT                
PRINT *                          
ON TABLE HOLD AS TEMPLT         
FORMAT FOCUS                     
END                              
OFFLINE                          
-*                               
TABLE FILE TEMPLT               
SUM CNT.DST.LT_ID AS TOT_LT_CNT
BY LT_ID AS TEMP_LT_ID         
ON TABLE HOLD AS TEMPCV          
ON TABLE SET ASNAMES ON          
ON TABLE SET HOLDLIST PRINTONLY  
END                   

DEFINE FILE TEMPLT
TOT_DST_LT_CNT/I5  MISSING ON =  DB_LOOKUP(TEMPCV,LT_ID,TEMP_LT_ID,TOT_LT_CNT);
END
OFFLINE                                         
-*
TABLE FILE TEMPLT
PRINT
 LT_ID                    AS 'LT ID' 
 LY_ID                    AS 'LY ID'                             
 TOT_DST_LT_CNT           NOPRINT
 AND COMPUTE                                               
 TOT_DST_LY_CNT/I5 = IF LY_ID EQ LAST LY_ID             
      THEN LAST TOT_DST_LY_CNT                           
      ELSE LAST TOT_DST_LY_CNT + 1; AS LY_COUNT  NOPRINT
-*if is sort by LY ill get the correct LY count. If i sort by LT ill get the correct LT count. I tried sorting by LT then LY but it doesnt work.
 BY LY_ID NOPRINT                                         
 FOOTING CENTER                                         
 "**END OF PAGE**"                                      
 ON TABLE SET PAGE-NUM ON                               
 ON TABLE SUBFOOT                                       
    "LT COUNT:  <20 <TOT_DST_LT_CNT"               
    "LY COUNT:  <20 <TOT_DST_LY_CNT"               
 ON TABLE SET STYLE *                                   
 TYPE=TITLE, JUSTIFY=CENTER, STYLE=BOLD, $              
 TYPE=DATA, GRID=OFF, $                                 
 END                                                    
 -RUN                                                   
 FIN                   

When i executed this, i got the below error message

 ON TABLE SUBFOOT                                                     
    "LT COUNT:  <20 <TOT_DST_LT_CNT"                             
    "LY COUNT:  <20 <TOT_DST_LY_CNT"                             
 ON TABLE SET STYLE *                                                 
(FOC1070) VALUE FOR JOIN 'FROM' FIELD OUT OF SEQUENCE. RETRIEVAL ENDED
 ...RETRIEVAL KILLED                                                  
(FOC026) THE REPORT IS NO LONGER AVAILABLE                            

Please advise.

Thanks,
Ajmal

This message has been edited. Last edited by: Ajmal18,

As per the functions manual:
"A fixed format sequential file can be the lookup file if it is sorted in the same order as the source file."