iBase.ru Forum

Код: Выделить всё

SELECT A.A_DATE, SUM(A.A_SUM), G.G_DATE, SUM(G.GAIN) FROM OUT_ALLOWS A, OUT_GAINS G  GROUP BY A.A_DATE, G.G_DATE

Код: Выделить всё

SELECT DT, A_SUM1, GAIN1	
FROM ( SELECT A_DATE AS DT, SUM(A_SUM) AS A_SUM1, 	
  FROM OUT_ALLOWS	
  GROUP BY A_DATE	
) A	
FULL JOIN ( SELECT G_DATE AS DT, SUM(GAIN) AS GAIN1	
  FROM OUT_GAINS	
  GROUP BY G_DATE	
) G	
ON A.DT = G.DT

Код: Выделить всё

SELECT A.A_DATE, SUM(A.A_SUM), G.G_DATE, SUM(G.GAIN) FROM OUT_ALLOWS A FULL JOIN OUT_GAINS G ON A.A_DATE=G.G_DATE GROUP BY A.A_DATE, G.G_DATE

Код: Выделить всё

for select id, name from sometable
  into :id, :name do
  begin
    -- некоторые действия
    suspend;
  end  

Код: Выделить всё

CREATE VIEW OUT_ALLOWS_SUM(
    DT,
    ALLOWS_SUM)
AS
select A_DATE AS DT, SUM(A_SUM) AS ALLOWS_SUM from OUT_ALLOWS
GROUP BY A_DATE

Код: Выделить всё

CREATE VIEW OUT_GAINS_SUM(
    DT,
    GAINS_SUM)
AS
select G_DATE AS DT, SUM(GAIN) AS GAINS_SUM from OUT_GAINS
GROUP BY G_DATE

Код: Выделить всё

ALTER PROCEDURE "OUTPUT" 
RETURNS (
    O_DATE DATE,
    GAINS DOUBLE PRECISION,
    ALLOWS DOUBLE PRECISION,
    O_SUM DOUBLE PRECISION)
AS
DECLARE VARIABLE ADT DATE;
DECLARE VARIABLE ASUM DOUBLE PRECISION;
DECLARE VARIABLE GDT DATE;
DECLARE VARIABLE GSUM DOUBLE PRECISION;
begin
o_sum = 0;
  for SELECT A.DT, ALLOWS_SUM, G.DT, GAINS_SUM
  FROM OUT_ALLOWS_SUM A
  FULL JOIN OUT_GAINS_SUM G
  ON A.DT = G.DT
  into :adt, :asum, :gdt, :gsum
  do
  begin

  if(gsum is null) then
  begin
    gains = 0.0;
    o_date = adt;
    allows = asum;
    o_sum = allows;
  end
  else if(asum is null) then
  begin
    gains = gsum;
    o_date = gdt;
    allows = 0.0;
    o_sum = gsum;
  end
  else
  begin
   o_date = adt;
   gains = gsum;
   allows = asum;
   o_sum = gains+allows;
  end

  suspend;
  end

end

Код: Выделить всё

{скипаем заголовок}
AS
declare variable adt date;
begin
  for select a.dt, allows_sum, g.dt, gains_sum
    from out_allows_sum a full join out_gains_sum g on (a.dt = g.dt)
    into o_date, allows, adt, gains
  do begin
    if (o_date is null) then o_date=adt;
    if (allows is null) then allows=0;
    if (gains is null) then gains=0;
    o_sum=allows+gains;
    suspend;
  end
end;

Код: Выделить всё

CREATE VIEW OUT_ALLOWS_SUM( 
    DT, 
    ALLOWS_SUM) 
AS 
select A_DATE AS DT, SUM(A_SUM) AS ALLOWS_SUM from OUT_ALLOWS 
GROUP BY A_DATE

Код: Выделить всё

CREATE VIEW OUT_ALLOWS_SUM( 
    DT,
    ALLOWS_SUM)
AS 
select A_DATE AS DT, COALESCE(SUM(A_SUM), 0) AS ALLOWS_SUM from OUT_ALLOWS 
GROUP BY A_DATE

Код: Выделить всё

    if (allows is null) then allows=0; 
    if (gains is null) then gains=0;