By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,559 Members | 1,073 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,559 IT Pros & Developers. It's quick & easy.

Use of MONTH() function

P: n/a
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
Dec 5 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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.

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
Dec 5 '07 #2

P: n/a
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.
Dec 5 '07 #3

P: n/a
That did it! Thank you.
Dec 5 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.