Use of MONTH() function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

paulvonhippel@yahoo.com - 05 Dec 2007 19:41 GMT

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

Reply to this Message

Tomas - 05 Dec 2007 20:06 GMT

Paul,

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

/T

On Dec 5, 11:41 am, paulvonhip...@yahoo.com wrote:

> I'd like to count the number of transactions for each debit card and
> month over a 3-month period.
[quoted text clipped - 17 lines]
>
> Paul

Reply to this Message

CRPence - 05 Dec 2007 20:09 GMT

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

Signature

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

> I'd like to count the number of transactions for each debit card and
> month over a 3-month period.
[quoted text clipped - 15 lines]
> I haven't used the MONTH() function, before and clearly I must be
> using it wrong. Many thanks for any advice.

Reply to this Message

paulvonhippel@yahoo.com - 05 Dec 2007 21:09 GMT

That did it! Thank you.

Reply to this Message