Message
From
17/03/2017 07:30:00
 
 
To
Todo
General information
Fórum:
Visual FoxPro
Category:
Programação, sintaxe e comandos
Título:
Query case
Environment versions
Visual FoxPro:
VFP 9
OS:
Windows 7
Network:
Windows XP
Database:
MS SQL Server
Application:
Desktop
Miscellaneous
ID da thread:
01649116
ID da mensagem:
01649116
Views:
77
Hello Experts

I have this query
select max(DATE)as date, count(data.vou_no) as tot,
(case when data.type=1  then sum(data.vou_no) end) as ac_cr,
(case when data.type=2  then sum(data.vou_no) end) as ac_dr
	from (
	select count(vou_no)as vou_no,date, 1 as type  from vouchers where VOU_type='CR' and cr_amount>0  group by date
	union all
	select count(vou_no)as vou_no,date, 2 as type from vouchers where VOU_type='CP' and dr_amount>0 group by date
	) data
	group by date
but this query does not work
it says:
Msg 8120, Level 16, State 1, Line 3
Column 'data.type' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

as shown in image

How to overcome this?

Please help
Next
Responder
Mapa
View