Yii Framework Forum: [Solved] Multiple Query In A Statement?!? - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

[Solved] Multiple Query In A Statement?!? Rate Topic: ***** 1 Votes

#1 User is offline   ricardo_rm 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 47
  • Joined: 16-January 12

Posted 22 January 2013 - 05:55 AM

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
0

#2 User is offline   suriyansuresh 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 35
  • Joined: 23-July 10
  • Location:Coimbatore, India

Posted 22 January 2013 - 07:16 AM

Let me show your code to fix issues faster

View Postricardo_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

Suriyan

Yii application development | Web design
0

#3 User is offline   lagogz 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 478
  • Joined: 30-November 12
  • Location:Galiza

Posted 22 January 2013 - 10:00 AM

What multiple query and where?
-----------------------------------------------------------------------------------------------
------------------------------- VISITEN FOCUCHASART --------------------------------
-----------------------------------------------------------------------------------------------
0

#4 User is offline   ricardo_rm 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 47
  • Joined: 16-January 12

Posted 22 January 2013 - 10:08 AM

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;

0

#5 User is offline   lagogz 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 478
  • Joined: 30-November 12
  • Location:Galiza

Posted 22 January 2013 - 10:25 AM

You could start by reviewing this code:

Temporary Tables

Regards.
-----------------------------------------------------------------------------------------------
------------------------------- VISITEN FOCUCHASART --------------------------------
-----------------------------------------------------------------------------------------------
0

#6 User is offline   Keith 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,600
  • Joined: 04-March 10
  • Location:UK

Posted 22 January 2013 - 10:56 AM

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

#7 User is offline   ricardo_rm 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 47
  • Joined: 16-January 12

Posted 22 January 2013 - 11:35 AM

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

0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users