Hi all,

I created a simple WebFOCUS procedure to get a row count to compare to a row count in a report generated by a different tool. My WebFOCUS procedure did not return the same number of rows. To determine which was the 'correct' number of rows I ran a direct SQL Query. It turns out my WebFOCUS results were 'wrong'. I tried two different methods in WebFOCUS, one going directly at the SQLMSS_TABLE and another putting the initial results in a HOLD file.

My question is this - why did the method with the HOLD file give me the results I expected but the method going directly against the SQLMSS_TABLE does not? Both methods use the same SUM QTY_FIELD BY SORT_FIELD ACROSS DATE_FIELD coding. I must be missing something in my understanding of how WebFOCUS creates SQL. Any explanation is much appreciated!

*** Notice how both of these methods return the same number of records from the SQL table (115706). Also notice how the SUM / ACROSS method from the HOLD table returns 22134 (the 'correct' number) lines but the SUM / ACROSS method from the SQL table only returns 21943 (the 'incorrect' number).
*** Also notice how the method with a HOLD table retrieves two additional columns from the SQL table (OE_REGION and ENG_FUEL). Both of these are used in the WHERE clauses but I'm not sure why they are used in the SELECT portion of the SQL stmt.
*** Finally, the grand totals of my QTY_FIELDs are the same in both methods so I know that all 115706 records are accounted for I just don't understand why I get different aggregation using the HOLD table versus going directly at the SQL table.

PROCEDURE WITHOUT HOLD LOOKS LIKE...

TABLE FILE SQLMSS_TABLE
SUM
	QTY_FIELD
BY     	SORT_FIELD1
BY     	SORT_FIELD2
BY     	SORT_FIELD3
BY     	SORT_FIELD4
BY     	SORT_FIELD5
BY     	SORT_FIELD6
BY     	SORT_FIELD7
BY     	SORT_FIELD8
BY     	SORT_FIELD9
ACROSS	DATE_FIELD
WHERE FILTER_CONDITION1;
WHERE FILTER_CONDITION2;
WHERE FILTER_CONDITION3;
WHERE FILTER_CONDITION4;
WHERE FILTER_CONDITION5;
WHERE FILTER_CONDITION6;
END


SQLTRACE OUTPUT LOOKS LIKE...

AGGREGATION DONE ...

SELECT 
SORT_FIELD1,
SORT_FIELD2,
SORT_FIELD3,
SORT_FIELD4,
SORT_FIELD5,
SORT_FIELD6,
SORT_FIELD7,
SORT_FIELD8,
SORT_FIELD9,
DATE_FIELD,
SUM(QTY_FIELD)

FROM dbo.SQLMSS_TABLE T1 

WHERE (
FILTER_CONDITION1 AND
FILTER_CONDITION2 AND
FILTER_CONDITION3 AND
FILTER_CONDITION4 AND
FILTER_CONDITION5 AND
FILTER_CONDITION6 AND
)

GROUP BY 
SORT_FIELD1,
SORT_FIELD2,
SORT_FIELD3,
SORT_FIELD4,
SORT_FIELD5,
SORT_FIELD6,
SORT_FIELD7,
SORT_FIELD8,
SORT_FIELD9,
DATE_FIELD 

ORDER BY
SORT_FIELD1,
SORT_FIELD2,
SORT_FIELD3,
SORT_FIELD4,
SORT_FIELD5,
SORT_FIELD6,
SORT_FIELD7,
SORT_FIELD8,
SORT_FIELD9,
DATE_FIELD;

0 NUMBER OF RECORDS IN TABLE=   115706  LINES=  21943
0 HOLDING HTML FILE ON PC DISK ...

  
PROCEDURE WITH HOLD LOOKS LIKE...

TABLE FILE SQLMSS_TABLE
PRINT
	QTY_FIELD
BY     	SORT_FIELD1
BY     	SORT_FIELD2
BY     	SORT_FIELD3
BY     	SORT_FIELD4
BY     	SORT_FIELD5
BY     	SORT_FIELD6
BY     	SORT_FIELD7
BY     	SORT_FIELD8
BY     	SORT_FIELD9
BY	DATE_FIELD
WHERE FILTER_CONDITION1;
WHERE FILTER_CONDITION2;
WHERE FILTER_CONDITION3;
WHERE FILTER_CONDITION4;
WHERE FILTER_CONDITION5;
WHERE FILTER_CONDITION6;
ON TABLE HOLD
END

TABLE FILE HOLD
SUM
	QTY_FIELD
BY     	SORT_FIELD1
BY     	SORT_FIELD2
BY     	SORT_FIELD3
BY     	SORT_FIELD4
BY     	SORT_FIELD5
BY     	SORT_FIELD6
BY     	SORT_FIELD7
BY     	SORT_FIELD8
BY     	SORT_FIELD9
ACROSS	DATE_FIELD
END

SQLTRACE OUTPUT LOOKS LIKE...

FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
FOC2594 - AGGREGATION IS NOT APPLICABLE TO THE VERB USED

SELECT 
SORT_FIELD1,
SORT_FIELD2,
SORT_FIELD3,
SORT_FIELD4,
SORT_FIELD5,
SORT_FIELD6,
SORT_FIELD7,
SORT_FIELD8,
SORT_FIELD9,
DATE_FIELD
QTY_FIELD
FIELD_FOR_FILTER_CONDITION6,
FIELD_FOR_FILTER_CONDITION2 

FROM SQLMSS_TABLE T1 

WHERE (
FILTER_CONDITION1 AND 
FILTER_CONDITION2 AND 
FILTER_CONDITION3 AND 
FILTER_CONDITION4 AND 
FILTER_CONDITION5 AND 
FILTER_CONDITION6
)

ORDER BY 
SORT_FIELD1,
SORT_FIELD2,
SORT_FIELD3,
SORT_FIELD4,
SORT_FIELD5,
SORT_FIELD6,
SORT_FIELD7,
SORT_FIELD8,
SORT_FIELD9;

0 NUMBER OF RECORDS IN TABLE=   115706  LINES= 115706
0 NUMBER OF RECORDS IN TABLE=   115706  LINES=  22134
0 HOLDING HTML FILE ON PC DISK ...

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

I would use SUM (rather than PRINT) in the SQL to HOLD request. That's a fairer comparison; see if it aligns the stats.

Have you compared the number of rows and columns of the two PCHOLD results? Are you getting the identical sequences of Date values across?

Have you looked at the actual SQL that WebFOCUS is generating?

What do your filter conditions look like? Are any of them using dates? In our shop with Oracle, all date fields are really datetime stamps. Now if all that is stored is the date, then the timestamp is set to midnight (00:00:00.00000). In our .mas files, if a date field is defined as USAGE=YYMD,ACTUAL=DATE and the field in the data base has a timestamp that is not midnight, and Oracle is doing the filtering, the following will return a different number of rows. In the following, I want all the rows for the prior month and &BEGDT=20080801, &ENDDT=20080831, &NXBOM=20080901.

WHERE DATEFIELD IS-FROM '&BEGDT' TO '&ENDDT'

or

WHERE DATEFIELD GE '&BEGDT'
  AND DATEFIELD LE '&ENDDT'

will return a different number of rows than

WHERE DATEFIELD GE '&BEGDT'
  AND DATEFIELD LT '&NXBOM'

Now, assume DATEFIELD contains '2008/08/31 14:00:00.00000'.

When you use & variable for dates and there is no time component, the SQL generated will look something like this:

WHERE DATEFIELD >= TO_DATE('20080801','YYYYMMDD')
  AND DATEFIELD <= TO_DATE('20080831','YYYYMMDD')

Since there is no time component specified in the TO_DATE function, the time is set to midnight (00:00:00.00000). Now if DATEFIELD contains '2008/08/31 14:00:00.00000', that row will fail the test and not be returned.

Now if for some reason, the SQL for the filter is not generated and WebFOCUS does the screening, then all 3 examples return the same number of rows. The reason is the field in the .mas is YYMD and the timestamp is truncated before WebFOCUS does the filter.

Bottom line is if you always use the 3rd technique (DATEFIELD GE '&BEGDT' AND DATEFIELD LT '&NXBOM'), you will always get the same number of rows no matter where the filter is performed.

Dan,

jgelona's solution is almost there in that the problem is due to date time truncation, but it's a bug in wf not a problem with an IF test.

If you examine the lines count the same number of records are going into the report.

What happens is that the aggregation is done on the MSSQL machine and not webfocus.

Thus when the answer set is returned you might have 2 records for 2008-09-22

date time cnt
2008-09-22 04:05:00 4
2008-09-22 14:05:00 2

This is then truncated to only contain the date when the record is returned to wf.

date cnt
2008-09-22 4
2008-09-22 2

The trouble comes when across tries to put values into its matrix it is expecting only one line per date. Thus it erroneously will only show a count of 4 in this case.

in the second case there is another explicit summation done on date which results in there only being one record per date and this time across works fine.


date cnt
2008-09-22 6

This is a bug which should be reported to ibi who will probably have it fixed for release 9.0

jgelona,

The SQL Code shown is what WebFOCUS is creating. I just changed the field names.

No, my problem is not related to the date format. I guess I should have used a different term for the Date_Field. It is actually an I4 format that contains 4-digit 'year' values.

Using SUM in the initial query returns the same number of rows in the result set. This is expected because the series of BY fields selected are actually those that make up the key. Anyway, the fact that the SUM/BY/ACROSS method taken directly from the SQL Table returns a different number of lines than the SUM/BY/ACROSS method from the HOLD File seems strange.

I guess I could save the HOLD File and then do a MATCH between it and the SQL Table to see what is different.

Thanks,

Dan

OK, so I re-coded by procedure from scratch in order to save the output to another SQL Table. I have no idea why but now I am getting the same results directly from the original SQL Table and from the 'hold' table.

Perhaps there was some erroneous character or something in the original procedure...

Thanks for all your input on this!!!

Dan