Use of MONTH() function

[paulvonhippel]

I'd like to count the number of transactions for each debit card and
month over a 3-month period.

But this query --

select DBT_CRD_DIM_NB,
month (tran_dt) as tran_mnth,
count(*) as txns_per_crd_mnth
from cdwdba.VW_DCM_EB_DLY_ALL_TRN_MSR
where tran_dt between '2007-08-01' and '2007-10-31'
group by DBT_CRD_DIM_NB, tran_mnth

-- returns this error:

[IBM][CLI Driver][DB2/AIX64] SQL0206N "TRAN_MNTH" is not valid in the
context where it is used

I haven't used the MONTH() function, before and clearly I must be
using it wrong. Many thanks for any advice.

Paul

[Tomas]

Paul,

try grouping by month(tran_dt) instead of the correlation name.

/T
On Dec 5, 11:41 am, [e-mail] wrote:

I'd like to count the number of transactions for each debit card and
month over a 3-month period.

But this query --

select DBT_CRD_DIM_NB,
month (tran_dt) as tran_mnth,
count(*) as txns_per_crd_mnth
from cdwdba.VW_DCM_EB_DLY_ALL_TRN_MSR
where tran_dt between '2007-08-01' and '2007-10-31'
group by DBT_CRD_DIM_NB, tran_mnth

-- returns this error:

[IBM][CLI Driver][DB2/AIX64] SQL0206N "TRAN_MNTH" is not valid in the
context where it is used

I haven't used the MONTH() function, before and clearly I must be
using it wrong. Many thanks for any advice.

Paul

[CRPence]

Repeat the expression in the GROUP BY clause. It is not the MONTH()
function [expression], but with any expression. For the given
expression, the scalar function month(tran_dt), the corrected GROUP BY:
group by DBT_CRD_DIM_NB, month (tran_dt)

Regards, Chuck
--
All comments provided "as is" with no warranties of any kind
whatsoever and may not represent positions, strategies, nor views of my
employer

pa***********@yahoo.com wrote:

I'd like to count the number of transactions for each debit card and
month over a 3-month period.

But this query --

select DBT_CRD_DIM_NB,
month (tran_dt) as tran_mnth,
count(*) as txns_per_crd_mnth
from cdwdba.VW_DCM_EB_DLY_ALL_TRN_MSR
where tran_dt between '2007-08-01' and '2007-10-31'
group by DBT_CRD_DIM_NB, tran_mnth

-- returns this error:

[IBM][CLI Driver][DB2/AIX64] SQL0206N "TRAN_MNTH" is not valid in the
context where it is used

I haven't used the MONTH() function, before and clearly I must be
using it wrong. Many thanks for any advice.

[paulvonhippel]

That did it! Thank you.