text TO cmd TEXTMERGE noshow

select vou_no,MAX(date)as date,MAX(acc_code) as acc_code,
SUM(case type1 when 'P' then cr_amount else 0 END) as P_amount,
SUM(case type1 when 'A' then cr_amount else 0 END) as A_amount,
SUM(case type1 when 'D' then dr_amount else 0 END) as D_amount
 from crsalp where date=?thisform.text4.value
 group by vou_no
 
 ENDTEXT

MartinaJ

>Dear Experts
>
>I have these codes
>
>

>text TO cmd TEXTMERGE noshow
>
>select distinct(vou_no)as vou_no,MAX(date)as date,MAX(acc_code)as acc_code,
>(case type1 when 'P' then SUM(cr_amount) end )as A_amount,
>(case type1 when 'A' then SUM(cr_amount) end )as A_amount,
>(case type1 when 'D' then SUM(dr_amount) end )as D_amount
> from crsalp where date=?thisform.text4.value
> group by vou_no
> 
> ENDTEXT
>

>
>type1 is a field (char 1)
>
>This query show following error message
>
>Column 'crsalp.type1' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
>
>I want to get SUM(cr_amount) in case type1=P,A,D
>
>Please help