Let me explain better … I need to create a temporary table based in a query, after this query, I need to insert some registers in the same temporary table, finally, I need to consult the temporary table … got it?
like this:
create temporary table entestacao_temp as
select
sum(a.qt_incidencia_passageiro) as entradas,
a.dt_diario,
a.cd_zon,
b.nrseqestacao
from gop_seo.gop_seo_opincpas a
inner join gop_seo.gop_seo_mnzonlin b on
b.cdzon = a.cd_zon and b.nrlin = a.nr_lin
where
exists(select null from gop_seo.opfechamento f where f.nr_mes = extract(month from a.dt_diario) and f.nr_ano = extract(year from a.dt_diario)) and
a.dt_diario between '2011-01-01' and '2011-12-31' and
a.nr_lin = 1 and
a.nr_tab not in ('BUSV', 'G05')
group by a.dt_diario, a.cd_zon, b.nrseqestacao;
insert into entestacao_temp
select
sum(c.qt_integracao),
a.dt_diario,
a.cd_zon,
a.nrseqestacao
from entestacao_temp a
inner join gop_seo.gop_seo_opzlint b on
b.cd_zon = a.cd_zon
inner join gop_seo.gop_seo_opintgra c on
c.nr_zolin = b.nr_zolin and
c.dt_diario = a.dt_diario
group by a.cd_zon, a.dt_diario, a.nrseqestacao
order by a.dt_diario;
select sum(a.entradas), a.dt_diario, a.cd_zon, a.nrseqestacao from entestacao_temp a group by a.dt_diario, a.cd_zon, a.nrseqestacao order by a.dt_diario, a.nrseqestacao;
I did the querys separeted and it works … like this:
$sql = 'insert into entradas_temp
select sum(c.qt_integracao), a.dt_diario, a.cd_zon, \'integracao\'
from entradas_temp a
inner join gop_seo.gop_seo_opzlint b on
b.cd_zon = a.cd_zon
inner join gop_seo.gop_seo_opintgra c on
c.nr_zolin = b.nr_zolin and
c.dt_diario = a.dt_diario
group by a.dt_diario, a.cd_zon
order by a.dt_diario;';
Yii::app()->db->createCommand($sql)->queryAll();
$sql = 'select sum(a.entradas) as entradas, extract(month from a.dt_diario) as mes from entradas_temp a group by extract(month from a.dt_diario) order by mes;';
$result = Yii::app()->db->createCommand($sql)->queryAll();