469,344 Members | 6,672 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,344 developers. It's quick & easy.

Use of MONTH() function

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
3 9589
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
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
That did it! Thank you.
Dec 5 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by James Fortune | last post: by
18 posts views Thread by PC Datasheet | last post: by
4 posts views Thread by laredotornado | last post: by
10 posts views Thread by Jim | last post: by
3 posts views Thread by ats | last post: by
6 posts views Thread by Nkhosinathie | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.