Focal Point
[SOLVED] CNT operation always generates COUNT(*) in SQL

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/5931061203

October 09, 2008, 04:00 PM
jwoelfel
[SOLVED] CNT operation always generates COUNT(*) in SQL
I'm noticing something strange with the FOCUS CNT operator. I'm running a simple query like:

TABLE FILE PD_UTILITY_CONSUMPTIONSUM
CNT.MO_EXP_A
WHERE ACCT_YR_I EQ 2008
WHERE ACCT_MO_I EQ 4
WHERE ST_PROV_C EQ 'MN'
END

The SQL that is being generated is
SELECT
COUNT(*)
FROM
PD_DM.dbo.UTIL_FSCL_MO_USE_V T1,
PD_DM.dbo.CAL_FSCL_YR_MO_V T2,
PD_DM.dbo.PD_RPT_LOC_V T6
WHERE
(T2."ACCT_YR_I" = T1."YR_I") AND (T2."ACCT_MO_I" = T1."MO_I") AND
(T6."CO_LOC_GL_I" = T1."CO_LOC_I") AND
(T2."ACCT_MO_I" = 4) AND
(T2."ACCT_YR_I" = 2008) AND
(T6."ST_PROV_C" = 'MN');

The SQL that should be generated is:
SELECT
COUNT(MO_EXP_A)
FROM
PD_DM.dbo.UTIL_FSCL_MO_USE_V T1,
PD_DM.dbo.CAL_FSCL_YR_MO_V T2,
PD_DM.dbo.PD_RPT_LOC_V T6
WHERE
(T2."ACCT_YR_I" = T1."YR_I") AND (T2."ACCT_MO_I" = T1."MO_I") AND
(T6."CO_LOC_GL_I" = T1."CO_LOC_I") AND
(T2."ACCT_MO_I" = 4) AND
(T2."ACCT_YR_I" = 2008) AND
(T6."ST_PROV_C" = 'MN');

Because MO_EXP_A contains NULLs in the database, we really need the COUNT(MO_EXP_A) instead of COUNT(*); these 2 queries produce very different results.

Has anyone run across this issue before? This seems like a pretty big deal, it also seems to be affecting how the AVG operation works as well. Is there just a setting that we need to make someplace?

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


Web Server: iPlanet 6.0
App Server: Websphere 6.0
WFClient: 7.6.9
WFServer: 7.6.9 (Windows)
MRE & SSA
October 09, 2008, 04:13 PM
FrankDutch
What happens if you say

SUM COUNT fieldname

or

create a defined field

NEWFIELD/I5=IF MO_EXP_A IS MISSING THEN 0 ELSE 1;

and do a SUM on that field

(But this is a workaround and not solving this strange behavior)




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

October 09, 2008, 04:50 PM
jwoelfel
quote:
SUM COUNT fieldname


This syntax doesn't seem to be valid. However, "COUNT fieldname" without the SUM keyword is valid syntax, but produces the exact same SQL.

I'm sure your workaround would work, I was just hoping that I wouldn't have to code a workaround. There has to be something easy that I'm missing... I hope.


Web Server: iPlanet 6.0
App Server: Websphere 6.0
WFClient: 7.6.9
WFServer: 7.6.9 (Windows)
MRE & SSA
October 09, 2008, 05:13 PM
Tony A
Years ago WRITE CNT.fieldname produced different SQL against DB2 than SUM CNT.fieldname did. I doubt that would be the case nowadays but it's always worth a try.

The method I would use to enforce the SQL that I wanted run is to use SQL passthru. I wouldn't consider it a workaround, more like controlling the processing.

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
October 15, 2008, 04:35 PM
jwoelfel
Unfortunately, I'm trying to implement the COUNT option in an adhoc tool, and thus don't really have control of the SQL being created.

Here's another interesting finding: CNT.fieldname will work correctly if the fieldname is a text field. (Axxx) In this case, the SQL being generated is COUNT(fieldname) as I'd expect. However, if fieldname is numeric (D20.2 in my case) the SQL that gets generated is COUNT(*). It seems as if this was intentionally done when the SQL generator was created. Why..? I don't know.


Web Server: iPlanet 6.0
App Server: Websphere 6.0
WFClient: 7.6.9
WFServer: 7.6.9 (Windows)
MRE & SSA
October 15, 2008, 05:04 PM
Francis Mariani
Quite intriguing.

I did a test on a MS SQL Server table (WF version 5.3.2) and it looks like it's not whether a column is numeric or alpha, but whether or not nulls are allowed in the column:

                Data           Allow  Generated
Column Name     Type   Length  Nulls  Count
--------------  -----  ------  -----  ---------
Date            char        8  No     COUNT(*)
Fiscal_Quarter  char        7  Yes    COUNT(Fiscal_Quarter)
Fiscal_Year     numeric     9  Yes    COUNT(Fiscal_Year)
Time_Key        numeric     5  No     COUNT(*)
Date_Fmt        datetime    8  Yes    COUNT(Date_Fmt)

--

TABLE FILE TIME
SUM
CNT.DATE
BY YEAR
END

SELECT T1."Year", COUNT(*) FROM EIDW.Time T1 GROUP BY T1."Year" ORDER BY T1."Year";

--

TABLE FILE TIME
SUM
CNT.FISCAL_QUARTER
BY YEAR
END

SELECT T1."Year", COUNT(T1."Fiscal_Quarter") FROM EIDW.Time T1 GROUP BY T1."Year" ORDER BY T1."Year";

--

TABLE FILE TIME
SUM
CNT.FISCAL_YEAR
BY YEAR
END

SELECT T1."Year", COUNT(T1."Fiscal_Year") FROM EIDW.Time T1 GROUP BY T1."Year" ORDER BY T1."Year";

--

TABLE FILE TIME
SUM
CNT.TIME_KEY
BY YEAR
END

SELECT T1."Year", COUNT(*) FROM EIDW.Time T1 GROUP BY T1."Year" ORDER BY T1."Year";

--

TABLE FILE TIME
SUM
CNT.DATE_FMT
BY YEAR
END

SELECT T1."Year", COUNT(T1."Date_Fmt") FROM EIDW.Time T1 GROUP BY T1."Year" ORDER BY T1."Year";

In some cases, I have found that adding a column to a database view that contains the value 1 and then summing that column helps with counting.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
October 16, 2008, 07:14 AM
Baillecl
DEFINE FILE xxxx
NB_TO_COUNT/I08 WITH FIELD_IN_SEGMENT = 1 ;
NB_OVER_50000/I08 = IF AMOUNT IS GE 500000 THEN 1 ELSE 0;
END
These are my way of Counting (Number of Occurrences)
And I Watch if SQL Generator can translate those "Virtual Fields" so that DB2 handle them.
Sooner or Later, the SQL translator will ...
(I'm not fond of Count because I really love DEFINE. DEFINE is very professional - I mean 80's professional)


Focus Mainframe 7.6.11
Dev Studio 7.6.11 and !!!
PC Focus, Focus for OS/2, FFW Six, MSO
October 16, 2008, 07:25 AM
hammo1j
count(*) is quicker. This is a post done on the move by phone



Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo
October 16, 2008, 11:58 PM
jwoelfel
quote:
Originally posted by Francis Mariani:
Quite intriguing.

I did a test on a MS SQL Server table (WF version 5.3.2) and it looks like it's not whether a column is numeric or alpha, but whether or not nulls are allowed in the column:

                Data           Allow  Generated
Column Name     Type   Length  Nulls  Count
--------------  -----  ------  -----  ---------
Date            char        8  No     COUNT(*)
Fiscal_Quarter  char        7  Yes    COUNT(Fiscal_Quarter)
Fiscal_Year     numeric     9  Yes    COUNT(Fiscal_Year)
Time_Key        numeric     5  No     COUNT(*)
Date_Fmt        datetime    8  Yes    COUNT(Date_Fmt)

--

TABLE FILE TIME
SUM
CNT.DATE
BY YEAR
END

SELECT T1."Year", COUNT(*) FROM EIDW.Time T1 GROUP BY T1."Year" ORDER BY T1."Year";

--

TABLE FILE TIME
SUM
CNT.FISCAL_QUARTER
BY YEAR
END

SELECT T1."Year", COUNT(T1."Fiscal_Quarter") FROM EIDW.Time T1 GROUP BY T1."Year" ORDER BY T1."Year";

--

TABLE FILE TIME
SUM
CNT.FISCAL_YEAR
BY YEAR
END

SELECT T1."Year", COUNT(T1."Fiscal_Year") FROM EIDW.Time T1 GROUP BY T1."Year" ORDER BY T1."Year";

--

TABLE FILE TIME
SUM
CNT.TIME_KEY
BY YEAR
END

SELECT T1."Year", COUNT(*) FROM EIDW.Time T1 GROUP BY T1."Year" ORDER BY T1."Year";

--

TABLE FILE TIME
SUM
CNT.DATE_FMT
BY YEAR
END

SELECT T1."Year", COUNT(T1."Date_Fmt") FROM EIDW.Time T1 GROUP BY T1."Year" ORDER BY T1."Year";

In some cases, I have found that adding a column to a database view that contains the value 1 and then summing that column helps with counting.


I think you helped me to stumble upon the answer. The key is the MISSING=ON value in the MFD. When MISSING=ON, the SQL will get translated into COUNT(fieldname). Without it, the SQL generated will be COUNT(*).

Thanks for your help.


Web Server: iPlanet 6.0
App Server: Websphere 6.0
WFClient: 7.6.9
WFServer: 7.6.9 (Windows)
MRE & SSA
October 17, 2008, 09:23 AM
Francis Mariani
The MISSING keyword in a WF Master is directly related to the Nulls indicator in a DBMS table.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
October 17, 2008, 10:44 AM
hammo1j
Good work Francis

COUNT(*) is a good choice when not missing since it will be garnered from database statistics rather than a tablescan. So in this case the wf adapter is correct to translate it as such.

This advantage is only available when counting the whole table.



Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo