use month & sum in grouping query

[Ian Elliott]

Thanks for any help.
I don't know much about queries, so I don't know if this will work, but I
have two queries in a database, and I would like to consolidate to one, for
simplicity's sake. The two queries before had the first run query doing a
'Month([myfield])' function, then the second run query did a grouping of the
table made by the first query, and there's also a sum in the second query. I
made a new single query that has the grouping, the sum, and the 'Month'
function, but I get a:
[IBM][CLI Driver][DB2] SQL0104N An unexpected token "(" was found following
"". Expected tokens may include: "FOR WITH FETCH ORDER UNION EXCEPT QUERYNO
OPTIMIZE ". SQLSTATE=42601 (#-104).

If I delete the month function field, the query runs fine. Is it possible to
do the month function in this kind of query? I will include the SQL code for
those interested:

SELECT LVFPROD_FIXED_VALUES.VALUATION_DATE, LVFPROD_FIXED_VALUES.PLAN_CODE,
Month([POLICY_EFF_DATE]) AS Mn, LVFPROD_FIXED_VALUES.FUND_NUMBER,
LVFPROD_FIXED_VALUES.BUCKET_START_DATE, LVFPROD_FIXED_VALUES.BUCKET_END_DATE,
Sum(LVFPROD_FIXED_VALUES.BUCKET_VALUE) AS SumOfBUCKET_VALUE,
LVFPROD_FIXED_VALUES.BUCKET_CURR_RATE
FROM LVFPROD_POLICY_DATA INNER JOIN LVFPROD_FIXED_VALUES ON
(LVFPROD_POLICY_DATA.MASTER_ID = LVFPROD_FIXED_VALUES.MASTER_ID) AND
(LVFPROD_POLICY_DATA.PLAN_CODE = LVFPROD_FIXED_VALUES.PLAN_CODE)
GROUP BY LVFPROD_FIXED_VALUES.VALUATION_DATE,
LVFPROD_FIXED_VALUES.PLAN_CODE, Month([POLICY_EFF_DATE]),
LVFPROD_FIXED_VALUES.FUND_NUMBER, LVFPROD_FIXED_VALUES.BUCKET_START_DATE,
LVFPROD_FIXED_VALUES.BUCKET_END_DATE, LVFPROD_FIXED_VALUES.BUCKET_CURR_RATE,
LVFPROD_POLICY_DATA.POLICY_STATUS
HAVING (((LVFPROD_FIXED_VALUES.VALUATION_DATE)=#8/31/2008#) AND
((LVFPROD_FIXED_VALUES.PLAN_CODE)="A041N" Or
(LVFPROD_FIXED_VALUES.PLAN_CODE)="A042N" Or
(LVFPROD_FIXED_VALUES.PLAN_CODE)="A051N" Or
(LVFPROD_FIXED_VALUES.PLAN_CODE)="A052N") AND
((LVFPROD_FIXED_VALUES.FUND_NUMBER)="001") AND
((LVFPROD_POLICY_DATA.POLICY_STATUS)="A" Or
(LVFPROD_POLICY_DATA.POLICY_STATUS)="E"));

 

[John Spencer]

The problem could be that Policy_Eff_Date contains something other than a date
or a null value. IF Policy_Eff_Date is a text field then you could get this
kind of error.

Try the following expression and see if the query works.

Month(IIF(IsDate(Policy_Eff_Date),Policy_Eff_Date,Null)) as MN

You will also need that in the Group By.

And by the way you can make the query more efficient by moving the Having
clause into a WHERE clause

SELECT LVFPROD_FIXED_VALUES.VALUATION_DATE
, LVFPROD_FIXED_VALUES.PLAN_CODE,
Month(IIF(IsDate(Policy_Eff_Date),Policy_Eff_Date,Null)) AS Mn
, LVFPROD_FIXED_VALUES.FUND_NUMBER
, LVFPROD_FIXED_VALUES.BUCKET_START_DATE
, LVFPROD_FIXED_VALUES.BUCKET_END_DATE
, Sum(LVFPROD_FIXED_VALUES.BUCKET_VALUE) AS SumOfBUCKET_VALUE
, LVFPROD_FIXED_VALUES.BUCKET_CURR_RATE

FROM LVFPROD_POLICY_DATA INNER JOIN LVFPROD_FIXED_VALUES ON
(LVFPROD_POLICY_DATA.MASTER_ID = LVFPROD_FIXED_VALUES.MASTER_ID) AND
(LVFPROD_POLICY_DATA.PLAN_CODE = LVFPROD_FIXED_VALUES.PLAN_CODE)

WHERE(((LVFPROD_FIXED_VALUES.VALUATION_DATE)=#8/31/2008#) AND
((LVFPROD_FIXED_VALUES.PLAN_CODE)="A041N" Or
(LVFPROD_FIXED_VALUES.PLAN_CODE)="A042N" Or
(LVFPROD_FIXED_VALUES.PLAN_CODE)="A051N" Or
(LVFPROD_FIXED_VALUES.PLAN_CODE)="A052N") AND
((LVFPROD_FIXED_VALUES.FUND_NUMBER)="001") AND
((LVFPROD_POLICY_DATA.POLICY_STATUS)="A" Or
(LVFPROD_POLICY_DATA.POLICY_STATUS)="E"))

GROUP BY LVFPROD_FIXED_VALUES.VALUATION_DATE,
LVFPROD_FIXED_VALUES.PLAN_CODE
, Month(IIF(IsDate(Policy_Eff_Date),Policy_Eff_Date,Null))
, LVFPROD_FIXED_VALUES.FUND_NUMBER, LVFPROD_FIXED_VALUES.BUCKET_START_DATE,
LVFPROD_FIXED_VALUES.BUCKET_END_DATE, LVFPROD_FIXED_VALUES.BUCKET_CURR_RATE,
LVFPROD_POLICY_DATA.POLICY_STATUS


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Thanks for any help.
I don't know much about queries, so I don't know if this will work, but I
have two queries in a database, and I would like to consolidate to one, for
simplicity's sake. The two queries before had the first run query doing a
'Month([myfield])' function, then the second run query did a grouping of the
table made by the first query, and there's also a sum in the second query. I
made a new single query that has the grouping, the sum, and the 'Month'
function, but I get a:
[IBM][CLI Driver][DB2] SQL0104N An unexpected token "(" was found following
"". Expected tokens may include: "FOR WITH FETCH ORDER UNION EXCEPT QUERYNO
OPTIMIZE ". SQLSTATE=42601 (#-104).

If I delete the month function field, the query runs fine. Is it possible to
do the month function in this kind of query? I will include the SQL code for
those interested:

SELECT LVFPROD_FIXED_VALUES.VALUATION_DATE, LVFPROD_FIXED_VALUES.PLAN_CODE,
Month([POLICY_EFF_DATE]) AS Mn, LVFPROD_FIXED_VALUES.FUND_NUMBER,
LVFPROD_FIXED_VALUES.BUCKET_START_DATE, LVFPROD_FIXED_VALUES.BUCKET_END_DATE,
Sum(LVFPROD_FIXED_VALUES.BUCKET_VALUE) AS SumOfBUCKET_VALUE,
LVFPROD_FIXED_VALUES.BUCKET_CURR_RATE
FROM LVFPROD_POLICY_DATA INNER JOIN LVFPROD_FIXED_VALUES ON
(LVFPROD_POLICY_DATA.MASTER_ID = LVFPROD_FIXED_VALUES.MASTER_ID) AND
(LVFPROD_POLICY_DATA.PLAN_CODE = LVFPROD_FIXED_VALUES.PLAN_CODE)
GROUP BY LVFPROD_FIXED_VALUES.VALUATION_DATE,
LVFPROD_FIXED_VALUES.PLAN_CODE, Month([POLICY_EFF_DATE]),
LVFPROD_FIXED_VALUES.FUND_NUMBER, LVFPROD_FIXED_VALUES.BUCKET_START_DATE,
LVFPROD_FIXED_VALUES.BUCKET_END_DATE, LVFPROD_FIXED_VALUES.BUCKET_CURR_RATE,
LVFPROD_POLICY_DATA.POLICY_STATUS
HAVING (((LVFPROD_FIXED_VALUES.VALUATION_DATE)=#8/31/2008#) AND
((LVFPROD_FIXED_VALUES.PLAN_CODE)="A041N" Or
(LVFPROD_FIXED_VALUES.PLAN_CODE)="A042N" Or
(LVFPROD_FIXED_VALUES.PLAN_CODE)="A051N" Or
(LVFPROD_FIXED_VALUES.PLAN_CODE)="A052N") AND
((LVFPROD_FIXED_VALUES.FUND_NUMBER)="001") AND
((LVFPROD_POLICY_DATA.POLICY_STATUS)="A" Or
(LVFPROD_POLICY_DATA.POLICY_STATUS)="E"));