problems with a where and the and

0

I have this procedure and as it works it works well, but I want to modify it because in the total sum it makes me the sum of all the records and not the ones in the range of a specific date, so I wrote this:

and *****a.fecha >= :fechainicio or a.fecha <=  :fechafin ),*****


begin
if (quincena = '1') then
   dia='1';
   if (quincena = '2') then
   dia='16';
ftdfecha =(select cast (:anno||'-'||:mes||'-'||:dia as date) from rdb$database);

  fechainicio = ftdfecha;
  fechafin = dateadd(15 day to fechainicio);
for select  a.codigoproveedor,
(SELECT b.nombreproveedor FROM proveedores b
WHERE a.codigoproveedor = b.codigoproveedor and *****where a.fecha >= :fechainicio or a.fecha <=  :fechafin),*****
sum (a.montototal)
FROM produccion a
where (a.fecha >= :fechainicio or a.fecha <=  :fechafin)
group by a.codigoproveedor
into :codigoproved,:nombreproved,:producciones

do begin

suspend;
end
end

and this error gives me the Firebird:

Can't format message 13:896 -- message file C:\WINDOWS\firebird.msg not found.
Dynamic SQL Error.
SQL error code = -104.
Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause).

Can you tell me how to fix it or write me the code correctly? a thousand thanks

    
asked by Ciudad Dragon 19.05.2016 в 14:48
source

2 answers

0

Be sure to place the fields at group by . It seems you should put b.nombreproveedor .

Speaking specifically about the SQL query, I see that it may be possible to use a JOIN instead of a subquery.

But I do not know if Firebird allows that instruction.

    
answered by 19.05.2016 в 17:29
0

In SQL Server I have had similar problems and from what I have seen you could opt for 2 possible solutions:

  • Add the field b.nombreproveedor in the% share group by
  • In the select make a subquery searching from the field a.codigoproveedor that you return the b.nombreproveedor .
  • answered by 19.05.2016 в 15:26