[ricardo_rm]

Hey guys!!


Does anyone know how to run a multiple query in a statement?!?

I get the following error:

CDbCommand falhou ao executar o comando SQL: SQLSTATE[42601]: Syntax error: 7 ERROR: cannot insert multiple commands into a prepared statement

thanks,

Ricardo

[suriyansuresh]

Let me show your code to fix issues faster

 ricardo_rm, on 22 January 2013 - 05:55 AM, said:

Hey guys!!


Does anyone know how to run a multiple query in a statement?!?

I get the following error:

CDbCommand falhou ao executar o comando SQL: SQLSTATE[42601]: Syntax error: 7 ERROR: cannot insert multiple commands into a prepared statement

thanks,

Ricardo

[lagogz]

What multiple query and where?

[ricardo_rm]

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;

[lagogz]

You could start by reviewing this code:

Temporary Tables

Regards.

[Keith]

You could also bundle that into one query. Look into using UNION ALL to combine the first and second result sets.

[ricardo_rm]

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();