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,

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)

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.

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

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.

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.

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)

count(*) is quicker. This is a post done on the move by phone

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.

The MISSING keyword in a WF Master is directly related to the Nulls indicator in a DBMS table.

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.